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();
}
}
}
Hibernate transforms this into a cross join which creates the same result as an inner join when you add the join condition to the WHERE statement. This approach has two disadvantages: It is not as easy to read as an inner join especially if the WHERE statement gets complex and is often the cause of additional bugs.
CROSS JOIN is the full cartesian product of the two sides of a JOIN. INNER JOIN is a reduction of the cartesian product—we specify a predicate and get a result where the predicate matches.
A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.
Most database engines will optimize the CROSS JOIN
with a WHERE clause to a JOIN
anyway, but I prefer to always use an explicit JOIN
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With