JDO : SQL Queries

The ability to query the datastore is an essential part of any system that persists data. Sometimes an object-based query language (such as JDOQL) is not considered suitable, maybe due to the lack of familiarity of the application developer with such a query language. In this case it is desirable (when using an RDBMS) to query using SQL. JDO standardises this as a valid query mechanism, and DataNucleus supports this. Please be aware that the SQL query that you invoke has to be valid for your RDBMS, and that the SQL syntax differs across almost all RDBMS.

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

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

You have several forms of SQL queries, depending on what form of output you require.

  • No candidate class and no result class - the result will be a List of Objects (when there is a single column in the query), or a List of Object[]s (when there are multiple columns in the query)
  • Candidate class specified, no result class - the result will be a List of candidate class objects, or will be a single candidate class object (when you have specified "unique"). The columns of the querys result set are matched up to the fields of the candidate class by name. You need to select a minimum of the PK columns in the SQL statement.
  • No candidate class, result class specified - the result will be a List of result class objects, or will be a single result class object (when you have specified "unique"). Your result class has to abide by the rules of JDO result classes (see Result Class specification) - this typically means either providing public fields matching the columns of the result, or providing setters/getters for the columns of the result.
  • Candidate class and result class specified - the result will be a List of result class objects, or will be a single result class object (when you have specified "unique"). The result class has to abide by the rules of JDO result classes (see Result Class specification).

Setting candidate class

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();

Unique results

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();

Defining a result type

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.


Inserting/Updating/Deleting

In strict 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 and 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. If your query starts with "SELECT" then it is invoked using preparedStatement.executeQuery(...). If your query starts with "UPDATE", "INSERT", "MERGE", "DELETE" it is treated as a bulk update/delete query and is invoked using preparedStatement.executeUpdate(...). All other statements will be invoked using preparedStatement.execute(...) and true returned. If your statement really needs to be executed differently to these basic rules then you should look at contributing support for those statements to DataNucleus.


Parameters

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 further 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);


Example 1 - Using SQL aggregate functions, without candidate class

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];

Example 2 - Using SQL aggregate functions, with result class

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.


Example 3 - Retrieval using candidate class

When we want to retrieve objects of a particular persistable 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>

Example 4 - Using parameters, without candidate class

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 substituted 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();

Example 5 - Named Query

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();