aspose file tools*
The moose likes JDBC and the fly likes When to close static PreparedStatements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "When to close static PreparedStatements" Watch "When to close static PreparedStatements" New topic
Author

When to close static PreparedStatements

Kevin Bolton
Greenhorn

Joined: Nov 20, 2001
Posts: 8
I would like to share a preparedStatement across instances of a class.
e.g. static PreparedStatement preparedStatement = someStaticConnection.prepareStatement(someSql);
The preparedStatement will be opened when the class is loaded. Many instances use the prepared statement. But, where would I put code to close the connection? Does the class ever get unloaded?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

As I understand PreparedStatements (and I'd be happy for someone to correct me) they are a mechanism to allow the query to be built and cached at the database side, so there is little advantage in trying to increase the performance at the application. You'll probably even run into resource handling and threading problems.
Personally I think you should just keep creating new ones.
Dave.
Kevin Bolton
Greenhorn

Joined: Nov 20, 2001
Posts: 8
I ran this performance test showing that using a PreparedStatement was roughly 2.5x - 3.0x faster than creating the query each time:
private static void testQueries(Connection connection)
throws SQLException {
int numberOfRuns = 50;
//try regular statements - created and closed each time
long startTime = System.currentTimeMillis();
for(int i = 0; i < numberOfRuns; i++) {
ResultSet resultSet = executeQuery(connection,
"select ct.new_account_number "
+ "from dang0.customertypes@cmsm ct, dang0.customers@cmsm c "
+ "where ct.account_number = c.account_number "
+ "and c.status = 'A' "
+ "and rownum <= 100");
resultSet.getStatement().close();
}
System.out.println("Regular statements took " + (System.currentTimeMillis() - startTime));
//try creating and deleting preparedStatements
startTime = System.currentTimeMillis();
for(int i = 0; i < numberOfRuns; i++) {
PreparedStatement preparedStatement = connection.prepareStatement(
"select ct.new_account_number "
+ "from dang0.customertypes@cmsm ct, dang0.customers@cmsm c "
+ "where ct.account_number = c.account_number "
+ "and c.status = 'A' "
+ "and rownum <= ?");
preparedStatement.setInt(1,100);
ResultSet resultSet = preparedStatement.executeQuery();
preparedStatement.close();
}
System.out.println("Prepared statements took " + (System.currentTimeMillis() - startTime));
//try reusing 1 prepared statement
startTime = System.currentTimeMillis();
PreparedStatement preparedStatement
= createAndStorePreparedStatement("com.mpowercom.util.SQLTools.test",
connection,
"select ct.new_account_number "
+ "from dang0.customertypes@cmsm ct, dang0.customers@cmsm c "
+ "where ct.account_number = c.account_number "
+ "and c.status = 'A' "
+ "and rownum <= ?");
for(int i = 0; i < numberOfRuns; i++) {
preparedStatement.setInt(1,100);
ResultSet resultSet = preparedStatement.executeQuery();
}
preparedStatement.close();
System.out.println("1 Prepared execute took " + (System.currentTimeMillis() - startTime));
}
and got:
Regular statements took 27410
Prepared statements took 27570
1 Prepared execute took 9340
Subrahmanyam Allamaraju
Greenhorn

Joined: Nov 01, 2001
Posts: 20
I think the comparision is between using "static" PreparedStatement objects and non-static PreparedStatement objects. Since a PreparedStatement is bound to a resource (database), you should avoid making it static.


Subrahmanyam Allamaraju<BR>Author of <A HREF="http://www.amazon.com/exec/obidos/ASIN/1861005377/ref=ase_electricporkchop/103-0514572-3811868" TARGET=_blank rel="nofollow">Professional Java Server Programming J2EE 1.3 Edition</A>
Kevin Bolton
Greenhorn

Joined: Nov 20, 2001
Posts: 8
I could think of other ways to hold onto the PreparedStatement in a non-static way, e.g a hashtable in another class. I still wouldn't know when I could close the PreparedStatements. I suppose I could use a thread to close PreparedStatements that haven't been used in a while. Any better ideas?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

If you have many instances sharing one static PreparedStatement, wouldn't you run into simultaneous use problems? Since many objects share the same PreparedStatement, if instance 1 uses setString(1, "Jimmy") and instance 2 calls setString(1, "Sammy"), the instance 1 will also be set to "Sammy" instead of Jimmy because they share the same preparedStatement. Won't this produce unpredictable results with concurrent users/instances?
let me know if you agree/disagree with my statement.
Jamie
Kevin Bolton
Greenhorn

Joined: Nov 20, 2001
Posts: 8
Yes I suppose I would in a multi-threaded app. So I'll keep that in mind when I convert over. Perhaps the cost of synchronizing the preparedStatement will outweigh the benefit of it.
I'm looking at PoolMan currently. An associate turned me onto it. PoolMan supposedly provides connection pooling and PreparedStatement pooling. So perhaps I will have the benefit of prepared statements without worring about synchronizing the prepared statement.
Monty Ireland
Ranch Hand

Joined: Oct 03, 2000
Posts: 161
For a quick and dirty approach to fix your problem...
Create a class that contains all of you JDBC activity for you application.
Make this class a parent to all of you application code....
that way you do not have to use multi threading... all though is is a good solution. Also, you do not have to code multi prepared statements...
Apply the kiss method to your design....

------------------
Multi-Platform Database Developer ( on E.S.T. )


Multi Platform Database Developer & DBA on E.S.T.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: When to close static PreparedStatements
 
Similar Threads
Why close the connection, statement and resultset?
catching errors on inserts into multiple tables
How do you classify a method-local inner class defined inside a static method?
Object creation doubt
prepare statement or just statement