Sounds like a case for the SQL JOIN clause so that you need only one query.
Are you familiar with it and if so, have you considered its applicability in this case?
Joined: Apr 22, 2006
It's a web App and needs to query DB several times on every page, even for contents that might look static to users. so many hits are being made to the DB.
It might not sound like good design, but that's how the application is designed! and I want to optimize it to better performance and resource management.
For eg. the App is designed to retrieve most of its contents from the DB i.e page body, side links etc. as you see selections are being made from different locations(Tables) I'm not sure JOIN clause can help here.
Ultimately I'd default to Connection pooling and that wont require much change as only getting the connection differs, so the concept of querying DB apply anywhere.
Actually you are creating three ResultSets in that code.
You only have one variable to assign them to, though, so only the last of the three gets closed properly. It's true that the API document for Statement says "All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists", so that should mean that the Statement will close the first ResultSet when it creates the second one. However it's possible that JDBC drivers might not implement that rule correctly, so you ought to close each ResultSet after you finish using it.