Chapter 17. SQL Queries

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.

17.1. Creating SQL Queries

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.

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)

Note

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.