Question on Prepared Statement in Java

Question

We have a Prepared Statement in Java and we hear that it is different from Statement in the Way that this prepared statement need not be compiled every time it is used.

My question is where is this Compiled Statement stored ? In the Client code that uses it or is it stored by the Database ?

Why would a DB Store a Compiled statement and if it does than for how long ?

Answer 1

Databases have a query cache, which means that when you execute a query the database caches the parsed/compiled query and maybe the query plan to eliminate future computations.

The prepared statements are typically cached first at the application level, where the application container is responsible for managing the statements cache. Most application containers have options to control the prepared statements cache (eg: glassfish).

As you can see here, the main difference is like you stated: with prepared statements, the application reduce the cost of compiling the same statement over and over. As a side note, for most app containers the statement must be exactly the same (whitespace included) in order to be reused correctly, so be careful when using hand-written queries.