Thursday, September 21, 2006

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):

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:

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:

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:

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:

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:

Now that our Hibernate objects are defined, we need to create a HibernateQuery 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 HibernateQuery 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");

You will see that the results returned by our HibernateQuery 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.