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:
1) Use a join query like:
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.
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.