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 |
|---|---|
SQL queries are part of the JDO 2 specification. Though Kodo supports all of the features detailed in this chapter, many JDO 1 implementations may not. This chapter describes SQL queries as they appear in the JDO 2 Early Draft specification; their behavior may change before JDO 2 is finalized. Kodo also supports embedding SQL into standard JDOQL queries. See Section 10.7, “Query Extensions” in the Reference Guide for details. SQL queries require Kodo JDO Enterprise Edition. | |
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 query template. The template might be a query from another persistence manager, or a query that has been deserialized and has lost its persistence manager 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 12.1. Creating a SQL Query
PersistenceManager pm = ...;
Query query = pm.newQuery ("javax.jdo.query.SQL", "SELECT * FROM MAGAZINE");
query.setClass (Magazine.class);
processMagazines ((Collection) query.execute ());
query.closeAll ();
Query template = deserializeTemplateQuery ();
query = pm.newQuery (template);
processMagazines ((Collection) 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)