Chapter 17. SQL Queries
Prev | Part 3. Java Data Objects | Next

## 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.

| Important
This feature requires Kodo Enterprise Edition.

| Note
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)`

| 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.

Prev | Up | Next
Chapter 16. Sequence | Home | 17.2. Retrieving Persistent Objects with SQL