DataNucleus Access Platform - JDO Queries

When writing the "filter" for a JDOQL Query you can make use of some methods on the various Java types. The range of methods included as standard in JDOQL is not as flexible as with the true Java types, but the ones that are available are typically of much use. In addition, DataNucleus adds on many extensions to the JDO specifications providing all of the commonly required methods. Below is a list of what is required by JDOQL in JDO 1.0 and JDO 2.0, and what DataNucleus supports.

Java Type	Method	Description	Specification
String	startsWith(String)	Returns if the string starts with the passed string	JDO 1.0, JDO 2.0
String	endsWith(String)	Returns if the string ends with the passed string	JDO 1.0, JDO 2.0
String	indexOf(String)	Returns the first position of the passed string	JDO 2.0
String	indexOf(String,int)	Returns the position of the passed string, after the passed position	JDO 2.0
String	substring(int)	Returns the substring starting from the passed position	JDO 2.0
String	substring(int,int)	Returns the substring between the passed positions	JDO 2.0
String	toLowerCase()	Returns the string in lowercase	JDO 2.0
String	toUpperCase()	Retuns the string in UPPERCASE	JDO 2.0
String	matches(String pattern)	Returns whether string matches the passed expression. The pattern argument follows the rules of java.lang.String.matches method.	JDO 2.0
String	like(String pattern)	Returns whether string matches the passed expression. The pattern argument is database specific.
String	charAt(int)	Returns the character at the passed position
String	startsWith(String,int)	Returns if the string starts with the passed string, from the passed position
String	length()	Returns the length of the string
String	equals(String)	Returns if the string is equals to the passed string
String	trim()	Returns a trimmed version of the string
String	trimLeft()	Returns a trimmed version of the string (trimmed for leading spaces)
String	trimRight()	Returns a trimmed version of the string (trimmed for trailing spaces)
String	translate(to,from) [2]	Translates the "from" characters into "to"
Collection	isEmpty()	Returns whether the collection is empty	JDO 1.0, JDO 2.0
Collection	contains(value)	Returns whether the collection contains the passed element	JDO 1.0, JDO 2.0
Collection	size()	Returns the number of elements in the collection	JDO 2.0
Map	isEmpty()	Returns whether the map is empty	JDO 1.0, JDO 2.0
Map	contains(value)	Returns whether the map contains the passed value
Map	containsKey(key)	Returns whether the map contains the passed key	JDO 2.0
Map	containsValue(value)	Returns whether the map contains the passed value	JDO 2.0
Map	containsEntry(key,value)	Returns whether the map contains the passed entry
Map	get(key)	Returns the value from the map with the passed key	JDO 2.0
Map	size()	Returns the number of entries in the map	JDO 2.0
Math	abs(number)	Returns the absolute value of the passed number	JDO 2.0
Math	sqrt(number)	Returns the square root of the passed number	JDO 2.0
Math	cos(number)	Returns the cosine of the passed number
Math	sin(number)	Returns the absolute value of the passed number
Math	tan(number)	Returns the tangent of the passed number
Math	acos(number)	Returns the arc cosine of the passed number
Math	asin(number)	Returns the arc sine of the passed number
Math	atan(number)	Returns the arc tangent of the passed number
Math	exp(number)	Returns the exponent of the passed number
Math	log(number)	Returns the log(base e) of the passed number
Math	floor(number)	Returns the floor of the passed number
Math	ceil(number)	Returns the ceiling of the passed number
Date	getDay()	Returns the day (of the month) for the date
Date	getMonth()	Returns the month for the date
Date	getYear()	Returns the year for the date
Time	getHour()	Returns the hour for the time
Time	getMinute()	Returns the minute for the time
Time	getSecond()	Returns the second for the time
JDOHelper	getObjectId(object)	Returns the object identity of the passed persistent object	JDO 2.0
Analysis	rollup({object})	Perform a rollup operation over the results.
Analysis	cube({object})	Perform a cube operation over the results.
{}	length	Returns the length of an array
{}	contains(object)	Returns true if the array contains the object
Enum	startsWith(String) [1]	Returns if the string starts with the passed string
Enum	endsWith(String) [1]	Returns if the string ends with the passed string
Enum	indexOf(String) [1]	Returns the first position of the passed string
Enum	indexOf(String,int) [1]	Returns the position of the passed string, after the passed position
Enum	substring(int) [1]	Returns the substring starting from the passed position
Enum	substring(int,int) [1]	Returns the substring between the passed positions
Enum	toLowerCase() [1]	Returns the string in lowercase
Enum	toUpperCase() [1]	Retuns the string in UPPERCASE
Enum	matches(String pattern) [1]	Returns whether string matches the passed expression. The pattern argument follows the rules of java.lang.String.matches method.
Enum	like(String pattern) [1]	Returns whether string matches the passed expression. The pattern argument is database specific.
Enum	charAt(int) [1]	Returns the character at the passed position
Enum	startsWith(String,int) [1]	Returns if the string starts with the passed string, from the passed position
Enum	length() [1]	Returns the length of the string
Enum	equals(String) [1]	Returns if the string is equals to the passed string
Enum	trim() [1]	Returns a trimmed version of the string

[1] - This method is only available if java.lang.Enum is stored as String (the Enum name) instead of the numeric value (ordinal value).
[2] - This method is only available for some databases, such as DB2, Oracle and Postgresql.

The following sections provide some examples of what can be done using JDOQL methods.

Example 1 - Map methods (I)

Here's another example using the same Product class as a value in a Map. This introduces how you query Collection and Map fields using the operations available. Collections and Maps act very similarly. Our example represents an organisation that has several Inventories of products. Each Inventory of products is stored using a Map, keyed by the Product name. The query searches for all Inventories that contain a product with the name "product 1".

Declarative JDOQL :
Extent e=pm.getExtent(org.datanucleus.samples.store.Inventory.class,false);
Query query = pm.newQuery(e,"products.containsKey(\"product 1\")");
List results = (List)query.execute();

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.datanucleus.samples.store.Inventory EXCLUDE SUBCLASSES " +
                "WHERE products.containsKey(\"product 1\")");
List results = (List)query.execute();

Here's the source code for reference

class Inventory
{
    Map products;
    ...
}
class Product
{
    String name;
    double price;
    double salePrice;
    java.util.Date endDate;
    String[] composition;
    ...
}

<jdo>
    <package name="org.datanucleus.samples.store">
        <class name="Inventory">
            <field name="products">
                <map key-type="java.lang.String" value-type="org.datanucleus.samples.store.Product"/>
                <key mapped-by="name"/>
            </field>
        </class>

        <class name="Product">
            <field name="name">
                <column length="100" jdbc-type="VARCHAR"/>
            </field>
            <field name="price"/>
            <field name="endDate"/>
        </class>
    </package>
</jdo>

Example 2 - Map methods (II)

We might want to to check if a Collection field contains one or other elements. We extend the previous example that is using the Product class as a value in a Map. Our example represents an organisation that has several Inventories of products. Each Inventory of products is stored using a Map, keyed by the Product name. The query searches for all Inventories that contain a product with the name "product 1" or "product 2".

Declarative JDOQL :
Extent e=pm.getExtent(org.datanucleus.samples.store.Inventory.class,false);
Query query = pm.newQuery(e);
query.declareVariables("String productName");
query.setFilter("products.containsKey(productName) && (productName==\"product 1\" || productName==\"product 2\")");
List results = (List)query.execute();

Single-String JDOQL:
Query query = pm.newQuery("SELECT FROM org.datanucleus.samples.store.Inventory EXCLUDE SUBCLASSES " + 
    "WHERE products.containsKey(productName) && (productName==\"product 1\" || productName==\"product 2\") " + 
    "VARIABLES String productName");
List results = (List)query.execute();

Example 3 - String.startsWith() method

Here's another example using the same Product class as above, but this time looking for objects which their abreviation is the begin of a trade name. The trade name is provided as parameter.

Declarative JDOQL :
Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.declareImports("import java.lang.String");
query.declareParameters("java.lang.String tradeName");
query.setFilter("tradeName.startsWith(this.abbreviation)");
List results = (List)query.execute("Workbook Advanced");

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.datanucleus.samples.store.Product " +
                "WHERE tradeName.startsWith(this.abbreviation) " +
                "PARAMETERS java.lang.String tradeName import java.lang.String");
List results = (List)query.execute("Workbook Advanced");

Example 4 - Date.getYear() method

Here's an example using the Sale's in a store, where all Sale's are persisted with a date. We want to find all sales in a particular year. Clearly we dont have a field called "year", but we can make use of the DataNucleus extension "Date.getYear()" method

Declarative JDOQL :
Query query = pm.newQuery(org.datanucleus.samples.store.Sale.class);
query.declareParameters("int theYear");
query.setFilter("date.getYear() == theYear");
List results = (List)query.execute(new Integer(2004));

Single-String JDOQL :
Query query = pm.newQuery("SELECT FROM org.datanucleus.samples.store.Sale " +
                "WHERE date.getYear() == theYear" +
                "PARAMETERS int theYear");
List results = (List)query.execute(new Integer(2004));

Example 5 - Using contains with arrays

Here's an example using the Product and checking if one element is part of the Product composition. Note that Production.composition is of type String[].

Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.setFilter("this.composition.contains(element)");
query.declareParameters("String element");
List results = (List)query.execute("sugar");

Example 6 - Defining arrays

Here's an example of array definition using curly braces {} in the query. In this example we check that the Product can be easily manufactured by verifying the number of elements in the composition. Note that Production.composition is of type String[].

Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.setFilter("{'Candy','Ice cream'}.contains(this.name) && this.composition.length < 5");
List results = (List)query.execute();

Example 7 - Analysis.rollup() method

Here's an example using the Product in a store and the rollup method. We want to find the max price of a product and all products at given end dates.

Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.setResult("name, endDate, max(price)");
query.setGrouping("Analysis.rollup({name,endDate})");
List results = (List)query.execute();