@Fetch(FetchMode.SUBSELECT) and OneToMany in OneToMany

Minaki · **Joined:** Thu Jun 05, 2008 2:18 am **Posts:** 2

Hi,

I have a relation like that ;

Person <OneToMany> Home
Home <OneToMany> Room

I load a group of Person. So Hibernate generate :

A) 1 SQL for load the group of Person (for exemple 10 person)
B) 10 SQL for loading the Home of each person (for exemple 5 home/person)
C) 5 * 10 SQL for loading the Room

So I want load all Person in single SQL, all Home in single SQL and all Room in single SQL.

I try this mapping :

Code:

@Entity
class Person {

   @OneToMany(fetch=FetchType.EAGER, mappedBy = "person")
   @OrderBy("index ASC")
   @JoinColumn(name = "personId")
   @IndexColumn(name="someIndex")
   @Fetch(FetchMode.SUBSELECT)
   private final List<Home> homes;
...
}


@Entity
class Home {

   @OneToMany(fetch=FetchType.EAGER, mappedBy = "room")
   @OrderBy("index ASC")
   @JoinColumn(name = "homeId")
   @IndexColumn(name="someIndex")
   @Fetch(FetchMode.SUBSELECT)
   private final List<Room> rooms;

   @ManyToOne
   @JoinColumn(name="personId")
   private Person person;
...
}

@Entity
class Room {

   @ManyToOne
   @JoinColumn(name="homeId")
   private Home home;
...
}

I want stay in EAGER mode.

So, Hibernate still generate a lot of SQL. It is normal? Or, I'm sure, I don't uderstand how the @Fetch(SUBSELECT) work.

JPA
Java 1.5
Hibernate Annotations 3.3.1.GA
Hibernate 3.2.6
Hibernate EntityManager 3.3.2.GA
Oracle

hibernate.dialect=org.hibernate.dialect.Oracle8iDialect
hibernate.show_sql=true
hibernate.max_fetch_depth=3

[/code]

Cameron McKenzie · **Posted:** Thu Jun 05, 2008 11:48 am

You should try lazy loading, and you'll minimize the amount of sql generated. Use extended Sessions or the Open Session in View Pattern, and you'll find that you'll only see the key SQL that is needed when properties are loaded or invoked.

Minaki · **Joined:** Thu Jun 05, 2008 2:18 am **Posts:** 2

Tank you,

but if you readat end of question I can't use lazy. I need all thing on client, so I must keep the EAGER loading.

And the application it's not a WEB application and all thing it's need when I'm come back from server.

So, the real question it's first can I configure the mapping for optain 3 SQL :

- One for all Person, one for all home and one for all room and make the link between instance in memory before return.

So, if it's impossible, I must make the load by hand or change the model.

Minaki