JDOQL2 : Methods

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 RDBMS supports. This is for the "JDOQL2" implementation for RDBMS . To enable the "JDOQL2" implementation refer to the persistence property datanucleus.query.JDOQL.implementation .

Java Type Method Description Specification DataNucleus support
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 equals(String) Returns if the strings are equal
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 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)
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
List get(index) Returns the element at that position
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 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 ceil(number) Returns the ceiling of the passed number
Math cos(number) Returns the cosine of the passed number
Math exp(number) Returns the exponent of the passed number
Math floor(number) Returns the floor of the passed number
Math log(number) Returns the log(base e) of the passed number
Math sin(number) Returns the absolute value of the passed number
Math sqrt(number) Returns the square root of the passed number JDO 2.0
Math tan(number) Returns the tangent 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
JDOHelper getVersion(object) Returns the object version of the passed persistent object
{} length Returns the length of an array
{} contains(object) Returns true if the array contains the object
Enum ordinal Returns the ordinal of the enum (not implemented for enum expression when persisted as a string)
Enum toString() Returns the string form of the enum (not implemented for enum expression when persisted as a numeric)
SQL_rollup({object}) Perform a rollup operation over the results. Only available for some datastores e.g DB2, MSSQL, Oracle
SQL_cube({object}) Perform a cube operation over the results. Only available for some datastores e.g DB2, MSSQL, Oracle
SQL_boolean({sql}) Embed the provided SQL and return a boolean result
SQL_numeric({sql}) Embed the provided SQL and return a numeric result

Here are a few examples using some of the extensions available for RDBMS with "JDOQL2". Please refer to the JDOQL Methods Guide for more examples of the JDO standard methods.

Example 1 - 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 2 - 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 3 - 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 4 - SQL_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("SQL_rollup({name,endDate})");
List results = (List)query.execute();


Example 5 - SQL_boolean() method

Here's an example using the Product in a store and injecting some SQL. We want to find the max price of a product and there is some non-managed column that we want to apply a condition on (so we use SQL on that column). 

Query query = pm.newQuery(org.datanucleus.samples.store.Product.class);
query.setResult("name, endDate, max(price)");
query.setFilter("SQL_boolean(\"OTHER_COL = 5\")");
List results = (List)query.execute();