PreparedStatement in the JDBC

Anatomy→ PreparedStmt as a Data Carrier

PreparedStatement ( “precompiling”) is refer the process by which the SQL query structure is parsed, validated, and optimized by the database engine before the actual execution.

This precompilation happens only once, even if the query is executed multiple times with different parameters.

Anatomy of the PreparedStatement

I.PARSING THE QUERY

1. When you create a `PreparedStatement` in Java, the SQL query (with placeholders for parameters) is sent to the database.

2. The database engine first parses the query to ensure it is valid SQL. The placeholders (‘?’) are noted as positions for future parameter binding.

--Example 
Select * from users where ID is ?
-- now database engine valdate the keywors such as SELECT ,FROM  ,WHERE 

During parsing:

- The database identifies the SQL keywords (‘SELECT’, ‘FROM’, ‘WHERE’).

- It verifies that the table (‘users’) exists.

• It checks that the column (`id`) is valid and has the correct data type.

II.VALIDATION of

1. Syntax.

2. The tables and columns exist.

3. Checking that user permissions are sufficient to execute the query.

( At this stage, the database doesn’t know the actual values for the parameters (since the values are bound later), but it prepares the query structure.)

III.OPTIMIZATION(query execution plan)

1. Database will retrieve the data efficiently.

Get Manoj AKP’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

a) Table indexes,

b) Primary Key and decides the best way to execute the query. for instance O(log n)complexity

( The result of this step is an optimized plan that can be reused whenever the prepared statement is executed.)

Select * from users where ID
-- HERE ID IS AN PRIMARY KEY WHICH HAS  O(log n)complexity

IV.STORING THE PRECOMPILED STATEMENT

1) After creating the query execution plan, the database stores the compiled query (the parsed and optimized version of the SQL statement) in its internal memory or cache.

2) This step is crucial because it allows the database to skip parsing and optimization for subsequent executions of the prepared statement.

1) The database doesn’t need to parse or optimize the query again.

2) It simply reuses the precompiled execution plan.

3) The only difference will be the new parameter values bound to the placeholders.

V.PARAMETER BINDING VIA values to the placeholders (‘?’)

1) The database engine replaces the placeholders with the actual parameter values.

2) Since the query has already been parsed and optimized, the database simply inserts these values into the existing query execution plan.

SELECT * FROM users WHERE id = 1 — First execution

SELECT * FROM users WHERE id = 2 — Second execution

Each time, the database uses the same precompiled execution plan and only swaps out the bound parameters.

Merits & Mileage:-

1) Once-Per-Session Parsing: The query is parsed and optimized only once, which saves time if the same query is executed multiple times with different parameters.

2) Reduced CPU Overhead: Since parsing and optimization are resource-intensive tasks, reducing their frequency can improve overall database performance.

3) SQL Injection Protection: Since the structure of the query is precompiled, there’s no way for user-provided parameters to change the underlying query logic, preventing SQL injection attacks.

4) Batch Processing: Prepared statements are particularly useful when running the same query multiple times with different parameters (e.g., inserting or updating multiple rows), as the database doesn’t need to reparse or optimize the query each time.