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

CREATE TABLE students (
    age INT,
    name VARCHAR(50)
);

Step 2: Insert Sample Data

INSERT INTO students (age, name) VALUES 
(18, 'Arpit'),
(19, 'Chhavi'),
(20, 'Prakhar'),
(20, 'Prateek');

Step 3: Display Table Content

After insertion, the table will look like this:

Step 4: Use PreparedStatement Queries

4.1 SELECT Query Example

import java.sql.*;

public class GFG {
    public static void main(String[] args) throws Exception {

        // Register Driver Class (Derby Embedded or Client)
        Class.forName("org.apache.derby.jdbc.ClientDriver");

        // Establish Connection (update URL, user, password as per your DB setup)
        String url = "jdbc:derby://localhost:1527/yourDatabaseName";
        String username = "yourUsername";
        String password = "yourPassword";
        Connection con = DriverManager.getConnection(url, username, password);

        // SQL Query with parameters
        String query = "SELECT * FROM students WHERE age > ? AND name = ?";

        // Create PreparedStatement
        PreparedStatement myStmt = con.prepareStatement(query);

        // Set parameters
        myStmt.setInt(1, 20);
        myStmt.setString(2, "Prateek");

        // Execute query
        ResultSet myRs = myStmt.executeQuery();

        // Display results
        System.out.println("Name\tAge");
        while (myRs.next()) {
            String name = myRs.getString("name");
            int age = myRs.getInt("age");
            System.out.println(name + "\t" + age);
        }

        // Close resources
        myRs.close();
        myStmt.close();
        con.close();
    }
}

 Output:

4.2 INSERT Query Example

import java.sql.*;

public class GFG {
    public static void main(String[] args) throws Exception {

        // Register the JDBC Driver
        Class.forName("org.apache.derby.jdbc.ClientDriver");

        // Connect to the database (update with your actual DB info)
        String url = "jdbc:derby://localhost:1527/yourDatabaseName";
        String username = "yourUsername";
        String password = "yourPassword";
        Connection con = DriverManager.getConnection(url, username, password);

        // Insert query with placeholders
        String query = "INSERT INTO Students (age, name) VALUES (?, ?)";

        // Prepare the statement
        PreparedStatement myStmt = con.prepareStatement(query);

        // Set the parameter values
        myStmt.setInt(1, 20);
        myStmt.setString(2, "Prajjwal");

        // Execute the insert operation
        int res = myStmt.executeUpdate();

        // Show how many rows were inserted
        System.out.println(res + " record(s) inserted");

        // Close resources
        myStmt.close();
        con.close();
    }
}

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.