Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Batch Reading"

 
Line 22: Line 22:
 
* Needs verification if works with pagination.
 
* Needs verification if works with pagination.
  
Two new configuration options are introduced in EclipseLink 2.1.  One such option is to use an EXISTS with a sub-select instead of a JOIN.  This should not result in duplicate rows, so avoid issues with DISTINCT.
+
Two new configuration options are introduced in EclipseLink 2.1.   
  
Another option is to load the target objects using a IN clause containing the source query object's primary keys.  This would also work with cursors, but as the limitation of requiring custom SQL support for composite primary keys, and produces a large dynamic SQL query.
+
* Use an EXISTS with a sub-select instead of a JOIN.  This should not result in duplicate rows, so avoid issues with DISTINCT.
  
A new BatchFetchType enum will be define and the usesBatchReading flag will enhance to setBatchFetch allowing for JOIN, EXISTS or IN.  This option will also be added to ObjectLevelReadQuery, rolling up the current 4 batch reading properties into a new BatchFetchPolicy, also moving them up from ReadAllQuery to allow ReadObjectQuery to also specify nested batched attributes.  A new BatchFetch annotation and query hint will be added.
+
* Load the target objects using an IN clause containing the source query object's primary keys.  This also works with cursors, but as the limitation of requiring custom SQL support for composite primary keys, and produces a large dynamic SQL query.
  
The EXISTS option will operate very similar to the existing JOIN batching, just putting the same join condition inside a sub-select.  Although it should have be straight forward, I hit several issues with our current sub-selects support that I had to debug and fix.  This also lead to discovering some issues with our JPQL support, that also needed to be fixed.  EXISTS supports all of the mappings the same as JOIN, but does not require a distinct.  m-1 will not use a DISTINCT by default for EXISTS (even though it would avoid duplicates, as the reason for using the EXISTS is normally to avoid the distinct), but the distinct can be enabled on the originating query if desired.  EXISTS will still select duplicates for m-m, and not work well with cursors.
 
  
The IN option will query a batch of the target objects using an SQL IN clause containing the key values.  For a 1-1 the foreign keys from the source rows will be extracted, if these are the primary keys of the target, they will first be filtered by checking the cache for each object.  The remaining keys will be split into batches of a query configurable size, defaulting to 256.  For composite keys the multi array SQL syntax for ((key1, key2), (...)) IN ((:key1, :key2), (...)) will be used.  Only some databases support this syntax, so composite primary keys will only be supported on some databases.  For 1-m or m-m the source rows do not contain the primary keys of the target objects.  The IN will still be supported, but will be based on join the source query as in JOIN (or maybe EXISTS?) for the set of keys.  For cursors the IN size will default to the cursor pageSize, and each cursor page will be processed separately.
+
There is a new <tt>BatchFetchType</tt> enum. The <tt>usesBatchReading</tt> flag is enhanced to <tt>setBatchFetch</tt>. This allows for JOIN, EXISTS or IN
 +
This option is also in ObjectLevelReadQuery, rolling up the current four batch reading properties into a new BatchFetchPolicy, also moving them up from ReadAllQuery to allow ReadObjectQuery to also specify nested batched attributes.  A new BatchFetch annotation and query hint will be added.
 +
 
 +
The EXISTS option operate similarly to the existing JOIN batching; )except that?) the same join condition is inside a sub-select. 
 +
 
 +
EXISTS supports all of the mappings the same as JOIN, but does not require a distinct.  Many-to-one does not use a DISTINCT by default for EXISTS (even though it would avoid duplicates, as the reason for using the EXISTS is normally to avoid the distinct), but the distinct can be enabled on the originating query if desired.  EXISTS still selects duplicates for many-to-many. EXISTS does not work well with cursors.
 +
 
 +
The IN option queries a batch of the target objects using an SQL IN clause containing the key values.  For a one-to-one mapping, the foreign keys from the source rows are extracted. If these are the primary keys of the target, they are first filtered by checking the cache for each object.  The remaining keys are split into batches of a query configurable size, defaulting to 256.  For composite keys, the multi array SQL syntax for <tt>((key1, key2), (...)) IN ((:key1, :key2), (...))</tt> is used.  There is only some databases support this syntax, so composite primary keys are supported only on some databases.  For one-to-many or many-to-many, the source rows do not contain the primary keys of the target objects.  The IN is still supported, but it is now based on join the source query as in JOIN (or maybe EXISTS?) for the set of keys.  For cursors, the IN size defaults to the cursor <tt>pageSize</tt>, and each cursor page is processed separately.
  
 
-->
 
-->

Latest revision as of 12:03, 25 February 2011

EclipseLink JPA

link="http://wiki.eclipse.org/EclipseLink"
EclipseLink
Website
Download
Community
Mailing ListForumsIRC
Bugzilla
Open
Help Wanted
Bug Day
Contribute
Browse Source


Batch Reading Using EXIST and IN

Doc in process 02/24/11


Eclipselink-logo.gif
Version: 2.2.0 DRAFT
Other versions...