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
(New page: {{EclipseLink_UserGuide |info=y |toc=n |eclipselink=y |eclipselinktype=JPA}} = Batch Reading Using exist and IN = Doc in process 02/24/11 <!-- ({{bug|298985}}) Typically batch readi...) |
|||
(One intermediate revision by the same user not shown) | |||
Line 6: | Line 6: | ||
− | = Batch Reading Using | + | = Batch Reading Using EXIST and IN = |
Doc in process 02/24/11 | Doc in process 02/24/11 | ||
Line 12: | Line 12: | ||
<!-- | <!-- | ||
− | |||
− | |||
Typically batch reading uses a join of the batch query to the source query. | Typically batch reading uses a join of the batch query to the source query. | ||
This join has some issues: | This join has some issues: | ||
− | * If a | + | * If a many-to-one or a many-to-many, using join causes duplicate rows to be selected. (A DISTINCT clause is used to filter rows for many-to-one, but duplicates are returned for many-to-many, because join table information is needed). |
* DISTINCT does not work with LOBs. | * DISTINCT does not work with LOBs. | ||
* DISTINCT may be less efficient on some databases than alternatives. | * DISTINCT may be less efficient on some databases than alternatives. | ||
Line 24: | 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. | ||
+ | |||
− | + | 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 | + | 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