aspose file tools*
The moose likes JDBC and the fly likes Difference between Statement and PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difference between Statement and PreparedStatement" Watch "Difference between Statement and PreparedStatement" New topic
Author

Difference between Statement and PreparedStatement

Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
Hi friends,
Could you please tell me when I should use statement and when I should use PreparedSstatment?
Thanks,
ELahe


Thanks,
Elahe
Jun Hong
Ranch Hand

Joined: Sep 05, 2001
Posts: 181
Choose the right Statement
Statement, preparedStatement, and CallableStatement are different objects used to send commands to a database.
A Statement object is used to execute simple SQL statements with no parameters; a PreparedStatement object is used for precompiled SQL statements that may have IN parameters; and a CallableStatement object is used to execute SQL stored procedures, which are groups of SQL statements used to do certain tasks. CallableStatement may have IN, OUT, and INOUT parameters.
There is a misunderstanding that using a PreparedStatement is always faster than using a Statement object. In fact, this is not true. A PreparedStatement object is precompiled and stored for future use. If you want to execute a SQL command several times, you should use Statement because the time used in precompiling is too long and the benefit will show up only after you use a PreparedStatement for more than 50 ~ 60 times.
CallableStatement is used to handle stored procedures. The reason for using a stored procedure is that you want to execute a series of SQL commands in the same sequence every time. By putting them together, you save the time to move all the related data back and forth over the network. Also, a stored procedure can be compiled before it is used. To improve performance, can we define single Statement as a stored procedure and execute it as CallableStatement? The answer is no. There is some overhead in using stored procedure. If your procedure is very simple, the overhead will hurt the performance. You should use CallableStatement, when you have a complex task that requires several SQL statements to complete.


Jun Hong<br />SCJP, SCJD, SCWCD, SCEA<br />IBM Certified Systems Expert(V4.0)
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

The best thing about a PreparedStatement is that it handles special characters for you.
Ex.
but with PreparedStatements, it is automatically handled for you.

Just for the record, I find that when you use a Statement, you should run every query through a method that escapes special characters so the program doesn't blow up in your face. This total process is just as slow as creating a PreparedStatement which I tend to find easier to read/follow anyways.
If you didn't already notice, I prefer PreparedStatement due to the better reliability, readability, maintainability, and minimal performance loss.
I've done some benchmarks using Oracle 8.1.6, classes12.zip, and java and I found no difference in time when executing 1000 different queries using a statement or PreparedStatment. I believe I posted the actual code in a previous thread, I'll see if I can find it for you.
Jamie
Jun Hong
Ranch Hand

Joined: Sep 05, 2001
Posts: 181
If you are interested in details there is another difference between PreparedStatement and Statement. If you are using OCI or thin driver, prepared statement supports update batch and statement doesn't support update batching.
Details:
Batching updates:
Batching updates is good for performance because it allows Statement or PreparedStatement objects to submit multiple SQL updates at one time to a database. However, batching is not always supported by Statement objects. For example, Oracle thin and OCI driver only support batching when a PreparedStatement object is used. Note, if you use PreparedStatement, you need to batch at least 50 updates to gain the benefit of precompiling. If batching is supported by Statement objects, you don�t need to worry about precompiling time. You can code a batched Statement like this:
Statement stmt = con.createStatement();
stmt.addBatch(�INSERT INTO table1
VALUES (1, 2, 3, 4)�);
stmt.addBatch(�INSERT INTO table1
VALUES (2, 2, 3, 4)�);
stmt.addBatch(�INSERT INTO table1
VALUES (3, 2, 3, 4)�);
int [ ] results = stmt.executeBatch();
Note that since CallableStatement implements PreparedStatement interface, it also supports batching. However, you can only call stored procedures that have input parameters or no parameters at all.
CallableStatement cstmt =
con.prepareCall({call myProcedure(?, ?)});
cstmt.setInt(1, 2);
cstmt.setString(2, �Employee�);
cstmt.addBatch();
cstmt.setInt(1, 3);
cstmt.setString(2, �Employee�);
cstmt.addBatch();
int [ ] results = cstmt.executeBatch();
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

"However, batching is not always supported by Statement objects. For example, Oracle thin and OCI driver only support batching when a PreparedStatement object is used. "
The above is not a true statement because I use the classes12.zip(thin) drivers to an Oracle 8.1.6 database and have no problems batching using a Statement??
I also have a question for Jun Hong. I have not been able to find any data on performance comparisons of Statement/PreparedStatement found in the thin/OCI drivers, so I created my own benchmarks(with a large error factor I'm sure! ). You mentioned twice not that "you need to batch at least 50 updates to gain the benefit of precompiling." Is this from experience, or did you read this somewhere? I've been looking for a benchmark or study that was done a little more professionally than mine!
Jamie
[ January 26, 2002: Message edited by: Jamie Robertson ]
Jun Hong
Ranch Hand

Joined: Sep 05, 2001
Posts: 181
Check out this link:
http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html
Jun Hong
Ranch Hand

Joined: Sep 05, 2001
Posts: 181
Java Programming with Oracle JDBC
By Donald Bales
December 2001
This is a new book. I hope the info there is not some old facts.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

looks good! This was EXACTLY what I was looking for! We'll have to see how this book is reviewed here at the ranch (Currently under Review). I hope the book is as informative as the sample chapter too.
Jamie
Elahe Shafie
Ranch Hand

Joined: Dec 12, 2001
Posts: 291
Thanks all for the answers
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Difference between Statement and PreparedStatement