How to Use PreparedStatement in Java

A PreparedStatement in Java is a pre-compiled SQL statement. It is a subinterface of Statement, but comes with additional benefits like improved performance, cleaner code and protection against SQL injection attacks. Instead of hardcoding values into SQL queries, PreparedStatement allows you to use placeholders (?) that can be set dynamically at runtime.

Why Use PreparedStatement

• Performance Improvement: Queries are compiled once and reused.
• Reusability: The same query can be executed with different parameter values.
• Security: Prevents SQL injection by safely binding user input.
• Code Readability: Avoids messy string concatenations in SQL queries.

Steps to use PreparedStatement

Step 1: Create a Database Connection

Connection myCon = DriverManager.getConnection(url, username, password)

Step 2: Prepare the SQL Statement

Instead of hardcoding queries like,

SELECT * FROM students WHERE age > 10 AND name = 'Chhavi';

Set parameter placeholders(use question mark for placeholders) like,

select * from students where age> ? and name = ?

PreparedStatement myStmt;
myStmt = myCon.prepareStatement("SELECT * FROM students WHERE age > ? AND name = ?");

Step 3: Set Parameter Values

Each ? corresponds to a parameter index starting from 1. 

myStmt.setInt(1,10);
myStmt.setString(2,"Chhavi");

Step 4: Execute the Query

ResultSet myRs = myStmt.executeQuery(); // For SELECT queries
int rowsAffected = myStmt.executeUpdate(); // For INSERT, UPDATE, DELETE

Step 5: Process the Results (for SELECT)

while (myRs.next()) {

int id = myRs.getInt("id");

String name = myRs.getString("name");

int age = myRs.getInt("age");

// Process the retrieved data

}

Step 6: Close Resources

myRs.close();
myStmt.close();
myCon.close();

Common Methods in PreparedStatement

• setInt(int index, int value): This method can be used to set integer value at the given parameter index.
• setString(int index, String value): This method can be used to set string value at the given parameter index.
• setFloat(int index, float value): This method can be used to set float value at the given parameter index.
• setDouble(int index, double value): This method can be used to set a double value at the given parameter index.
• executeUpdate(): This method can be used to create, drop, insert, update, delete etc. It returns int type.
• executeQuery(): It returns an instance of ResultSet when a select query is executed.

Example Table: students

We will work on a table named students with the following structure and sample data:

Step 1: Create the Table

Step 2: Insert Sample Data

Step 3: Display Table Content

After insertion, the table will look like this:

Step 4: Use PreparedStatement Queries

4.1 SELECT Query Example

 Output:

4.2 INSERT Query Example

Output:

Updated table:

Note:

Class.forName("org.apache.derby.jdbc.ClientDriver"); is needed only for older JDBC versions (pre-4.0). In JDBC 4.0+, drivers are auto-loaded if the correct JDBC JAR is in the classpath. If you get a No suitable driver error, check that the proper driver dependency is added.