The PreparedStatement Class

The PreparedStatement class is used to create and compile a statement at the database, and then invoke that statement multiple times. The statement usually has one or more IN parameters that change each time the statement is executed.

Both the executeUpdate() and executeQuery() methods work with the PreparedStatement class. The class is an extension of the standard Statement class; it has the additional flexibility of being stored in a compiled form and run many times.

To create the PreparedStatement class statement, use a SQL string that contains references to one or more unknown parameters:

String sqlselect =

"Select * from retail_item where company_id = ?";

// create Statement object

PreparedStatement stmt = con.prepareStatement(sqlselect);

Before executing the statement (in this example, it is a query), you must set the IN

The only difference between using the PreparedStatement class and the Statement class is that the same statement would then be processed for the former with different parameters.

A full example of using this class is given in the following Listing.

Listing. The sample6 application using the PreparedStatement class.

import java.lang.*;

import java.sql.*;

import sun.jdbc.odbc.*;

public class sample6 {

public static void main(String arg[]) { String result;

try {

//connect to ODBC database

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url = "jdbc:odbc:Zoo";

// connect

Connection con = DriverManager.getConnection(url,"dba", "sql");

con.setAutoCommit(false);

String sqlselect =

"Select * from retail_item where company_id = ?";

// create Statement object

PreparedStatement stmt = con.prepareStatement(sqlselect);

ResultSet rs;

Issuing an update instead of a query is no different except that the IN parameters are used to modify data instead of to select it, and that the executeUpdate() method is used instead of executeQuery():

String sqlselect =

"Update retail_item set company_id = ? where company_id = ?";

// create Statement object

The purpose of the PreparedStatement class is to compile the query ahead of time, which cuts back on the time necessary to process each query or update. If you are not planning to run the SQL statement multiple times, using the PreparedStatement class is not an effective approach.

PROBLEMS WITH PreparedStatement

The PreparedStatement class may not work if the database does not maintain an open connection between transactions; if the database does not support the use of compiled SQL; or if the JDBC drivers or the database drivers do not support this type of statement. If you use the statement with a JDBC driver that doesn't support it, you get a class exception.

If you use the statement with a database or driver that does not support it, you get a SQLException. In my experience, the PreparedStatement class did not work with the ODBC driver for Sybase SQL Anywhere; it also did not work with the driver I had for Microsoft Access at the time this chapter was written. Using the PreparedStatement class resulted in the error Invalid Cursor State, as shown here:

java sample6

Values are: 1 Eagle T-Shirt 1 8.50 14.95 1 Black S null Values are: 4 Wolf T-Shirt 1 8.50 14.95 1 Green S null Values are: 5 Wolf T-Shirt 1 8.50 14.95 1 Green M null Values are: 6 Wolf T-Shirt 1 8.50 14.95 1 Green L null Values are: 19 Snake Shirt 1 13.50 22.00 1 Green S null Values are: 20 Snake Shirt 1 13.50 22.00 1 Green M null Values are: 21 Snake Shirt 1 13.50 22.00 1 Green L null Values are: 22 Cat Shirt 1 8.00 13.00 1 Green XLG null Values are: 2 Eagle T-Shirt 1 8.50 14.95 1 Black M null Values are: 3 Eagle T-Shirt 1 8.50 14.95 1 Black L null Values are: 7 Wolf T-Shirt 1 8.50 14.95 1 Blue L ADSDFS

Values are: 23 Get Wild Tie Dyed T-Shirt 1 10.50 20.95 1 null XL null Values are: 24 Leopard T-Shirt 1 9.00 14.95 1 Brown L null

Values are: 30 Test 1 12.00 13.00 1 Orange S null

Values are: 40 Cats of the World T-Shirt 1 8.00 14.00 1 Black L null [Sybase][ODBC Driver]Invalid cursor state

The execute() Method

The execute() method is defined for use with the Statement class, which also makes it available for use with the PreparedStatement and CallableStatement classes. This method is used to process an unknown statement, a statement that may return multiple result sets and update counts, or both.

For example, you may have an ad-hoc SQL tool that allows the user to enter any valid SQL statement and then process the statement. The user passes in the SQL statement with a program call like this:

The result returned from the execute() method is a boolean value: it is false if there are no results or the statement contains an update; it is true if the statement returns at least one result set and no update counts.

If your application cares only about processing result sets from a statement, you can process the results as follows:

// if true, result set provide feedback to the user. The following Listing provides a complete example of using the execute() method.

Listing. The sample7 application using the execute() method.

import java.lang.*;

import java.sql.*;

import sun.jdbc.odbc.*;

public class sample7 {

public static void main(String arg[]) { String result;

try {

//connect to ODBC database

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url = "jdbc:odbc:Zoo";

// connect

Connection con = DriverManager.getConnection(url,"dba", "sql");

// create Statement object

System.out.println("Values are: " + result);

} } else {

int ct = stmt.getUpdateCount();

result = "Update count is " + result.valueOf(ct);

System.out.println(result);

}

//close connection con.close();

}

catch(Exception e) {

System.out.println(e.getMessage());

} } }

Running the example and passing in the string "Select * from retail_item" returns this result (only the first few lines of the result are shown because the result is fairly lengthy):

java sample7 "select * from retail_item"

Values are: 1 Eagle T-Shirt 1 8.50 14.95 1 Black S null Values are: 2 Chocolate Tigers 5 1.00 1.50 3 null M null Values are: 3 Stuffed Panda 1 13.50 21.00 2 null L null Values are: 4 Wolf T-Shirt 1 8.50 14.95 1 Green S null Values are: 5 Wolf T-Shirt 1 8.50 14.95 1 Green M null Values are: 6 Wolf T-Shirt 1 8.50 14.95 1 Green L null Values are: 3 Keychain Zoo Pen 1 .52 1.95 8 null null null Values are: 8 Taffy Pulls 3 .75 1.25 3 null null null

Values are: 9 Chocolate Pandas 5 1.00 1.50 3 null null null Values are: 10 Stuffed Giraffe 1 9.95 16.95 2 null null null

An application rarely wants to process an unknown statement, but you may want to run a stored procedure that has multiple result sets. This type of procedure is demonstrated in the next section.

PROBLEMS WITH Using Cursors

The execute() method requires the database to support cursors, which enable processing of multiple result sets. The execute() method also requires the database and database driver to support maintaining an open connection after a transaction. If the database does not support these features, the execute() method can result in an error such as invalid cursor state (as happened when I ran the sample7 application with both the Sybase SQL Anywhere ODBC driver and the Microsoft Access ODBC driver). Before spending time coding for something that may not work, test your JDBC and database drivers with the sample7 code in the above Listing.