Total Pageviews

Friday, January 20, 2012

Iterating over result list from a JPA query

Sometimes when using EJB 3.x, it is necessary to use JPA QL or SQL native queries to fetch objects from the database. It might be used to achieve better performance or to just fetch a particular set of attributes of the object but not the complete object with all its dependencies.

There's one thing you have to keep in mind when doing this: The result list containing the object will be an array of objects!

"The SELECT clause queries more than one column or entity, the results are aggregated in an object array (Object[]) in the java.util.List returned by getResultList( )"

Working example:
 Query query = manager.createQuery("SELECT v1.bitbit, v1.numnum, v1.someTime, t1.username, t1.anotherNum FROM MasatosanTest t1 JOIN MasatoView v1 ON v1.username = t1.username;");  
   List results = query.getResultList( ); // Fetches list containing arrays of object  
   Iterator it = results.iterator( );  
   while (it.hasNext( )) {  
     Object[] result = (Object[]); // Iterating through array object   
     Boolean first = (Boolean) result[0]; // Fetching the field from array  
     /* Likewise for all the fields, casting accordingly to the sequence in SELECT query*/  

There's is even the possibility to avoid casting completely by using a constructor expression with the appropriate arguments in the SELECT section:
 SELECT new org.somepackage.XEntity(x.a, x.b) FROM XEntity x  

Remember to declare the appropriate constructor.
The code fragments and the solution in this blog post was taken from this question on stackoverflow.