How to Use PreparedStatement in Java?

0 0 2 minutes read

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();
    }
}