bidirectionnal OneToOne and n+1 select

deeeed · **Joined:** Sun Dec 24, 2006 10:23 pm **Posts:** 11

I always have "n+1 select" problem when I go throught a bidirectionnal OneToOne, for example sim.getPhone().getSim() will fetch the sim twise.
Is it possible to use a cache to avoid this problem ?

Hibernate version: 3.2

Mapping documents:

Code:

PhoneEntity {
   @OneToOne(mappedBy = "phone", fetch = FetchType.LAZY, optional = true)
    private SimEntity sim;
    ........
}
SimEntity {
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "`phoneId`", nullable = true)
    private PhoneEntity phone;
    .......
}

Here is criteria in my DAO:

Code:

Criteria crit2 = session.createCriteria(SimEntity.class);
            crit2.add(Restrictions.idEq(50));
            se = (SimEntity) crit2.uniqueResult();
            System.out.println(se);
            if(se!=null) {
               pe = se.getPhone();
               System.out.println(pe);
            }

Then the generated log where we can see the SimEntity object is fetched twice.

Quote:

Hibernate:
/* criteria query */ select
this_.simId as simId3_0_,
this_."phoneId" as phoneId3_3_0_,
this_.phoneNumber as phoneNum2_3_0_
from
SimEntity this_
where
this_.simId = ?
hibernate.SimEntity@1042c25

Hibernate:
/* load hibernate.PhoneEntity */ select
phoneentit0_.phoneId as phoneId2_0_,
phoneentit0_.color as color2_0_
from
PhoneEntity phoneentit0_
where
phoneentit0_.phoneId=?
Hibernate:
/* load hibernate.SimEntity */ select
simentity0_.simId as simId3_0_,
simentity0_."phoneId" as phoneId3_3_0_,
simentity0_.phoneNumber as phoneNum2_3_0_
from
SimEntity simentity0_
where
simentity0_."phoneId"=?
hibernate.PhoneEntity@1979eb

mosa · **Posted:** Tue Apr 22, 2008 7:20 pm

that's wierd....

from what it is printed we see that you have one select at the beginning which is ok since you load the SimEntity and then when you lazy load the PhoneEntity the SimEntity is also loaded as if it had been mapped with eager fetch!

I am not really familiar with annotations but are you sure you are mapping the SimEntity with lazy fetch in your PhoneEntity??

just a thought...

cheers

ferozz · **Posted:** Wed Apr 23, 2008 2:47 am

it looks like this:

http://www.hibernate.org/162.html

deeeed · **Joined:** Sun Dec 24, 2006 10:23 pm **Posts:** 11

Ferozz, I read your link before but it doesn't explain why the first time I can lazy load the Phone entity.
It looks this impossibility to lazy load OneToOne is only when I use mappedBy.
On the other hand with a JoinColumn it's possible to retrieve lazy the corresponding entity.
In my example I have:

Code:

PhoneEntity {
   @OneToOne(mappedBy = "phone", fetch = FetchType.LAZY, optional = true)
    private SimEntity sim;
    ........
}
SimEntity {
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "`phoneId`", nullable = true)
    private PhoneEntity phone;
    .......
}

And my query:

Code:

Criteria crit2 = session.createCriteria(SimEntity.class);
            crit2.add(Restrictions.idEq(50));
            se = (SimEntity) crit2.uniqueResult();
            System.out.println(se);
            if(se!=null) {
               pe = se.getPhone();
               System.out.println(pe);
            }

So when I do crit2.uniqueResult(); it generates:

Quote:

/* criteria query */ select
this_.simId as simId3_0_,
this_."phoneId" as phoneId3_3_0_,
this_.phoneNumber as phoneNum2_3_0_
from
SimEntity this_
where
this_.simId = ?

So the associated OneToOne PhoneEntity, isn't fetch yet.
When I debug step by step I can see the query to retrieve the Phone is only created when I try to display it with

Code:

System.out.println(pe);

Hibernate:
/* load hibernate.PhoneEntity */ select
phoneentit0_.phoneId as phoneId2_0_,
phoneentit0_.color as color2_0_
from
PhoneEntity phoneentit0_
where
phoneentit0_.phoneId=?
Hibernate:
/* load hibernate.SimEntity */ select
simentity0_.simId as simId3_0_,
simentity0_."phoneId" as phoneId3_3_0_,
simentity0_.phoneNumber as phoneNum2_3_0_
from
SimEntity simentity0_
where
simentity0_."phoneId"=?

So for the second fetch it's eager, which is kind of normal when we see http://www.hibernate.org/162.html .
But I don't really understand why it's lazy the first time.
Anyone with more information ?

Thanks