The JDO specification (1.0.1) requires that JDO implementations provide Query ability using JDOQL. JDOQL defines a series of methods necessary for compatible querying across implementations. JPOX provides the JDO 1.0.1 JDOQL implementation. It also provides the ability to query using SQL syntax (also known as JPOXSQL - this is part of JDO 2.0) -- this is only available for RDBMS datastores. A JDO Query can be performed in many ways, here is one example using standard JDOQL

Query q=pm.newQuery(pm.getExtent(org.jpox.myclass.class,true),"param2 < 300");
q.setOrdering("param1 ascending");
            

So we query for all instances of org.jpox.myclass where the attribute param2 is less than 300, returning them in ascending order (using attribute param1).

Various methods on the Query object operate differently between JDOQL and SQL, as follows :-

JPOX provides all functionality required by the JDO 1.0.1 specification, some functionality required by the JDO 2.0 specification, and in addition provides a series of vendor extensions.

JDOQL Operations

The various Java types have various operations that they allow. These are typically not supported in JDOQL. Below is a list of what is required by JDOQL in JDO 1.0.1 and JDO 2.0, and what JPOX supports.

Type	Operation	JDO spec	JPOX support
String	startsWith(String)	1.0.1	Yes
String	endsWith(String)	1.0.1	Yes
String	indexOf(String)	2.0	Yes
String	indexOf(String,int)	2.0	Yes
String	substring(int)	2.0	Yes
String	substring(int,int)	2.0	Yes
String	toLowerCase()	2.0	Yes
String	toUpperCase()	2.0	Yes
String	charAt(int)	[extension]	Yes
String	startsWith(String,int)	[extension]	Yes
String	length()	[extension]	Yes
String	equals(String)	[extension]	Yes
Collection	isEmpty()	1.0.1	Yes
Collection	contains(value)	1.0.1	Yes
Map	isEmpty()	1.0.1	Yes
Map	contains(value)	1.0.1	Yes
Map	containsKey(key)	2.0	Yes
Map	containsValue(value)	2.0	Yes
Map	containsEntry(key,value)	[extension]	Yes

JDOQL : Example 1

Here's a simple example for finding the elements of a class with a field below a particular threshold level. Here we pass in the threshold value (limit), and sort the output in order of ascending price.

Query query = pm.newQuery(org.jpox.samples.store.Product.class,"price < limit");
query.declareParameters("double limit");
query.setOrdering("price ascending");
Collection results = (Collection)query.execute(150.00);
                

class Product
{
    String name;
    double price;
    java.util.Date endDate;
    ...
}

<jdo>
    <package name="org.jpox.samples.store">
        <class name="Product">
            <field name="name">
                <extension vendor-name="jpox" key="length" value="max 100"/<
            </field>
            <field name="abreviation">
                <extension vendor-name="jpox" key="length" value="max 20"/<
            </field>            
            <field name="price"/>
            <field name="endDate"/>
        </class>
    </package>
</jdo>
                

JDOQL : Example 2

Here's another example using the same Product class as above, but this time comparing to a Date field. Because we are using a type in our query, we need to import it ... just like you would in a Java class if you were using it there.

Query query = pm.newQuery(org.jpox.samples.store.Product.class,"endDate > best_before_limit");
query.declareImports("import java.util.Date");
query.declareParameters("java.util.Date best_before_limit");
query.setOrdering("endDate descending");
Collection results = (Collection)query.execute(my_date_limit);
                

JDOQL : Example 3

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 searches for all objects of type org.jpox.samples.store.InventoryMap that contain a value with the key key1.

Extent e=pm.getExtent(org.jpox.samples.store.InventoryMap.class,true);
Query query = pm.newQuery(e,"products.containsKey(\"key1\")");
Collection results = (Collection)query.execute();
                

Here's the source code for reference

class InventoryMap
{
    Map products;
    ...
}
class Product
{
    String name;
    double price;
    java.util.Date endDate;
    ...
}

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

        <class name="Product">
            <field name="name">
                <extension vendor-name="jpox" key="length" value="max 100"/<
            </field>
            <field name="price"/>
            <field name="endDate"/>
        </class>
    </package>
</jdo>
                

JDOQL : Example 4

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 commercial product name. The commercial product name is provided as parameter.

Query query = pm.newQuery(org.jpox.samples.store.Product.class);
query.declareImports("import java.lang.String");
query.declareParameters("java.lang.String commercialName");
query.setFilter("commercialName.startsWith(this.abbreviation)");
Collection results = (Collection)query.execute("Workbook Advanced");
                

To utilise SQL syntax in queries, you create a Query as follows

Query q=pm.newQuery("javax.jdo.query.SQL",the_query);
            

It should be noted that this is part of the JDO 2.0 specification, and its definition is not fully defined as yet, but we provide it here as an early access offering. SQL is provided as a way of making queries more powerful and allowing syntax that is more familiar to developers.

SQL provides two forms of macro support in SQL querying. These are for SQL identifiers, and for query parameters. SQL identifier macros are used to specify the table and column names used for persistent classes and fields, and are enclosed in braces { }. They work exactly the same as in view definitions. The {this} class refers to the query result object class. Query parameter macros are used to indicate where named query parameter values should be substituted in the SQL text, and are enclosed in question marks. For example, ?param2? means the value of the param2 parameter should be substituted; param2 must be declared with Query.declareParameters() and its value passed as an argument to Query.execute(). Internally, each macro is substituted with a single JDBC-style question mark and the appropriate value is set into each statement parameter by name.

To use SQL you also must provide a class representing the output rows of the query, specifying this with Query.setClass(). The columns of the query's result set are matched up to the fields of the query result object by name. Therefore, for every result set column it is necessary to use SQL aliases that correspond to the Java field names. For example, the SQL identifier macro {this.myField} would be used to alias the column containing the values for myField in the query result object (this will typically expand to something like MY_FIELD as an SQL identifier). Executing the query will return a Collection of query result objects. Instances of query result objects are very similar to view objects. Query result objects:

• Are read-only. Persistent instances cannot be created (using makePersistent), deleted (using deletePersistent), nor can any of their fields be updated
• Cannot declare a persistence-capable superclass.
• Cannot contain Collection or Map fields (i.e containers, as used in relationships)
• Use non-durable identity (specified in the MetaData for the class).
• Have no corresponding Extent in the database (specified in the MetaData for the class - requires-extent="false")

SQL : Example 1

Here's a simple example using SQL. Here we want to retrieve 2 columns from a table given a particular value of a third column. We create a dummy class to receive the returned columns, and define it as nondurable

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT columnA AS COLUMNA, columnB AS COLUMNB FROM TABLE WHERE COL3 = ?param?");
query.setClass(YourClass.class);
query.declareParameters("Integer param");
Collection results = (Collection)query.execute(new Integer(1));
                

class YourClass
{
    Integer columnA;
    String columnB;
}

<jdo>
    <package name="org.jpox.samples.sql">
        <class name="YourClass" identity-type="nondurable" requires-extent="false"/>
    </package>
</jdo>
                

SQL : Example 2

Here's an example for getting the size of a table.

query = pm.newQuery("javax.jdo.query.JPOXSQL", "SELECT count(*) AS THESIZE FROM TABLE");
query.setClass(TableSize.class);
col = (Collection) query.execute();
tableSize = (TableSize) col.iterator().next();
                

class TableSize
{
    Integer thesize;

    public Integer getTheSize() { ... };
    public void setTheSize(Integer size) { ... };
}

<jdo>
    <package name="org.jpox.samples.sql">
        <class name="TableSize" identity-type="nondurable" requires-extent="false"/>
    </package>
</jdo>