JDO : JDOQL Queries

JDO defines ways of querying objects persisted into the datastore. It provides its own object-based query language (JDOQL). JDOQL is designed as the Java developers way of having the power of SQL queries, yet retaining the Java object relationship that exist in their application model. A typical JDOQL query may be created in several ways. Here's an example expressed in the 3 supported ways

Single-String JDOQL :
Query q = pm.newQuery(
    "SELECT FROM mydomain.Person WHERE lastName == \"Jones\" && " +
    "age < age_limit PARAMETERS int age_limit");
List<Person> results = (List<Person>)q.execute(20);

Declarative JDOQL :
Query q = pm.newQuery(Person.class);
q.setFilter("lastName == \"Jones\" && age < age_limit");
q.declareParameters("int age_limit");
List<Person> results = (List<Person>)q.execute(20);

Typesafe JDOQL (DataNucleus) :
TypesafeQuery<Person> tq = pm.newTypesafeQuery(Person.class);
QPerson cand = QPerson.candidate();
List<Person> results = 
    tq.filter(cand.lastName.eq("Jones").and(cand.age.lt(tq.intParameter("age_limit"))))
        .setParameter("age_limit", "20").executeList();

So here in our example we select all "Person" objects with surname of "Jones" and where the persons age is below 20. The language is intuitive for Java developers, and is intended as their interface to accessing the persisted data model. As can be seen above, the query is made up of distinct parts. The class being selected (the SELECT clause in SQL), the filter (which equates to the WHERE clause in SQL), together with any sorting (the ORDER BY clause in SQL), etc.



Accessing Fields/Properties

In JDOQL you access fields/properties in the query by referring to the field/bean name. For example, if you are querying a class called Product and it has a field "price", then you access it like this

Query query = pm.newQuery(Product.class);
query.setFilter("price < 150.0");

In addition to the persistent fields, you can also access "public static final" fields of any class. You can do this as follows

Query query = pm.newQuery(Product.class, "taxPercent < mydomain.Product.TAX_BAND_A");

So this will find all products that include a tax percentage less than some "BAND A" level. Where you are using "public static final" fields you can either fully-qualify the class name or you can include it in the "imports" section of the query (see later).



Data types : literals

JDOQL supports the following literals: IntegerLiteral, FloatingPointLiteral, BooleanLiteral, CharacterLiteral, StringLiteral, and NullLiteral.



Operators precedence

The following list describes the operator precedence in JDOQL.

  1. Cast
  2. Unary ("~") ("!")
  3. Unary ("+") ("-")
  4. Multiplicative ("*") ("/") ("%")
  5. Additive ("+") ("-")
  6. Relational (">=") (">") ("<=") ("<") ("instanceof")
  7. Equality ("==") ("=!")
  8. Boolean logical AND ("&")
  9. Boolean logical OR ("|")
  10. Conditional AND ("&&")
  11. Conditional OR ("||")

Concatenation Expressions

The concatenation operator(+) concatenates a String to either another String or Number. Concatenations of String or Numbers to null results in null.

Control over locking of fetched objects

JDOQL allows control over whether objects found by a query are locked during that transaction so that other transactions can't update them in the meantime. To do this you would do

Query q = pm.newQuery(...);
q.setSerializeRead(true);

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

tx.setSerializeRead(true);

If the datastore in use doesn't support locking of objects then this will do nothing



Parameter checking

In some situations you may have a map of parameters keyed by their name, yet the query in question doesn't need all parameters. Normal JDO execution would throw an exception here since they are inconsistent with the query. You can omit this check by setting

q.addExtension("datanucleus.query.ignoreParameterCountCheck", "true");


instanceof

JDOQL allows the Java keyword instanceof so you can compare objects against a class.

Let's take an example. We have a class A that has a field "b" of type B and B has subclasses B1, B2, B3. Clearly the field "b" of A can be of type B, B1, B2, B3 etc, and we want to find all objects of type A that have the field "b" that is of type B2. We do it like this

Declarative JDOQL :
Query query = pm.newQuery(A.class);
query.setFilter("b instanceof mydomain.B2");
List results = (List)query.execute();

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM mydomain.A WHERE b instanceof mydomain.B2");
List results = (List)query.execute();


Deletion by Query

If you want to delete instances of a candidate using a query, you simply define the query candidate/filter in the normal way, and then instead of calling query.execute() you call query.deletePersistentAll() . Like this

Query query = pm.newQuery("SELECT FROM mydomain.A WHERE this.value < 50");
Long number = (Long)query.deletePersistentAll();

The value returned is the number of instances that were deleted. Note that this will perform any cascade deletes that are defined for these instances. In addition, all instances in memory will reflect this deletion.



Bulk Delete

DataNucleus provides an extension to allow bulk deletion. This differs from the "Deletion by Query" above in that it simply goes straight to the datastore and performs a bulk delete, leaving it to the datastore referential integrity to handle relationships. To enable "bulk delete" you need the persistence property datanucleus.query.jdoql.allowAll set to true . You then perform "bulk delete" like this

Query query = pm.newQuery("DELETE FROM mydomain.A WHERE this.value < 50");
Long number = (Long)query.execute();

Again, the number returned is the number of records deleted.



Bulk Update

DataNucleus provides an extension to allow bulk update. This allows you to do bulk updates direct to the datastore without having to load objects into memory etc. To enable "bulk update" you need the persistence property datanucleus.query.jdoql.allowAll set to true . You then perform "bulk update" like this

Query query = pm.newQuery("UPDATE mydomain.A SET this.value=this.value-5.0 WHERE this.value > 100");
Long number = (Long)query.execute();

Again, the number returned is the number of records updated.