If you need predictable access to triggers and other stored procedures, you
can't use JPA and be portable. That stated, there are a few tricks which may
help...
[Triggers]
Let's divide triggers into three categories:
1. Triggers that only modify internal data (i.e. data is not exposed through
any JPA entities). These triggers are safe but, because JPA flushes the SQL
calls at will, you cannot rely on them being called at a specific time. For
situations, such as recording who modified a record and when, this would seem
to be a perfectly safe technique...but there is a real danger that a developer
in future will extent the entity to cover the previously internal fields.
2. Triggers that modify entity data within a table. Here, the JPA cache will
be out of sync with the database. This can be acceptable if you make the JPA
calls, then call flush() to trigger the SQL statements, and then call merge()
on any modified entities to refresh the cache. It is safe to say that this is
NOT RECOMMENDED and can lead to problems if merge() is not called.
3. Triggers that modify entity data across multiple tables. DANGER. Because
the sequencing of the SQL is not under developer control, you cannot rely on
this safely working. Don't do it.
In other words, I do not believe that database triggers should be used with
JPA. Thankfully, JPA 2.0 attempts to define a replacement for triggers. These
are called the lifecycle callbacks (see section 3.5 of JPA 2.0). These appear
to be useful for simple cases, but I've found them inappropriate for more
complex operations. The specification places a significant limitation on what
can be done in a lifecycle callback:
> In general, the lifecycle method of a portable application should not invoke
> EntityManager or Query operations, access other entity instances, or modify
> relationships within the same persistence context. A lifecycle callback
> method may modify the non-relationship state of the entity on which it is
> invoked.
More on this later.
[Stored Procedures / SQL functions / DB-specific functions]
Native Queries are your answer. An unfortunate reality of JPA 2.0 is that any
query that needs access to a function not contained in JPQL MUST be turned into
a native query. This is a significant weakness of JPA in my not so humble
opinion. I would love to see a mechanism in JPA 3.0 which allows the author to
define aliases within JPQL for stored procedures, SQL functions, or DB-specific
functions. This would then reduce porting to updating those aliases rather
than updating all the individual native queries.
I recommend defining a <mapping-file> within your persistence.xml in which as
many of the native queries are defined as possible. This allows the code to
access the queries by name and simplifies the task of finding and migrating all
the native queries.
[My Wish and a Use Case]
I have an entity which I'll call MagazineArticle. The entity might look
something like this (getters / setters omitted for brevity):
@Entity @Table(name="magazine_articles")
public class MagazineArticle {
@SequenceGenerator(name="ArticleIdGenerator",
sequence="article_id_gen")
@Id @GeneratedValue(generator="ArticleIdGenerator",
strategy=Generator.Type.SEQUENCE)
private long id;
private String title;
private String author;
private String body;
// NOTE: Getters / Setters omitted for brevity
@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="article_figures",
joincolum...@joincolumn(name="article_ref"))
private Collection<Figure> figures;
}
Hidden the entity but contained within the 'magazine_articles" table is a
DB-field which is used for text search operations. The contents of this field
are generated from content within the article entity (e.g. title / author /
body) and from data in other tables (e.g. article_figures). Optimally, the
contents would be generated within the database whenever the entity is created
or updated without action from JPA. However, because the sequence of database
operations is hidden and MagazineArticle would probably be flushed before the
Figures, I do not believe that a trigger on MagazineArticle can be safely used.
One might then consider a @PostUpdate and @PostPersist callback, but the JPA
2.0 specification recommends against EntityManager or Query operations. So
this is out.
Instead, a combination of JPA and a NativeQuery appears to be necessary.
em.persist(article);
em.flush(); // to write out MagazineArticle and associated Figures.
Query query = em.createNativeQuery("SELECT generate_article_index(?1)");
query.setParameter(1, article.getId());
query.getSingleResult(); // generate the index
em.flush();
em.refresh(article); // not strictly necessary but recommended
Not quite as smooth as a trigger, but it works.
Search operations naturally benefit from having the index.
Query query = em.createNamedQuery("FindArticle);
query.setParameter(1, searchTerms);
List<Articles> results = query.ResultList();
Here the 'FindArticle' query is referenced from the persistence.xml file. But
this is a simple search operation. Why shouldn't we be able to use JPQL
instead? Why not call
Query query = em.createQuery("SELECT a,calculate_search_rank(a) AS
score FROM MagazineArticle a WHERE article_matches_terms(:terms) ORDER BY score
DESC");
query.setParameter("terms", searchTerms);
List<Articles> results = query.ResultList();
where the 'calculate_search_rank' and 'article_matches_terms' are aliases which
get converted into SQL at the last minute? I would expect these aliases to be
defined either within persistence.xml or within the <mapping-file>. With this
simple change, many situations currently requiring native queries can migrate
to JPQL queries.
On Jun 3, 2010, at 4:34 AM, Jean-Baptiste BRIAUD -- Novlog [via OpenJPA] wrote:
> Hi,
>
> Is there a way to use JPA abstraction from Java code to manipulate database
> instances, triggers, stored procedure ?
>
> Before going to the complex scenario, simple things first : How can I create
> a database instance in a Java way : "dev once, run everywhere" ?
>
> I had to write some SQL to create a MySQL database empty instance from Java
> code but that code is not portable now.
>
> Any thoughts ?
>
> Ps : more complex scenarios : need to manage triggers, how can I create,
> update and delete it in a portable manner ?
>
> View message @
> http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5134215.html
>
> To start a new topic under OpenJPA Users, email
> ml-node+208411-1595610943-93...@n2.nabble.com
> To unsubscribe from OpenJPA Users, click here.
>
--
View this message in context:
http://openjpa.208410.n2.nabble.com/JPA-for-portable-database-management-tp5134215p5136127.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
