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?
posted 10 years ago
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.
I have a scenario where a loop creates statement Object using con.createStatement() , morethan 500 times. The database we used is Oracle.
In the result set, I was getting only for initial 500 queries, remaining was null.
Refactored the code to PreparedStatement, It worked for n number of queries in the same connection Object.
I am not sure 500 is the Limit from JDBC or the Oracle side.
But it is sure that there is a limit for it.