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 JPOX 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).
In JDO2 all SQL queries must begin "SELECT ...", and consequently it is not possible to execute
stored procedures. In JPOX we have an extension that allows this to be overridden. To enable this you should
pass the property org.jpox.query.allowAllSQLStaments 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.
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.jpox.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@jpox.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.jpox.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();
