Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Cartesian products when lazy loading collection
PostPosted: Thu Sep 08, 2016 7:42 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
Hello!

Sorry for my bad English.

There are a set of classes:
DBObject annotated @MappedSuperclass is not a separate entity, but it inherited from other classes.
Code:
@MappedSuperclass
public class DBObject {
   
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
    protected Integer id;
   
    @Column(name = "correctdt")
    @Temporal(TemporalType.TIMESTAMP)
    protected Date correctDate;

   /* Getters, setters and other properties, shared for all Entities */
}


BuildingList - class "List". It contains a collection of "List items".
Code:
/*Class with collection*/
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "TB_BUILDING_LIST")
@SequenceGenerator(name = "seq",
            sequenceName = "sq_building_list_id",
            allocationSize = 1)
public class BuildingList extends DBObject implements Serializable {

    @Column
    private String name;   
   
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "list")
    @LazyCollection(LazyCollectionOption.EXTRA)
    //@Fetch(FetchMode.JOIN)
    private Collection<BuildingListEntry> entries;
   
   /* Getters, setters and other properties */
}


BuildingListEntry - class "list item".
Code:
/*Main class*/
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "TB_BUILDING_LIST_ENTRY")
@SequenceGenerator(name = "seq",
            sequenceName = "sq_building_list_entry_id",
            allocationSize = 1)
public class BuildingListEntry extends DBObject implements Serializable {


    @Column   
    protected String comments;
   
    @Column
    protected Integer priority;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "list_id")
    protected BuildingList list;

    @ManyToOne
    @JoinColumn(name = "correctuid")
    protected User correctUser;
   
   /* Getters, setters and other properties */
}


BLEHome and BLENonLiving expand BuildingListEntry class.
Code:
/* Child class */
@Entity
@Table(name = "TB_BLE_HOME")
public class BLEHome extends BuildingListEntry {

    @Column(name = "has_ctv")   
    protected Integer hasCtv;
   
   /* Getters and setters */
}


Using inheritance strategy "Table Per Subclass".
In class BuildingList collection of BuildingListEntry as Lazy.

When accessing collections after loading the list, formed sql-query with the Cartesian product, where there is no connection between the tables.

Code:
SELECT entries0_.list_id AS list_id8_20_0_,
  entries0_.id AS id1_20_0_,
  entries0_.id AS id1_20_1_,
  entries0_.correctdt AS correctdt2_20_1_,
  entries0_.building_id AS building_id6_20_1_,
  entries0_.comments AS comments3_20_1_,
  entries0_.correctuid AS correctuid7_20_1_,
  entries0_.creation_date AS creation_date4_20_1_,
  entries0_.list_id AS list_id8_20_1_,
  entries0_.priority AS priority5_20_1_,
  entries0_.resp_person_id AS resp_person_id9_20_1_,
  entries0_1_.has_ctv AS has_ctv1_12_1_,
  entries0_2_.crm_client_id AS crm_client_id6_13_1_,
  entries0_2_.delay_reason AS delay_reason1_13_1_,
  entries0_2_.delayed AS delayed2_13_1_,
  entries0_2_.due_date AS due_date3_13_1_,
  entries0_2_.install_info AS install_info4_13_1_,
  CASE
    WHEN entries0_1_.id IS NOT NULL
    THEN 1
    WHEN entries0_2_.id IS NOT NULL
    THEN 2
    WHEN entries0_.id IS NOT NULL
    0 THEN
  END AS clazz_1_
FROM TB_BUILDING_LIST_ENTRY entries0_,
  TB_BLE_HOME entries0_1_,
  TB_BLE_NONLIVING entries0_2_
WHERE entries0_.list_id =?


What am I doing wrong?

If collection annotated @Fetch (FetchMode.JOIN), it will be correctly loaded immediately upon loading the object.
But I would like it to load on demand, as collection has in most cases not needed.


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Thu Sep 08, 2016 11:10 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 890
What if you remove @LazyCollection(LazyCollectionOption.EXTRA)?

What version of Hibernate are you using?

_________________
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: Cartesian products when lazy loading collection
PostPosted: Thu Sep 08, 2016 11:25 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
Hibernate 5.2.2.
Removing @LazyCollection(LazyCollectionOption.EXTRA) does not change anything.


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Fri Sep 09, 2016 3:46 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 890
Ok, I created the following test on GitHub using your entity classes.

When I execute:

Code:
doInHibernate( this::sessionFactory, session -> {
    List<BuildingList> buildingLists = session
        .createQuery( "from BuildingList" )
        .getResultList();
       
    BuildingList buildingList = buildingLists.get( 0 );
    assertEquals(2, buildingList.getEntries().size());
});


Hibernate generates the following queries:

Code:
select
    joinedinhe0_.id as id1_2_,
    joinedinhe0_.correctDate as correctD2_2_,
    joinedinhe0_.name as name3_2_
from
    TB_BUILDING_LIST joinedinhe0_
   
select
    entries0_.list_id as list_id5_3_0_,
    entries0_.id as id1_3_0_,
    entries0_.id as id1_3_1_,
    entries0_.correctDate as correctD2_3_1_,
    entries0_.comments as comments3_3_1_,
    entries0_.list_id as list_id5_3_1_,
    entries0_.priority as priority4_3_1_,
    entries0_1_.has_ctv as has_ctv1_0_1_,
    entries0_2_.delayed as delayed1_1_1_,
    case
        when entries0_1_.id is not null then 1
        when entries0_2_.id is not null then 2
        when entries0_.id is not null then 0
    end as clazz_1_
from
    TB_BUILDING_LIST_ENTRY entries0_
left outer join
    TB_BLE_HOME entries0_1_
        on entries0_.id=entries0_1_.id
left outer join
    TB_BLE_NONLIVING entries0_2_
        on entries0_.id=entries0_2_.id
where
    entries0_.list_id=?


So, there's no Cartesian Product since Hibernate uses LEFT JOIN for these associations.

The only things that I changed are:

- I removed the @LazyCollection(LazyCollectionOption.EXTRA)
- I initialized the Collection with ArrayList since null does not make much sense for Collections.

_________________
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: Cartesian products when lazy loading collection
PostPosted: Fri Sep 09, 2016 7:52 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
Thank You. I see that is a problem in my project, and not in Hibernate
Unfortunately, nothing helps. If this collection is loaded separately, the queries are formed correctly. I check this problem later on other entities in this project.


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Thu Sep 15, 2016 3:31 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
Another attempt to solve this problem.
I created a very simple project using Spring 4.2.5 and Hibernate 5.2.2 and added 4 entities similar to the previous example.
To simplify the example, DAO layer is absent. Loading collection as previously generates an incorrect query without a left outer join.
It may be an error in the configuration file of Spring or Hibernate?

Spring context
Code:
<?xml version="1.0" encoding="UTF-8"?>
<beans
       xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
">

    <context:annotation-config />
   
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@ndbs.vrn.ru:1521:ICDBS" />
        <property name="username" value="devman" />
        <property name="password" value="ecnh0qcndf" />
    </bean>
   
    <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:hibernate.cfg.xml" />
    </bean>
   
    <tx:annotation-driven transaction-manager="transactionManager" />
    <bean id="transactionManager"
          class="org.springframework.orm.hibernate5.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

</beans>


Hibernate configuration file
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!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>
    <property name="dialect">org.hibernate.dialect.Oracle9iDialect</property>
    <property name="show_sql">true</property>
    <property name="connection.pool_size">25</property>
    <property name="connection.autocommit">true</property>   
   
    <mapping class="TestClass"/>
    <mapping class="TestParent"/>
    <mapping class="TestChild1"/>
  </session-factory>
</hibernate-configuration>


Entities:
Code:
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@MappedSuperclass
public class DBObject {
   
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
    protected Integer id;
   
    @Column(name = "correctdt")
    @Temporal(TemporalType.TIMESTAMP)
    protected Date correctDate;

    @Override
    public boolean equals(Object o) {
        boolean result = false;
        if (o != null && (o instanceof DBObject)) {
            DBObject oo = (DBObject) o;
            if (id != null) {
                result = id.equals(oo.id);
            }
        }
       
        return result;
    }

    @Override
    public int hashCode() {
        return (id != null) ? id.hashCode() : 0;
    }

    public Integer getId() {
        return this.id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Date getCorrectDate() {
        return this.correctDate;
    }

    public void setCorrectDate(java.util.Date correctDate) {
        this.correctDate = correctDate;
    }
}



import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Entity;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "tmp_test_class")
@SequenceGenerator(name = "seq",
            sequenceName = "sq_test_class_id",
            allocationSize = 1)
public class TestClass extends DBObject implements Serializable{

    private static final long serialVersionUID = -8753086349251775837L;

    @OneToMany(mappedBy = "testClass")
    protected Collection<TestParent> tests = new ArrayList<>();

    public Collection<TestParent> getTests() {
        return tests;
    }

    public void setTests(Collection<TestParent> tests) {
        this.tests = tests;
    }

}



import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "tmp_test_parent")
@SequenceGenerator(name = "seq",
            sequenceName = "sq_test_entry_id",
            allocationSize = 1)
public class TestParent extends DBObject implements Serializable{

    private static final long serialVersionUID = -8753086349251775837L;

    @Column
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "test_id")
    protected TestClass testClass;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public TestClass getTestClass() {
        return testClass;
    }

    public void setTestClass(TestClass testClass) {
        this.testClass = testClass;
    }
   
}



import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

@Entity
@Table(name = "tmp_test_child1")
public class TestChild1 extends TestParent{

    private static final long serialVersionUID = -2363194053234011840L;
   
    @Column
    private String field1;

    public String getField1() {
        return field1;
    }

    public void setField1(String field1) {
        this.field1 = field1;
    }
   
}



import java.util.Collection;
import org.hibernate.SessionFactory;
import org.springframework.context.support.GenericXmlApplicationContext;


Class, where I test collection loading:
Code:
public class MainClass {

    public static void main(String[] args) throws Exception {

        GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
        ctx.load("classpath:applicationContext.xml");
        ctx.refresh();       
        SessionFactory sessionFactory = (SessionFactory)ctx.getBean("sessionFactory");
       
        TestClass test = sessionFactory.openSession().get(TestClass.class, 10098);                       
        Collection<TestParent> tests = test.getTests();
       
        System.out.println(tests.size());
       
    }
}


SQL-queries:
Code:
SELECT testclass0_.id   AS id1_1_0_,
  testclass0_.correctdt AS correctdt2_1_0_
FROM tmp_test_class testclass0_
WHERE testclass0_.id=?

SELECT tests0_.test_id AS test_id4_2_0_,
  tests0_.id           AS id1_2_0_,
  tests0_.id           AS id1_2_1_,
  tests0_.correctdt    AS correctdt2_2_1_,
  tests0_.name         AS name3_2_1_,
  tests0_.test_id      AS test_id4_2_1_,
  tests0_1_.field1     AS field1_0_1_,
  CASE
    WHEN tests0_1_.id IS NOT NULL
    THEN 1
    WHEN tests0_.id IS NOT NULL
    THEN 0
  END AS clazz_1_
FROM tmp_test_parent tests0_,
  tmp_test_child1 tests0_1_
WHERE tests0_.test_id=?


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Thu Sep 22, 2016 7:02 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
It was found that the problem is present only on Oracle. The problem is not seen on the MySQL. This is a bug or need a special configuration for Oracle?


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Fri Sep 23, 2016 4:57 am 
Newbie

Joined: Thu Sep 08, 2016 4:31 am
Posts: 6
After changing property dialect from org.hibernate.dialect.Oracle9iDialect to org.hibernate.dialect.Oracle10gDialect problem was solved.


Top
 Profile  
 
 Post subject: Re: Cartesian products when lazy loading collection
PostPosted: Fri Sep 23, 2016 8:36 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 890
Strange thing. Oracle9I uses OracleJoinFragment instead of ANSIJoinFragment, but then I see no filtering in the where clause. Maybe it's some legacy bug hidden in that old Oracle driver.

_________________
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  [ 9 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:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.