When should we use a PreparedStatement instead of a Statement?

Question

I know the advantages of using PreparedStatement, which are

• query is rewritten and compiled by the database server
• protection against SQL injection

But I want to know when we use it instead of Statement?

Answer 1

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

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