JPA ManyToOne Additional Select

mikaelst · June 29, 2018, 2:13pm

Hi,

I have a legacy DB and i want to select an entity with children. But, i dont have foreign key and I join on “natural key”. But when I select an entity A with entities B hibernate does second select for A. NaturalId doesn’t help and with normal join (on keys) there is only one select.

Its a bug or no? Its look like hibernate doesn’t recognize that entity A its already fetch.

Thx for help @Naros on HipChat, but we didnt resolve problem.
Maybe @vlad

Thanks mikaelst

selects:


Hibernate: 
    select
        jpaunittes0_.id as id1_0_0_,
        set1_.id as id1_1_1_,
        jpaunittes0_.otherId as otherId2_0_0_,
        set1_.otherId as otherId2_1_1_,
        set1_.otherId as otherId2_1_0__,
        set1_.id as id1_1_0__ 
    from
        A jpaunittes0_ 
    left outer join
        B set1_ 
            on jpaunittes0_.otherId=set1_.otherId
Hibernate: 
    select
        jpaunittes0_.id as id1_0_0_,
        jpaunittes0_.otherId as otherId2_0_0_ 
    from
        A jpaunittes0_ 
    where
        jpaunittes0_.otherId=?

Code:

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Access;
import javax.persistence.AccessType;
import javax.persistence.CascadeType;
import javax.persistence.ConstraintMode;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.ForeignKey;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * This template demonstrates how to develop cos test case for Hibernate ORM, using the Java Persistence API.
 */
public class JPAUnitTestCase {

  private EntityManagerFactory entityManagerFactory;

  @Before
  public void init() {
    entityManagerFactory = Persistence.createEntityManagerFactory("templatePU");
  }

  @After
  public void destroy() {
    entityManagerFactory.close();
  }

  // Entities are auto-discovered, so just add them anywhere on class-path
  // Add your tests, using standard JUnit.
  @Test
  public void hhh123Test() throws Exception {
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();

    final A a = new A(123L);
    entityManager.persist(a);
    entityManager.persist(new B(a));
    entityManager.persist(new B(a));

    entityManager.getTransaction().commit();
    entityManager.close();

    EntityManager em = entityManagerFactory.createEntityManager();
    em.createQuery("select a from A a left join fetch a.set b")
        .getResultList();
    entityManager.close();
  }

  @Entity(name = "A")
  @Access(AccessType.PROPERTY)
  public static class A implements Serializable {

    private Long id;
    private Long otherId;
    private Set<B> set = new HashSet<>(0);

    A() {
    }

    public A(final Long otherId) {
      this.otherId = otherId;
    }

    @Id
    @GeneratedValue
    private Long getId() {
      return id;
    }

    private void setId(final Long id) {
      this.id = id;
    }

    private Long getOtherId() {
      return otherId;
    }

    private void setOtherId(final Long otherId) {
      this.otherId = otherId;
    }

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "cos")
    private Set<B> getSet() {
      return set;
    }

    private void setSet(final Set<B> set) {
      this.set = set;
    }
  }

  @Entity(name = "B")
  @Access(AccessType.PROPERTY)
  public static class B implements Serializable {

    private Long id;
    private A cos;

    B() {
    }

    public B(final A cos) {
      this.cos = cos;
    }

    @Id
    @GeneratedValue
    private Long getId() {
      return id;
    }

    private void setId(final Long id) {
      this.id = id;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "otherId", referencedColumnName = "otherId", foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
    private A getCos() {
      return cos;
    }

    private void setCos(final A cos) {
      this.cos = cos;
    }
  }
}

vlad · June 29, 2018, 3:06pm

Change this:

@ManyToOne(optional = false)

To this:

@ManyToOne(optional = false, fetch = FetchType.LAZY)

The default @ManyToOne EAGER fetching is bad for performance.

mikaelst · June 29, 2018, 3:08pm

Thx for reply.

I add fetch strategy but it doesnt work.

selects:

Hibernate: 
    select
        jpaunittes0_.id as id1_0_0_,
        set1_.id as id1_1_1_,
        jpaunittes0_.otherId as otherId2_0_0_,
        set1_.otherId as otherId2_1_1_,
        set1_.otherId as otherId2_1_0__,
        set1_.id as id1_1_0__ 
    from
        A jpaunittes0_ 
    left outer join
        B set1_ 
            on jpaunittes0_.otherId=set1_.otherId
Hibernate: 
    select
        jpaunittes0_.id as id1_0_0_,
        jpaunittes0_.otherId as otherId2_0_0_ 
    from
        A jpaunittes0_ 
    where
        jpaunittes0_.otherId=?

Code:

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Access;
import javax.persistence.AccessType;
import javax.persistence.CascadeType;
import javax.persistence.ConstraintMode;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.FetchType;
import javax.persistence.ForeignKey;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * This template demonstrates how to develop cos test case for Hibernate ORM, using the Java Persistence API.
 */
public class JPAUnitTestCase {

  private EntityManagerFactory entityManagerFactory;

  @Before
  public void init() {
    entityManagerFactory = Persistence.createEntityManagerFactory("templatePU");
  }

  @After
  public void destroy() {
    entityManagerFactory.close();
  }

  // Entities are auto-discovered, so just add them anywhere on class-path
  // Add your tests, using standard JUnit.
  @Test
  public void hhh123Test() throws Exception {
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();

    final A a = new A(123L);
    entityManager.persist(a);
    entityManager.persist(new B(a));
    entityManager.persist(new B(a));

    entityManager.getTransaction().commit();
    entityManager.close();

    EntityManager em = entityManagerFactory.createEntityManager();
    em.createQuery("select a from A a left join fetch a.set b")
        .getResultList();
    entityManager.close();
  }

  @Entity(name = "A")
  @Access(AccessType.PROPERTY)
  public static class A implements Serializable {

    private Long id;
    private Long otherId;
    private Set<B> set = new HashSet<>(0);

    A() {
    }

    public A(final Long otherId) {
      this.otherId = otherId;
    }

    @Id
    @GeneratedValue
    private Long getId() {
      return id;
    }

    private void setId(final Long id) {
      this.id = id;
    }

    private Long getOtherId() {
      return otherId;
    }

    private void setOtherId(final Long otherId) {
      this.otherId = otherId;
    }

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "cos")
    private Set<B> getSet() {
      return set;
    }

    private void setSet(final Set<B> set) {
      this.set = set;
    }
  }

  @Entity(name = "B")
  @Access(AccessType.PROPERTY)
  public static class B implements Serializable {

    private Long id;
    private A cos;

    B() {
    }

    public B(final A cos) {
      this.cos = cos;
    }

    @Id
    @GeneratedValue
    private Long getId() {
      return id;
    }

    private void setId(final Long id) {
      this.id = id;
    }

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "otherId", referencedColumnName = "otherId", foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
    private A getCos() {
      return cos;
    }

    private void setCos(final A cos) {
      this.cos = cos;
    }
  }
}

vlad · June 29, 2018, 3:11pm

Then, debug it to see who generated that query and you’ll know what to do next.

mikaelst · June 29, 2018, 3:13pm

Ok, how can i do that?
But for me its look like this statement generate two queries

em.createQuery(“select a from A a left join fetch a.set b”).getResultList();

You mean I should debug hibernate internals, yes?

vlad · June 29, 2018, 3:25pm

I just tested it in my own test repository and it works like a charm.

Just do a comparison debug between my test which also uses a non-PK @JoinColumn with @ManyToOne and, for this JPQL query:

Publication publication = entityManager.createQuery(
	"select p " +
	"from Publication p " +
	"join fetch p.book b " +
	"where " +
	"   b.isbn = :isbn and " +
	"   p.currency = :currency", Publication.class)
.setParameter( "isbn", "978-9730228236" )
.setParameter( "currency", "&" )
.getSingleResult();

There’s just one SQL query:

DEBUG [Alice]: n.t.d.l.SLF4JQueryLoggingListener - Name:DATA_SOURCE_PROXY, Time:0, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["select manytoonej0_.id as id1_1_0_, manytoonej1_.id as id1_0_1_, manytoonej0_.isbn as isbn5_1_0_, manytoonej0_.currency as currency2_1_0_, manytoonej0_.price_in_cents as price_in3_1_0_, manytoonej0_.publisher as publishe4_1_0_, manytoonej1_.author as author2_0_1_, manytoonej1_.isbn as isbn3_0_1_, manytoonej1_.title as title4_0_1_ from publication manytoonej0_ inner join book manytoonej1_ on manytoonej0_.isbn=manytoonej1_.isbn where manytoonej1_.isbn=? and manytoonej0_.currency=?"], Params:[(978-9730228236, &)]

So, there’s no bug. Just compare my test with yours and see where they differ.

mikaelst · June 29, 2018, 4:03pm

First of all, if hibernate can’t handle same outputs from queries where queries differs only in join on clause, its a bug (for me).

pk join:

select
        jpaunittes0_.id as id1_0_0_,
        set1_.id as id1_1_1_,
        jpaunittes0_.otherId as otherId2_0_0_,
        set1_.otherId as otherId2_1_1_,
        set1_.otherId as otherId2_1_0__,
        set1_.id as id1_1_0__ 
    from
        A jpaunittes0_ 
    left outer join
        B set1_ 
            on jpaunittes0_.id=set1_.otherId

non-pk join:

select
        jpaunittes0_.id as id1_0_0_,
        set1_.id as id1_1_1_,
        jpaunittes0_.otherId as otherId2_0_0_,
        set1_.otherId as otherId2_1_1_,
        set1_.otherId as otherId2_1_0__,
        set1_.id as id1_1_0__ 
    from
        A jpaunittes0_ 
    left outer join
        B set1_ 
            on jpaunittes0_.otherId=set1_.otherId
Hibernate: 
    select
        jpaunittes0_.id as id1_0_0_,
        jpaunittes0_.otherId as otherId2_0_0_ 
    from
        A jpaunittes0_ 
    where
        jpaunittes0_.otherId=?

Secondly, I check your test case and it is different. You don’t have OneToMany so for me it is different scenario and cant be compared or I am wrong.

At the end, rly, rly, thanks for responses. Great job guys, I am impressed how fast I get feedback on hipchat and forum

vlad · June 29, 2018, 4:14pm

Try to replicate it with this test case like this

http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/

And send a Pull Request when you are done.

mikaelst · June 29, 2018, 4:23pm

I based my test case on http://in.relation.to/2016/01/14/hibernate-jpa-test-case-template/, its ok?
And PR on github? hiberane orm? Should I create any issue on your jira?

vlad · June 29, 2018, 4:26pm

Yes. Please create a Jira issue too.

qray · June 4, 2019, 2:17pm

Hi, What was the reason of this issue? I have the same and I would like to know how I can solve it

Topic		Replies	Views
@manyToOne and °outer join" Hibernate ORM	1	1644	November 12, 2019
How hibernate\spring fetches entites in jparepository save method? Hibernate ORM	1	433	August 22, 2022
How to make lazy loading truly lazy (V5.4) Hibernate ORM	31	15065	March 12, 2021
OneToMany join on non PK Composite key - not mapped to a single property Hibernate ORM	8	16625	October 16, 2018
Fetch LAZY doesn't work when JoinColumn name is primary key Hibernate ORM	0	450	October 3, 2019

JPA ManyToOne Additional Select

Related topics