Thoughts on…

Java Middleware & Systems Management

Archive for February 2010

Hibernate Many-To-Many Revisited

with 12 comments

The modeling problem is classic: you have two entities, say Users and Roles, which have a many-to-many relationship with one another. In other words, each user can be in multiple roles, and each role can have multiple users associated with it.

The schema is pretty standard and would look like:

CREATE TABLE app_user ( 
   id INTEGER,
   PRIMARY KEY ( id ) );

CREATE TABLE app_role (
   id INTEGER,
   PRIMARY KEY ( id ) );

CREATE TABLE app_user_role ( 
   user_id INTEGER,
   role_id INTEGER,
   PRIMARY KEY ( user_id, role_id ),
   FOREIGN KEY ( user_id ) REFERENCES app_user ( id ),
   FOREIGN KEY ( role_id ) REFERENCES app_role ( id ) );

But there are really two choices for how you want to expose this at the Hibernate / EJB3 layer. The first strategy employs the use of the @ManyToMany annotation:

@Entity 
@Table(name = "APP_USER")
public class User {
    @Id
    private Integer id;
    
    @ManyToMany
    @JoinTable(name = "APP_USER_ROLE", 
       joinColumns = { @JoinColumn(name = "USER_ID") }, 
       inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") })
    private Set<Role> roles = new HashSet<Role>();
}

@Entity 
@Table(name = "APP_ROLE")
public class Role {
    @Id
    private Integer id;
    
    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<User>();
}

The second strategy uses a set of @ManyToOne mappings and requires the creation of a third “mapping” entity:

public class UserRolePK {
    @ManyToOne
    @JoinColumn(name = "USER_ID", referencedColumnName = "ID")
    private User user;

    @ManyToOne
    @JoinColumn(name = "ROLE_ID", referencedColumnName = "ID")
    private Role role;
}

@Entity @IdClass(UserRolePK.class) 
@Table(name = "APP_USER_ROLE")
public class UserRole {
    @Id
    private User user;

    @Id
    private Role role;
}

@Entity 
@Table(name = "APP_USER")
public class User {
    @Id
    private Integer id;
    
    @OneToMany(mappedBy = "user")
    private Set<UserRole> userRoles;
}

@Entity 
@Table(name = "APP_ROLE")
public class Role {
    @Id
    private Integer id;
    
    @OneToMany(mappedBy = "role")
    private Set<UserRole> userRoles;
}

The most obvious pro for the @ManyToMany solution is simpler data retrieval queries. The annotation automagically generates the proper SQL under the covers, and allows access to data from the other side of the linking table with a simple join at the HQL/JPQL level. For example, to get the roles for some user:

SELECT r 
FROM User u 
JOIN u.roles r 
WHERE u.id = :someUserId

You can still retrieve the same data with the other solution, but it’s not as elegant. It requires traversing from a user to the userRoles relationship, and then accessing the roles associated with those mapping entities:

SELECT ur.role 
FROM User u 
JOIN u.userRoles ur 
WHERE u.id = :someUserId

The inelegance of the second strategy becomes clear if you had several many-to-many relationships that you needed to traverse in a single query. If you had to use explicit mapping entities for each join table, the query would look like:

SELECT threeFour.four
FROM One one 
JOIN one.oneTwos oneTwo 
JOIN oneTwo.two.twoThrees twoThree 
JOIN twoThree.three.threeFours threeFour
where one.id = :someId

Whereas using @ManyToMany annotations, exclusively, would result in a query with the following form:

SELECT four 
FROM One one 
JOIN one.twos two 
JOIN two.threes three 
JOIN threes.four 
WHERE one.id = :someId

Some readers might wonder why, if we have explicit mapping table entities, we don’t just use them directly to make the query a little more intelligible / human-readable:

SELECT threeFour.four
FROM OneTwo oneTwo, TwoThree twoThree, ThreeFour threeFour
WHERE oneTwo.two = twoThree.two
AND twoThree.three = threeFour.three
AND oneTwo.one.id = :someId

Although I agree this query may be slightly easier to understand at a glance (especially if you’re used to writing native SQL), it definitely doesn’t save on keystrokes. Aside from that, it starts to pull away from thinking about your data model purely in terms of its high-level object relations.

In a read-mostly system, where access to data is the most frequent operation, it just makes sense to use the @ManyToMany mapping strategy. It achieves the goal while keeping the queries as simple and straight forward as possible.

However, elegance of select-statements should not be the only point considered when choosing a strategy. The more elaborate solution using the explicit mapping entiies does have its merits. Consider the problem of having to delete users that have properties matching a specific condition, which due to the foreign keys also require deleting user-role relationships matching that same criteria:

DELETE UserRole ur 
WHERE ur.user.id IN ( 
   SELECT u 
   FROM User u 
   WHERE u.someProperty = :someInterestingValue );
DELETE User u WHERE u.someProperty = :someInterestingValue;

If the mapping entity did not exist, the role objects would have to be loaded into the session, traversed one at a time, and have all of their users removed…after which, the role objects themselves could be deleted from the system. If your application only had a handful of users that matched this condition, either solution would probably perform just fine.

But what if you had tens of millions of users in your system, and this query happened to match 10% of them? (OK, perhaps this particular scenario is a bit contrived, but there *are* plenty of applications out there where the number of many-to-many relationships order in the tens of millions or more.) The logic would have to load more than a million users across the wire from the database which, as a result, might require you to implement a manual batching mechanism. You would load, say, 1000 users into memory at once, operate on them, flush/clear the session, then load the next batch, and so on. Memory requirements aside, you might find the transaction takes too long or might even time-out. In this case, you would need to execute each of the batches inside its own transaction, driving the process from outside of a transactional context.

Unfortunately, the data-load isn’t the only issue. The actual deletion work has problems too. You’re going to have to, for each user in turn, remove all of its roles (e.g., “user.getRoles().clear()”) and then delete the user itself (e.g., “entityManager.remove(user)”). These operations translate into two native SQL delete statements for each matched user – one to remove the related entries from the app_user_role table, and the other to remove the user itself from the app_user table).

All of these performance issues stem from the fact that a large amount of data has to be loaded across the wire and then manipulated, which results in a number of roundtrips proportional to the number of rows that match the criteria. However, by creating the mapping entity, it becomes possible to execute everything in two statements, neither of which even load data across the wire.

So what’s the right solution? Well, the interesting thing about this problem space is that the two solutions described above are not mutually exclusive. There’s nothing that prevents you from using both of them simultaneously:

public class UserRolePK {
    @ManyToOne
    @JoinColumn(name = "USER_ID", referencedColumnName = "ID")
    private User user;

    @ManyToOne
    @JoinColumn(name = "ROLE_ID", referencedColumnName = "ID")
    private Role role;
}

@Entity @IdClass(UserRolePK.class) 
@Table(name = "APP_USER_ROLE")
public class UserRole {
    @Id
    private User user;

    @Id
    private Role role;
}

@Entity 
@Table(name = "APP_USER")
public class User {
    @Id
    private Integer id;
    
    @OneToMany(mappedBy = "user")
    private Set<UserRole> userRoles;
    
    @ManyToMany
    @JoinTable(name = "APP_USER_ROLE", 
       joinColumns = { @JoinColumn(name = "USER_ID") }, 
       inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") })
    private Set<Role> roles = new HashSet<Role>();
}

@Entity 
@Table(name = "APP_ROLE")
public class Role {
    @Id
    private Integer id;
    
    @OneToMany(mappedBy = "role")
    private Set<UserRole> userRoles;
    
    @ManyToMany(mappedBy = "roles")
    private Set<User> users = new HashSet<User>();
}

This hybrid solution actually gives you the best of both worlds: elegant queries and efficient updates to the linking table. Granted, the boilerplate to set up all the mappings might seem tedious, but that extra effort is well worth the pay-off.

Advertisements

Written by josephmarques

February 22, 2010 at 12:52 pm

Posted in hibernate

Tagged with

Web Development Tips – automate the little things

with 3 comments

I recall a colleague of mine mentioning several weeks ago that it’s annoying to have to log into RHQ every time you redeploy UI code that causes portal-war’s web context to reload. I completely agreed at the time, but it wasn’t until today that I finally got annoyed enough to look for a workaround myself. Here’s the solution I ended up with:

1) Use FireFox
2) Download and install GreaseMonkey
3) Install the AutoLogin script
4) Log into “http://localhost:7080/Login.do&#8221; and make sure to tell FF to remember your password
5) Test that auto-login is working properly by logging out of the application…you should be forwarded to the login page, which FF will automatically fill in with your saved credentials, and the grease monkey script will perform the login for you

This should also work when you get logged out due to session expiry. The expiry handler will redirect you back to /Login.do, which will now automatically log you back in and – on a best effort basis – redirect you back to the last “valid” page you were on. RHQ has a mechanism for recording the last couple of pages you visited (see WebUserTrackingFilter) and will try them in most-recently-visited order until it finds a page that doesn’t blow up with JSF’s “classic” ViewExpiredException. I discuss the details of how this mechanism works in my other post.

Note: if you ever want to log into localhost with a different user, all you have to do is click the GreaseMonkey icon (on the far right-hand side of the status bar at the bottom of your browser) and you’ll temporarily disable the AutoLogin script from executing.

How would you solve this? How have you solved this? I’m eager to read your post backs.

Written by josephmarques

February 5, 2010 at 4:19 pm

Posted in webdev

Tagged with