aspose file tools*
The moose likes JDBC and the fly likes How many Statements/ResultSets should be used per one Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How many Statements/ResultSets should be used per one Connection" Watch "How many Statements/ResultSets should be used per one Connection" New topic
Author

How many Statements/ResultSets should be used per one Connection

ahmed yehia
Ranch Hand

Joined: Apr 22, 2006
Posts: 424
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.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Apr 22, 2006
Posts: 424
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.

Balu Sadhasivam
Ranch Hand

Joined: Jan 01, 2009
Posts: 874

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".
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18155
    
    8

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.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Apr 22, 2006
Posts: 424

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How many Statements/ResultSets should be used per one Connection
 
Similar Threads
Resultset
Problem in Seperating Core Servlet to Database Query Java
Hi,I have a problem in database connection
closing Connection before returning ResultSet
transferring ResultSet from EJB to JSP/Java Class