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 Statementobject.

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 PreparedStatementobject.

Creating PreparedStatement Object: Just as a Connection object creates theStatement object, it also creates a PreparedStatement object. The following code snippet shows how to employ its prepareStatement() method to instantiate aPreparedStatement 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 thesetXXX() 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,"Tousif Khan");
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

Tips

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.