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 considered not suitable, maybe due to the lack of familiarity of the application developer with such a query language. In this case it is desirable to query using SQL . JDO 2 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_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 JDO2 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 JDO2 result classes (see Result Class specification).


Stored Procedures

In JDO2 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.



Inserting/Updating/Deleting

In JDO2 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.



Parameters

In JDO2 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);




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


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


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"><![CDATA[
                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();