Why does Hibernate generate a CROSS JOIN for an implicit join of a @ManyToOne association?

java sql database hibernate cross-join
anirban picture anirban · Apr 21, 2015 · Viewed 15.4k times · Source

Baur & King said in their book:

Implicit joins are always directed along many-to-one or one-to-one association, never through a collection-valued association.

[P 646, Ch 14]

But when I am doing that in the code it is generating a CROSS JOIN instead of an INNER JOIN.

Mapping is from Member2 (many-to-one) -> CLub.

But Club2 has no information about members and Member2 is having a Foreign Key of Club2.

My query is

// Implicit: Find all UK club member who is female
Transaction t1 = HibernateUtil.begin();
Query query =
    HibernateUtil.getSession().createQuery("From Member2 m where m.club2.country = 'UK' ");
List<Member2> memList = query.list();
for (Member2 m : memList)
  System.out.println(m);
HibernateUtil.end(t1);

And, Hibernate is generating the following SQL query:

Hibernate: 
    select
        member2x0_.member_id as member_i1_1_,
        member2x0_.club_id as club_id5_1_,
        member2x0_.member_age as member_a2_1_,
        member2x0_.member_name as member_n3_1_,
        member2x0_.member_sex as member_s4_1_ 
    from
        TBL_MEMBER2 member2x0_ cross 
    join
        TBL_CLUB2 club2x1_ 
    where
        member2x0_.club_id=club2x1_.club_id 
        and club2x1_.country='UK'
Hibernate: 
    select
        club2x0_.club_id as club_id1_0_0_,
        club2x0_.club_name as club_nam2_0_0_,
        club2x0_.country as country3_0_0_ 
    from
        TBL_CLUB2 club2x0_ 
    where
        club2x0_.club_id=?
aaa 25 m
bbb 28 f

Club2.java

package com.lilu.de.onetomany.uni.other;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "TBL_CLUB2")
public class Club2 {

  @GeneratedValue(generator = "pkey_Club2", strategy = GenerationType.SEQUENCE)
  @SequenceGenerator(name = "pkey_Club2", initialValue = 1000, allocationSize = 10,
      sequenceName = "seq_pkey_Club2")
  @Id
  private int club_id;

  private String club_name;

  private String country;

  // private Set Member2 = new HashSet();

  public Club2() {
    super();
  }

  public Club2(String cname, String ccountry) {
    this.club_name = cname;
    this.country = ccountry;
  }

  @Override
  public String toString() {
    String temp = club_name + " " + country + " ";
    // Iterator<Member2> iter = Member2.iterator();
    // String mems = null;
    // while (iter.hasNext()) {
    // mems += iter.next();
    // }
    // temp += "\n" + mems;
    return temp;
  }

  public int getClub_id() {
    return club_id;
  }

  public void setClub_id(int club_id) {
    this.club_id = club_id;
  }

  public String getClub_name() {
    return club_name;
  }

  public void setClub_name(String club_name) {
    this.club_name = club_name;
  }

  public String getCountry() {
    return country;
  }

  public void setCountry(String country) {
    this.country = country;
  }

  /*
   * public Set<Member2> getMember2() { return Member2; }
   * 
   * public void setMember2(Set<Member2> Member2) { this.Member2 = Member2; }
   */
}

Member2.java

package com.lilu.de.onetomany.uni.other;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "TBL_MEMBER2")
public class Member2 {

  @GeneratedValue(generator = "pkey_member2", strategy = GenerationType.SEQUENCE)
  @SequenceGenerator(name = "pkey_member2", sequenceName = "seq_pkey_member2")
  @Id
  private int member_id;

  private String member_name;

  private int member_age;

  private char member_sex;

  @ManyToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = "club_id")
  private Club2 club2;

  public Member2() {
    super();
  }

  public Member2(String mname, int age, char sex) {
    this.member_name = mname;
    this.member_age = age;
    this.member_sex = sex;
  }

  public Club2 getClub2() {
    return club2;
  }

  public void setClub2(Club2 club2) {
    this.club2 = club2;
  }

  @Override
  public String toString() {
    return member_name + " " + member_age + " " + member_sex;
  }

  public String getMember_name() {
    return member_name;
  }

  public void setMember_name(String member_name) {
    this.member_name = member_name;
  }

  public int getMember_age() {
    return member_age;
  }

  public void setMember_age(int member_age) {
    this.member_age = member_age;
  }

  public char getMember_sex() {
    return member_sex;
  }

  public void setMember_sex(char member_sex) {
    this.member_sex = member_sex;
  }

  public int getMember_id() {
    return member_id;
  }

  public void setMember_id(int member_id) {
    this.member_id = member_id;
  }

}

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!--
  ~ Hibernate, Relational Persistence for Idiomatic Java
  ~
  ~ Copyright (c) 2010, Red Hat Inc. or third-party contributors as
  ~ indicated by the @author tags or express copyright attribution
  ~ statements applied by the authors.  All third-party contributions are
  ~ distributed under license by Red Hat Inc.
  ~
  ~ This copyrighted material is made available to anyone wishing to use, modify,
  ~ copy, or redistribute it subject to the terms and conditions of the GNU
  ~ Lesser General Public License, as published by the Free Software Foundation.
  ~
  ~ This program is distributed in the hope that it will be useful,
  ~ but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
  ~ or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
  ~ for more details.
  ~
  ~ You should have received a copy of the GNU Lesser General Public License
  ~ along with this distribution; if not, write to:
  ~ Free Software Foundation, Inc.
  ~ 51 Franklin Street, Fifth Floor
  ~ Boston, MA  02110-1301  USA
  -->
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://localhost:5432/hibernatedb1</property>
        <property name="connection.username">postgres</property>
        <property name="connection.password">ani155</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        <property name="format_sql">true</property> 

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

        <!-- Names the annotated entity class -->
        <mapping class="com.lilu.de.onetomany.uni.other.Club2"/>
        <mapping class="com.lilu.de.onetomany.uni.other.Member2"/>
       <!--   <mapping class="com.apal.mapping.onetoone.User"/> -->
    </session-factory>

</hibernate-configuration>

Test.java

package com.lilu.de.onetomany.uni.other;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class Test {
  public static void main(String[] args) {

    Test test = new Test();
    // test.setup();
    test.SelectQuery1Implicit();
    // test.SelectQuery2ExplicitFromClause();
    // test.SelectQuery3JoinFetch();
    // test.SelectQuery4ThetaJoin();
  }

  private void SelectQuery1Implicit() {
    // Implicit: Find all UK club member who is female
    Transaction t1 = HibernateUtil.begin();
    Query query =
        HibernateUtil.getSession().createQuery("From Member2 m where m.club2.country = 'UK' ");
    List<Member2> memList = query.list();
    for (Member2 m : memList)
      System.out.println(m);
    HibernateUtil.end(t1);
  }

  private void setup() {
    Transaction t1 = HibernateUtil.begin();
    Club2 c1 = new Club2("MulaRougne", "UK");
    Member2 m1 = new Member2("aaa", 25, 'm');
    Member2 m2 = new Member2("bbb", 28, 'f');
    m1.setClub2(c1);
    m2.setClub2(c1);
    HibernateUtil.getSession().save(c1);
    HibernateUtil.getSession().save(m1);
    HibernateUtil.getSession().save(m2);
    Club2 c2 = new Club2("Queen's Club", "UK");
    Club2 c3 = new Club2("Disney", "USA");
    Member2 m3 = new Member2("ccc", 32, 'm');
    Member2 m4 = new Member2("ddd", 23, 'm');
    m3.setClub2(c3);
    m4.setClub2(c3);
    HibernateUtil.getSession().save(m3);
    HibernateUtil.getSession().save(m4);
    HibernateUtil.getSession().save(c2);
    HibernateUtil.getSession().save(c3);


    /*
     * Club2 c1 = new Club2("MulaRougne", "UK"); Club2 c2 = new Club2("Queen's Club", "UK"); Club2
     * c3 = new Club2("Disney", "USA");
     * 
     * Member2 m1 = new Member2("aaa", 25, 'm'); Member2 m2 = new Member2("bbb", 28, 'f'); Member2
     * m3 = new Member2("ccc", 32, 'm'); Member2 m4 = new Member2("ddd", 23, 'm'); Member2 m5 = new
     * Member2("ee", 30, 'f');
     * 
     * c1.getMember2().add(m1); c1.getMember2().add(m2); c1.getMember2().add(m3);
     * 
     * c2.getMember2().add(m4); c2.getMember2().add(m5);
     * 
     * HibernateUtil.getSession().save(c2); HibernateUtil.getSession().save(m4);
     * HibernateUtil.getSession().save(m5);
     * 
     * HibernateUtil.getSession().save(c1); HibernateUtil.getSession().save(m1);
     * HibernateUtil.getSession().save(m2); HibernateUtil.getSession().save(m3);
     */
    HibernateUtil.end(t1);
  }


  private static class HibernateUtil {
    private static SessionFactory factory;
    private static Session session;
    static {
      factory = new Configuration().configure().buildSessionFactory();
    }

    public static Session getSession() {
      return session;
    }

    public static Transaction begin() {
      session = factory.openSession();
      return session.beginTransaction();
    }

    public static void end(Transaction tran) {
      tran.commit();
    }
  }
}

Answer

Vlad Mihalcea picture Vlad Mihalcea · Apr 21, 2015

Most database engines will optimize the CROSS JOIN with a WHERE clause to a JOIN anyway, but I prefer to always use explicit JOINs instead.

The CROSS JOIN is generated by the JOIN:

where m.club2.country = 'UK'

To avoid the second Club query you could write the query as follows:

Query query = session.createQuery("""
        select m 
        from Member2 m
        join fetch m.club2 c
        where
           c.country = :country
        """, Member2.class)
.setParameter("country", "UK");

This query will remove the CROSS JOIN and the secondary select while using bind parameters instead of hard-coded ones.

Related questions

java hibernate: selecting the discriminator column in polymorphic hql query

In hibernate, I want to select the discriminator value. Something like select discriminator, id, name, age from Animal The idea is to send the result of this query to the client side, so that I can display a different icon …

Read More
JOOQ vs Hibernate

When I chat to stackoverflowers on chat and read other tutorials about database with Java then they are guide me to use JOOQ instead of HIBERNATE. I am totally aware about ORM with Hibernate and I prefer to use Hibernate …

Read More
Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More