It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Best Practice For Joined Result Set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Best Practice For Joined Result Set" Watch "Best Practice For Joined Result Set" New topic

Best Practice For Joined Result Set

John Skroper

Joined: May 08, 2009
Posts: 1
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.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33106

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Best Practice For Joined Result Set
It's not a secret anymore!