Thursday, September 21, 2006

Complex Many-to-Many Relations in Hibernate

SQL makes querying structured data very easily, especially when the data in tables is related. Using simple joins, it's possible to query for data in two separate tables that shares a common element. For example, imagine if we have a table users_tbl that contains our user account information, and a table roles_tbl that contains our roles information. Additionally, we make use of a join table users_roles_tbl that allows us to have many-to-many relationships between our users and roles. To create our tables, we could use the following commands (on MySQL 5):


create table users_tbl (
id INT AUTO_INCREMENT NOT NULL,
username VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;

create table roles_tbl (
id INT AUTO_INCREMENT NOT NULL,
rolename VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;

create table users_roles_tbl (
user_id INT NOT NULL,
role_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users_tbl (id),
FOREIGN KEY (role_id) REFERENCES roles_tbl (id),
PRIMARY KEY (user_id, role_id)
) TYPE=INNODB;


Suppose we have created 3 users: user_a, user_b, and user_c. Additionally, we have created 2 roles: role_a, and role_b. Our first user, user_a, is a member of role_a. Our second user, user_b, is a member of role_b. And, our third user, user_c, is a member of both role_a and role_b. We can see these users and roles displayed with the following SQL SELECT statement:


SELECT DISTINCT u.username, r.rolename FROM users_tbl u
INNER JOIN users_roles_tbl j ON u.id = j.user_id
INNER JOIN roles_tbl r ON r.id = j.role_id;


There are many instances in which we would like to find members of specific role, such as for access control. If we wanted to find all members of role_a, a simple modification to the previous SELECT statement will do the trick:


SELECT DISTINCT u.username, r.rolename FROM users_tbl u
INNER JOIN users_roles_tbl j ON u.id = j.user_id
INNER JOIN roles_tbl r ON r.id = j.role_id WHERE r.rolename = 'role_a';


In this example, user_a and user_c are returned, since they are the only members of role_a. But what happens when we want to perform a more complex query? For example, what if we only wanted to return users who are members of role_a AND role_b? This is where things become a bit more complicated. The IN statement allows us to perform a logical ORing of values. This is what we would use if wanted to find users who are members of role_a OR role_b. But what if we want users who are members of BOTH roles?

In this case, our SQL SELECT statement becomes a bit more complicated. Our SELECT statement would now look like this:


SELECT DISTINCT u.username FROM users_tbl u
INNER JOIN( roles_tbl r INNER JOIN users_roles_tbl j
ON ( ( r.id = j.role_id ) AND ( ( r.rolename = 'role_a' )
OR ( r.rolename = 'role_b' ) ) )) ON ( u.id = j.user_id )
GROUP BY u.id HAVING COUNT( j.user_id ) = 2;


As you can see, this SELECT statement gets very complex, very quickly. However, it accomplishes exactly what we like. Running this statement provides us with only user_c, the only user who is a member of both role_a and role_b.

Our question now becomes, how do we accomplish the same thing using Hibernate? For example, we have our core domain model objects, UserModel and RoleModel which have the appropriate Hibernate mappings configured. One way we could accomplish our goal is to create a many-to-many relation in Hibernate for our UserModel object that contains all the roles a user is a member of. We could then query on role_a, query on role_b, and then determine the intersection of those two Collections that are returned. However, this is a very expensive and time-consuming operation.

There is a much better way to accomplish our goal. We can create an additional domain model object that represents the data stored in our join table, users_roles_tbl. This object can be called UserRoleRelation. For these examples, I will be using XDoclet to generate our Hibernate mapping classes. Our UserModel class would look like the following:


/**
* @hibernate.class table="users_tbl" lazy="true"
*/
public class UserModel {

private Serializable identifier = null;
private String username = null;

/**
* @hibernate.id column="id"
* generator-class="native"
* type="java.lang.Integer"
*/
public Serializable getIdentifier() {
return identifier;
}

public void setIdentifier(Serializable identifier) {
this.identifier = identifier;
}

/**
* @hibernate.property column="username" not-null="true"
*/
public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}
}


Our RoleModel object would be configured similarly, but with the Hibernate table as roles_tbl. As for our UserRoleRelation object, it would be defined as:


/**
* @hibernate.class table="users_roles_tbl" lazy="true"
*/
public class UserRoleRelation {

private UserRoleRelationID identifier = null;
private UserModel user = null;
private RoleModel role = null;

/**
* @hibernate.id unsaved-value="any"
*/
public UserRoleRelationID getIdentifier() {
return identifier;
}

public void setIdentifier(UserRoleRelationID identifier) {
this.identifier = identifier;
}

/**
* @hibernate.many-to-one column="user_id"
* class="UserModel"
* not-null="true" cascade="all"
* insert="false"
* update="false"
*/
public UserModel getUser() {
...
}

/**
* @hibernate.many-to-one column="role_id"
* class="RoleModel"
* not-null="true" cascade="all"
* insert="false"
* update="false"
*/
public RoleModel getRole() {
...
}

...

}


You will notice the user of a class called UserRoleRelationID as our Hibernate identifier. This is due to the fact that XDoclet does not currently support composite primary keys. However, by defining our UserRoleRelationID class in the following way, we are able to get around this limitation and use our composite primary key:


public class UserRoleRelationID implements Serializable {

private Serializable userID = null;
private Serializable roleID = null;

/**
* @hibernate.property column="user_id"
* type="java.lang.Integer" not-null="true"
*/
public Serializable getUserID() {
return userID;
}


/**
* @hibernate.property column="role_id"
* type="java.lang.Integer" not-null="true"
*/
public Serializable getRoleID() {
return roleID;
}

public int hashCode() {
...
}

public boolean equals(Object o) {
...
}

...

}


Now that our Hibernate objects are defined, we need to create a Hibernate Query that accomplishes the same thing as our SELECT statement does. However, by working with Hibernate and utilizing our UserRoleRelation class, you will find the complexity of our query is greatly reduced. Our Hibernate Query can be defined as follows:


SELECT DISTINCT j.user AS p FROM UserRoleRelation j
INNER JOIN j.user AS urj
WHERE j.role.rolename IN (:roleList)
GROUP BY j.user HAVING COUNT (j.role) = :roleCount


To execute this query, our Java command would look something like this:


/*
* The query we defined above
*/
String queryString = ...

/*
* Declare a List containning the names of the roles we
* wish to match on
*/
ArrayList roleList = new ArrayList();
roleList.add("role_a");
roleList.add("role_b");

Session s = ....
Query q = s.createQuery(queryString);
q.setParameterList("roleList", roleList);
q.setInteger("roleCount", roleList.size());

List users = q.list();


You will see that the results returned by our Hibernate Query are exactly the same as those returned by our SQL SELECT statement, but with a greatly reduced amount of complexity.

This type of scenario is very common when dealing with many-to-many relations. For example, a site that maintains user profiles and keywords describing those profiles. We could modify our code slightly to allow a user to search for profiles that contain the keywords "jazz" and "hamburgers". Another example would be an online shopping site. It's very easy to search for the keyword "dogs", or search for the keyword "books". Our approach now allows us to search for books about dogs. Additionally, we can modify our approach to allow for less refined matches. For example, modifying the last line in our query to read:

GROUP BY j.user HAVING COUNT (j.role) >= :roleCount

will allow us to match on a variable amount of roles. If we have a list of 25 roles, we could search for those users that are members of 10 or more by changing the roleCount variable to 10 instead of roleList.size().

As you can see, using Hibernate in our Java application makes performing these complex queries much easier than coding in raw JDBC.