Hibernate stuck with many selects with OneToOne

Question

I assume I am having the N+1 select problem.

I got this entity:

@Entity
@Table(name = "Devices")
public class Device implements Serializable {

 @OneToOne(mappedBy="holdingDevice", fetch=FetchType.LAZY)
    @Cascade(CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private WarrantyEntry warranty;
}

This is the other entity:

@Entity
@Table(name = "Warranty")
public class WarrantyEntry implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    @OneToOne
    @JoinColumn(name = "serial")
    @PrimaryKeyJoinColumn
    private Device holdingDevice;

Now when I am starting to iterate over this loop:

 Set<Device> customerDevices = user.getCustomer().getDevices();
            for (Device device : customerDevices) {
               ...
            }

I am stuck and I see in the log that Hibernate selects:

Hibernate: */ select warrantyen0_.id as... Hibernate: /* load myclass.Device */

Hibernate: */ select warrantyen0_.id as .. Hibernate: /* load myclass.Device */...

Over and over I guess I am having the n+1 select problem.

Any suggestions how could I solve and replace all these selects with only one?

After doing this

 String query="from Customer c left join fetch c.devices d  \n" +
                    "left join fetch d.tradeInOldDevice "  +
                    "left join fetch d.tradeInNewDevice  "+
                    "left join fetch d.warranty";



            deviceDao.getSessionFactory().openSession().createQuery(query);


            for (Device device : customerDevices) {
               ..
            }

I still get that:

Hibernate: 
        devices0_.owningCompany_customerRefId as owningC15_0_1_,
        devices0_.serial as serial1_,
        devices0_.serial as serial9_0_,
        devices0_.blackListed as blackLis2_9_0_,
        devices0_.Creation_id as Creation12_9_0_,
        devices0_.deactivated as deactiva3_9_0_,
        devices0_.deviceComment as deviceCo4_9_0_,
        devices0_.deviceName as deviceName9_0_,
        devices0_.deviceType as deviceType9_0_,
        devices0_.distributor_customerRefId as distrib13_9_0_,
        devices0_.endCustomer_customerRefId as endCust14_9_0_,
        devices0_.owningCompany_customerRefId as owningC15_9_0_,
        devices0_.paChallenge as paChalle7_9_0_,
        devices0_.parent_serial as parent16_9_0_,
        devices0_.pendingDeactivation as pendingD8_9_0_,
        devices0_.safetyStock as safetySt9_9_0_,
        devices0_.serialSalt as serialSalt9_0_,
        devices0_.signedBlackBerry as signedB11_9_0_,
        devices0_.tradeInOldDevice as tradeIn17_9_0_ 
    from
        Devices devices0_ 
    where
        devices0_.owningCompany_customerRefId=?
Hibernate: 
        device0_.serial as serial9_0_,
        device0_.blackListed as blackLis2_9_0_,
        device0_.Creation_id as Creation12_9_0_,
        device0_.deactivated as deactiva3_9_0_,
        device0_.deviceComment as deviceCo4_9_0_,
        device0_.deviceName as deviceName9_0_,
        device0_.deviceType as deviceType9_0_,
        device0_.distributor_customerRefId as distrib13_9_0_,
        device0_.endCustomer_customerRefId as endCust14_9_0_,
        device0_.owningCompany_customerRefId as owningC15_9_0_,
        device0_.paChallenge as paChalle7_9_0_,
        device0_.parent_serial as parent16_9_0_,
        device0_.pendingDeactivation as pendingD8_9_0_,
        device0_.safetyStock as safetySt9_9_0_,
        device0_.serialSalt as serialSalt9_0_,
        device0_.signedBlackBerry as signedB11_9_0_,
        device0_.tradeInOldDevice as tradeIn17_9_0_ 
    from
        Devices device0_ 
    where
        device0_.tradeInOldDevice=?
Hibernate: 
        warrantyen0_.id as id34_2_,
        warrantyen0_.createdTime as createdT2_34_2_,
        warrantyen0_.deleted as deleted34_2_,
        warrantyen0_.expiryDate as expiryDate34_2_,
        warrantyen0_.serial as serial34_2_,
        warrantyen0_.updateTime as updateTime34_2_,
        warrantyen0_.updateUser as updateUser34_2_,
        device1_.serial as serial9_0_,
        device1_.blackListed as blackLis2_9_0_,
        device1_.Creation_id as Creation12_9_0_,
        device1_.deactivated as deactiva3_9_0_,
        device1_.deviceComment as deviceCo4_9_0_,
        device1_.deviceName as deviceName9_0_,
        device1_.deviceType as deviceType9_0_,
        device1_.distributor_customerRefId as distrib13_9_0_,
        device1_.endCustomer_customerRefId as endCust14_9_0_,
        device1_.owningCompany_customerRefId as owningC15_9_0_,
        device1_.paChallenge as paChalle7_9_0_,
        device1_.parent_serial as parent16_9_0_,
        device1_.pendingDeactivation as pendingD8_9_0_,
        device1_.safetyStock as safetySt9_9_0_,
        device1_.serialSalt as serialSalt9_0_,
        device1_.signedBlackBerry as signedB11_9_0_,
        device1_.tradeInOldDevice as tradeIn17_9_0_,
        management2_.id as id22_1_,
        management2_1_.deleted as deleted22_1_,
        management2_1_.firstName as firstName22_1_,
        management2_1_.lastLogin as lastLogin22_1_,
        management2_1_.lastName as lastName22_1_,
        management2_1_.password as password22_1_,
        management2_1_.primaryEmail as primaryE7_22_1_,
        management2_1_.userName as userName22_1_,
        management2_.authority as authority23_1_,
        management2_.isViewer as isViewer23_1_,
        management2_3_.distributor as distribu1_25_1_,
        management2_4_.umeKeysQuota as umeKeysQ1_27_1_,
        case 
            when management2_2_.id is not null then 2 
            when management2_3_.id is not null then 3 
            when management2_4_.id is not null then 5 
            when management2_5_.id is not null then 6 
            when management2_6_.id is not null then 7 
            when management2_.id is not null then 1 
        end as clazz_1_,
        cids3_.Users_id as Users1_22_4_,
        cids3_.element as element4_,
        emails4_.Users_id as Users1_22_5_,
        emails4_.element as element5_,
        roles5_.Users_Management_id as Users1_22_6_,
        roles5_.element as element6_ 
    from
        Warranty warrantyen0_ 
    left outer join
        Devices device1_ 
            on warrantyen0_.serial=device1_.serial 
    left outer join
        Users_Management management2_ 
            on warrantyen0_.updateUser=management2_.id 
    left outer join
        Users management2_1_ 
            on management2_.id=management2_1_.id 
    left outer join
        Users_Management_Administrators management2_2_ 
            on management2_.id=management2_2_.id 
    left outer join
        Users_Management_Distributors management2_3_ 
            on management2_.id=management2_3_.id 
    left outer join
        Users_Management_Limited management2_4_ 
            on management2_.id=management2_4_.id 
    left outer join
        Users_Management_Managers management2_5_ 
            on management2_.id=management2_5_.id 
    left outer join
        Users_Management_Workers management2_6_ 
            on management2_.id=management2_6_.id 
    left outer join
        Users_CID cids3_ 
            on management2_.id=cids3_.Users_id 
    left outer join
        Users_Emails emails4_ 
            on management2_.id=emails4_.Users_id 
    left outer join
        Users_Management_roles roles5_ 
            on management2_.id=roles5_.Users_Management_id 
    where
        warrantyen0_.serial=?

Thanks, ray.

Answer 1

Assuming you have defined the customerDevices association in your Customer class as something like this:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "device")
private Set<Device> customerDevices;

This LAZY mapping associations exposes you to n+1 select problems. Let us consider a simple query that retrieves a customer for a given customerId:

session().createQuery("from Customer c where c.name=:name").setParameter("name", name);

This will return you a customer where the collection of customerDevices is an uninitialized collection wrapper. Now when you are iterating over the loop:

Set<Device> customerDevices = user.getCustomer().getDevices();
            for (Device device : customerDevices) {
               ...
            }

and while accessing the collection of devices, Hibernate must fetch this lazy collection from the database executing extra select statements.

The recommended solution for this problem is to override the default fetching strategy at runtime in code, which you can achieve by using a query like this:

session().createQuery(from Customer c left join fetch c.devices d  
left join fetch d.warrantyEntry)

This will return you a Customer along with the associated collections.So, instead of retrieving just the top-level objects in the initial query, fetch all needed data in the initial query by specifying exactly which associations will be accessed in the ongoing unit of work.

EDIT:

It looks like sometimes the one to one mapping causes the HQL eager fetch failure. You can find more about this issue at these discussions : HQL eager fetch failure and N+1 selects on left join.

One solution mentioned there is to change the OneToOne mapping to ManyToOne and OneToMany, which I tried (using a similar type of model classes)and it works perfectly for me. I got all the results selected with one sql query.

For example, in your Device class, you can change the OneToOne mapping to ManyToOne:

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "warrantyFK", unique = true)
private WarrantyEntry warranty;

The unique=true attribute will make this association an one-to-one association, as no two Device can have the same WarrantyEntry. And you can define a collection of Device in the WarrantyEntry class:

@OneToMany(mappedBy = "warranty")
private Set<Device> holdingDevices;

The main idea is to use OneToMany and ManyToOne instead of OneToOne. You just need to make sure that you add at most one item to the set on the OneToMany side of the mapping.

Answer 2

It can be "n+1 select problem", you can make sure putting these properties in your persistence.xml:

    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.use_sql_comments">true</property>

I think you could also use unique=true, nullable=false in your JoinColumn.

You can try to use JPQL/HQL to do a eager fetch and solve this problem (maybe using Criteria would also resolve, not sure).