Best Practice For Joined Result Set

[John Skroper]

Please excuse me if this question doesn't exactly fit under the JDBC section.

I'm curious as to how others handle a situation like the following:

Let's assume we are writing a small piece of code that retrieves users and privileges from a database. For each user, we could potentially have many privileges associated with the user. I can think of four ways to handle this:

Assume we are using a bean like the following to hold the user data and these User objects are all put into a list and passed back to the client:

public class User {
    String name;
    long id;
    List<String> privs;
    ......
}

1) Use a join query like:

select users.*, privs.priv_name
from users
left join privs on privs.user_id = users.user_id;

In this case, we need to write application code that checks which user we are on and keep track when the resultset hits a new user.

2) Use some sort of function like "group_concat" from MySQL to generate only a single row per user with the privileges concatenated and separated by a token. Then, in application code, split that column and store them in a list. The drawback here is, I don't believe the "group_concat" function is standard SQL.

3) Issue a query for all users, and as the result set is iterated through, issue another query for the privileges. Obviously this is the least efficient and instead of a single database call, you end up with a call for each user found.

4) Only fetch the privileges when the client requests them, possibly via an ajax call or similar functionality.

I usually find myself using a mixture of these (except option 3 due to fear of porting issues) but was curious if anyone had an alternative suggestion or some advice on how to deal with this sort of problem.

Thanks in advance.

[Jeanne Boyarsky]

John,
In this case, I would probably just use the join since Users only has two fields. (I would list out the fields in the select query rather than using a "*" though so if more columns are added they don't get pulled. If there were enough users for the name to be causing enough network traffic to be a problem, I would go on the below approach.

If Users had more columns, I would do a variant of #3.
1) Get all the users I need.
2) In batches, get the privilege for groups of users. See the article I wrote on why batching is useful over one monster query that gets the privileges for all users.