JPA input parameters in update queries

Working on a project for my current client I run into the following problem. I had to do some specification reading and create a test project in order to understand it so this is a summary of the findings if someone else runs in to this issue at some point.

The problem

During project import I got a warning when Eclipse (actually the JBoss Tools plugin) validated my JPA queries:

Input parameters can only be used in the WHERE clause or HAVING clause of a query.

The error came from a query like this:

@NamedQuery(name = "updateCompanyName", query = "UPDATE CompanyCache SET companyName = :companyName WHERE companyId = :companyId")

According to the error message I’m not allowed to use the input parameter :companyName.

The problem? All our unit- ant integration-tests pass, so it seems to work. The platform is developed targeted at JEE6 (hence, using JPA 2.0) but unfortunately we need to backport to JEE5 for policy-reasons (enterprise decision that the chosen application server is not mature enough in the version supporting JEE6). Backporting means running on a JPA 1.0-based platform so this might change the behavior.

What does the specification say?

Let’s take a look in the specification documents delivered by the JCP:

JPA 1.0 (JSR 220 – Final version)

http://download.oracle.com/otn-pub/jcp/ejb-3_0-fr-eval-oth-JSpec/ejb-3_0-fr-spec-persistence.pdf

In chapter 4.6.4 (page 89) we find the same statement as above:

"Input parameters can only be used in the WHERE clause or HAVING clause of a query."

Seems obvious enogh – according to spec. I can not use it in the update query.

JPA 2.0 (JSR 317 – Final version)

http://download.oracle.com/otn-pub/jcp/persistence-2.0-fr-eval-oth-JSpec/persistence-2_0-final-spec.pdf

In chapter 4.6.4 (page 146) we find the same statement as above:

"Input parameters can only be used in the WHERE clause or HAVING clause of a query."

Seems obvious enough again – according to spec. I can not use it in the update query.

JPA 2.1 (JSR 338 – Early draft)

http://download.oracle.com/otn-pub/jcp/persistence-2_1-edr-spec/persistence-2_1-edr-spec.pdf

In chapter4.6.4 (page 162) we find this text (my highlight):

"Input parameters can only be used in the WHERE clause or HAVING clause of a query or as the new value for an update item in the SET clause of an update statement."

Aha! This is a difference – according to this spec. we are allowed to use it as values in update queries.

A test project to validate this

In order to find out how it behaves in reality I created a test project where I could switch between JPA 2 and JPA 1. I used Hibernate-Entitymanger as the persistence provider so the findings are only valid for Hibernate – another provider might have a different behavior.

Using the lastest Hibernate-Entitymanger (4.1.5.SP1) I tested an update query on the form above.

It works even though it is not supposed to work according to specification.

Using Hibernate-Entitymanager (3.4.0.GA), which is the last version to support JPA 1 but not JPA 2, I run the sam code.

It works even though it is not supposed to work according to specification.

Conclusion

Hibernate does not follow the specification on this point but one might guess that the new version of the JPA-spec will allow this behavior as indicated by the draft JSR. JBoss Tools is probably validating the query against the JPQL-grammar which is based on the specification and is therefore showing a validation error.

End remark

After a discussion in out team we decided to keep the current implementation despite the breach of specification. Changing the behavior would mean string concatenation or string substitution to build the query and the current approach is much cleaner. As we see no indications of a shift in persistence provider or application server at this stage we believe the gains of keeping the code are larger than the risks at this point.

 

This entry was posted in Software Development and tagged , , , . Bookmark the permalink.

2 Responses to JPA input parameters in update queries

  1. Jonathan says:

    This would be very useful if tests more JPA provider implementations like Eclipselink..etc.

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*