Using the dynamic query service

There are times in the development process when you might prefer to use the dynamic query service rather than the regular Enterprise JavaBean (EJB) query service (which can be referred to as deployment query). During testing, for instance, the dynamic query service can be used at application run time, so you do not have to re-deploy your application.

About this task

Following are common reasons for using the dynamic query service rather than the regular EJB query service:
  • You need to programmatically define a query at application run time, rather than at deployment.
  • You need to return multiple CMP or CMR fields from a query. (Deployment queries allow only a single element to be specified in the SELECT clause.) For more information, see the topic, Example: EJB queries.
  • You want to return a computed expression in the query.
  • You want to use value object methods or bean methods in the query statement. For more information, see the topic, Path expressions.
  • You want to interactively test an EJB query during development, but do not want to repeatedly deploy your application each time you update a finder or select query.

The dynamic query API is a stateless session bean; using it is similar to using any other J2EE EJB application bean. It is included in the com.ibm.websphere.ejbquery in the API package.

The dynamic query bean has both a remote and a local interface. If you want to return remote EJB references from the query, or if the query statement contains remote methods, you must use the query remote interface:

remote interface = com.ibm.websphere.ejbquery.Query
remote home interface = com.ibm.websphere.ejbquery.QueryHome

If you want to return local EJB references from the query, or if the query statement contains local methods, you must use the query local interface:

local interface = com.ibm.websphere.ejbquery.QueryLocal
local home interface = com.ibm.websphere.ejbquery.QueryLocalHome

Because it uses less application server memory, the local interface ensures better overall EJB performance than the remote.

Procedure

  1. Verify that the query.ear application file is installed on the application server on which your application is to run, if that server is different from the default application server created during installation of the product.
    The query.ear file is located in the app_server_root directory, where <WAS_HOME> is the location of the WebSphere Application Server. The product installation program installs the query.ear file on the default application server using a JNDI name of
    com/ibm/websphere/ejbquery/Query
    (You or the system administrator can change this name.)
  2. Set up authorization for the methods executeQuery(), prepareQuery(), and executePlan() in the remote and local dynamic query interfaces to control access to sensitive data. (This step is necessary only if your application requires security.)

    Because you cannot control which ASN names, CMP fields, or CMR fields can be used in a dynamic EJB query, you or your system administrator must place restrictions on use of the methods. If, for example, a user is permitted to run the executeQuery method, he or she can run any valid dynamic query. In a production environment, you certainly want to restrict access to the remote query interface methods.

  3. Write the dynamic query as part of your application client code. You can refer to the example topics, Remote interface dynamic query example, and Local interface dynamic query example, as query models; they illustrate which import statements to use.
  4. If the CMP you want to query is on a different module, you should:
    1. do a remote lookup on query.ear
    2. map the query.ear file to the server that the queried CMP bean is installed on.
  5. Compile and run your client program with the file qryclient.jar in the classpath.

Example

Example: Using the local interface for Dynamic query.When you run a dynamic Enterprise JavaBeans (EJB) query using the local interface, you are calling the executeQuery method on the QueryLocal interface. This interface does not initiate a transaction for the method; therefore you must explicitly establish a transaction context for the query to run.

Note: To establish a transaction context, the following example calls the begin() and commit() methods. An alternative to using these methods is simply embedding your query code within an EJB method that runs within a transaction context.

Begin your query code with the following import statements:

import com.ibm.websphere.ejbquery.QueryLocalHome;
import com.ibm.websphere.ejbquery.QueryLocal;
import com.ibm.websphere.ejbquery.QueryLocalIterator;
import com.ibm.websphere.ejbquery.IQueryTuple;
import com.ibm.websphere.ejbquery.QueryException;

Next, write your query statement in the form of a string, as in the following example that retrieves the names and ejb-references for underpaid employees:

String query = 
"select e.name, object(e) from EmpBean e where e.salary < 50000 ";

Create a QueryLocal object by obtaining a reference from the QueryLocalHome class. (This class defines the executeQuery method.) Note that in the following example, ejb/query is used as a local EJB reference pointing to the dynamic query JNDI name (com/ibm/websphere/ejbquery/Query):

InitialContext ic =  new InitialContext(); 
   QueryLocalHome  qh =  ( LocalQueryHome) ic.lookup( "java:comp/env/ejb/query" );
QueryLocal qb = qh.create();

The last portion of code initiates a transaction, calls the executeQuery method, and displays the query results. The QueryLocalIterator class is instantiated because it defines the query result set. This class is included in the Class QueryIterator API package. Keep in mind that the iterator loses validity at the end of the transaction; you must use the iterator in the same transaction scope as the executeQuery call.

userTransaction.begin();
QueryLocalIterator it = qb.executeQuery(query, null, null);
while (it.hasNext() ) { 
	IQueryTuple tuple = (IQueryTuple) it.next();
	System.out.print( it.getFieldName(1) );
	String s = (String) tuple.getObject(1);
	System.out.println( s);
	System.out.println( it.getFieldName(2) );
	EmpLocal e = ( EmpLocal ) tuple.getObject(2);
	System.out.println( e.getPrimaryKey().toString());
}
userTransaction.commit();

In most situations, the QueryLocalIterator object is demand-driven. That is, it causes data to be returned incrementally: for each record retrieval from the database, the next() method must be called on the iterator. (Situations can exist in which the iterator is not demand-driven. For more information, consult the "Local query interfaces" subsection of the Dynamic query performance considerations topic.)

Because the full query result set materializes incrementally in the application server memory, you can easily control its size. During a test run, for example, you may decide that return of only a few tuples of the query result is necessary. In that case you should use a call of the close() method on the QueryLocalIterator object to close the query loop. Doing so frees SQL resources that the iterator uses. Otherwise, these resources are not freed until the full result set accumulates in memory, or the transaction ends.

Example: Using the remote interface for Dynamic query.When you run a dynamic Enterprise JavaBeans (EJB) query using the remote interface, you are calling the executeQuery method on the Query interface. The executeQuery method has a transaction attribute of REQUIRED for this interface; therefore you do not need to explicitly establish a transaction context for the query to run.

Begin with the following import statements:

import com.ibm.websphere.ejbquery.QueryHome;
import com.ibm.websphere.ejbquery.Query;
import com.ibm.websphere.ejbquery.QueryIterator;
import com.ibm.websphere.ejbquery.IQueryTuple;
import com.ibm.websphere.ejbquery.QueryException;

Next, write your query statement in the form of a string, as in the following example that retrieves the names and ejb-references for underpaid employees:

String query = 
"select e.name as name , object(e) as emp from EmpBean e where e.salary < 50000"; 

Create a Query object by obtaining a reference from the QueryHome class. (This class defines the executeQuery method.) Note that for the sake of simplicity, the following example uses the dynamic query JNDI name for the Query object:

InitialContext ic =  new InitialContext(); 

Object obj =  ic.lookup("com/ibm/websphere/ejbquery/Query");

QueryHome  qh = 
 ( QueryHome) javax.rmi.PortableRemoteObject.narrow( obj, QueryHome.class );
Query qb = qh.create();

You then must specify a maximum size for the query result set, which is defined in the QueryIterator object, which is included in the Class QueryIterator. This class is included in the You then must specify a maximum size for the query result set, which is defined in the QueryIterator object, which is included in the QueryIterator API package. This example sets the maximum size of the result set to 99:

QueryIterator it = qb.executeQuery(query, null, null ,0, 99 );
The iterator contains a collection of IQueryTuple objects, which are records of the return collection values. Corresponding to the criteria of our example query statement, each tuple in this scenario contains one value of name and one value of object(e). To display the contents of this query result, use the following code:
while (it.hasNext() ) { 
	IQueryTuple tuple = (IQueryTuple) it.next();
	System.out.print( it.getFieldName(1) );
	String s = (String) tuple.getObject(1);
	System.out.println( s);
	System.out.println( it.getFieldName(2) );
	Emp e = ( Emp) javax.rmi.PortableRemoteObject.narrow( tuple.getObject(2), Emp.class );
	System.out.println( e.getPrimaryKey().toString());
}
The output from the program might look something like the following:
name Bob 
emp 1001
name Dave
emp 298003
...
Finally, catch and process any exceptions. An exception might occur because of a syntax error in the query statement or a run-time processing error. The following example catches and processes these exceptions:
} catch (QueryException qe) {
    System.out.println("Query Exception "+ qe.getMessage() );
}

Handling large result collections for the remote interface query

If you intend your query to return a large collection, you have the option of programming it to return results in multiple smaller, more manageable quantities. Use the skipRow and maxRow parameters on the remote executeQuery method to retrieve the answer in chunks. For example:

int skipRow=0;
int maxRow=100;
QueryIterator it = null;
do {
	it = qb.executeQuery(query, null, null ,skipRow, maxRow );
	while (it.hasNext() ) { 
	// display result 
	skipRow = skipRow + maxRow;
}
} while ( ! it.isComplete() ) ;



In this information ...


IBM Redbooks, demos, education, and more

(Index)

Use IBM Suggests to retrieve related content from ibm.com and beyond, identified for your convenience.

This feature requires Internet access.

Task topic    

Terms of Use | Feedback

Last updated: Oct 21, 2010 10:04:34 PM CDT
http://www14.software.ibm.com/webapp/wsbroker/redirect?version=compass&product=was-nd-mp&topic=tque_dynamic
File name: tque_dynamic.html