Java Persistent Objects

As shown in JDOQL Reference JPOX supports queries using the JDOQL query language, using a Java-like syntax. When using JDOQL under RDBMS there are some specific situations where it can be useful to benefit from special treatment. These are listed here.

Control over Joins

JDOQL queries are converted into SQL and use a mix of INNER and LEFT OUTER joins. In some situations the chosen join may not be optimal and you may know that using only INNER joins would be more efficient. If you have this situation you can use JPOX extensions to enable this behaviour. org.jpox.rdbms.jdoql.joinType, has valid values of "INNER" and "LEFT OUTER" and JPOX will attempt to make joins of that type when compiling the query. This can be specified as either a JDOQL extension (apply to this query only), or as a PMF property (apply to all queries).

Control over locking of fetched objects

JPOX allows control over whether objects found by a fetch (JDOQL query) are locked during that transaction so that other transactions can't update them in the meantime. You can use the JPOX extension org.jpox.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 JDOQL extension (apply to this query only), or as a PMF property (apply to all queries).

You can also specify this for all queries for all PMs using a PMF property org.jpox.rdbms.useUpdateLock. In addition you can perform this on a per-transaction basis by doing

((org.jpox.jdo.JDOTransaction)pm.currentTransaction()).setOption("transaction.serializeReadObjects", "true");

Use of contains() and variables

When using the method contains on a collection (or containsKey, containsValue on a map) this will add an "EXISTS" subquery to the executed SQL. This can have an impact on where JPOX should apply other conditions for the variable in order to get correct syntax. Let's take an example. We have classes A and B, with A having a Set of Bs (using ForeignKey).

SELECT FROM org.jpox.samples.A
WHERE type == 'Technology' && elements.contains(b1) && b1.name == 'Jones'
VARIABLES org.jpox.samples.B b1

By default JPOX will create a statement like

SELECT `THIS`.`ID`
FROM `A` `THIS`
WHERE EXISTS (
    SELECT 1 FROM `B` `THIS_ELEMENTS_B1` 
    WHERE `THIS_ELEMENTS_B1`.`A_ID` = `THIS`.`ID` 
    AND `THIS`.`TYPE` = 'Technology'
    AND `THIS_ELEMENTS_B1`.`NAME` = 'Jones'
)

So, as you can see, the restriction on "A.type" is applied to the EXISTS subquery. This is not as efficient as it could be. We would like the "type == 'Technology'" to be applied to the main query, and the "b1.name == 'Jones'" to be applied to the subquery. To do this we need to change the query

SELECT FROM org.jpox.samples.A
WHERE type == 'Technology' && (elements.contains(b1) && b1.name == 'Jones')
VARIABLES org.jpox.samples.B b1

So we used parentheses (brackets) to reinforce that the variable constraints should be treated together (and hence in the same subquery), and we now use a JPOX extension org.jpox.rdbms.jdoql.existsIncludesConstraints", set to "false". This can be specified as either a JDOQL extension (apply to this query only), or as a PMF property (apply to all queries). This means that the "type == 'Technology'" is applied to the main query and not to the EXISTS subquery.

SELECT `THIS`.`ID`
FROM `A` `THIS`
WHERE `THIS`.`TYPE` = 'Technology'
AND (EXISTS (
    SELECT 1 FROM `B` `THIS_ELEMENTS_B1`
    WHERE `THIS_ELEMENTS_B1`.`A_ID` = `THIS`.`ID` 
    AND `THIS_ELEMENTS_B1`.`PROPERTY_NAME` = 'Jones'
)

A further extension is useful where, in some situation, when having contains(this.field) or contains(this) and this doesnt use an EXISTS statement. To force it to use EXISTS you should specify the query extension org.jpox.rdbms.query.containsUsesExistsAlways as "true". The query will then use EXISTS for that contains clause.