-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: OneToOne n+1 selects problem on a fetch eager association
PostPosted: Wed May 02, 2012 2:02 pm 
Newbie

Joined: Wed May 02, 2012 1:08 pm
Posts: 4
I have three associated models:
House has a bidirectional OneToMany association with Person and Person has bidirectional OneToOne with PersonDetail.

When loading Person direct by Criteria or Query the PersonDetail are correctly eager loaded as defined on Annotation.
But when lazy loading the persons collection of House, many additional and unnecessary queries are triggered.

Hibernate Version 4.1.2 (previous tests on 3.6.10 given the same error)

Models:

Code:
package testehibernate4;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

/**
*
* @author andremerlo
*/
@Entity
public class House implements Serializable {

    private Long id;
    private List<Person> persons = new ArrayList<>();

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

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

    @OneToMany
    public List<Person> getPersons() {
        return persons;
    }

    public void setPersons(List<Person> persons) {
        this.persons = persons;
    }

    public void addPerson(Person person) {
        persons.add(person);
    }
}


Code:
package testehibernate4;

import java.io.Serializable;
import javax.persistence.*;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

/**
*
* @author andremerlo
*/
@Entity
public class Person implements Serializable {

    private Long id;
    private PersonDetail detail;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long getId() {
        return id;
    }

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

    @Fetch(FetchMode.JOIN)
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public PersonDetail getDetail() {
        return detail;
    }

    public void setDetail(PersonDetail detail) {
        this.detail = detail;
        //detail.setPerson(this);
    }
    private String name;

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "Person{"
                + "id=" + id
                + ", name=" + name
                + ", detail=" + detail
                + '}';
    }
}


Code:
package testehibernate4;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

/**
*
* @author andremerlo
*/
@Entity
public class PersonDetail implements Serializable {

    private Long id;
    private Person person = new Person();
    private Integer age;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long getId() {
        return id;
    }

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

    @Fetch(FetchMode.JOIN)
    @OneToOne(mappedBy = "detail", optional = false, fetch = FetchType.EAGER)
    public Person getPerson() {
        return person;
    }

    public void setPerson(Person person) {
        this.person = person;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "PersonDetail{" + "age=" + age + '}';
    }
}


HibernateUtil for SessionFactory and test data creation:

Code:
package testehibernate4;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.jdbc.Work;
import org.hibernate.tool.hbm2ddl.SchemaExport;

/**
*
* @author andremerlo
*/
public class HibernateUtil {

    private static SessionFactory factory;

    public static void main(String[] args) {
        buildAllReload();
    }

    public static SessionFactory getSessionFactory() {
        if (factory == null) {
            init();
        }
        return factory;
    }

    public static void init() {
        Configuration config = getConfig();
        try {
            factory = config.buildSessionFactory();
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    private static Configuration getConfig() {
        Configuration config = new Configuration();

        /*
         * as classes mapeadas
         */
        config.addAnnotatedClass(House.class);
        config.addAnnotatedClass(Person.class);
        config.addAnnotatedClass(PersonDetail.class);

        /*
         * as propriedades de configuracao do hibernate
         */
        Properties props = new Properties();
        props.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        props.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
        props.setProperty("hibernate.connection.url", "jdbc:mysql://127.0.0.1:3306/teste_hibernate_4");
        props.setProperty("hibernate.connection.username", "root");
        props.setProperty("hibernate.connection.password", "1010");
        props.setProperty("hibernate.show_sql", "true");
        props.setProperty("hibernate.format_sql", "true");

        config.addProperties(props);
        return config;
    }

    public static void buildAllReload() {
        final Configuration config = getConfig();
        config.setProperty("hibernate.show_sql", "false");
        try {
            factory = config.buildSessionFactory();
            factory.openSession().doWork(new Work() {

                @Override
                public void execute(final Connection conn) throws SQLException {
                    SchemaExport schemaExport = new SchemaExport(config, conn);
                    System.out.println("Criando estrutura da base");
                    schemaExport.create(true, true);
                    System.out.println("Carregando dados");
                    load(factory);
                }
            });
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed

            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static void load(SessionFactory factory) {

        Session session = factory.openSession();
        Transaction tx = session.beginTransaction();

        House house = new House();
        Person person;
        PersonDetail detail;

        for (int i = 1; i <= 2; i++) {
            detail = new PersonDetail();
            detail.setAge(i);

            person = new Person();
            person.setName("JOHN-" + i);
            person.setDetail(detail);
           
            session.save(detail);
            session.save(person);

            house.addPerson(person);
        }

        session.save(house);
       
        tx.commit();
        session.close();
        //*********************************************************************/
    }
}


And the test code:
Code:
package testehibernate4;

import org.hibernate.Query;
import org.hibernate.Session;

/**
*
* @author andremerlo
*/
public class FetchTest {

    public static void main(String[] args) {
       
        Session s = HibernateUtil.getSessionFactory().openSession();
        //get the House
        Query q = s.createQuery("from House");       
        House h = (House) q.uniqueResult();
       
        //iterate over person which trigger the lazy load of person collection
        for (Person person : h.getPersons()) {
            System.out.println(person);
        }
    }
}


When run FetchTest a first query returns the House.
When accessing the lazy load persons collection, iterating over it, a second query is triggered to retrieve the Persons and associated PersonDetails(in eager fetch mode), and 2 unnecessary queries to retrieve the PersonDetails again:

Code:
Hibernate:
    select
        house0_.id as id0_
    from
        House house0_
Hibernate:
    select
        persons0_.House_id as House1_0_3_,
        persons0_.persons_id as persons2_3_,
        person1_.id as id1_0_,
        person1_.detail_id as detail3_1_0_,
        person1_.name as name1_0_,
        persondeta2_.id as id2_1_,
        persondeta2_.age as age2_1_,
        person3_.id as id1_2_,
        person3_.detail_id as detail3_1_2_,
        person3_.name as name1_2_
    from
        House_Person persons0_
    inner join
        Person person1_
            on persons0_.persons_id=person1_.id
    left outer join
        PersonDetail persondeta2_
            on person1_.detail_id=persondeta2_.id
    left outer join
        Person person3_
            on persondeta2_.id=person3_.detail_id
    where
        persons0_.House_id=?
Hibernate:
    select
        person0_.id as id1_2_,
        person0_.detail_id as detail3_1_2_,
        person0_.name as name1_2_,
        persondeta1_.id as id2_0_,
        persondeta1_.age as age2_0_,
        person2_.id as id1_1_,
        person2_.detail_id as detail3_1_1_,
        person2_.name as name1_1_
    from
        Person person0_
    left outer join
        PersonDetail persondeta1_
            on person0_.detail_id=persondeta1_.id
    left outer join
        Person person2_
            on persondeta1_.id=person2_.detail_id
    where
        person0_.detail_id=?
Hibernate:
    select
        person0_.id as id1_2_,
        person0_.detail_id as detail3_1_2_,
        person0_.name as name1_2_,
        persondeta1_.id as id2_0_,
        persondeta1_.age as age2_0_,
        person2_.id as id1_1_,
        person2_.detail_id as detail3_1_1_,
        person2_.name as name1_1_
    from
        Person person0_
    left outer join
        PersonDetail persondeta1_
            on person0_.detail_id=persondeta1_.id
    left outer join
        Person person2_
            on persondeta1_.id=person2_.detail_id
    where
        person0_.detail_id=?


Any help will be appreciated.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

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.