[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] To query an entity with filters on a OneToMany attribute gives a weird result
|
Hi Tom,
------------------------------------------------------------------------------------------------------------------------------------------------------------
"I'm surprised Hibernate can do this with out running extra SQL. What does
the
SQL output look like?"
------------------------------------------------------------------------------------------------------------------------------------------------------------
The main difference between EclipseLink and Hibernate in the sql queries
displayed in logs is that Hibernate uses a inner join whereas EclipseLink
joins the two tables with a clause WHERE.
You can see the two sql generated by Hibernate and EclipseLink.
Note that I have removed all fields retrieved and replaced them by
"table.*" and simplified the business conditions on the tables in order to
make the queries more understandable and confidentiality reasons.
sql generated by EclipseLink :
SELECT t0.*, t1.*
FROM PERSON t0, ADDRESS t1
WHERE ((t0.NAME = ?) AND (t1.NAME=?) AND (t1.IDPERSON = t0.ID) )
sql generated by Hibernate :
select person0_.*, address1_.*
from PERSON person0_ inner join ADDRESS address1_ on
person0_.id=address1_.idPersonn
where person0_.name=? and address1_ .name=?
------------------------------------------------------------------------------------------------------------------------------------------------------------
"Although we do not have explicit support for this kind of query, you could
probably do something like this:
1. define an extra unmapped list on your object
2. When you run your query use our event mechanism to populate the list.
(depending on what data is comes back from your query, you may be able to
do
this without running extra SQL."
------------------------------------------------------------------------------------------------------------------------------------------------------------
When you speak about "event mechanism", do your refer to the Session Events
or another thing ?
I have looked Session events and its method which could be useful :
postQuery(). But unfortunately, the solution is not adapted at all to my
needs.
To use this complex enough bypassing, one time for a single case could be
acceptable but I need to retrieve entity and one or several of its
relations in many cases. Begin to explode the query logic of several of my
dao at several places will create a undesirable high coupling between the
session customizer and entities and dao, and so increase the maintenance
cost.
Any less intrusive solution is possible ?
Cheers and Happy new year
David
Tom Ware
<tom.ware@oracle.
com> A
Envoyé par : eclipselink-users@xxxxxxxxxxx
eclipselink-users cc
-bounces@eclipse.
org Objet
Re: [eclipselink-users] To query an
entity with filters on a OneToMany
09/12/2011 15:37 attribute gives a weird result
Veuillez répondre
à
EclipseLink User
Discussions
<eclipselink-user
s@xxxxxxxxxxx>
There is no explicit support for this in the JPA spec. Is there an extra
annotation or query hint you have to provide to do this in hibernate?
I'm surprised Hibernate can do this with out running extra SQL. What does
the
SQL output look like?
Although we do not have explicit support for this kind of query, you could
probably do something like this:
1. define an extra unmapped list on your object
2. When you run your query use our event mechanism to populate the list.
(depending on what data is comes back from your query, you may be able to
do
this without running extra SQL.
-Tom
On 09/12/2011 6:21 AM, david.haccoun@xxxxxxx wrote:
> Tom,
>
> Thank you for your response.
>
> "o get a subset of the addresses, you should query for just addresses."
> You propose exactly the solution that i want to avoid : too many requests
> to retrieve data related between objects.
>
> "i.e. If you use that instance of Person later,
> how do you know what query was used to retrieve it and what parts of the
> addresses collection are properly populated"
> I don't agree. A service is called in a precise context. If I retrieve
one
> person with some of his addresses, it's because the client of my
> entiyService has this need. So, it would know what parts of the addresses
> collection are properly populated.
>
> For information, with Hibernate, this kind of fetch queries works well.
>
> The JPA specification says nothing to this subject ?
>
> David HACCOUN
> CNAV - DSI - DED
> Unité Droit et Service
> 01 55 45 60 45
> david.haccoun@xxxxxxx
>
>
>
> Tom Ware
> <tom.ware@oracle.
> com>
A
> Envoyé par : eclipselink-users@xxxxxxxxxxx
> eclipselink-users
cc
> -bounces@eclipse.
> org
Objet
> Re: [eclipselink-users] To query
an
> entity with filters on a
OneToMany
> 06/12/2011 19:12 attribute gives a weird result
>
>
> Veuillez répondre
> à
> EclipseLink User
> Discussions
> <eclipselink-user
> s@xxxxxxxxxxx>
>
>
>
>
>
>
> In an Object-Relational world, retrieving a Person and only some of it's
> addresses does not really make sense. If that kind of thing is allowed,
> you
> cannot trust the instance of Person you have found to represent the
Person
> as it
> is represented in the database. (i.e. If you use that instance of Person
> later,
> how do you know what query was used to retrieve it and what parts of the
> addresses collection are properly populated)
>
> To get a subset of the addresses, you should query for just addresses.
>
> select a From Person p join p.addresses a where......
>
> -Tom
>
> On 06/12/2011 12:36 PM, david.haccoun@xxxxxxx wrote:
>> Hello,
>>
>> I use EclipseLink for 9 months and so far no problem. Since I have the
>> need to query an entity with a OneToMany attribute, it's all the
> contrary.
>> It gives me a strange result.
>> I have simplified my entities until the maximum but the problem remains.
>>
>> I will explain my need which is ultra simple : I have two entities :
>> Person which has a bidirectional relation with Address.
>> Person has potentially several Addresses but an Address belongs to one
> and
>> only Person.
>>
>> In Classes, it gives that :
>>
>> @Entity
>> public class Person implements Serializable {
>>
>> @Id
>> private Long id;
>>
>> @OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
>> private Set<Address> addresses;
>>
>> // Getter and setter
>> ...
>>
>> }
>>
>>
>> @Entity
>> public class Address implements Serializable {
>>
>> @Id
>> private String idAddress;
>>
>> @ManyToOne(fetch = FetchType.LAZY)
>> @JoinColumn(name = "idPerson", referencedColumnName = "idPerson")
>> private Person person;
>>
>> // Getter and setter
>> ...
>> }
>>
>> I want to query personne with their adresses. All that with some
> conditions
>> on personne and adresse.
>> My simplified query "select pers FROM Person pers join pers.addresses
>> address
>> where pers.matricule=:matricule and
>> address.date=:dateContract"
>>
>> When i execute it, i retrieve the right person but with all addresses
>> linked (with foreign key) with this person. Even the addresses which
> don't
>> match with the dateContract condition.
>>
>> It seems that it's a problem related to the use of filtering on a
> oneToMany
>> attribute in my query. The problem is solved if i do several requests
but
>> it will give low performances as I have several requests like this.
>> I have tried with the oneToMany in eager initialization and with a
>> fetch-join query hint but i have got the same result.
>>
>> Thank you for having read me :)
>>
>> PS : I have written the code manually, so a little typo is not
impossible
>>
>> David
>>
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
