aspose file tools*
The moose likes JDBC and the fly likes PreparedStatement's - how do you use them CORRECTLY? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement Watch "PreparedStatement New topic
Author

PreparedStatement's - how do you use them CORRECTLY?

Ivan Jouikov
Ranch Hand

Joined: Jul 22, 2003
Posts: 269
How can you actually SAVE a prepared statement, instead of creating a new one each time you want to query the database?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
arnel nicolas
Ranch Hand

Joined: Dec 16, 2003
Posts: 149
That is why it is expensive to use prepared statement.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jul 22, 2003
Posts: 269
So am I doing it right:



(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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Dec 16, 2003
Posts: 149
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
Ranch Hand

Joined: Jul 22, 2003
Posts: 269


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

Joined: Dec 16, 2003
Posts: 149
Originally posted by Ivan Jouikov:


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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61437
    
  67

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
arnel nicolas
Ranch Hand

Joined: Dec 16, 2003
Posts: 149
quoting from the code of Mr. Ivan Jouikov:

.....rest of the code

....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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30780
    
157

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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.


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Rancher

Joined: Mar 06, 2001
Posts: 13459

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:



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
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
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.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
arnel nicolas
Ranch Hand

Joined: Dec 16, 2003
Posts: 149
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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61437
    
  67

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

Joined: Dec 16, 2003
Posts: 149
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
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

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.


http://home.arcor.de/hirnstrom/bewerbung
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

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

Joined: Jul 09, 2001
Posts: 1879

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
Greenhorn

Joined: Mar 08, 2005
Posts: 2
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
Ranch Hand

Joined: Mar 24, 2004
Posts: 83
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:



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



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.
 
Consider Paul's rocket mass heater.
 
subject: PreparedStatement's - how do you use them CORRECTLY?