When to close static PreparedStatements

[Kevin Bolton]

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]

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]

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]

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.

[Kevin Bolton]

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]

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]

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]

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. )