Chapter 17. SQL Queries

17.1. Creating SQL Queries
17.2. Retrieving Persistent Objects with SQL
17.3. SQL Projections
17.4. Named SQL Queries
17.5. Conclusion

JDOQL is a powerful query language, but there are times when it is not enough. Maybe you're migrating a JDBC application to JDO on a strict deadline, and you don't have time to translate your existing SQL selects to JDOQL. Or maybe a certain query requires database-specific SQL your JDO implementation doesn't support. Or maybe your DBA has spent hours crafting the perfect select statement for a query in your application's critical path. Whatever the reason, SQL queries can remain an essential part of an application.

You are probably familiar with executing SQL queries by obtaining a java.sql.Connection, using the JDBC APIs to create a Statement, and executing that Statement to obtain a ResultSet. And of course, you are free to continue using this low-level approach to SQL execution in your JDO applications. However, JDO also supports executing SQL queries through the javax.jdo.Query interface introduced in Chapter 11, Query. Using a JDO SQL query, you can retrieve either persistent objects or projections of column values. The following sections detail each use.


Kodo also supports embedding SQL into standard JDOQL queries. See Section 9.6, “Query Language Extensions” in the Reference Guide for details.

17.1. Creating SQL Queries

The PersistenceManager has two factory methods suitable for creating SQL queries:

public Query newQuery (Object query);
public Query newQuery (String language, Object query);

The first method is used to create a new Query instance with the same properties as the passed-in template. The template might be a Query from another PersistenceManager, or a Query that has been deserialized and has lost its PersistenceManager association. The method works for any query, regardless of the language used.

The second method was designed specifically for non-JDOQL queries. Its first parameter is the query language to use. For SQL queries, the language is javax.jdo.query.SQL (in case you are wondering, the official JDOQL language string is javax.jdo.query.JDOQL). Its second parameter represents the query to run - in this case, the SQL string. The example below shows these methods in action.

Example 17.1. Creating a SQL Query

PersistenceManager pm = ...;
Query query = pm.newQuery ("javax.jdo.query.SQL", "SELECT * FROM MAG");
query.setClass (Magazine.class);
processMagazines ((List) query.execute ());
query.closeAll ();

Query template = deserializeTemplateQuery ();
query = pm.newQuery (template);
processMagazines ((List) query.execute ());
query.closeAll ();

While JDOQL queries have separate result, filter, grouping, and ordering strings, a single SELECT statement encompasses a complete SQL query. Thus, most methods of SQL Query objects throw an exception. In particular, you cannot call the following methods:

  • setCandidates (Collection)

  • setFilter (String)

  • setResult (String)

  • setGrouping (String)

  • setOrdering (String)

  • declareImports (String)

  • declareVariables (String)

  • declareParameters (String)


In addition to SELECT statements, Kodo supports stored procedure invocations as SQL queries. Kodo will assume any SQL that does not begin with the SELECT keyword (ignoring case) is a stored procedure call, and invoke it as such at the JDBC level.


Skip navigation bar   Back to Top