What does it mean when I say Prepared statement is pre-compiled?

Question

I am using MySQL in Java. I don't have a good understanding of PreparedStatement.
I know it is better to use PreparedStatement than Statement. The reason being it is compiled.

What do we mean by compiled?

Answer 1

When you use prepared statement(i.e pre-compiled statement), As soon as DB gets this statement, it compiles it and caches it so that it can use the last compiled statement for successive call of same statement. So it becomes pre-compiled for successive calls.

You generally use prepared statement with bind variables where you provide the variables at run time. Now what happens for successive execution of prepared statements, you can provide the variables which are different from previous calls. From DB point of view, it does not have to compile the statement every time, will just insert the bind variables at rum time. So becomes faster.

Other advantages of prepared statements are :-

1)protection against SQL-injection attack

2) Faster for successive calls of same statements

How it works :-

1. Precompilation is done by the database. Some simpler databases don't precompile statements at all. Others might precompile it on the prepareStatement call, and yet others might do it when execute is first called on the statement, taking values of the parameters into account when compiling (creating a plan for) the statement.

2. Databases that do precompile statements usually cache them, so in all probability ps1 won't be compiled again. Some JDBC drivers (eg. Oracle's) even cache prepared statements, so they haven't actually closed it when ps.close() was called.

3. Databases generally cache statements until something evicts them from the cache.

For details go through this wiki link