If you want to return instances of persistable types, then you can set the candidate class.

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT MY_ID, MY_NAME FROM MYTABLE");
query.setClass(MyClass.class);
List<MyClass> results = (List<MyClass>) query.execute();

If you know that there will only be a single row returned from the SQL query then you can set the query as unique . Note that the query will return null if the SQL has no results.

Sometimes you know that the query can only every return 0 or 1 objects. In this case you can simplify your job by adding

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT MY_ID, MY_NAME FROM MYTABLE");
query.setClass(MyClass.class);
query.setUnique(true);
MyClass obj = (MyClass) query.execute();

If you want to dump each row of the SQL query results into an object of a particular type then you can set the result class.

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT MY_ID, MY_NAME FROM MYTABLE");
query.setResultClass(MyResultClass.class);
List<MyResultClass> results = (List<MyClass>) query.execute();

The Result Class has to meet certain requirements. These are

• Can be one of Integer, Long, Short, Float, Double, Character, Byte, Boolean, String, java.math.BigInteger, java.math.BigDecimal, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, or Object[]
• Can be a user defined class, that has either a constructor taking arguments of the same type as those returned by the query (in the same order), or has a public put(Object, Object) method, or public setXXX() methods, or public fields.

For example, if we are returning two columns like above, an int and a String then we define our result class like this

public class MyResultClass
{
    protected int id = 0;
    protected String name = null;

    public MyResultClass(int id, String name)
    {
        this.id = id;
        this.name = name;
    }

    ...
}

So here we have a result class using the constructor arguments. We could equally have provided a class with public fields instead, or provided setXXX methods or a put method. They all work in the same way.

In JDO all SQL queries must begin "SELECT ...", and consequently it is not possible to execute stored procedures. In DataNucleus we have an extension that allows this to be overridden. To enable this you should pass the property datanucleus.query.sql.allowAll as true when creating the PersistenceManagerFactory. Thereafter you just invoke your stored procedures like this

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

Where "sp_who" is the stored procedure being invoked. Clearly the same rules will apply regarding the results of the stored procedure and mapping them to any result class. The syntax of calling a stored procedure differs across RDBMS. Some require "CALL ..." and some "EXECUTE ...". Go consult your manual.

In JDO all SQL queries must begin "SELECT ...", and consequently it is not possible to execute queries that change data. In DataNucleus we have an extension that allows this to be overridden. To enable this you should pass the property datanucleus.query.sql.allowAll as true when creating the PersistenceManagerFactory. Thereafter you just invoke your statements like this

Query q = pm.newQuery("javax.jdo.query.SQL", 
    "UPDATE MY_TABLE SET MY_COLUMN = ? WHERE MY_ID = ?");

you then pass any parameters in as normal for an SQL query.

In JDO SQL queries can have parameters but must be positional . This means that you do as follows

Query q = pm.newQuery("javax.jdo.query.SQL",
    "SELECT col1, col2 FROM MYTABLE WHERE col3 = ? AND col4 = ? and col5 = ?");
List results = (List) q.execute(val1, val2, val3);

So we used traditional JDBC form of parametrisation, using "?".

DataNucleus also supports two further variations. The first is called numbered parameters where we assign numbers to them, so the previous example could have been written like this

Query q = pm.newQuery("javax.jdo.query.SQL",
    "SELECT col1, col2 FROM MYTABLE WHERE col3 = ?1 AND col4 = ?2 and col5 = ?1");
List results = (List) q.execute(val1, val2);

so we can reuse parameters in this variation. The second variation is called named parameters where we assign names to them, and so the example can be furtehr rewritten like this

Query q = pm.newQuery("javax.jdo.query.SQL",
    "SELECT col1, col2 FROM MYTABLE WHERE col3 = :firstVal AND col4 = :secondVal and col5 = :firstVal");
Map params = new HashMap();
params.put("firstVal", val1);
params.put("secondVal", val1);
List results = (List) q.executeWithMap(params);

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

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT count(*) FROM MYTABLE");
List results = (List) query.execute();
Integer tableSize = (Integer) result.iterator().next();

Here's an example for getting the maximum and miminum of a parameter without a candidate class.

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT max(PARAM1), min(PARAM1) FROM MYTABLE");
List results = (List) query.execute();
Object[] measures = (Object[])result.iterator().next();
Double maximum = (Double)measures[0];
Double minimum = (Double)measures[1];

Here's an example for getting the size of a table with a result class. So we have a result class of

public class TableStatistics
{
    private int total;

    public setTotal(int total);
}

So we define our query to populate this class

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT count(*) AS total FROM MYTABLE");
query.setResultClass(TableStatistics.class);
List results = (List) query.execute();
TableStatistics tableStats = (TableStatistics) result.iterator().next();

Each row of the results is of the type of our result class. Since our query is for an aggregate, there is actually only 1 row.

When we want to retrieve objects of a particular PersistenceCapable class we specify the candidate class. Here we need to select, as a minimum, the identity columns for the class.

Query query = pm.newQuery("javax.jdo.query.SQL",
                        "SELECT MY_ID, MY_NAME FROM MYTABLE");
query.setClass(MyClass.class);
List results = (List) query.execute();
Iterator resultsIter = results.iterator();
while (resultsIter.hasNext())
{
    MyClass obj = (MyClass)resultsIter.next();
}

class MyClass
{
    String name;
    ...
}

<jdo>
    <package name="org.datanucleus.samples.sql">
        <class name="MyClass" identity-type="datastore" table="MYTABLE">
            <datastore-identity strategy="identity">
                <column name="MY_ID"/>
            </datastore-identity>
            <field name="name" persistence-modifier="persistent">
                <column name="MY_NAME"/>
            </field>
        </class>
    </package>
</jdo>

Here's an example for getting the number of people with a particular email address. You simply add a "?" for all parameters that are passed in, and these are subsitituted at execution time.

Query query = pm.newQuery("javax.jdo.query.SQL", "SELECT count(*) FROM PERSON WHERE EMAIL_ADDRESS = ?");
List results = (List) query.execute("nobody@datanucleus.org");
Integer tableSize = (Integer) result.iterator().next();

While "named" queries were introduced primarily for JDOQL queries, we can define "named" queries for SQL also. So let's take a Product class, and we want to define a query for all products that are "sold out". We firstly add this to our MetaData

<jdo>
    <package name="org.datanucleus.samples.store">
        <class name="Product" identity-type="datastore" table="PRODUCT">
            <datastore-identity strategy="identity">
                <column name="PRODUCT_ID"/>
            </datastore-identity>
            <field name="name" persistence-modifier="persistent">
                <column name="NAME"/>
            </field>
            <field name="status" persistence-modifier="persistent">
                <column name="STATUS"/>
            </field>

            <query name="SoldOut" language="javax.jdo.query.SQL">
                SELECT PRODUCT_ID FROM PRODUCT WHERE STATUS == "Sold Out"
            </query>
        </class>
    </package>
</jdo>

And then in our application code we utilise the query

Query q = pm.newNamedQuery(Product.class, "SoldOut");
List results = (List)q.execute();