This week's book giveaway is in the Agile and other Processes forum. We're giving away four copies of The Mikado Method and have Ola Ellnestam and Daniel Brolund on-line! See this thread for details.
Difference between Statement and PreparedStatement
Elahe Shafie
Ranch Hand
Joined: Dec 12, 2001
Posts: 291
posted
0
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
posted
0
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)
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
posted
0
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();
"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 ]
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
0
). 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! 
