Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Best Practice For Joined Result Set  RSS feed

John Skroper
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36417
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!