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.
Thanks in advance.