Example: Dynamic query remote interface

When you run a dynamic 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. (See Class QueryIterator in Reference: Generated API documentation for more details.) 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. (See Class IQueryTuple in Reference: Generated API documentation for more details.) 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() ) ;

---

 

---