Menu
I know the advantages of using PreparedStatement, which are
But I want to know when we use it instead of Statement?
697
Statement interface cannot accept parameters and useful when you are using static SQL statements at runtime. If you want to run SQL query only once then this interface is preferred over PreparedStatement.
Interface PreparedStatement. An object that represents a precompiled SQL statement. A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Prepared statements are much faster when you have to run the same statement multiple times, with different data. Thats because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.
Query is rewritten and compiled by the database server
If you don't use a prepared statement, the database server will have to parse, and compute an execution plan for the statement each time you run it. If you find that you'll run the same statement multiple times (with different parameters) then its worth preparing the statement once and reusing that prepared statement. If you are querying the database adhoc then there is probably little benefit to this.
Protected against SQL injection
This is an advantage you almost always want hence a good reason to use a PreparedStatement everytime. Its a consequence of having to parameterize the query but it does make running it a lot safer. The only time I can think of that this would not be useful is if you were allowing adhoc database queries; You might simply use the Statement object if you were prototyping the application and its quicker for you, or if the query contains no parameters.
135
Ask Tom's opinion:
The use of a Statement in JDBC should be 100% localized to being used for DDL (ALTER, CREATE, GRANT, etc) as these are the only statement types that cannot accept BIND VARIABLES.PreparedStatements or CallableStatements should be used for EVERY OTHER type of statement (DML, Queries). As these are the statement types that accept bind variables.
This is a fact, a rule, a law -- use prepared statements EVERYWHERE. Use STATEMENTS almost no where.
He's specifically talking about Oracle but the same principle applies to any database that caches execution plans.
Database apps that scale and prevent SQL injection attacks at the same time? What's the downside?
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
