CODEWITHSUNDEEP
javasqlhibernatejoin
m0rv4i
m0rv4i

Reputation: 435

Hibernate Joins using Criteria

I am trying to use Hibernate to access persisted data for our rights management, but I am very new to it and struggling to get the data I need.

I have Users table, with an ID and name, a Groups table with an ID and name, and a User/Groups mapping which just consists of the group ids and user ids that are linked.

What I want to do is get all the names of the members of a given group, so the standard SQL query I want to execute is this:

SELECT 
    u.NAME
FROM 
    USERS u
JOIN
    GROUP_USERS gu
ON
    u.ID = gu.USER_ID
JOIN
    GROUPS g
ON
    gu.GROUP_ID = g.ID
WHERE
     g.NAME = 'test'

But despite hours of looking and playing I cannot seem to get anywhere.

I want to use Criteria as they seem clearer, so my code is as follows:

@Override
public final List<String> getGroupMembers(final String groupName) {
    @SuppressWarnings("unchecked")
    List<User> groupUsers = getHibernateTemplate().execute(new HibernateCallback<List<User>>() {

                @Override
                public List<User> doInHibernate(Session session) throws HibernateException, SQLException {
                    Criteria criteria = session.createCriteria(User.class)
                        .setFetchMode("GroupUsers", FetchMode.JOIN)
                        .setFetchMode("Group", FetchMode.JOIN)
                        .add(Restrictions.eq("name", groupName));
                    return criteria.list();
                }
            });

    List<String> groupUsernames = new ArrayList<String>();
    for (User groupUser : groupUsers) {
        groupUsernames.add(groupUser.getName());
    }
    return groupUsernames;
}

But when I test it the result set is empty, and according to the logs the executed query is this: 

select this_.ID as M1_4_0_, this_.NAME as M2_4_0_ from USERS this_ where this_.NAME=?

I let Hibernate create the tables using hibernate.hbm2ddl.auto, but then removed it so that the tables are definitely as hibernate expects, but the data is not being cleaned.

Any help with the Criteria would be greatly appreciated, so thanks in advance!

Edit: I am using xml mapping files:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.company">
    <class name="com.company.Group" table="GROUPS">
        <id name="id" column="ID" type="int">
            <generator class="identity"/>
        </id>
        <property name="name" column="NAME" type="java.lang.String" unique="true" not-null="true"/>
    </class>
</hibernate-mapping>

and

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.company">
    <class name="com.company.GroupUsers" table="GROUP_USERS">
        <composite-id>
            <key-many-to-one name="groupId" class = "Group" column="GROUP_ID" />
            <key-many-to-one name="userId" class = "User" column="USER_ID" />
        </composite-id>
    </class>
</hibernate-mapping>

and

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.company">
    <class name="com.company.User" table="USERS">
        <id name="id" column="ID" type="int">
            <generator class="identity"/>
        </id>
        <property name="name" column="NAME" type="java.lang.String" unique="true" not-null="true"/>
    </class>
</hibernate-mapping>

Upvotes: 2

Views: 11053

Answers (2)

kothvandir
kothvandir

Reputation: 2161

You don't need to map the user-group table, just define your relation between User and Group as a many-to-many relation, take a look at this and this about how to map many-to-many relations.

In your case, it will look like:

<hibernate-mapping package="com.company">
  <class name="com.company.User" table="USERS">

    <id name="id" column="ID" type="int">
        <generator class="identity"/>
    </id>
    <property name="name" column="NAME" type="java.lang.String" unique="true" not-null="true"/>

<many-to-many name="userGroups"
    target-entity="com.company.Group">
   <join-table name="YOUR_USER_GROUP_TABLE">
    <join-column name="USER_ID" />
    <inverse-join-column name="GROUP_ID" />
   </join-table>
</many-to-many>

</class>

And to filter your users using the name field from the Group entity for example:

Criteria criteria = session.createCriteria(User.class);  
criteria.createAlias("userGroups", "usrGrp",CriteriaSpecification.LEFT_JOIN); 
criteria.add( Restrictions.eqProperty("usrGrp.name", "test") )

Upvotes: 4

Vlad
Vlad

Reputation: 1763

The way you map your object to tables does not benefit from the usage of Hibernate as ORM. Consider a more object-oriented model, eg:

class Group {
  private Set<User> users;
  // ...
}

class User {
  private Set<Group> groups;
  //..
}

So, you've got a classical many-to-many association. Here you can find an example of such mapping with Hibernate.

Upvotes: 0

Related Questions