JDBC INTRICACIES:
PREPAREDSTATEMENTS AND CALLABLESTATEMENTS

The JDBC API can make implementing database connectivity for a Java application much easier. It doesn't take much to get started, just a Connection using the right driver, a Statement to execute, and the query results in a ResultSet. However, there are two more classes that make the JDBC even more powerful - PreparedStatement and CallableStatement. The former is very useful for dynamically determining insertion values or selection criteria, and the latter for calling stored procedures or functions. This article will provide an introduction to the usage of these two powerful JDBC constructs.

PreparedStatement is actually a sub-interface of Statement, and is initialized in a very similar manner. The query string for a PreparedStatement looks normal except for the question marks that mark the variables to replace at run-time. Each question mark must be initialized with the appropriate setXXX() method. Then the ResultSet is obtained in the same manner as a regular Statement. Here is an example using the table defined by:

CREATE TABLE employee
  (eid CHAR(9) NOT NULL
  ,fname VARCHAR2(10) NOT NULL
  ,lname VARCHAR2(10) NOT NULL
  ,age NUMBER(3)
  ,entrydate DATE
  );

...

  PreparedStatement stmt =
   con.prepareStatement(
    "SELECT fname, lname, age FROM employee WHERE eid = ?");

 stmt.setString(1, "999999999");

  ResultSet rs = stmt.executeQuery();

 while (rs.next()) {
   System.out.println("first name = " + rs.getString(1));
   System.out.println("last name = " + rs.getString(2));
   System.out.println("age = " + rs.getInt(3));
  }

...

This example retrieves the employee with the id number set by the setString() method. The first parameter specifies the number of the question mark to set, and the last argument is the value of that variable. PreparedStatements are easy to use and provide a much better alternative than using StringBuffers or String concatenation for creating the query strings at runtime.

CallableStatement is a sub-interface of PreparedStatement that is used to execute PL/SQL stored procedures directly on the database side. They also use the question mark placeholders to signify variables, and setXXX() methods to initialize those variables. In a stored procedure, the parameters may have one of three values: IN, OUT, or IN OUT, and for an argument that is either OUT or IN OUT, a registerOutParameter() method must be prepared before executing the CallableStatement. An example clarifies this:

CREATE OR REPLACE PROCEDURE insert_employee
 (p_eid IN CHAR
  ,p_fname IN VARCHAR2
  ,p_lname IN VARCHAR2
  ,p_age IN NUMBER
  ,p_entrydate OUT DATE
  ) IS
BEGIN
  p_entrydate := SYSDATE;

INSERT INTO employee
  (eid, fname, lname, age, entrydate) VALUES
  (p_eid, p_fname, p_lname, p_age, p_entrydate);
END insert_employee;

...

  CallableStatement stmt =
  con.prepareCall(
   "{call insert_employee(?, ?, ?, ?, ?)}");

 stmt.setString(1, "999999999");
  stmt.setString(2, "John");
  stmt.setString(3, "Smith");
  stmt.setInt(4, 30);
  stmt.registerOutParameter(5, Types.DATE);

 stmt.execute();

 System.out.println("entry date = " + stmt.getDate(5));

...

In this example, there is no ResultSet returned, but rather the entry date is obtained from the CallableStatement. Also, when registering the out parameter, its type must be specified in the second argument of registerOutParameter(), and in this case, it's a date. Another thing to keep in mind is that if a parameter is IN OUT, then it must be both initialized and registered.

CallableStatements and PreparedStatements are beneficial because they can be used to effectively decrease development time by eliminating unnecessarily complex code. This advantage, along with their relative ease of use, are compelling reasons why these classes should be used as much as possible when implementing database connectivity in Java applications.

Please send feedback to articles@thirdeyeconsulting.com