TOC
BACK
FORWARD
HOME

Java 1.1 Unleashed

- 43 -
Getting Started with JDBC

by Shelley Powers

IN THIS CHAPTER

  • Connecting to a Database and Making Simple Queries

  • More Complex Uses of JDBC

  • Working with Multiple Databases

The Java Database Connectivity (JDBC) API includes classes that can handle most database access needs. From simple select statements to processing the results of a complex stored procedure to accessing more than one database at a time, the JDBC provides the functionality most applications need.

This chapter provides examples that use the main JDBC classes. For general information about the classes, review Chapter 19, "The SQL Package." Additionally, Chapter 42, "Databases and Java," provides information about accessing data using techniques other than the JDBC.

To use the JDBC, you must have one or more JDBC drivers written specifically to connect to the database types you are using. A list of these drivers can be found at the Sun JavaSoft site at http://www.javasoft.com/jdbc.

Connecting to a Database and Making Simple Queries

One very attractive aspect of the JDBC is how easy it is to use. To connect to a database, make a query, and process the results takes no more than a few lines of code, as the following sections demonstrate.

THE SAMPLES IN THIS CHAPTER
The samples in this chapter use either the JDBC-ODBC bridge that comes with the Java Development Kit or the Imaginary mSQL-JDBC driver. Three databases are used: a Sybase SQL Anywhere database, a Microsoft Access 97 database, and an mSQL database. The databases and the script to make the databases can be found on the CD-ROM that accompanies this book.

Database Connections

The database connection string is the only JDBC component that references the particular database being accessed. You must provide a specific driver connection string that uses the protocol defined for the driver. These protocols, currently maintained by JavaSoft, are used by the JDBC DriverManager class to determine which driver to load.

Remember to establish the database connection with the ODBC Administrator before using these sample programs. For example, to connect to a Sybase SQL Anywhere database using the JDBC-ODBC bridge, use the following connection string and connection method:

//connect to ODBC database
String url = "jdbc:odbc:Zoo";
// connect
Connection con = DriverManager.getConnection(url,"dba", "sql");

The second line of the code creates a String object that contains the URL for the database. The string also includes the protocol jdbc, the subprotocol odbc, and the location of the database Zoo. The fourth line creates the Connection object for the database. Because this example uses the JDBC-ODBC bridge, the database access is through the name defined with the ODBC Administration tool, found in the Control Panel group for Windows NT or 95.

Compare this example with one using the mSQL-JDBC driver and note that the URL string differs in format as well as in content:

//connect to database
Class.forName("imaginary.sql.iMsqlDriver");
String url = "jdbc:msql://yasd.com:1112/test";
// connect
Connection con = DriverManager.getConnection(url);

With this driver, the subprotocol is msql, and the database connection information required by the driver is a double slash followed by the name of the site, the port, and the name of the database. The port number 1112 specifically tells the mSQL-JDBC driver that the database is being run by root rather than by some other user name. Specifying a port number of 4333 states that the database is running under another user name, which should also be specified in the connection.

In these two examples, note that the second example loads the driver classes explicitly using the Class.forName() method. With this method, the class loader checks for the driver classes in the local path designated by the CLASSPATH variable. If the driver classes are not found and the method is called from an applet, the class loader attempts to download them from the network. Chapter 44, "Building Database Applets with JDBC," provides more information about classes and applets.

Another difference between the two examples is that the first connection, for the JDBC-ODBC bridge, uses three parameters: The connection string, the database user name, and the password. The second example, for the mSQL-JDBC driver, passes just the connection string and assumes that the connection is open to all. A third example, which follows (and which also uses the JDBC-ODBC bridge), shows a connection that uses the third getConnection() method, which uses a Properties class:

//connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Zoo";
        // connect
        Properties p = new Properties();
        p.put("user", "dba");
        p.put("password","sql");
        Connection con = DriverManager.getConnection(url,p);

The benefit of this third technique is that the application can prompt the user for the user name and password so that the password can be entered into an encrypted field and not be hard coded into the application.

Listing 43.1 shows the complete code for connecting to an ODBC database and processing a simple query. Note that, at the end of the application, the Connection instance is closed. Unless resources are limited, closing the Connection instance is usually not necessary. When the object is no longer in scope (as is the case when the application is no longer running, the applet is unloaded, or the object is contained within a procedure or control structure that ends), normal Java garbage collection frees up the resources.

Listing 43.1. The sample1 application using JDBC.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;
public class sample1
{
public static void main(String arg[]) {
int id;
float amount;
Date dt;
String status;
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
        Statement stmt = con.createStatement();
        String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
        + " from dba.purchase_order";
        // run query
        ResultSet rs = stmt.executeQuery(sqlselect);
        // process results
        while(rs.next()) {
                result  = "";
                id      = rs.getInt(1);
                amount  = rs.getFloat(3);
                dt      = rs.getDate(2);
                status  = rs.getString(4);
                result = result.valueOf(id) + " ";
                result+= result.valueOf(amount) + " ";
                result+= dt.toString() + " " + status;
                System.out.println("Values are: " + result);
                }

        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}


}

Once a connection is established to the database, it is used to create some kind of statement using the Statement class or one of its extensions.

The Statement Class

A statement for simple querying of a database takes the following form:

// create Statement object
Statement stmt = con.createStatement();

This type of statement can be used for single-use updates or queries to the database that do not return multiple result sets or update counts.

After the statement is created, it can be used to issue a simple query, as shown here:

String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
        + " from dba.purchase_order";
// run query
ResultSet rs = stmt.executeQuery(sqlselect);

This example shows how a String object is used to hold the select statement, which is then used in the executeQuery() method call. This method, in turn, generates a ResultSet object containing the results of the query.

The preceding example shows a select statement that accesses all the data in a table. Parameters passed to the Java application can be appended to the SQL statement for a more dynamic query, as shown here:

String srch = arg[0];
String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
        + " from dba.purchase_order where company_id = " + srch;
// run query
ResultSet rs = stmt.executeQuery(sqlselect);

In addition to searching for specific values, wildcard characters (also referred to as escape or pattern match characters) can be used in a search. In Java, an underscore character (_) is used to search for a single character, and a percent sign (%) is used to search for zero or more characters, as shown in Listing 43.2.

Listing 43.2. The sample2 application using escape characters.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;
public class sample2
{
public static void main(String arg[]) {
int id;
float amount;
Date dt;
String status;
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
        Statement stmt = con.createStatement();
        String srch = arg[0];
        String sqlselect = "Select company_id, order_dt, po_amount, status_cd"
        + " from dba.purchase_order where company_id = " + srch;
        // run query
        ResultSet rs = stmt.executeQuery(sqlselect);
        // process results
        while(rs.next()) {
                result  = "";
                id      = rs.getInt(1);
                amount  = rs.getFloat(3);
                dt      = rs.getDate(2);
                status  = rs.getString(4);
                result = result.valueOf(id) + " ";
                result+= result.valueOf(amount) + " ";
                result+= dt.toString() + " " + status;
                System.out.println("Values are: " + result);
                }

        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}


}

This code in Listing 43.2 looks for any company name that contains the value specified by the variable srch. Notice that the results are pulled from the row using the data type specific to the column. These values are then converted to String values using the valueOf() method. A more efficient method would have been to pull the values in directly as strings using the getString() method, but the example does demonstrate some of the other ResultSet get methods.

CAUTION: If you are using the escape characters, don't forget to include the surrounding quotation marks (") for the pattern-match sequence. Pattern matching is for character-based or like fields; if you forget the quotation marks, you will get an error.

You can also run update statements with the JDBC. Instead of using executeQuery(), your application must use executeUpdate().

The update statement can be any valid data modification statement: UPDATE, DELETE, or INSERT. The result is the count of rows impacted by the change. Listing 43.3 shows an example of each of the different types of data manipulation statements.

Listing 43.3. The sample3 application demonstrating database updates.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;
public class sample3
{
public static void main(String arg[]) {
     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);

        // create Statement object
        Statement stmt = con.createStatement();
        String updatestring = "INSERT INTO RETAIL_ITEM(item_nbr,item_desc, qty_per_pkg, "
                + "wholesale_cost, retail_cost, company_id, color, size) "
                + "VALUES(25,'some item', 1, 10.00, 15.00, 1, 'orange', 'M')";
        // run query
        int ct = stmt.executeUpdate(updatestring);
        // process results
        System.out.println("Insert row: " + updatestring.valueOf(ct));
        updatestring = "UPDATE PERSON "
                       + "set zip_cd = '97228' where "
                       + "zip_cd = '97229'";
        // run query
        ct = stmt.executeUpdate(updatestring);
        // process results
        System.out.println("Updated rows: " + updatestring.valueOf(ct));
        //back out modifications
        updatestring = "DELETE FROM RETAIL_ITEM "
                     + "where item_nbr = 25 and company_id = 1";
        // run query
        ct = stmt.executeUpdate(updatestring);
        // process results
        System.out.println("Delete row: " + updatestring.valueOf(ct));
        updatestring = "UPDATE PERSON "
                       + "set zip_cd = '97229' where "
                       + "zip_cd = '97228'";
        // run query
        ct = stmt.executeUpdate(updatestring);
        // process results
        System.out.println("Updated rows: " + updatestring.valueOf(ct));
        //close connection
        con.commit();
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
           }
}


}
TIP: In Listing 43.3, note that autocommit is turned off for the set of transactions. If you don't turn autocommit off, each of the transactions would be committed as soon as it completed, rather than committing after all the transactions have completed. Because we are backing changes out, we want all the transactions to succeed before issuing a commit; otherwise all the transactions will roll back if an exception occurs (the database implements this functionality by default).

The Statement object can also be used in a call to a database stored procedure if no dynamic parameters are given. For procedures with IN, OUT, and INOUT parameters, the CallableStatement class is used, as explained later in this chapter.

If the statement is a query, it always returns a ResultSet object unless an exception occurs. The ResultSet object is demonstrated in more detail in the following section.

The ResultSet Class

A query returns a ResultSet object. This object contains the results of the query in a form that can be accessed by the application. If the query has no results, the ResultSet object contains no rows; otherwise, it contains rows of data matching the query (up to the limit specified for the database). If the database supports doing so, you can set the maximum number of rows using the Statement class method setMaxRows(). Once the result set is returned, you can use methods to access and process individual columns as shown in Listing 43.4.

Listing 43.4. The sample4 application with a result set.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;
public class sample4
{
public static void main(String arg[]) {
int id;
String name;
String address;
String city;
String state;
String result;
     String srch = arg[0];
     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
        Statement stmt = con.createStatement();
         stmt. setMaxRows(10);
        String sqlselect = "Select company_id, company_name, address_1, city, state_cd"
        + " from dba.company where company_name like '%" + srch + "%'";
        // run query
        ResultSet rs = stmt.executeQuery(sqlselect);
        // process results
        while(rs.next()) {
                result  = "";
                id      = rs.getInt(1);
                name    = rs.getString(2);
                address = rs.getString(3);
                city    = rs.getString(4);
                state   = rs.getString(5);
                result = result.valueOf(id) + " ";
                result+= name + " " + address + " ";
                result+= city + " " + state;
                System.out.println("Values are: " + result);
                }
        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}


}

Individual data types have matching getXXX() methods to retrieve the value in the form the application prefers. You pass to each method the number of the column representing the position it holds in the original select statement, or a text string containing the name of the column. The next() method maintains a cursor that points to the current row being processed. Each call to the method moves the cursor to the next row. When no more rows are found, the next() method returns a value of false.

You can access the columns even if you don't know the order in which the columns will be returned. Using the findColumn() method, you can look for a column with the same name as the one passed to the method; the method returns the column index. You can then use the index used to access the value, as shown in Listing 43.5.

Listing 43.5. The sample5 application using the findColumn() method.

// run query
        ResultSet rs = stmt.executeQuery(sqlselect);
        // process results
        while(rs.next()) {
                result  = "";
                id      = rs.getInt(rs.findColumn("company_id"));
                name    = rs.getString(rs.findColumn("company_name"));
                address = rs.getString(rs.findColumn("address_1"));
                city    = rs.getString(rs.findColumn("city"));
                state   = rs.getString(rs.findColumn("state_cd"));
                result = result.valueOf(id) + " ";
                result+= name + " " + address + " ";
                result+= city + " " + state;
                System.out.println("Values are: " + result);


                }

This technique is a little convoluted because you can also use the get methods that accept a string representing the column name and return the result. A partial listing of the results of running the sample5 Java application are shown here:

java sample5
Values are: 1 Portland T-Shirt Company 18050 Industrial Blvd. Portland, OR
Values are: 2 Tri-State Stuffed Critter 923 Hawthorne Way Vancouver WA
Values are: 3 Tigard Candy Shop 1900 Mountain Rd Tigard OR
Values are: 4 LA T-Shirt Company 13090 SW 108th Ave SW Los Angeles CA
. . .

In addition, you can also check to see whether a column value is null by using the wasNull() method after the getXXX() method call:

state = rs.getString("state_cd");
boolean b = rs.wasNull();
if (b) {
. . .

The examples in this section introduced and demonstrated the Connection, Statement, and ResultSet classes. The next two sections demonstrate the use of some more complex features of the JDBC and how to access multiple heterogeneous databases.

More Complex Uses of JDBC

Occasionally, a database developer has to program for more complex database access situations. You may want to create a statement and then execute it many times, or call a stored procedure that returns multiple result sets, or issue a dynamic SQL statement. This section covers some techniques for handling these types of statements.

Two of the classes discussed, PreparedStatement and CallableStatement, are extended classes: the former is an extension of Statement and the latter is an extension of PreparedStatement.

In addition to the classes, the following sections also demonstrate and discuss the execute() method of the Statement class.

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 parameter value:

stmt.setInt(1,i);
rs = stmt.executeQuery();

After executing the query or update, you can process the results in the same way you process a regular statement:

int colcount = rsmd.getColumnCount();
// process results
while(rs.next()) {
       result = "";
       for (int k = 1; k <= colcount; k++) {
            result+= rs.getString(k) + " ";
            }
       System.out.println("Values are: " + result);
      }

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 Listing 43.6.

Listing 43.6. 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;
        ResultSetMetaData rsmd;
        for (int i = 1; i <= 3; i++) {
                stmt.setInt(1,i);
                rs = stmt.executeQuery();
                rsmd = rs.getMetaData();
                int colcount = rsmd.getColumnCount();
                // process results
                while(rs.next()) {
                   result = "";
                   for (int k = 1; k <= colcount; k++) {
                      result+= rs.getString(k) + " ";
                      }
                   System.out.println("Values are: " + result);
                   }
             }

        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}


}

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
PreparedStatement stmt = con.prepareStatement(sqlselect);
for (int i = 1; i <= 3; i++) {
       stmt.setInt(1,i);
       stmt.setInt(2, i + 1);
       int count = stmt.executeUpdate();
. . .

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:

// create Statement object
Statement stmt = con.createStatement();
String sqlstmt = arg[0];
// run statement
boolean b = stmt.execute(sqlstmt);

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
        result = "";
        if (b) {
          // process results
          ResultSet rs = stmt.getResultSet();
          ResultSetMetaData rsmd = rs.getMetaData();
          int colcount = rsmd.getColumnCount();
          while(rs.next()) {
                result = "";
                for (int i=1; i <= colcount; i++) {
                   result+= rs.getString(i) + " ";
                   }
                System.out.println("Values are: " + result);
                }
           }
. . .

Normally, however, your application wants to process all the return results--if only to provide feedback to the user. Listing 43.7 provides a complete example of using the execute() method.

Listing 43.7. 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
        Statement stmt = con.createStatement();
        String sqlstmt = arg[0];
        // run statement
        boolean b = stmt.execute(sqlstmt);
        // if true, result set
        result = "";
        if (b) {
          // process results
          ResultSet rs = stmt.getResultSet();
          ResultSetMetaData rsmd = rs.getMetaData();
          int colcount = rsmd.getColumnCount();
          while(rs.next()) {
                result = "";
                for (int i=1; i <= colcount; i++) {
                   result+= rs.getString(i) + " ";
                   }
                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 Listing 43.7.

The CallableStatement Class

The CallableStatement class is used to accept several parameters for a stored procedure call. The parameters can be defined as input only, output only, or both.

CallableStatement is an extension of PreparedStatement, which is itself an extension of Statement. The CallableStatement class adds methods to register and access output parameters.

To create a CallableStatement object, issue the procedure call with question marks (?) in place of parameters:

String scall = "call new_po(?,?,?,?,?,?,?,?,?,?)";
// create Statement object
CallableStatement stmt = con.prepareCall(scall);

You must first set the IN parameters using the appropriate setXXX() methods:

stmt.setInt(1, 1);
        stmt.setDate(2, dt);
        stmt.setInt(3,1);
        stmt.setDouble(4, 10.00);
        stmt.setString(5,"OP");
        stmt.setInt(6,1);
        stmt.setInt(7,1);
        stmt.setDouble(8, 10.00);
        stmt.setInt(9,61);

The last parameter is not set because it is an OUT parameter only. It must be registered using one of the RegisterOutParameter() methods:

stmt.registerOutParameter(10,java.sql.Types.INTEGER);

Because the new_po() procedure contains two INSERT commands and multiple result sets, in addition to the one output parameter, the execution method to use for this statement is execute():

stmt.registerOutParameter(10,java.sql.Types.INTEGER);
// run statement
stmt.execute();

The result sets and update counts are then processed. Listing 43.8 shows all the code that demonstrates the use of CallableStatement with two insert commands.

Listing 43.8. The sample8 application using the CallableStatement class.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;
public class sample8
{
public static void main(String arg[]) {
String result;
Date dt = new Date(97, 12,1);
     try {
        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Zoo";
        // connect
        Connection con = DriverManager.getConnection(url,"dba", "sql");
        String scall = "call new_po(?,?,?,?,?,?,?,?,?,?)";
        // create Statement object
        CallableStatement stmt = con.prepareCall(scall);
        stmt.setInt(1, 1);
        stmt.setDate(2, dt);
        stmt.setInt(3,1);
        stmt.setDouble(4, 10.00);
        stmt.setString(5,"OP");
        stmt.setInt(6,1);
        stmt.setInt(7,1);
        stmt.setDouble(8, 10.00);
        stmt.setInt(9,61);
        stmt.registerOutParameter(10,java.sql.Types.INTEGER);
        // run statement
        stmt.execute();
        // get update counts
        boolean cont = true;
        while(cont) {
                int colcount = stmt.getUpdateCount();
                if (colcount > 0) {
                        System.out.println("Updated rows are: " + colcount);
                        stmt.getMoreResults();
                        }
                else {
                        cont = false;
                        }
                }
        cont =true;
        result="";
        while(cont) {
                ResultSet rs = stmt.getResultSet();
                ResultSetMetaData rsms = rs.getMetaData();
                int colcount = rsms.getColumnCount();
                for (int i = 1; i <= colcount; i++) {
                  result+=rs.getString(i) + " ";
                  }
                System.out.println("results are " + result);
                result="";
                cont =  stmt.getMoreResults();
                }
        int po_count = stmt.getInt(10);
        System.out.println("Number of POS is " + po_count);
        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }
}


}

Unfortunately, when I use this code with the JDBC-ODBC bridge to access a Sybase SQL Anywhere database, I get the Function Sequence Error error:

java sample8
Updated rows are: 1
[Microsoft] [ODBC Driver Manager] Function sequence error

This ODBC DriverManager error occurs because an asynchronously executing function is called when the function is still running from the first call. According to the ODBC documentation, this error occurs when a procedure call or SQL execution statement occurs while a previous call still requires data to be passed in from parameters. After examining the data in the tables, however, I did find that the updates had occurred.

As a workaround to this problem, I modified the code (the modified example is in sample8b.java) to call a stored procedure that contains only the two updates and not the multiple result sets, as shown in Listing 43.9.

Listing 43.9 The sample8b.java application containing the stored procedure call with two updates.

import java.lang.*;
import java.sql.*;
import sun.jdbc.odbc.*;

public class sample8b
{

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

Date dt = new Date(97, 12,1);

     try {

        //connect to ODBC database
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Zoo";

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

        String scall = "call new_po_copy(?,?,?,?,?,?,?,?,?,?)";

        // create Statement object
        CallableStatement stmt = con.prepareCall(scall);

        stmt.setInt(1, 1);
        stmt.setDate(2, dt);
        stmt.setInt(3,1);
        stmt.setDouble(4, 10.00);
        stmt.setString(5,"OP");
        stmt.setInt(6,1);
        stmt.setInt(7,1);
        stmt.setDouble(8, 10.00);
        stmt.setInt(9,62);

        stmt.registerOutParameter(10,java.sql.Types.INTEGER);

        // run statement
        stmt.executeUpdate();

        int po_count = stmt.getInt(10);
        System.out.println("Number of POS is " + po_count);

        //close connection
        con.close();
        }
     catch(Exception e) {
           System.out.println(e.getMessage());
        }

}



}

This procedure is then used in an executeUpdate() call. The output parameter is printed after the update occurs.

This version of the application worked without error and printed the number of purchase orders in the database, as returned in the OUT parameter.

TIP: Chapter 42, "Databases and Java," describes a database utility tool, dbutil, that you can modify to get information about what the database, driver, and JDBC driver can process (such as multiple result sets). You should modify this tool to check what your database and drivers can do before you code your application.

Working with Multiple Databases

The last Java application created in this chapter is one that refreshes a table in an mSQL database with the contents of a table in a Microsoft Access database. This example demonstrates the ease with which you can open and maintain multiple database connections at the same time.

The first part of the code loads the mSQL-JDBC driver classes and opens a connection to the mSQL database:

//connect to mSQL database
Class.forName("imaginary.sql.iMsqlDriver");
String url = "jdbc:msql://yasd.com:1112/yasd";
// mSQL connection
Connection con = DriverManager.getConnection(url);
// mSQL statement
Statement stmt = con.createStatement();

Next, an update is issued to delete the contents from the table being refreshed, in this case, the company table:

// clean out existing data
stmt.executeUpdate("DELETE from company");

The Access database connection and statement is created next. Note that the mSQL database is remote and that the Access database is local to the application:

//connect to Access database
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
url = "jdbc:odbc:cityzoo";
// Access Connection
Connection con2 = DriverManager.getConnection(url,"Admin","sql");
// Access Statement
Statement stmt2 = con2.createStatement();
ResultSet rs = stmt2.executeQuery("Select company_id,"
        + "company_name, address_1, address_2, address_3,"
        + "city, state_cd, country_cd, postal_cd, phone_nbr "
        + "from company");

The data is pulled from the Access database as strings, which are then concatenated in to an insert string for the mSQL database, as shown in Listing 43.10.

Listing 43.10. The code for sample9, which accesses two different databases at the same time.

import java.sql.*;
import sun.jdbc.odbc.*;
import java.io.*;
public class sample9
{
public static void main(String arg[]) {
String invalue;
String outvalue;
String id, name, add1, add2, add3,city,state,country,post,phone;
int pos, endpos;
        try {
                 System.out.println("Refreshing company...");
                //connect to mSQL database
                Class.forName("imaginary.sql.iMsqlDriver");
                String url = "jdbc:msql://yasd.com:1112/yasd";
                // mSQL connection
                Connection con = DriverManager.getConnection(url);
                // mSQL statement
                Statement stmt = con.createStatement();
                // clean out existing data
                stmt.executeUpdate("DELETE from company");
                //connect to Access database
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                url = "jdbc:odbc:cityzoo";
                // Access Connection
                Connection con2 = DriverManager.getConnection(url,"Admin","sql");
                // Access Statement
                Statement stmt2 = con2.createStatement();
                ResultSet rs = stmt2.executeQuery("Select company_id,"
                    + "company_name, address_1, address_2, address_3,"
                    + "city, state_cd, country_cd, postal_cd, phone_nbr "
                    + "from company");
                // for each line, enter into database
                while (rs.next()) {
                   // get id
                   id = rs.getString(1);
                   name = rs.getString(2);
                        add1= rs.getString(3);
                        add2=rs.getString(4);
                        add3=rs.getString(5);
                        city=rs.getString(6);
                        state=rs.getString(7);
                        country=rs.getString(8);
                        post=rs.getString(9);
                        phone=rs.getString(10);
                        // create and execute insert statement
                        stmt.executeUpdate("insert into company (company_id, company_name,"
                           + "address_1,address_2,address_3, "
                           + "city,state_cd,country_cd,postal_cd,phone_nbr) values("
                           + id + ",'" + name + "','" + add1 + "','"
                           + add2 + "','"
                           + add3 + "','" + city + "','" + state + "','"
                           + country + "','" + post +
                           "','" + phone + "')");
                        }
                        stmt.close();
                        stmt2.close();
                        rs.close();
                }
            catch(Exception e) {
                 System.out.println(e.getMessage());
                 }
}


}

The PreparedStatement class would have been ideal for this type of operation, but the mSQL database does not support cursors, and the drivers for Microsoft Access do not maintain an open connection between transactions. However, the technique that was used works very well--and works fairly quickly, considering that the transactions occurred over a modem.

As a reminder, if you are working with multiple databases at the same time, you must create separate connections and statements for each of the different databases.

Summary

This chapter demonstrates how to use the most common aspects of the JDBC; all the examples use the JDBC-ODBC bridge or the mSQL-JDBC driver. Most SQL statements can be processed with all drivers; however, some statements (such as cursor-based statements) may not work with all drivers or databases. You can usually find a workaround or use a different JDBC-ODBC driver.

Using the JDBC is simple, as this chapter has shown. By using this Java API, you can run simple and complex queries and use all forms of database updates. In addition, the JDBC provides methods and classes that compile statements for later use (the PreparedStatement class), execute an unknown SQL statement (the execute() method), process multiple results and updates (the execute() method), and call procedures with parameters (the CallableStatement class).

Refer to Chapter 19, "The SQL Package," for an overview of the JDBC API. You may also want to refer to Chapter 42, "Databases and Java," for an introduction to the JDBC and the beginnings of a handy database utility called dbutil. Chapter 44, "Building Database Applets with the JDBC," provides examples of using the JDBC in applets.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.