Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • salvin francis
  • fred rosenberger

Best Practice For Joined Result Set

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.

author & internet detective
Posts: 39997
Eclipse IDE VI Editor Java
  • 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.
Hey! You're stepping on my hand! Help me tiny ad!
Devious Experiments for a Truly Passive Greenhouse!
    Bookmark Topic Watch Topic
  • New Topic