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.
![[Note]](img/note.gif) | Note |
|---|---|
Kodo also supports embedding SQL into standard JDOQL queries. See Section 9.6, “Query Language Extensions” in the Reference Guide for details. |
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)
