• Post Reply Bookmark Topic Watch Topic
  • New Topic

How many Statements/ResultSets should be used per one Connection  RSS feed

 
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Howdy,
I have a code that creates one Connection object and does many queries on that particular object, using one Statement and one ResultSet.
My pseudo code looks like this:

My question is how efficient is this code, and is it best practice to use one Statement and one RS throughout the Connection.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends how often you're hitting the database. If you're runnning a web application with many users you will almost certainly be better off with a Connection Pool
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
ahmed yehia
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.



If you use Statements to execute same query when web app access DB several time in a single request , then opt for Prepared Statement as it caches the query in DB and helps in "executin plan".
 
Sheriff
Posts: 22949
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may want to consider creating a database view that reconciles the relationships between the 3 query statements and then execute a single query for your application.
 
ahmed yehia
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


If you use Statements to execute same query when web app access DB several time in a single request , then opt for Prepared Statement as it caches the query in DB and helps in "executin plan".


Yea I think Prepared Statements would fit much better here with lots of equivalent queries.


Actually you are creating three ResultSets in that code.
....


Absolutely correct, though I'd expect that MySQL driver properly implements Statement's Interface methods, but still something to keep in mind.


You may want to consider creating a database view that reconciles the relationships between the 3 query statements and then execute a single query for your application.


Sounds like good idea!

Thanks All.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Saloon Keeper
Posts: 8097
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to CodeRanch!

Why aren't you closing your statements?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!