Statement and PreparedStatement question?

[Joe San]

Guys,

What exactly is the difference between PreparesStatement and Statement?? It is said that a PreparedStatement is given the SQL statement at the time it is created which means, a PreparedStatement object contains a precompiled SQL statement?? What it means by saying a precompiled SQL statement??

[Freddy Wong]

As far as I know, most databases handle the SQL JDBC in few steps, i.e. parse the SQL statement, compile it, and execute it. By using PreparedStatement, the steps of parsing and compiling aren't necessary anymore because those steps have been pre-executed. Thus, it speeds up the process. Another benefit of using PreparedStatement is to prevent the SQL injection.

[Joe San]

Parsing?? Is it an XML??

[Surya Lanka]

Prepared Statement will be much helpful if you have to use multiple sql statements of similar type.For example if you have to do multiple insertions of type INSERT into table_name values("a","b"...); for n number of times with a change in the values of a and b then we can use prepared statement in the below way:-

PreparedStatement pstmt = con.prepareStatement("INSERT into table_name values(?,?...);
pstmt.setDataType(1,value);
pstmt.setDataType(2,value);
...
Where DataType can be String,Int and so on as per datatype.

[Joe San]

The same thing of multiple insertions, I can do with just a Statement object by using a for loop....but why explicitly I need a PreparedStatement??

[Paul Sturrock]

Originally posted by Jothi Shankar Kumar Sankararaj:
Parsing?? Is it an XML??

No, but SQL is not what a database runs. Like all scripting languages something has to interpret the human-readable script and turn it into something the database can use. So a database will parse the SQL to compile it to something else before running it.

[Paul Sturrock]

Originally posted by Jothi Shankar Kumar Sankararaj:
The same thing of multiple insertions, I can do with just a Statement object by using a for loop....but why explicitly I need a PreparedStatement??

Yes, you can just use statement in a loop. However, each statement will be parsed, compiled and run. If your use a PreparedStatement and only change the values of the bound parameters in the loop you use one statement - so it is parsed and compiled once.

Freddy Wong highlights another useful side effect of prepared statements in that they prevet SQL injection. In addition, they are also useful in that they isolate the programmer from formatting or character escaping issues (i.e. they don't need to care about the format of a string that represents a date, they can just bind a Date object)

Have you read our JDBC FAQs?
[ June 26, 2007: Message edited by: Paul Sturrock ]

[Joe San]

Yes, I understood the concept now. Thanks!