How PreparedStatement Works Internally in Java: How compilation & caching of SQL works

Question

I read about it in the internet but I am not able to get answer for some queries.

Q1. It is said that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. My question is when preparedStetement.execute() is called what happens? (My understanding: The sql without any parameter is sent to DBMS which is compiled & cached by the DBMS for future use. Then the parameters are sent & DBMS replace them with the place holder & execute.)

Q2. If next time I execute preparedStetement.execute() with the same sql then what happens? (My understanding: The DBMS compares the sql with the previous sql & if it matches it takes the compiled sql, replace parameters & execute.)

Q3. If I call database occasionally then prepared statement won't help me in performance improvement because the database cache will be cleared during that time. So the sql will be compiled every-time. Right?

Answer 1

preparedStatement need the support from DBMS.

if the sql has been compiled, the DB will cache it. When the same one appears again, just send the parameters to complete the cached sql.

prepareStatement has three advantages:

1. make the code more clearly so you can read it easier.

2. improve the performance as far as possible. redue the compile time.

3. most important, it makes the sql more secure. if your sql is like this below:

   String sql = "select * from users where userid = " + userid; // use statement
   

   and someone give it a userid value like

   userid = "1;delete users;";
   

   the statement will execute the sql as

   "select * from users where userid=1;"
   "delete users;"
   

it is a very dangerouse operation for a database if the operator really has the right to do this.

if we use preparestatement

String sql = "select * from users where userid = ?"; // use preparestatement

the database will compile the sql as "select * from users where userid = '?'" and wait for the parameter "?" which means the sql will be execute like this

"select * from users where userid = '1;delete users;'  ;" // of course, it will select 0 column. 

treat the parameter just like a string. This is the Note in the Interface java.sql.Connection Class. read it

/**
 * Creates a <code>PreparedStatement</code> object for sending
 * parameterized SQL statements to the database.
 * <P>
 * A SQL statement with or without IN parameters can be
 * pre-compiled and stored in a <code>PreparedStatement</code> object. This
 * object can then be used to efficiently execute this statement
 * multiple times.
 *
 * <P><B>Note:</B> This method is optimized for handling
 * parametric SQL statements that benefit from precompilation. If
 * the driver supports precompilation,
 * the method <code>prepareStatement</code> will send
 * the statement to the database for precompilation. Some drivers
 * may not support precompilation. In this case, the statement may
 * not be sent to the database until the <code>PreparedStatement</code>
 * object is executed.  This has no direct effect on users; however, it does
 * affect which methods throw certain <code>SQLException</code> objects.
 * <P>
 * Result sets created using the returned <code>PreparedStatement</code>
 * object will by default be type <code>TYPE_FORWARD_ONLY</code>
 * and have a concurrency level of <code>CONCUR_READ_ONLY</code>.
 * The holdability of the created result sets can be determined by
 * calling {@link #getHoldability}.
 *
 * @param sql an SQL statement that may contain one or more '?' IN
 * parameter placeholders
 * @return a new default <code>PreparedStatement</code> object containing the
 * pre-compiled SQL statement
 * @exception SQLException if a database access error occurs
 * or this method is called on a closed connection
 */
PreparedStatement prepareStatement(String sql)
    throws SQLException;

Answer 2

java.sql.PreparedStatement is API defined by JRE.

Actual internal behavior of execute() depends on JDBC driver implementation provided by DBMS vendor.

For example, there are embedded databases (SQLite, H2) where sending statement to server makes no sense.

You should consult with documentation of JDBC driver you are using.

Answer 3

Adding to rkosegi's answer above, I want to describe a bit more about how this works with the PostgreSQL driver because it highlights some of the difficulties here.

With the PostgreSQL driver (see documentation), the behavior is actually configurable and quite complex.

The PostgreSQL query protocol allows queries and parameters to be sent separately and so the PreparedStatement API does not need to go through the process of asking the database to prepare a statement and then execute it later. The tradeoff is that there is extra overhead with server-side preparations in the initial preparation and problems of when to re-use query plans, but this is off-set against shorter start-up time for query re-use. So if you are optimizing and you are repeatedly looking up a single record you want to prepare, while if you are running on variable ranges or not re-using statements, then you probably don't. This is configured on the database connection. This is usually set as a threshold regarding when you start caching plans after a certain amount of re-use.

When server-side prepare is not used the driver sends to the database the parameterized query and arguments separately in the same command. The server then parses the query, inserts the variables into the appropriate places, and plans. The plan is then discarded after it is run.

Then server-side prepare is used, it works as you describe, however.

So here is a hard example of why you cannot ask the question and get a definite answer. JDBC provides a programming interface and the drivers provide the details in how it works. prepareStatement does NOT define whether the query plan is cached or not. That is a decision up to the driver.