-----Original Message-----
From:
eclipselink-users-bounces@xxxxxxxxxxx [mailto:
eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of James Sutherland
Sent: Dienstag, 29. Juni 2010 17:34
To:
eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] [TopLink Essentials] JPA-QL query returnstoo many details (was: "@SqlResultSetMappingJOINed details are gettingignored")
When you perform a select for an Entity in JPA, the real Entity object must
be returned. This is a managed object, and is assumed to reflect the state
of the object in the database, not of a query. If you then update, merge,
or delete this object, the real object must be modified. This object will
also be cached, either shared, or in the persistence context to be used by
other queries and other users, show must represent the real state.
JPA does not allow this, but EclipseLink does. You can set the
"eclipselink.maintain-cache" query hint to "false", and this will build the
results directly from the database, and ignore the cache and the persistence
context (so is read-only).
However you still need to get the correct data you want joined for the 1-m.
If you just do a normal fetch-join in JPQL, you cannot alias the fetch-join,
so you need to do another join for the filter of the 1-m. There is no way
do to this in EclipseLink's current JPQL support, please log a bug for this.
You can define this using EclipseLink expressions. You just use the same
_expression_ anyOf for the join-fetch and in the where clause. It might work
using Criteria as well.
Using SQL you can have EclipseLink use join-fetching. You just need to do a
native query for the Entity class and add the query hint to join-fetch the
1-m and ensure your SQL returns the 1-m data.
Markus KARG-3 wrote:
>
> Ok the proposed loop works well. Thanks for the help. :-)
>
> Next Problem:
>
> I just noticed that I don't need the query to be actually a native one, so
> I replaced it by a equivalent JPA-QL query:
>
> SELECT m FROM Master m JOIN m.details d WHERE m.x = ? AND d.y = ?
>
> And guess what, I am running into exactly the same problem of getting ALL
> details for each master but not just those fulfilling "d.y = ?"! ;-(
>
> So do I have to do the same trick that I did in native SQL here, too?
>
> Or is there a JPA trick that I have to do to tell the entity manager to
> return only what I actually wrote in JPA QL?
>
> Thanks!
>
> Markus
>
>
>
> From:
eclipselink-users-bounces@xxxxxxxxxxx
> [mailto:
eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Christopher
> Delahunt
> Sent: Donnerstag, 24. Juni 2010 15:07
> To: EclipseLink User Discussions
> Subject: Re: [eclipselink-users] FW: [TopLink Essentials]
> @SqlResultSetMappingJOINed details are gettingignored
>
>
>
> The problem with custom SQL returning Entities are that they managed
> entities, and such that incomplete data built into an entity remains in
> the cache (or the full managed object returned if it is already in the
> cache). Entities are not data objects that can be left incomplete. What
> it seems like you need would be a JPQL constructor type query but for
> native SQL.
>
> The results from the query should still give the object pairs you
> requested, assuming you have set the query to return the Master and Detail
> objects from the join. The application will need to process over the
> results to get all the Master->Detail pairings returned from the results,
> much like it would for rows when returning raw data.
>
> Best Regards,
> Chris
>
> On 24/06/2010 8:49 AM, Markus Karg wrote:
>
> Today for the first time now I had to use @SqlResultSetMapping. While
> everything works well there so far, there still is one issue I would like
> to solve. Possibly you can help me. :-)
>
>
>
> I am executing a typical master-detail JOIN:
>
>
>
> SELECT Master.PK_A, Detail.PK_B FROM Master JOIN Detail ON (Master.PK_A =
> Detail.FK_A) WHERE Master.X = ? AND Detail.Y = ?
>
>
>
> The result is exactly what I expected: A table containing all details
> having Y = ? and their corresponding masters.
>
>
>
> But actually I want to get not fields but entity instances, so I added
> SqlResultSetMapping for Master and Detail, which both are annotated with
> @Entity, @Table, @Column, etc.
>
>
>
> The result is not exactly what I want get…: The "AND Detail.Y = ?" has no
> effect on that details linked with each master.
>
>
>
> When iterating over the Master.details collection which was automatically
> filled by TopLink behind the scenes thanks to the @OneToMany relationship,
> I notice that ALL the master's details are contained - not just those with
> actually "Detail.Y = ?" as expected. Looking at the SQL TopLink does, it
> is rather clear why: The linked details are not filled from just my SQL's
> ResultSet, but actually TopLink executes another (automatically created)
> SQL to get ALL details per Master.
>
>
>
> This is not what I want.
>
>
>
> How to tell TopLink that it shall populate the linked details of each
> master with just the rows found in MY OWN SQL but not execute an
> unfiltered one?
>
>
>
> Thanks!
>
> Markus
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink ,
http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki:
http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums:
http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book:
http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
--
View this message in context:
http://old.nabble.com/FW%3A--TopLink-Essentials--%40SqlResultSetMapping-JOINed-details-are-gettingignored-tp28982131p29025074.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
