my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes running a query within a loop.  Need help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "running a query within a loop.  Need help" Watch "running a query within a loop.  Need help" New topic

running a query within a loop. Need help

Glenn J Timchishen

Joined: Sep 13, 2006
Posts: 3

This is my first java ranch posting so please be kind... I have a problem, I'm creating a jsp running on tomcat, I�m trying to use a mysql db to create a page that shows what permissions different users for the application have.

SO... imaging a table where the headers going across are the names of the permissions and then the first column going down is a listing of all the usernames. And then on each row going across you have Xs or blanks telling you if a certain user has that certain right.

So at first thought this is easy. One query for your permissions, loop through that.... then a second query for your usernames, loop through that... now here is the problem. When you go through the LAST needed query, the one that will get the permissions based per user, the program crashes the entire jvm.

This is because new RS objects are being created on each iteration of the users (2nd) query.

My question? How can I do this without crashing my jvm?


Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32818

Welcome to JavaRanch!

There isn't any inherent problem with what you are doing. It might perform slowly, but shouldn't cause the JVM to crash. (If you need it to work faster, you can look at joins or batching. If this is just to learn, it wouldn't matter.)

Are you closing all your resultset objects between queries? Also, if you aren't already, a PreparedStatement is better than a Statement since you are essentially doing the same query with different parameters.

For more specific help, feel free to post some code.

[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: running a query within a loop. Need help
It's not a secret anymore!