Lazy ResultSet Loading

Since 3.2.6rc1

By default, when a multiobject finder or ejbSelect method is executed the ResultSet is read till the end immediately. The client receives a collection of javax.ejb.EJB[Local|Local]Objects or CMP field values which is then used to iterate through. For big results (big collections) this approach is not efficient and it would be better to read the ResultSet when you need to read the next value from the collection. Fortunately, it is possible and all you need to do is to add <lazy-resultset-loading>true</lazy-resultset-loading> to the <query> element in jbosscmp-jdbc.xml. For example

<query>
   <query-method>
      <method-name>findAll</method-name>
   </query-method>
   <jboss-ql><![CDATA[select object(o) from A o\]\]\></jboss-ql>
   <lazy-resultset-loading>true</lazy-resultset-loading>
</query>

or for dynamic ejbSelect method

<query>
   <query-method>
      <method-name>ejbSelectLazySelect</method-name>
      <method-params>
         <method-param>java.lang.String</method-param>
         <method-param>java.lang.Object[]</method-param>
      </method-params>
   </query-method>
   <dynamic-ql></dynamic-ql>
   <lazy-resultset-loading>true</lazy-resultset-loading>
</query>

The are some issues you should be aware of when using lazy ResultSet loading. The result of a multiobject finder is of type java.util.Collection which has such methods like

add(Object o), remove(Object o), size() and iterator(). How do these work?

size()

Since java.sql.ResultSet has no size() method, we can't know the size untill we read the whole ResultSet. Therefore, as the first column in the result we 'SELECT COUNT()' of the query's result in a subselect. I.e. let's say we have the following EJBQL query

select object(o) from Address o where o.state='CA'

Normally, this query would be translated to SQL as

SELECT t0_o.id FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')

In case of lazy ResultSet loading this query will be translated to

SELECT (select count() from ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')), t0_o.id FROM ADDRESSEJB t0_o WHERE (t0_o.state = 'CA')

Now we have the correct size in the first column.

add(Object o)

If the first java.util.Iterator obtained from the lazy collection is not yet exhausted (in other words, the ResultSet has not been read till the end yet), this operation will throw java.lang.IllegalStateException("Can't modify collection while the first iterator is not exhausted."). When the first iterator is exhausted, i.e. ResultSet is exhausted and closed, you

can modify the collection by adding new elements.

remove(Object o)

The same as for add(Object o). But you can remove elements on the first iteration with java.util.Iterator.remove().

iterator()

The first call to java.util.Collection.iterator() will return a special iterator. You should not call java.util.Collection.iterator() again on the same lazy collection untill the first iterator is exhausted. After the first iterator is exhausted there are no other restrictions than those in the javadoc for java.util.Collection and java.util.Iterator.

Examples of EJBQL queries and their SQL translations with lazy-resultset-loading enabled

Compound primary key examples