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 than Statement objects. Instead of hard coding queries, PreparedStatement object provides a feature to execute a parameterized query.

Advantages of PreparedStatement

• When 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, DBMS can just run the query instead of first compiling it.
• We can use the same PreparedStatement and supply with different parameters at the time of execution.
• An important advantage of PreparedStatements is that they prevent SQL injection attacks.

Steps to use PreparedStatement

1. Create Connection to Database

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

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 = ?);

3. Set parameter values for type and position

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

4. Execute the Query

ResultSet myRs= myStmt.executeQuery();  

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.

Original Table

Execute Query Example Code

Java

// Java program to execute a query using PreparedStatement

import java.sql.*;

public class GFG {

    // Driver Code
    public static void main(String[] args) throws Exception
    {

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

        // Connection to your database
        Connection con = DriverManager.getConnection();

        // Query which needs parameters
        String query
            = "Select * from students where age> ? and name = ?";

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

        // Set Parameters
        myStmt.setInt(1, 20);
        myStmt.setString(2, 'Prateek');

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

        System.out.println('Age      Name');

        // Display function to show the Resultset
        while (myRs.next()) {
            String Name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println(Name + "     " + age);
        }

        // Close the connection
        con.close();
    }
}

Output

Execute Update Example Code

Java

// Java program to update a table using PreparedStatement

import java.sql.*;

public class GFG {

    // Driver Code
    public static void main(String[] args) throws Exception
    {

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

        // Connection to your database
        Connection con = DriverManager.getConnection();

        // Query which needs parameters
        String query = "insert into Students values(?,?)";

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

        // Set Parameters
        myStmt.setInt(1, 21);
        myStmt.setString(2, 'Prajjwal');

        // Execute SQL query
        int res = myStmt.executeUpdate();

        // Display the records inserted
        System.out.println(res + " records inserted");

        // Close the connection
        con.close();
    }
}

Output 

Table After Inserting Values