JPOXSQL queries can be invoked in 2 modes. The first mode is where no candidate class is supplied. In this case the result of the query will be with a List of Objects (where there is only a single column in the query result), or a List of Object[]s (where there are multiple columns in the query result). The second mode is where you specify a candidate class that will wrap the results of the query.

Where you want to specify the candidate class, you do this using 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 JPOXSQL 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 List of query result objects. Instances of query result objects are very similar to view objects. Query candidate 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). By default, when you use a "nondurable" identity type JPOX will create a table/view for that class in the datastore. You can turn this behaviour off using the JPOX Meta-Data extension "requires-table", setting it to false.
• Have no corresponding Extent in the database (specified in the MetaData for the class - requires-extent="false")

Please make sure that the column name aliases in the query (that map to the fields in the nondurable class) are in CAPITALS. Instances of query result objects are always fully populated and the default fetch group has no effect. The InstanceCallbacks jdoPostLoad() and jdoPreClear() will be called at the expected times, but jdoPreStore() and jdoPreDelete() will never be called as they do not apply.

JPOXSQL 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.

When you are using a candidate class you need to associate the columns of the ResultSet with the fields of the candidate class. Therefore, for every result set column it is necessary to use SQL aliases that correspond to the Java field names. For example, the JPOXSQL 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). The following examples will clarify this.

JPOXSQL can use "Named Queries" in the same way as JDOQL and SQL. The only difference lies in the specification of the imports and parameters for the query. Standard JDO MetaData doesn't have attributes for specification of these so we have to use extension tags. To specify a JPOXSQL named query you would do something like this

<class name="MyClass" identity-type="nondurable" requires-extent="false">
    <extension vendor-name="jpox" key="requires-table" value="false"/>
    <query name="NumberOfPeople" language="javax.jdo.query.JPOXSQL">
        <![CDATA[
        select {this.name}, {this.age} from {Person} where {this.age} > ?age_limit? 
        ]]>
        <extension vendor-name="jpox" key="imports" value="import org.jpox.samples.company.Person"/>
        <extension vendor-name="jpox" key="parameters" value="Integer age_limit;"/>
    </query>
</class>

So we just make use of extension elements for specifying what additional is needed.

Here's a simple example using JPOXSQL. 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.JPOXSQL",
    "SELECT columnA AS COLUMNA, columnB AS COLUMNB FROM TABLE WHERE COL3 = ?param?");
query.setClass(YourClass.class);
query.declareParameters("Integer param");
List results = (List) 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"/>
            <extension vendor-name="jpox" key="requires-table" value="false"/>
    </package>
</jdo>

Here we want to use the identifier macros to tie the field names in the class to the column names in the ResultSet. In this example we want to get the number of records in the table used by the persisted class Person, and put the results into our candidate class TableSize. In the macros used "this" refers to TableSize, so "this.thesize" is the field in our candidate class.

Query query=pm.newQuery("javax.jdo.query.JPOXSQL",
    "SELECT count(*) as {this.thesize} from {Person}");
query.declareImports("import org.jpox.samples.company.Person");
query.setClass(TableSize.class);
List results = (List) query.execute();
tableSize = (TableSize) list.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"/>
            <extension vendor-name="jpox" key="requires-table" value="false"/>
    </package>
</jdo>

Here's the same as Example 1 yet here we have no candidate class. Each row of the results will be of type Object[].

Query query = pm.newQuery("javax.jdo.query.JPOXSQL",
    "SELECT columnA AS COLUMNA, columnB AS COLUMNB FROM TABLE WHERE COL3 = ?param?");
query.declareParameters("Integer param");
List results = (List) query.execute(new Integer(1));
Iterator resultsIter = results.iterator();
while (resultsIter.hasNext())
{
    Object[] row = (Object[])resultsIter.next();
    Object columnA = row[0];
    Object columnB = row[1];
}