azl azl - 7 months ago 26
SQL Question

JPA Query with openJPA only works as 'native' SQL, but it does not when transformed as JPQL or with JPA Criteria

I have a SQL query and the mapping of the classes. Using openJPA, if the query is made as a @NamedNativeQuery it works fine, returning the correspondent class and the expected result. If it is transformed into a JPQL @NamedQuery it always returns a NoResultException for the getSingleResult method. I tried also the JPA Criteria but it is just not working, at least not in all environments. I cannot reproduce the problem locally, it only occurs in the test system. Locally all of the approaches work ok.

I think that it is a configuration problem, but I haven't found anything.

SQL:
SELECT * FROM MY_REL_TABLE map WHERE map.TYPE = ?1 AND map.REGION_ID = ?2

JPQL:
SELECT m FROM MyRelationClass m WHERE m.type= :type AND m.region = :region

Does anyone have an idea what could be wrong?

IBM openJPA, Websphere 8.0

Answer

I cannot reproduce the problem locally, it only occurs in the test system.

Are you connecting to the same database in both environments? If not, is it possible that your local database doesn't have the correct data in it?

You could enable OpenJPA SQL trace to see what the JPQL is being translated to:

openjpa.Log=SQL=trace

Also, set the following property to see parameter values :

openjpa.ConnectionFactoryProperties=PrintParameters=true