openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From No1UNo <je...@jerrycarter.org>
Subject Re: JPA for portable database management (and wishes for JPA 3.0)
Date Thu, 03 Jun 2010 17:41:20 GMT

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", joinColumns=@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-93721@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.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message