java.sql.ResultSet defines three possible result set types.

• forward-only : the result set is navegable forwards only
• scroll-sensitive : the result set is scrollable in both directions and is sensitive to changes in the datastore
• scroll-insensitive : the result set is scrollable in both directions and is insensitive to changes in the datastore

DataNucleus allows specification of this type as a query extension datanucleus.rdbms.query.resultSetType .

To do this on a per query basis for JDO you would do

query.addExtension("datanucleus.rdbms.query.resultSetType", "scroll-insensitive");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.rdbms.query.resultSetType", "scroll-insensitive");

The default is forward-only . The benefit of the other two is that the result set will be scrollable and hence objects will only be read in to memory when accessed. So if you have a large result set you should set this to one of the scrollable values.

When using a "scrollable" result set (see above for datanucleus.rdbms.query.resultSetType ) by default the query result will cache the rows that have been read. You can control this caching to optimise it for your memory requirements. You can set the query extension datanucleus.query.resultCacheType and it has the following possible values

• weak : use a weak hashmap for caching (default)
• soft : use a soft reference map for caching
• hard : use a HashMap for caching (objects not garbage collected)
• none : no caching (hence uses least memory)

To set this on a per query basis for JDO you would do

query.addExtension("datanucleus.query.resultCacheType", "weak");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.query.resultCacheType", "weak");

If you have a large result set you clearly don't want to instantiate all objects since this would hit the memory footprint of your application. To get the number of results many JDBC drivers will load all rows of the result set. This is to be avoided so DataNucleus provides control over the mechanism for getting the size of results. The persistence property datanucleus.query.resultSizeMethod has a default of last (which means navigate to the last object - hence hitting the JDBC driver problem). If you set this to count then it will use a simple "count()" query to get the size.

To do this on a per query basis for JDO you would do

query.addExtension("datanucleus.query.resultSizeMethod", "count");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.query.resultSizeMethod", "count");

When a transaction is committed by default all remaining results for a query are loaded so that the query is usable thereafter. With a large result set you clearly don't want this to happen. So in this case you should set the extension datanucleus.query.loadResultsAtCommit to false.

To do this on a per query basis for JDO you would do

query.addExtension("datanucleus.query.loadResultsAtCommit", "false");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.query.loadResultsAtCommit", "false");

When using optimistic transactions all updates to data are held until flush()/commit(). This means that executing a query may not take into account changes made during that transaction in some objects. DataNucleus allows a convenience of calling flush() just before execution of queries so that all updates are taken into account. The property name is datanucleus.query.flushBeforeExecution and defaults to "false".

To do this on a per query basis for JDO you would do

query.addExtension("datanucleus.query.flushBeforeExecution","true");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.query.flushBeforeExecution","true");

You can also specify this for all queries using a persistence property datanucleus.query.flushBeforeExecution which would then apply to ALL queries for that PMF/EMF.

DataNucleus provides a useful extension allowing control over the ResultSet's that are created by queries. You have at your convenience some properties that give you the power to control whether the result set is read only, whether it can be read forward only, the direction of fetching etc.

To do this on a per query basis for JDO you would do

query.addExtension("datanucleus.rdbms.query.fetchDirection", "forward");
query.addExtension("datanucleus.rdbms.query.resultSetConcurrency", "read-only");

To do this on a per query basis for JPA you would do

query.setHint("datanucleus.rdbms.query.fetchDirection", "forward");
query.setHint("datanucleus.rdbms.query.resultSetConcurrency", "read-only");

Alternatively you can specify these as persistence properties so that they apply to all queries for that PMF/EMF. Again, the properties are

• datanucleus.rdbms.query.fetchDirection - controls the direction that the ResultSet is navigated. By default this is forwards only. Use this property to change that.
• datanucleus.rdbms.query.resultSetConcurrency - controls whether the ResultSet is read only or updateable.

Bear in mind that not all RDBMS support all of the possible values for these options. That said, they do add a degree of control that is often useful.