PreparedStatement's - how do you use them CORRECTLY?

[Ivan Jouikov]

How can you actually SAVE a prepared statement, instead of creating a new one each time you want to query the database?

[Paul Sturrock]

You don't create a new one every time you query a database. First time you use a PS it is created and compile in the DB layer. Every subsequent call will reuse the compiled PS, binding different variables. Exactly how this is done is dependant on you DB platform, so have a read of its docs to find out more.

[arnel nicolas]

That is why it is expensive to use prepared statement.

[Paul Sturrock]

Quite the opposite. Yes using a statement cache in the DB layer will possibly use more memory than not using one, but it will reduce the work needed to run a query. Remember that SQL has to be interpreted each time a query is done if you just use Statements, where as Prepared Statements are interpreted and compiled only once.

[Ivan Jouikov]

So am I doing it right:

 /** Used to run the first test  :) .  Simply returns time by quering DB. */
public String testQuery() throws SQLException
{
Application.getInstance(); // make sure stuff's initialized
String result;
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
 
try
{
con = getConnection();
st = con.prepareStatement("SELECT CURTIME()");
rs = st.executeQuery();
rs.next();
result = rs.getString(1);
}
finally
{
try{ if ( st != null ) st.close(); } catch(Exception e){ e.printStackTrace(); }
try{ if ( con != null ) con.close(); } catch(Exception e){ e.printStackTrace(); }
}
 
return result;
} 

(except in real cases I would have variables and stuff, but I mean should I close it and all? Or should I keep it as a static object ?)

[Paul Sturrock]

You probably always want to close JDBC resources (I can't think of a situation where you would be better to keep them open anyway). Of course it depends what sort of behaviour your application requires, but generally that is the rule.

BTW: I understand your code is probably just a quick example, but you always should log/throw the exception rather than ignore it, and the only clean up statement you need in your finally block is to close the connection. All JDBC resources hang off this - so close the connection and your statement is also closed.
[ July 12, 2004: Message edited by: Paul Sturrock ]

[arnel nicolas]

Originally posted by Paul Sturrock:
Quite the opposite. Yes using a statement cache in the DB layer will possibly use more memory than not using one, but it will reduce the work needed to run a query. Remember that SQL has to be interpreted each time a query is done if you just use Statements, where as Prepared Statements are interpreted and compiled only once.

Yes i got your idea. Preparedstatement runs faster in the backend compared to ordinary SQL statement. But the resource of coming back and forth from the server is (too) expensive. Since it is a prepared statement, your JDBC driver will then send your SQL statement to your backend for precompilation then it goes back again to the client for the execution. That is why i think they run faster than ordinary SQL statement but they are network intensive. My suggestion is that try to avoid prepared statement as much as possible. If you can use ordinary SQL statement in your program, then don't try using prepared statement.

[ July 13, 2004: Message edited by: arnel nicolas ]
[ July 13, 2004: Message edited by: arnel nicolas ]

[Ivan Jouikov]

 If you can use ordinary SQL statement in your program, then don't try using prepared statement. 

Can somebody verify this? As far as I remember, JDBC ENCOURAGES to use PreparedStatement over normal statements...

[arnel nicolas]

Originally posted by Ivan Jouikov:

 If you can use ordinary SQL statement in your program, then don't try using prepared statement. 

Can somebody verify this? As far as I remember, JDBC ENCOURAGES to use PreparedStatement over normal statements...

In this case i am not discouraging you to use prepared statement. I just want to emphasize when and when NOT to use prepared statement.

[Bear Bibeault]

I almost always use PreparedStatements. I can think of no valid reason not to use them over "normal" statements; especially when any run-time parameters are used.

Anecdotal evidence of performance differences aside, I'd advise using what makes the most sense (which in most cases is a PreparedStatement) to your application, and then worry about any performance issues if any such issues pop up.

[arnel nicolas]

quoting from the code of Mr. Ivan Jouikov:

.....rest of the code

con = getConnection();
 
st = con.prepareStatement("SELECT CURTIME()");
 
rs = st.executeQuery();
 
rs.next();
 
result = rs.getString(1);

....rest of the code

What makes most sense with the above example code is to use "ordinary" statement rather than PreparedStatement because the SQL statement does not take any parameter.

Happy coding guys
[ July 12, 2004: Message edited by: arnel nicolas ]

[Jeanne Boyarsky]

I agree with Bear that you should use a prepared statement unless it is a proven performance problem. The prepared statement execution path gets cached so if you are executing the statement more than once, you get this benefit.

And if you are only executing it once, the performance doesn't matter anyway. So you can still use prepared statement.

[Sonny Gill]

Originally posted by Paul Sturrock:
the only clean up statement you need in your finally block is to close the connection. All JDBC resources hang off this - so close the connection and your statement is also closed.
[ July 12, 2004: Message edited by: Paul Sturrock ]

I cannot verify this, but I've heard/read about that, with some JDBC drivers, not closing the resultset and statement explicitly may leave some unreleased database resources even when you close the connection.
Also, if the connection was taken from a pool, calling close on it might simply return it back to the pool without actually closing it or the ResultSets and Statements.

[Paul Sturrock]

with some JDBC drivers, not closing the resultset and statement explicitly may leave some unreleased database resources even when you close the connection

This doesn't sound correct. The Statement and ResultSet both use the Connection instance to communicate with the DB. No Connection == no route to the DB. Specifically which drivers have you heard this about?

Also, if the connection was taken from a pool, calling close on it might simply return it back to the pool without actually closing it or the ResultSets and Statements

If you are using a connection pool then you are right - you are no longer directly manipulating the connection, you are delegating to the connection pool to do this for you. So it will just return it to he pool. However, unless the connection pool implementation is really bad it will behave (as far as you Java app is concerned) exactly like you have closed the connection.

[David O'Meara]

Originally posted by Bear Bibeault:
I almost always use PreparedStatements. I can think of no valid reason not to use them over "normal" statements; especially when any run-time parameters are used.

Anecdotal evidence of performance differences aside, I'd advise using what makes the most sense (which in most cases is a PreparedStatement) to your application, and then worry about any performance issues if any such issues pop up.

Ditto with bear, I almost always use PreparedStatements. I've seen articles which measure the performance (I appologise for not having the links available) and PS is almost always better.

There are two places I'm aware of where a Statement can be better than a PreparedStatement. The first is obviously where the statement keeps changing and therefore no statement caching gets carried out.

Interestingly, the second is where the statement statically rather than dynamically binds some values. A simple example is where you search for users by ID, but spend 90% of the time (for some reason) searching for the admin user. Consider the two queries:

String qry1 = "Select * from user where userID=?";
String qry2 = "Select * from user where userID=1";

The first will run faster in general, since the query can be cached would not need to be compiled every time it is run. The second, however, results in better optimised code after it has been compiled in the database.

If you ran the second query multiple times with hard-coded values you would almost certainly get worse performance than you would with a PS, but for a query which only really loads with static data, it is preferable to hard-code that data in the PS rather than binding them to allow better optimisation in the database.

The final point is that there is a point at which PS's become more efficient than plain statements. This takes into account the 'inefficiencies' mentioned above. The crossover point in the article I read was at about 60 operations, but this is a drop in the bucket for any program running a reasonable amount of time.

My conclusion was: best not to think about it. In the long run a PS will perform better, and in the cases where it doesn't you're unlikely to be able to tell the difference.

[Stan James]

Also prepared statements make handling of certain special characters much easier. You can set nearly any string into a parameter where you could esaily forget to escape the string into a vanilla SQL statement. It's fun to try '*$; in name fields to see if you can crash an app.

[arnel nicolas]

Everyone in this thread knows how to use their PreparedStatement correctly but i suggest not to do it blindly. Performancewise, if we know that we are going to execute a statement on one occasion then there is no reason to use PreparedStatement because a PreparedStatement execute results in two network round trips. If we prepare a statement then that's a network round-trip. If we execute that statement then that's another network round-trip. If we are going to execute that statement AGAIN at some point in the instance of the application, then we may use the PerparedStatement caching method.

We really don't need to wait until performance becomes an issue in our application, in fact we should avoid this before we get there. That is Defensive Programming.
[ July 18, 2004: Message edited by: arnel nicolas ]

[Bear Bibeault]

We really don't need to wait until performance becomes an issue in our application, in fact we should avoid this before we get there. That is Defensive Programming.

It can also be called Premature Optimization and can be the cause of some gnarly code. It is generally accepted that it is better to use clear and well-structured algorithms, and to optimize only when a concrete performance issue rears its head. That is not to say that there aren't some thing you can do to avoid known performance-killing patterns, but second-guessing the JVM optimizers rarely succeeds.

[arnel nicolas]

To use clear and well-structured algorithms is only part of the optimization. It should also be considered that we as developer should also know when to use the right method at the right place and at the right time.

When designing software at a system level, performance issues should always be considered from the beginning. A good software developer will do this automatically, having developed a feel for where performance issues will cause problems. Others will not bother, misguidedly believing that a bit of fine tuning at a later stage will fix any problems. This is usually happens in large scale projects.

[Stefan Wagner]

I agree to Arnel.
Using a prepared statement for a single-used query without parameters is only confusing?
It's not optimization - it's the appropriate method for a clear task.

I guess most benefits result from query-optimizers, solving complex 'WHERE'-clauses, from prepared statements, but that's only a guess.

[Sonny Gill]

Originally posted by Paul Sturrock:

with some JDBC drivers, not closing the resultset and statement explicitly may leave some unreleased database resources even when you close the connection


This doesn't sound correct. The Statement and ResultSet both use the Connection instance to communicate with the DB. No Connection == no route to the DB. Specifically which drivers have you heard this about?

Well I read it a while ago, but I cant find any references to where I read it now. As far as I can remember, it was some Oracle JDBC driver, and it was suggested that not closing the Statement explicitly might leave some cursors open in the database server. Most likely, if true, this was a problem in the early drivers, and was fixed.

May be I am just being paranoid, but to be safe I close all ResultSets and Statements explicitly in the finally block.

Referring to the other part of your posting, Is it possible to get references to all the Statement objects created from a Connection object. If not, how would a Connection Pool close the Statements created when a connection is returned?

[Jamie Robertson]

I don't think it is possible to compare 1 for 1 a statement vs preparedStatement. PreparedStatements do so much more for you without getting credit for it. PreparedStatements take care of a lot of the dirty work that is DB dependant such as escaping special characters and type checking. If you added those manually for every Statement you executed, the performance difference would not be as significant ( you know 10 ms vs 15 ms! ). You also have to take into consideration the increased protection against such things as SQL injection. Never have I come accross a situation where using a PreparedStatement over a Statement has been a reason for performance degredation. The only visible perfomance problems I have encountered in the last 5 years using JDBC have been network related, unoptimized queries, and too many rows.

Jamie
[ July 20, 2004: Message edited by: Jamie Robertson ]

[Phyllis @systemetrix]

Originally posted by Sonny Gill:


Well I read it a while ago, but I cant find any references to where I read it now. As far as I can remember, it was some Oracle JDBC driver, and it was suggested that not closing the Statement explicitly might leave some cursors open in the database server. Most likely, if true, this was a problem in the early drivers, and was fixed.

This is most certainly still a problem with Oracle JDBC Drivers. Close of the connection leaves cursors open (v$open_cursor). The resultset and statement have to be closed manually. As a result, there's something in my CallableStatement that is leaving cursors open now as well, in spite of closing the cs & the con.

[Jon Egan]

It seems like no one addressed Ivan's original question, which I interpretted as: How to use a PreparedStatement in such a way that you actually get the benefits they can provide.

Basically, you just need to find a way to keep the PreparedStatement object "alive" for multiple occasions when you need to run the same SQL.

Here are a couple of ways they can be used:

1. If you can defer making inserts/updates until you have more than one to process, then you can call the data access method passing a List:

public void myMethod(List<BusinessObject> valuesToInsert) throws DatabaseException {
 
    Connection conn = getConnection(); // from somewhere...
    PreparedStatement ps = conn.prepareStatement("insert into my_table (col1, col2) values (?, ?)");
 
    for (BusinessObject bo : valuesToInsert) {
        ps.setString(1, bo.getCol1Value());
        ps.setString(2, bo.getCol2Value());
 
        ps.executeUpdate();
    }
 
    ps.close();
 
    // assume all necessary Exception handling, etc
)

2. If you can afford one Connection object per thread (for example, batch processing, not web development), you can store off a Connection within the class doing the data access, or pass it into the data access method. Then, the Connection doesn't need to be closed between method calls, allowing you to keep the PreparedStatement object around between method cals as well (as an instance variable in the DAO class, for example).

 
/**
 * This method would be called once to initialize
 */
public void prepareToInsert() throws DatabaseException {
    conn = getConnection(); // from somewhere...
    ps = conn.prepareStatement("insert into my_table (col1, col2) values (?, ?)");
    // assume all necessary Exception handling, etc
}
 
/**
 * This method would be many times
 */
public void myMethod(BusinessObject bo) throws DatabaseException {
    ps.setString(1, bo.getCol1Value());
    ps.setString(2, bo.getCol2Value());
 
    ps.executeUpdate();
    // assume all necessary Exception handling, etc
}
 
/**
 * This method would be called once to wrap up
 */
public void allDoneWithInserts() throws DatabaseException {
    ps.close();
    conn.close();
 
    // assume all necessary Exception handling, etc
)
 
// instance variables
private Connection conn = null;
private PreparedStatement ps = null;
 

3. Wrap the Connection object by implementing the java.sql.Connection interface in a class with a "real" Connection as an instance variable. The implementation of most methods in the interface would be a simple pass-through to the "real" connection, but "prepareStatement" would be coded differently:

Build an instance variable to keep a HashMap<String, PreparedStatement> of PreparedStatement objects, stored by the String used to prepare them, which have been opened by that Connection.

Each time "prepareStatement" is called, check to see if the String passed is in the Hash. If so, return the corresponding value. Otherwise, create the PreparedStatement using the instance variable connection, and add it to the Hash.

Then, later, when you get that same "ConnectionWrapper" out of the pool, and try to call conn.prepareStatement() on it, it returns the PreparedStatement that was created previously.

This would work best if the number of Connections and the number of distinct PreparedStatements you needed was small, relative to the number of times you would end up running the queries/updates.

Hope this helps.