Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Batch Reading"
< EclipseLink | UserGuide | JPA | Basic JPA Development | Querying
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. | + | Two new configuration options are introduced in EclipseLink 2.1. |
− | + | * Use an EXISTS with a sub-select instead of a JOIN. This should not result in duplicate rows, so avoid issues with DISTINCT. | |
− | + | * 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 IN option will query a batch of the target objects using an SQL IN clause containing the key values. For a | + | 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
EclipseLink | |
Website | |
Download | |
Community | |
Mailing List • Forums • IRC • mattermost | |
Issues | |
Open • Help Wanted • Bug Day | |
Contribute | |
Browse Source |
Batch Reading Using EXIST and IN
Doc in process 02/24/11