How to Use PreparedStatement in Java?

A PreparedStatement is a pre-compiled SQL statement. It is a subinterface of Statement. Prepared Statement objects have some useful additional features compared to Statement objects. PreparedStatement allows you to execute SQL queries with parameters, avoiding the need to hard-code values directly into the query.

Advantages of PreparedStatement

• When a PreparedStatement is created, the SQL query is passed as a parameter. This Prepared Statement contains a pre-compiled SQL query, so when the PreparedStatement is executed, the DBMS can just run the query instead of first compiling it.
• The same PreparedStatement can be reused with different parameter values during execution.
• An important advantage of PreparedStatements is that they prevent SQL injection attacks.

Steps to use PreparedStatement

Step 1. Create Connection to Database

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

Step 2. Prepare 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 for type and position 

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 All Resources

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

Methods of PreparedStatement:

• setInt(int, int): This method can be used to set integer value at the given parameter index.
• setString(int, string): This method can be used to set string value at the given parameter index.
• setFloat(int, float): This method can be used to set float value at the given parameter index.
• setDouble(int, double): 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.

Execute Query Example Code

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:

Execute Update Example Code 

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, 21);
        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: