Example: Use the remote interface for Dynamic query

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 we 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 the 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 we intend the query to return a large collection, we 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() ) ;

Reference topic