Direct SQL Queries

The "normal" query mechanism described in the JDO spec is adequate for performing most typical kinds of data searching. It is designed to find all persistent objects of a specific type (sometimes in a specific collection) whose state satisfies some criteria. In all cases, whole persistent objects are always returned.

In TJDO, database views provide a way to pull together portions of objects for reporting purposes. They're also the preferred way to leverage some SQL functionality, such as aggregate functions, that isn't otherwise accessible through normal JDO queries.

However, in some situations a custom view alone isn't enough to accomplish a desired data search or computation, without performing the searching or computing in Java itself. An example would be the use of a SQL aggregate function, like SUM() or AVG(), applied to some subset of a whole table where the criteria defining the subset is not known until runtime.

For these situations the portable solution, and therefore the preferred solution, is to do normal queries for the relevant objects and perform the summing or averaging work in Java. This ensures your code will work with any JDO implementation, whether backed by a relational database, an object database, or whatever. Where the amount of data involved is large, however, the performance difference between doing the work in Java vs. delegating it to the database server may require you to resort to a direct SQL query instead.

A direct SQL query in TJDO allows you to perform any arbitrary SQL query, on either JDO-managed or non-managed tables, and retrieve the result rows as Java objects. It has several advantages over trying to perform the same query directly with JDBC:

• You don't need to know the generated names of TJDO-managed tables or columns in advance.
• You don't have to worry about how to acquire a Connection to the appropriate schema.
• The query automatically participates in the current JDO transaction.
• Any special translations normally done to map Java types to SQL types are automatically performed. On Oracle for example, this means mapping boolean to CHAR(1) and unlimited-length Strings to CLOBs, etc.

Direct SQL queries do have a significant disadvantage compared to views: there is no way to code separate vendor-specific versions of a query.

Usage

In JDO terms a direct SQL query is a JDO query that uses an alternate query language. You create a direct SQL query using:

PersistenceManager.newQuery(String language, Object query)

The returned Query object behaves differently from normal JDO Query objects:

SQL Macros

Two kinds of embedded macros are supported in the given SQL text:

1. SQL Identifier Macros
2. Query Parameter Macros

SQL identifier macros are used to specify the actual table and column names used for persistent classes and fields, and are enclosed in braces { }. They work exactly the same as in view definitions. The {this} class refers to the query result object class.

Query parameter macros are used to indicate where named query parameter values should be substituted in the SQL text, and are enclosed in question marks. For example, ?lastName? means the value of the lastName parameter should be substituted; lastName must be declared with Query.declareParameters() and its value passed as an argument to Query.execute(). Internally, each macro is substituted with a single JDBC-style question mark and the appropriate value is set into each statement parameter by name.

Query Results

You must create a class to represent the row data returned by the query. These are the query result objects. The class must be specified to the query using Query.setClass().

The columns of the query's result set are matched up to the fields of the query result object by name. Therefore, for every result set column it is necessary to use SQL aliases that correspond to the Java field names. For example, the SQL identifier macro {this.myField} would be used to alias the column containing the values for myField in the query result object (this will expand to something like MY_FIELD as a SQL identifier).

Executing the query will return a Collection of query result objects. Instances of query result objects are very similar to view objects. Query result objects:

• Are read-only. Persistent instances cannot be created (makePersistent()), deleted (deletePersistent()), nor can any of their fields be written.
• Cannot declare a persistence-capable superclass.
• Cannot contain Collection or Map fields.
• Use so-called non-durable identity , and must be so defined in their JDO metadata. This is accomplished by setting identity-type="nondurable" on the <class> element. Non-durable identity in JDO means that each instance has a unique ID, but that ID is not guaranteed to be the same from one access to the next, nor can objects be retrieved by ID (getObjectById()).
• Have no corresponding Extent in the database, and must be so defined in their JDO metadata. This is accomplished by setting requires-extent="false" on the <class> element. Having no Extent means there is no table or view serving as backing for objects of this class (PersistenceManager.getExtent() will always fail).

Any class meeting these criteria is usable as the result type of a direct SQL query.

Like view objects, instances of query result objects are always fully populated and the default fetch group has no effect. The InstanceCallbacks jdoPostLoad() and jdoPreClear() will be called at the expected times, but jdoPreStore() and jdoPreDelete() will never be called as they do not apply.

An Example

public class AvgSalary
{
    private double avgSalary;

    ...
}

<?xml version="1.0"?>
<jdo>
  <package name="com.acme">
    <class name="AvgSalary" identity-type="nondurable" requires-extent="false"/>
  </package>
</jdo>


{
    ...

    /*
     * Compute the average salary field for all Employee objects
     * having hire dates between the given dates.
     */
    String sqlText = "SELECT AVG({Employee.salary}) {this.avgSalary}"
                   + "  FROM {Employee}"
                   + " WHERE {Employee.hireDate} BETWEEN ?startDate? AND ?endDate?";

    Date startDate = ...
    Date endDate = ...

    Query q = pm.newQuery("javax.jdo.query.TJDOSQL", sqlText);
    q.setClass(AvgSalary.class);
    q.declareImports("import com.acme.Employee; import java.util.Date");
    q.declareParameters("Date startDate, Date endDate");

    Collection results = q.execute(startDate, endDate);

    ...
}