Optimized Queries to the Database

A shortcut method of querying the database is to use the executeQuery() method of the Statement object. This combines both execute() and getResultSet() in one method. The preceding example can be written as

// Obtain the ResultSet directly
ResultSet rs = stmt.executeQuery("SELECT * DAY09_STUDENTS");

Both simple queries, such as the one specified in the preceding example, and more sophisticated joins can be specified as a parameter String to the executeQuery() method.

Another variant used with the INSERT, UPDATE, and DELETE operations is the executeUpdate() method:

// Using the executeUpdate method instead of execute()
String sql = "INSERT into DAY09_STUDENTS values " +
   "('1', 'LILLIAN', 'GHALY', '15 DIAMOND ST, BOSTON, MA')");
stmt.executeUpdate(sql);

The executeUpdate() method specializes in DML operations. DDL operations, such as drop table, create table, and so on, are made available only through the execute() method as explained in the previous section.

Using a PreparedStatement

In situations where the same statement is performed repeatedly, a more optimized way is to use a PreparedStatement. This divides the operation into a creation phase and an execution phase. When creating the statement, the database is instructed to be ready by pre-parsing and compiling the statement, and preparing its buffers to assign variables to the table elements. The execution phase requests the database to execute the operation after the required elements filled up. Let's illustrate this with the following code:

// Create a PreparedStatement
PreparedStatement pstmt =
     conn.preparedStatement("INSERT INTO DAY09_STUDENT values (?,?,?,?)")

The database is now instructed to prepare the buffers for the operation. Each data element is mapped to the wild card in sequence. We use setString() method to fill up the holes:

// Fill up the data elements and execute
pstmt.setInt(1, 2);
pstmt.setString(2, "DOUG");
pstmt.setString(3, "ZANDER");
pstmt.setString(4, "11 ORANGE AVE, SACRAMENTO, CA");
int rcount = pstmt.executeUpdate();

Other methods to pass these parameters depend on the parameter type, and take the pattern setXXX(). For example, setInt() to pass in an int, setFloat() to pass in a float, setBoolean() to pass in a boolean, and so on.

Using PreparedStatement saves time for a repeated statement, and hence enhances the performance of your application. The return value of the last executeUpdate() indicates the number of rows affected as a result of any INSERT, UPDATE, or DELETE operation. The PreparedStatement inherits all its properties and methods from the Statement object.

Using a CallableStatement for Stored Procedures

The JDBC API provides support for calling a stored procedure. The CallableStatement inherits from PreparedStatement, and is used to call a stored procedure. A stored procedure is a group of SQL statements that can be called by name, and are stored in a file and managed by the underlying Relational Database Management System (RDBMS). Stored procedures, once written, can be compiled and then reused. They are executed on the database server, which relieves the application server of performing the task. The CallableStatement is created from the Connection method prepareCall().

The following snippet demonstrates how the stored procedure getStudentById is created and sent to the RDBMS to compile and store under the name getStudentById:

// Create stored procedure
String storedPoc = "create procedure GetStudentById(Stid integer)" +
     "as begin" +
     "SELECT * FROM DAY09_STUDENT" +
     "WHERE student_id = 'Stid'" +
     "end";
Statement stmt = conn.createStatement();
stmt.executeUpdate(storedPoc);

The next code demonstrates the use of a CallableStatement that calls the previous stored procedure getStudentById:

CallableStatement cstmt = conn.prepareCall(
               "{call getStudentById(?)}");
cstmt.setInt(1,4);
ResultSet rs = cstmt.executeQuery();

The variable cstmt contains a call to the stored procedure getStudentById, which has one IN parameter, represented by the wildcard ? placeholder. Normally, stored procedure parameter types can be one of type IN, OUT, or INOUT. Passing in any IN parameter values to a CallableStatement object is done using the setXXX() methods inherited from PreparedStatement. The JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. Registering the JDBC type is done with the method registerOutParameter() of the CallableStatement. After the statement has been executed, CallableStatement's getXXX() methods can be used to retrieve OUT parameter values. An INOUT parameter requires both treatments of IN and OUT parameter.