Handling CallableStatement using named parameters

Description: statement processing in JDBC

In the JDBC application, the JDBC Statement object is used to send SQL statements to the database server. A statement object is associated with a connection, and communication between the application and the database server is handled by the statement object.

There are three kinds of statement objects in JDBC:

Regular statements (general statement)

Preset statements (Prepared statement)

Callable statement (callable statement)

A statement object is associated with a connection, so to create a statement object, you should first establish a database connection.

Create a connection

The code example in Listing 1 shows how to create a connection:

Listing 1. Code example that loads the Informix driver and creates a connection

Connection con = null;
try {
Class.forName("com.informix.jdbc.IfxDriver");
String url = "jdbc:informix-sqli://hostname:port_number/dbname:
informixserver=servername; userid=userid;password=pwd;";
con = DriverManager.getConnection(url);
}

Now examine each of these three types of statement objects one by one.

General statements

You can use the Createstatement method of the connection to create this statement. This statement is dedicated to an SQL statement that does not need to pass any value as a parameter.

Listing 2. Demo example code to create a statement

Statement stmt = con.createStatement();
cmd = "create database testDB;";
rc = stmt.executeUpdate(cmd);
stmt.close();

Preset statements

A preset statement is a subclass of the statement class. The main difference between a preset statement and a statement class is that the former can be compiled and optimized only once, and then used more than once by setting different parameter values. So, if you want to execute a single statement multiple times, a preset statement is a better choice. The execution time is reduced because it has been precompiled. Thus, the advantage of a preset statement is that it contains not only a single SQL statement, but a precompiled SQL statement as well. Another difference is that an SQL statement is provided to a preset statement after it is created.

Listing 3. Example code to interpret a preset statement

PreparedStatement pstmt = con.prepareStatement("UPDATE tab1 "+
"set col1 = ? where key = 1");
pstmt.setShort(1, (short)2);
int rowcount = pstmt.executeUpdate();

Here, the same preset statement can be used for different col1 values. Once the parameter is set, its value remains unchanged until it is reset or clearparameters is invoked. This feature allows preset statements to be used for batch processing of insert/update.