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.

JDBC Tutorial

Commit or Rollback transaction in finally block 

In most of JDBC books, the transaction management idiom that is followed is, after executing the update statements commit, and if an SQLException is thrown, rollback.

That is,

Connection con = null;
try{
  con = //...
  con.setAutoCommit(false);
 
  Statement stmt1 = ...
  stmt1.executeUpdate();

  // Some operations

  Statement stmt2 = ...
  stmt2.executeUpdate();

  con.commit();
  con.setAutoCommit(true);
}catch(SQLException e){
  if(con!=null){
    try{
      con.rollback();
    }catch(SQLException e){
      // Log the error...
    }
  }
}

The similar structure is followed in the JDBC(TM) API Tutorial and Reference from the Sun Microsystems.  

Have a look at the Transactions Tutorial and the Sample code provided.

There is a severe problem with this way of commiting and rollback. The problem is we are handling only the SQLException. What will happen if a RuntimeException occured after executing the first update statement but beforethe second update statement?

The transaction is opened, but neither commited nor rolled back. This will leave the data integrity into trouble. If we are reusing the same connection (as in most cases), and we commit the transaction in the next statements, we are into serious trouble. We have inconsitent data.

What is the solution?
Catch Exception instead of SQLException
A simpler and not recommended solution is, catch all the execeptions, including RuntimeException. Even now, what if an Error is thrown, say OutOfMemoryError or some VirtualMachineError or something else? What ever happens in the code, we should either the database should be committed or rolledback. So, the worst thing is we should catch the Throwable class, instead of Exception.

Doesn't this look awkward,Whenever we use transactions we should catch a Throwable class or atleast Exception class?

Use finally block
A clean solution and yet simple solution is, use finally block. Since it is always guaranteed that the finally block will be executed even when any Exception is thrown or even when the method is returned.



Connection con = null;
boolean success = false;
try{
  con = //...
  con.setAutoCommit(false);
 
  Statement stmt1 = ...
  stmt1.executeUpdate();

  // Some operations

  Statement stmt2 = ...
  stmt2.executeUpdate();

  success = true;

}catch(SQLException e){
  success = false;
}finally{
  if(con!=null){
    try{
      if(success){
        con.commit();
        con.setAutoCommit(true);
      }else{
        con.rollback();
      }
    }catch(SQLException e){
      // Log the error...
    }
  }
}

This way of implementing transactions guarantees that the
transaction is either committed or rolledback before we exit the method.