Dynamic query API: prepareQuery() and executePlan() methods

Use these methods to more efficiently allocate the overhead associated with dynamic query. They are equivalent in function to the prepareStatement() and executeQuery() methods of the JDBC API.

To perform a dynamic EJB query, the application server must parse the query string into structured query language (SQL) at run time. We can, of course, eliminate run-time overhead by choosing to perform a standard EJB query instead of a dynamic query. Sometimes referred to as deployment queries, standard queries are parsed and built at deployment, then performed by a finder or select method.

Another option is to write code that redistributes dynamic query overhead for better application performance. Begin by calling the prepareQuery() method in place of the executeQuery() method. The prepareQuery() method parses and translates the query, and returns a string called a query plan. The plan contains the SQL statement produced by parsing and translation, as well as other information needed by the dynamic query API. Save this string in the application and call the executePlan() method with the string to run your query. (You also might want to use the prepareQuery() method simply to see the SQL translation product; just call the method and display the return value.)

Pass the parameters of the query as an array of type Object on the prepareQuery() and the executePlan() method calls. Ensure that you pass appropriate data types, because the application server validates your query according to parameter type (rather than actual values) when it processes the prepareQuery() method call.

Example code

In the example code that follows, the first executePlan() method call substitutes parms[0] for ?1. Hence the first query performed is functionally equivalent to the following query statement:

select e.name as name, object(e) as emp from EmpBean e where e.salary < 50000

The second call runs a query that is functionally equivalent to this statement:

select e.name as name, object(e) as emp from EmpBean e where e.salary < 60000

The example:

String query = 
"select e.name as name , object(e) as emp from EmpBean e where e.salary < ?1"; 
QueryIterator it = null;
Integer[] parms = new Integer[1];
parms[0] = new Integer(0);

In the call to prepareQuery(), pass any Integer value. Doing so defines ?1 as an Integer type, as in the following:

String queryPlan= qb.prepareQuery(query, parms, null );

   parms[0] = new Integer(50000);

Next you run the query with a real value of Integer(50000) for ?1:

select e.name as name, object(e) as emp from EmpBean e where e.salary < 50000it =
 qb.executePlan( queryPlan, parms, 0, 99);

parms[0] = new Integer(60000);

Run the query again with a different value of Integer(60000) for ?1:

it = qb.executePlan( queryPlan, parms, 0, 99);

Related tasks

Use the dynamic query service Reference topic