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
Â
