When we use PreparedStatement instead of statement?

Question

Hi, I know the advantages of useing prepareStatement which are

_When you execute a query this query is rewritten and compiled and the by the database server

_Protected against SQL injection

but I want to know that when we use it instead of Statement? thanks.

Answer 1

e.g. for a OLTP system, you may run the same query with only few parameter (username, login date, etc) change.

Answer 2

1. "Query is rewritten and compiled and the 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 resuing that prepared statement. If you are querying the database adhoc then there is probably little benifit to this.

2. "Protected against SQL injection"

   This is an advantage you almost always want hence a good reason to use a PrepareStatement everytime. Its a consequence of having to parameterise 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

Answer 3

I would turn this round: in a publicly distributed app, you should generally always use prepared statements unless you have a really compelling reason not to, and you should always supply parameters "properly" to the prepared statement, and not by splicing them into the query string.

Why? Well, basically because of the reasons you gave (or at least, the second one)...

Answer 4

Besides preventing SQL injection, formatting portability (which you can't get from Statement), performance is the obvious reason. However, PreparedStatement doesn't come without any penalty. For example, it is generally slower than Statement if running only once, as there is some overhead. So the general idea is PreparedStatement should be used when you are performing the same query many many times. However, how much overhead is very database server implementation-specific, so exactly when to choose PreparedStatement over Statement, from performance consideration, should really be based on your actual experience/experiments of a specific database server.