RDBMS JPQL Queries

As shown in JPQL Reference DataNucleus supports queries using the JPQL query language, using a Java-like syntax. Here we provide details of the implementation of JPQL for RDBMS datastores.



Control over locking of fetched objects

DataNucleus allows control over whether objects found by a fetch (JPQL query) are locked during that transaction so that other transactions can't update them in the meantime. You can use the DataNucleus extension datanucleus.rdbms.query.useUpdateLock , set to "true", and this will append "FOR UPDATE" on the end of the SELECT. This can be specified as either a JPQL hint (apply to this query only), or as an EMF property (apply to all queries).

You can also specify this for all queries for all PMs/EMs using a persistence property datanucleus.rdbms.useUpdateLock .



SQL Generation

With a JPQL query running on an RDBMS the query is compiled into SQL. Here we give a few examples of what SQL is generated. You can of course try this for yourself observing the content of the DataNucleus log.

In JPQL you specify a candidate class and its alias (identifier). In addition you can specify joins with their respective alias. The DataNucleus implementation of JPQL will preserve these aliases in the generated SQL.

JPQL:
SELECT Object(P) FROM mydomain.Person P INNER JOIN P.bestFriend AS B

SQL:
SELECT P.ID
FROM PERSON P INNER JOIN PERSON B ON B.ID = P.BESTFRIEND_ID

With the JPQL MEMBER OF syntax this is typically converted into an EXISTS query.

JPQL:
SELECT DISTINCT Object(p) FROM mydomain.Person p WHERE :param MEMBER OF p.friends

SQL:
SELECT DISTINCT P.ID FROM PERSON P
WHERE EXISTS (
    SELECT 1 FROM PERSON_FRIENDS P_FRIENDS, PERSON P_FRIENDS_1 
    WHERE P_FRIENDS.PERSON_ID = P.ID
    AND P_FRIENDS_1.GLOBAL_ID = P_FRIENDS.FRIEND_ID 
    AND 101 = P_FRIENDS_1.ID)