Hibernate version:
2.1.4
Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="User" table="USER">
<id name="id" column="USER_ID" type="int">
<generator class="native">
<param name="sequence">USER_ID_SEQ</param>
</generator>
</id>
<property name="firstName" column="FIRST_NAME" type="string"
length="50"/>
<property name="lastName" column="LAST_NAME" type="string"
length="50"/>
<property name="password" column="PASSWORD" type="string"
length="32"/>
<property name="email" column="EMAIL" type="string"
length="100"/>
<property name="active" column="ACTIVE" type="boolean"/>
<many-to-one name="primaryGroup" class="UserGroup"
column="PRIMARY_GROUP"/>
</class>
<class name="Group" table="GRP">
<id name="id" column="GROUP_ID">
<generator class="native">
<param name="sequence">GROUP_ID_SEQ</param>
</generator>
</id>
<many-to-one name="parent" class="Group" column="PARENT"/>
<property name="name" column="NAME" length="50"/>
<set name="children" lazy="false" inverse="true">
<key column="PARENT"/>
<one-to-many class="Group"/>
</set>
<set name="users" lazy="true" inverse="true">
<key column="PRIMARY_GROUP"/>
<one-to-many class="User"/>
</set>
</class>
</hibernate-mapping>
MS SQL 2000 SP3, PostgreSQL 7.4, 8.0
Hi,
I'm having the following problem: I want to list all groups together with their users. This is a typical many-to-one association; a user belongs to exactly one group. How can I do this in an efficient and elegant way?
I've tried a few approaches:
1)
Code:
List l = session.list("from Group");
for (Iterator i = l.iterator(); i.hasNext(); )
{
// don't remember the exact syntax
i.next().getUsers().instantiate()
}
This takes N + 1 queries where N is the number of groups
2)
Code:
List l = session.list("from Group");
/* leave session open, close using a servlet filter */
This has the same drawbacks as the method above
3)
Code:
Set l = new LinkedHashSet(session.list("from group g left join fetch g.users"));
This seems to be the best solution so far, using only a single query. However the group info is transmitted along with every user record, which is a waste of bandwith (please correct me if I'm wrong).
Using plain SQL I'd do it the following way:
Code:
List groups = SQL("select * from grp");
Map groupMap = new HashMap();
foreach (g in groups) {
groupMap.put(g.getID(), g);
}
List users = SQL("select * from user");
foreach (u in users) {
Group g = groupMap.get(u.getGroupID());
g.getUsers().add(u);
}
This takes 2 queries, but the amount of data transmitted from the DB to the application is exactly as big as needed.
Is it possible to use this scenario in hibernate?
Thanks for a great product, TIA for any help,
JJ[/code]
Login
FAQ
Search