Example: Dynamic query local interface

When you run a dynamic 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 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. (See Class QueryIterator in Reference: Generated API documentation for more details.) Keep in mind that the iterator loses validity at the end of the transaction; 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, one 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.

---

 

---