Kodo JDO Enterprise Edition allows you to execute SQL selects and stored procedures directly through the JDO query interface, retrieving matching objects rather than a low-level ResultSet. The only requirements are that your SQL selects all primary key columns, that it selects the class indicator column if present, and that it does not alias the columns to different names in the select statment. Kodo will take over the execution of the SQL and will scan the result set to determine which fields it can set in the objects it returns to you. If your select statement does not load all the data in the default or currently-configured fetch groups, Kodo will make additional trips to the datastore as needed to load the additional data.

// the SQL query language is 'kodo.jdbc.SQL'
Query q = pm.newQuery ("kodo.jdbc.SQL", null);

// set the type of objects that the method returns
q.setClass (Person.class);

// set the filter to the SQL to execute, using named placeholders for any 
// parameters you'd like to pass in
q.setFilter ("SELECT * FROM PERSON WHERE FIRSTNAME = first "
    + "AND LASTNAME = last");

// when you declare parameters, the parameter type is the JDBC type from
// java.sql.Types 
q.declareParameters ("VARCHAR first, VARCHAR last");

// this executes your SQL and transforms the result set into matching
// objects
Collection results = (Collection) q.execute ("Fred", "Lucas");

If you specify a SQL filter that does not begin with the SELECT keyword (regardless of case), Kodo will assume that you are executing a stored procedure and use the appropriate JDBC APIs for stored procedure calls rather than SQL selects. Parameter passing is done the same way for either SQL selects or stored procedure calls, as illustrated in the code sample above. Stored procedure OUT parameters are not supported; the stored procedure must return a single result set.

SQL queries can only be executed in the database; if you attempt to execute a SQL query under conditions that require in-memory evaluation, Kodo will throw an error.

The StoredProcMain driver program in the samples/ormapping directory of your Kodo distribution demonstrates a working SQL query.