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.

 

Posted in Software Development | Tagged , , , | 4 Comments

Nu bär det av!

På tågetResan mot Hasseludden Yasuragi och 24 Hour Business Camp har börjat. Ett snabbt överslag visar att vi har sju 3G-uppkopplingar på tre personer… vi borde inte sakna Internet iallafall. Med tre och en halv timma på tåget så hinner förhoppningsvis få lite gjort här också.

Vi har förberett mycket av det tekniska och ligger ganska bra till. För närvarande har vi lite strul (för gammal kärna) med en av våra VPS:er från GleSYS men supporten jobbar hårt (trots att det är söndag!) med att lösa det

Posted in Projekt | Tagged , , , | 1 Comment

Presentation:’Frog leap technology’on Chalmers

Edit 2012-11-03: Links updated.

Today I have had the honor to give a presentation on Chalmers University of  Technology on behalf of EWB Gothenburg. During 45 minutes I had the opportunity to give my view of technology as tool to build a better world. My presentation was titled “Frog leap technology: How access to knowledge levels the plane field for developing countries”. My main argument is that development is a non-linear process and that high tech is of great benefit for developing countries as a mean of development.

The full presentation can be found here: Frog leap technology

For convenience, this is the websites referred to in the presentation.

Posted in Philanthropy | Leave a comment

Clouds on the move…

Edit 2012-11-03: Removed dead links.

It appears that cloud services are increasing. Now, Sun is also in the battle for the cloud-users. Sun do have a very big advantage – the name : “Behind every Cloud, You’ll see the Sun” 🙂

So far it seems to be no public sign-up but there is an information-registration form for those who are curious. Remains to be seen whether I will get an evaluation account soon. One thing that is interesting is Sun’s target groups:

Use the Cloud– best option for startups, research projects, or Web 2.0 developers who want a simple, low-cost way to “load and go.”

Leverage the Cloud– alternative for existing enterprises to offload overburdened IT infrastructures.

Build the Cloud– many large enterprises are experimenting with developing private clouds and standards.

Be the Cloud– service providers offer new value-added capabilities

Options one and two are pretty straightforward. But option three is interesting for large companies may (or equiv.) who do not trust the public clouds, but still want to take advantage of a cloud infrastructure. Finally, there also seems as if Sun wants to take a piece of service provider cake by offering infrastructure for application development (Web2.0 applications, Facebook apps, etc.).

What makes the initiative even more exciting is that Sun is already working with Amazon in the sense that they offer Open Solaris-ami: s using EC2. They will in some sense to compete with themselves. Exiting times ahead.

Posted in Cloud computing | Tagged , , , | Leave a comment

Swedish providers of premium SMS services.

Edit 2012-11-03: Updated links.

Edit 2011-12-13: Telenor Mobilstart changed name to Multimobil. Name and link updated.

Today I had, for another project I’m working with, the need for a premium SMS service. I did a short investigation and found that there were a bunch of providers on the Swedish market (some of the providers listed below are Swedish only). There is also a bunch of international providers offering services on the Swedish market.

The price level is common for most of the providers. It’s an ‘expensive’ market in the sense that the operators share of the total price is quite large. In general the providers take about a third of the revenue. It might be fair but there is a trick – it’s the mandatory 50-öres (~5 cent)  confirmation SMS being sent.

For a premium-SMS on 1 SEK (1 SEK = 100 öre)  the provider will take a about 33 öre in revenue share (including tax). Then the confirmation SMS is 50 öre (62,5 öre inc tax). The remaining revenue will be 100-33-62,5 = 4,5 öre. The conclusion is that for small payments the model is not working and it basically invalidates the use of premium-SMS as micro payment system.

The investigation lead to one good thing though – a list of companies providing these services:

Note that the above list is by no means complete. It is also completely un-ordered and is only meant as a starting point to compare different providers.

grandpere de nouveau
Posted in Mobile | Tagged , , , | 2 Comments