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:

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


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)
where language is set to "javax.jdo.query.TJDOSQL" and query is a String containing the text of the SQL query.

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

Method Normal JDO Query Direct SQL Query
declareImports() Used to qualify class names in the parameter list and variable list Used to qualify class names in the parameter list and the SQL text
declareParameters() Declares any parameters referenced in the filter string Declares any parameters referenced in the SQL text
declareVariables() Declares any variables referenced in the filter string N/A (throws a JDOUserException)
setCandidates() Sets the Collection or Extent of objects to query N/A (throws a JDOUserException)
setClass() Sets the class of the objects to select from the candidate collection Sets the class of the query result objects to be returned (must be called)
setFilter() Sets the filter expression for the query N/A (throws a JDOUserException)
setOrdering() Sets the ordering expression for the query N/A (throws a JDOUserException)

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:

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"?>
  <package name="com.acme">
    <class name="AvgSalary" identity-type="nondurable" requires-extent="false"/>


     * 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.declareImports("import com.acme.Employee; import java.util.Date");
    q.declareParameters("Date startDate, Date endDate");

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