wood burning stoves*
The moose likes Object Relational Mapping and the fly likes n+1 selects in birectional one to one association Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "n+1 selects in birectional one to one association" Watch "n+1 selects in birectional one to one association" New topic
Author

n+1 selects in birectional one to one association

sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
I have a class A which has an object of Type B.A is associated with B with a one to one mapping and the association is bi-directional.In table A I have the id of B say for eg "b_Id" The problem is that when I try to Query using HQL "from A where some condition....",hibernate is generating sql "select * from A where b_Id =?" multiple times.I have joined A with B using left join fetch.Even then it gives n+1 selects.Can anybody help me on this to find an alternative
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

"tsajan",
Welcome to JavaRanch!

We're pleased to have you here with us here on the ranch, but there are a few rules that need to be followed. One is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious. Initials are OK for the first and middle names but not the last. You can change it here


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
Can anyone has any idea on the solution to this problem?
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Well n+1 in this case would be just 2 queries, when you say multiple times says to me this isn't n+1, but most like, and I repeat most likely an app issue. For instance, are you by any chance using JSF in your application and that the query is being run more times for each JSF phase.

Also is this all within the same Session?

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
Mark,

Thank you for replying.
Let me explain my problem a little bit further.I am using jsp with jstl.
I have two tables with Bidirectonal one-to-one (foreign-key) association
I have a table for eg Person with Adress as one of the field

My pojos looks like:
public class Person {
private int id;
private Address address;

@OneToOne(optional = true, fetch = FetchType.LAZY)
@JoinColumn(name = "address_id")
public Address getAddress () {
return address;
}
}
public class Address{

private int id;
private Person person;

@OneToOne(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY, mappedBy = "address")
public Audit getPerson() {
return person;
}
}

My tables are Person and Address and Person table has address_id in it.

Now I have written an HQL(namedQuery in person) which will get Person Details and joins with Address table

like from Person as person left join fetch Adress
left join anotherTable
where some condtion

If I have some 5 rows returned

In the queries generated from hibernate.I am getting a single query which corresponds to the query I have written and in addition to this I am getting 5 queries which is "select * from Person person left outer join
Address address on person.address_id =adress.id."

This is executed 5 times even after giving left join fetch.

I also tried giving FetchType.EAGER in address but doesn't solve this problem.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Taken from the Hibernate Annotations documentation.



Try just this basic mapping and see what happens.

Mark
sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
I tried the basic mapping but that doesn't solve the problem
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Originally posted by sajan Thankappan:
I tried the basic mapping but that doesn't solve the problem


OK, then with that in mind, then is should be the jsp/jstl calls. with jstl are you looping anywhere?

Mark
sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
No Mark.I am not looping it anywhere.Whether is this any hibernative problem ie for one to one bidirectional mapping using foreign key.In table I am having the foreign key of child table .but in my entity I am giving mapping on both classes.
sajan Thankappan
Greenhorn

Joined: Dec 10, 2003
Posts: 10
Anybody has any hints on the solution to this problem.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

OK, try making a test where you remove the jsp/jstl portion of the equation.

Just a simple class that has just one method that loads one record, no other apis or jars included, and see what happens there to verify if it is Hibernate or something else.

Mark
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: n+1 selects in birectional one to one association
 
Similar Threads
Class Diagram -Association & Inheritance
Composite foreign key mapping in Hibernate 3 using JPA
ManyToMany & fields of association table
Hibernate Association
Ternary Association