Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Why find() and Query on a single entity behave differently
PostPosted: Thu Feb 02, 2017 9:50 am 
Newbie

Joined: Thu Feb 02, 2017 9:38 am
Posts: 4
Hi,
I ended up in a weird case in which, if I call "find()" on an entity manager for a given id of an entity, I get a different results than using a Query object. By looking at the generated SQL, those are different: one is a single query using left outer joins, whereas the other is a sequence of selects.

Consider this example: class A has a eager list of B, which in turn has a eager list of C:

Code:
private long initData(){

        A a = new A();
        B b0 = new B();
        B b1 = new B();
        a.getListB().add(b0);
        a.getListB().add(b1);

        C c0 = new C();
        C c1 = new C();
        C c2 = new C();
        C c3 = new C();

        b0.getListC().add(c0);
        b1.getListC().add(c1);
        b1.getListC().add(c2);
        b1.getListC().add(c3);

        assertTrue(persistInATransaction(a, b0, b1, c0, c1, c2, c3));

        return a.getId();
    }


here, an A has 2 Bs. First B has 1 C, whereas the second B has 3 Cs.

The following test show the difference in behavior:

Code:
@Test
    public void test(){

        long id = initData();

        EntityManager em = factory.createEntityManager();
        Query query = em.createQuery("select a from A a where a.id="+id);

        System.out.println("\n* Going to execute JPQL query");
        A a = (A) query.getSingleResult();
        /*
            Hibernate: select a0_.id as id1_0_ from A a0_ where a0_.id=1
            Hibernate: select listb0_.A_id as A_id1_1_0_, listb0_.listB_id as listB_id2_1_0_, b1_.id as id1_2_1_ from A_B listb0_ inner join B b1_ on listb0_.listB_id=b1_.id where listb0_.A_id=?
            Hibernate: select listc0_.B_id as B_id1_3_0_, listc0_.listC_id as listC_id2_3_0_, c1_.id as id1_4_1_ from B_C listc0_ inner join C c1_ on listc0_.listC_id=c1_.id where listc0_.B_id=?
            Hibernate: select listc0_.B_id as B_id1_3_0_, listc0_.listC_id as listC_id2_3_0_, c1_.id as id1_4_1_ from B_C listc0_ inner join C c1_ on listc0_.listC_id=c1_.id where listc0_.B_id=?
         */

        assertEquals(2, a.getListB().size());

        em.clear();

        System.out.println("\n* Going to execute 'em.find'");
        A aFromFind = em.find(A.class, id);
        //Hibernate: select a0_.id as id1_0_0_, listb1_.A_id as A_id1_1_1_, b2_.id as listB_id2_1_1_, b2_.id as id1_2_2_, listc3_.B_id as B_id1_3_3_, c4_.id as listC_id2_3_3_, c4_.id as id1_4_4_ from A a0_ left outer join A_B listb1_ on a0_.id=listb1_.A_id left outer join B b2_ on listb1_.listB_id=b2_.id left outer join B_C listc3_ on b2_.id=listc3_.B_id left outer join C c4_ on listc3_.listC_id=c4_.id where a0_.id=?

        assertEquals(4, aFromFind.getListB().size());

        em.close();
    }


When I use Query to find A, it correctly gets 2 Bs. However, when I use "find", I get 4. I do understand this is an expected behavior when outer joins are involved (https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword), but still for me it is very surprising that Query and "find" behaves completely differently. Regardless of what should be the expected results (2 or 4 Bs), why do they behave differently? Can the fact that they are different be considered a bug? Or rather an expected behavior?

thanks


Top
 Profile  
 
 Post subject: Re: "find(...)" and query on single entity behave differently
PostPosted: Thu Feb 02, 2017 10:15 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
That's not weird at all.

The find method is using a LEFT JOIN because you have an EAGER association. EAGER associations are bad for performance, so switch to LAZY anyway.

The query method cannot use a LEFT JOIN unless you explicitly specify it. That's why you see a secondary query. Switch to LAZY, and that secondary query will not be issued at all, unless you navigate the association after the entity is fetched.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Why find() and Query on a single entity behave differently
PostPosted: Thu Feb 02, 2017 10:37 am 
Newbie

Joined: Thu Feb 02, 2017 9:38 am
Posts: 4
thanks for the prompt reply. So, if I understand correctly what you say, one should avoid using EAGER, as Hibernate does not (or cannot) handle it reliably (besides the obvious performance drop if you are not going to use those extra entities).

Still, if one does not know the internals of hibernate, the fact that

Code:
em.createQuery("select a from A a where a.id="+id)
em.find(A.class, id)


are different is not obvious... :(


Top
 Profile  
 
 Post subject: Re: Why find() and Query on a single entity behave differently
PostPosted: Thu Feb 02, 2017 11:19 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
Quote:
as Hibernate does not (or cannot) handle it reliably (besides the obvious performance drop if you are not going to use those extra entities).


No JPA provider handles EAGER fetching reliably. Even with plain JDBC, if you execute lots of unnecessary joins, you won't get a better performance.

Code:
em.createQuery("select a from A a where a.id="+id)
em.find(A.class, id)


You should know that the SQL injection vulnerability in your code snippet is a very big security risk. Don't do that ever!

Now, related to having this explanation in our User Guide, please add a Jira issue and add the link to this forum thread, thanks.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Why find() and Query on a single entity behave differently
PostPosted: Fri Feb 03, 2017 5:58 am 
Newbie

Joined: Thu Feb 02, 2017 9:38 am
Posts: 4
What I meant with "reliable" was not regarding performance, but rather functionality. If I have a entity X which i persist with "em.persist", then I find quite confusing that if I read it back with "em.find" I get a different result.

Regarding others JPA providers: I tried to run the same code with EclipseLink (2.6.4), and that acts reliably, ie:

Code:
em.createQuery("select a from A a where a.id=?1").setParameter(1, id);
em.find(A.class, id)


produce the same result, in contrast to Hibernate (5.2.6.Final)


Top
 Profile  
 
 Post subject: Re: Why find() and Query on a single entity behave differently
PostPosted: Fri Feb 03, 2017 6:17 am 
Newbie

Joined: Thu Feb 02, 2017 9:38 am
Posts: 4
I opened a Jira ticket at

https://hibernate.atlassian.net/browse/WEBSITE-487

cheers


Top
 Profile  
 
 Post subject: Re: Why find() and Query on a single entity behave differently
PostPosted: Mon Feb 06, 2017 3:47 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
Thanks. Issue is fixed, check out the commit.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.