Working with JDBC PreparedStatement

As you know, the PreparedStatement interface extends the Statement interface. Its added functionality also gives it a couple of advantages over a generic Statement object.

First, it gives you the flexibility of supplying arguments dynamically. Although you can use the Statement object to build and execute your SQL statements on the fly, the PreparedStatement object reduces your work.

Second, when you create a PreparedStatement object JDBC “prepares” the SQL statement for execution by sending it to the database, which then parses, compiles, and builds a query execution plan. This parsed statement lives in memory and remains ready to use during your database session or until you close the PreparedStatement object.

Creating PreparedStatement Object: Just as a Connection object creates the Statement object, it also creates a PreparedStatement object. The following code snippet shows how to employ its prepareStatement() method to instantiate a PreparedStatement object:

Connection conn = DriverManager.getConnection(url, "scott", "tiger");
String SQL = "Update employees SET salary = ? WHERE ename = ?";
PreparedStatement prepStmt = conn.prepareStatement(SQL);}

Using PreparedStatement Object: To bind values to parameters you use the setXXX() methods. JDBC uses the setXXX methods to convert the Java data type to the
appropriate SQL data type for your target database, as shown in the following code snippet:

String SQL = "UPDATE employees SET salary = ? WHERE ename = ?";
PreparedStatement pstmt = conn.prepareStatement(SQL);
//bind variables
pstmt.setInt(1,100000);
pstmt.setString(2,"John Smith");
pstmt.executeUpdate();

Let’s now look at an example to see the use of PreparedStatement Object.

import java.sql.*;
 
public class PreparedStatementDemo {
 
public static void main(String s[]) throws Exception {
 
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
 
Connection con= DriverManager.getConnection (
"jdbc:oracle:thin:@mysys:1521:khan","scott","tiger");
 
String query="insert into employee values (?,?,?)";
 
//Step1: Get PreparedStatement
PreparedStatement ps=con.prepareStatement (query);
//Step2: set parameters
ps.setString(1,"abc1");
ps.setInt(2,38);
ps.setDouble(3,158.75);
 
//Step3: execute the query
int i=ps.executeUpdate();
System.out.println("record inserted count:"+i);
 
//To execute the query once again
ps.setString(1,"abc2");
ps.setInt(2,39);
ps.setDouble(3,158.75);
 
i=ps.executeUpdate();
System.out.println("Second time count: "+i);
con.close();
}//main
 
}//class

Checking the update count value can be a sanity check when you’re executing your SQL statements. If you update a row by its primary key then you should always receive an update count of 1. Any other value may indicate an error.