The Oracle JDBC Extensions

As mentioned earlier, the Oracle extensions to JDBC contain packages and interfaces that enable you to access all of the data types provided by Oracle, along with Oracle-specific performance extensions. This section introduces you to the classes and interfaces in the Oracle JDBC packages and covers how to access some of the Oracle-specific types using the Oracle JDBC extensions. This section doesn t cover all of the Oracle types; you may read my book Oracle9 i JDBC Programmin g for all Oracle types and performance enhancements. You ll learn about handling of strings, numbers , dates, and row identifiers in this section.

There are two JDBC extension packages supplied by Oracle:

• oracle.sql    Contains the classes that support all the Oracle types

• oracle.jdbc    Contains the interfaces that support access to an Oracle database

To import the Oracle JDBC packages into your Java programs, you may add the following import statements to your program:

 import oracle.sql.*; import oracle.jdbc.*; 

Of course, you don t have to import all the packages: you could just import the classes and interfaces you actually use in your program. In addition, you should still import the java.sql packages you reference in your program. In the following sections, you ll learn the key features of the oracle.sql and oracle.jdbc packages.

The oracle.sql Package

The oracle.sql package contains the classes that support all of the Oracle types. Using objects of the classes defined in this package to access database columns is more efficient than using regular Java objects. This is because the database column values don t need to be converted to an appropriate base Java type first. Also, using a Java float or double to represent a floating-point number may result in a loss of precision for that number. If you use an oracle.sql.NUMBER object, your numbers never lose precision.

Tip  

If you are writing a program that moves a lot of data around in the database, you should use the oracle.sql .* classes.

All of the oracle.sql.* type classes extend the oracle.sql.Datum class, which contains the functionality that is common to all the classes. Table 15-6 shows a subset of the oracle.sq l classes, along with the mapping to the compatible Oracle database types.

Table 15-6: Classes and Compatible Oracle Database Types

Class	Compatible Database Type
oracle.sql.NUMBER	INTEGER NUMBER
oracle.sql.BINARY_FLOAT	BINARY_FLOAT (new for Oracle10 g )
oracle.sql.BINARY_DOUBLE	BINARY_DOUBLE (new for Oracle10 g )
oracle.sql.CHAR	CHAR VARCHAR2 NCHAR NVARCHAR2
oracle.sql.DATE	DATE
oracle.sql.ROWID	ROWID

From Table 15-6, you can see that an oracle.sql.NUMBER object is compatible with a database column defined using the INTEGER or NUMBER type, and a VARCHAR2 column is compatible with an oracle.sql.CHAR object. Notice that an oracle.sql.CHAR object is also compatible with the NCHAR and NVARCHAR2 database types. These types allow you to store multi-byte character sets in the database (for full details on how Oracle may be used with such types, refer to the Globalization Support Guide published by Oracle Corporation). The ROWID pseudo-column contains the physical address of a table row in the database. You can use an oracle.sql.ROWID object to store that.

Objects declared using the oracle.sql.* classes store the data as byte arrays ”also known as SQL format ”and don t reformat the data retrieved from the database. This means that no information is ever lost due to conversion into a core Java type. Each of the classes provides a getBytes() method that returns the binary data stored in an oracle.sql object as a byte array, and a toJdbc() method that returns the binary data as a compatible Java type. The only exception to this is the oracle.sql.ROWID , toJdbc() , which returns only an oracle.sql.ROWID .

Each class also provides methods to convert their SQL format data to a core Java type. For example, stringValue() returns the value as Java String , intValue() returns a Java int , floatValue() returns a float , doubleValue() returns a double , bigDecimalValue() returns a java.math.BigDecimal , dateValue() returns a java.sql.Date , and so forth. You use these methods when you want to store the SQL format data in a core Java type or output the SQL data on the screen.

As you will learn shortly, the OraclePreparedStatement class, which is defined in the oracle.jdbc package, contains a number of set methods that may be used to specify column values using oracle.sql.* objects. The OracleResultSet class defines a number of get methods that may be used to read column values as oracle.sql.* objects.

Each of the oracle.sql.* classes contains a constructor that may take a byte array as input, or, as you will more frequently use, a Java variable or object. The following sections describe the details of using some of the oracle.sql classes shown in Table 15-6 in your Java programs.

The oracle.sql.NUMBER Class

The oracle.sql.NUMBER class is compatible with the database INTEGER and NUMBER types and may be used to represent a number with up to 38 digits of precision. The following example creates an oracle.sql.NUMBER object named customerId , which is set to the value 6 using the constructor:

 oracle.sql.NUMBER customerId = new oracle.sql.NUMBER(6); 

You can read the value stored in customerId using the intValue() method, which returns the value as a Java int . For example:

 int customerIdInt = customerId.intValue(); 

You can also set an oracle.sql.NUMBER object to a floating-point number. The next example passes the value 19.95 to the constructor of an oracle.sql.NUMBER object named price :

 oracle.sql.NUMBER price = new oracle.sql.NUMBER(19.95); 

You can read the floating-point number stored in price using the floatValue() , doubleValue() , and bigDecimalValue() methods, which return a Java float , double , or bigDecimal respectively. You can also get the value truncated to an int using intValue() , so 19.95 would be returned as 19 . The following examples show the use of these methods:

 float priceFloat = price.floatValue(); double priceDouble = price.doubleValue(); java.math.BigDecimal priceBigDec = price.bigDecimalValue(); int priceInt = price.intValue(); 

The stringValue() method returns the value as a Java String :

 String priceString = price.stringValue(); 

The oracle.sql.CHAR Class

The oracle.sql.CHAR class is compatible with the database CHAR , VARCHAR2 , NCHAR , and NVARCHAR2 types. Both the Oracle database and the oracle.sql.CHAR class contain globalization support for many different languages. For full details of the various languages supported by Oracle, see the Globalization Support Guide published by Oracle Corporation.

When you retrieve character data from the database into an oracle.sql.CHAR object, the Oracle JDBC driver constructs and returns that object using either the database character set, WE8ISO8859P1 (ISO 8859-1 West European), or UTF8 (Unicode 3.0 UTF-8 Universal).

If you are creating your own oracle.sql.CHAR object for storage in the database, there are restrictions on which character set you may use, depending on the database column type that the object will be stored in. If you are storing your oracle.sql.CHAR object in a CHAR or VARCHAR 2 column, you must use US7ASCII (ASCII 7-bit American), WE8ISO8859P1 (ISO 8859-1 West European), or UTF8 (Unicode 3.0 UTF-8 Universal). If you are storing your oracle.sql.CHAR object in an NCHAR or NVARCHAR2 column, you must use the character set used by the database.

When creating your own oracle.sql.CHAR object, there are two steps you must follow:

1. Create an oracle.sql.CharacterSet object containing the character set you wish to use.

2. Create an oracle.sql.CHAR object using the oracle.sql.CharacterSet object to specify the character set.

The following sections describe the details of these steps.

Step 1: Create an oracle.sql.CharacterSet Object    The following example creates an oracle.sql.CharacterSet object named myCharSet :

 oracle.sql.CharacterSet myCharSet =   CharacterSet.make(CharacterSet.US7ASCII_CHARSET); 

The make() method accepts an int that specifies the character set to use. In this case, the constant US7ASCII_CHARSET (defined in the oracle.sql.CharacterSet class) is used to specify that the US7ASCII character set is to be used. Other values include UTF8_CHARSET (for UTF8), and DEFAULT_CHARSET (for the character set used by the database).

Step 2: Create an oracle.sql.CHAR Object    The following example creates an oracle.sql.CHAR object named firstName , using the myCharSet object created in the previous step:

 oracle.sql.CHAR firstName = new oracle.sql.CHAR("Jason", myCharSet); 

The firstName object is populated with the string Jason . You can read the value stored in firstName using the stringValue() method, which returns the value as a Java String . For example:

 String firstNameString = firstName.stringValue(); System.out.println("firstNameString = " + firstNameString); 

This will display firstNameString = Jason .

Similarly, the following example creates another oracle.sql.CHAR object named lastName :

 oracle.sql.CHAR lastName = new oracle.sql.CHAR("Price", myCharSet); 

You can also display the value in an oracle.sql.CHAR object directly, as shown in the following example:

 System.out.println("lastName = " + lastName); 

This statement will display the following:

 lastName = Price 

The oracle.sql.DATE Class

The oracle.sql.DATE class is compatible with the database DATE type. The following example creates an oracle.sql.DATE object named dob :

 oracle.sql.DATE dob = new oracle.sql.DATE("1969-02-22 13:54:12"); 

Notice that the constructor may accept a string in the format YYYY-MM-DD HH:MI:SS , where YYYY is the year, MM is the month, DD is the day, HH is the hour , MI is the minute, and SS is the second. You can read the value stored in dob as a Java String using the stringValue( ) method, as shown in the following example:

 String dobString = dob.stringValue(); 

In this case, dobString will contain 2/22/1969 13:54:12 ”notice the change in format to MM/DD/YYYY HH:MI:SS .

You can also pass a java.sql.Date object into the oracle.sql.DATE constructor, as shown in the following example:

 oracle.sql.DATE anotherDob =  new oracle.sql.DATE(java.sql.Date.valueOf("1969-02-22")); 

So, anotherDob will contain the oracle.sql.DATE 1969-02-22 00:00:00 .

The oracle.sql.ROWID Class

The oracle.sql.ROWID class is compatible with the database ROWID type. The following example creates an oracle.sql.ROWID object named rowid :

 oracle.sql.ROWID rowid; 

Because the ROWID pseudo-column in the database contains the internal address of a row and is set by Oracle, you should only retrieve values from the database into an oracle.sql.ROWID object. I ll show you how to do that shortly.

The oracle.jdbc Package

The classes and interfaces of the oracle.jdbc package allow you to read and write column values in the database using objects declared using the oracle.sql.* classes. The oracle.jdb c package also contains a number of performance enhancements specifically for use with an Oracle database. In this section, you ll learn about the contents of the oracle.sql package and how to create a row in the customers table. Then you ll learn how to read that row using the oracle.sql.* objects created in the previous section.

The Classes and Interfaces of the oracle.jdbc Package

Table 15-7 outlines the classes and interfaces of the oracle.jdbc package.

Table 15-7: Classes and Interfaces of the oracle.jdbc Package

Name	Class or Interface	Description
OracleDriver	Class	Implements java.sql.Driver . You input an object of this class when registering the Oracle JDBC drivers in your programs using the registerDriver() method of the java.sql.DriverManager class.
OracleConnection	Interface	Implements java.sql.Connection . This interface extends the standard JDBC connection functionality to use OracleStatement objects, plus Oracle performance extensions.
OracleStatement	Interface	Implements java.sql.Statement and is the superclass of the OraclePreparedStatement and OracleCallableStatement classes. This interface supports Oracle performance extensions on a per-statement basis.
OraclePreparedStatement	Interface	Implements java.sql.PreparedStatement , and is the superclass of OracleCallableStatement . This interface supports Oracle performance extensions on a per-statement basis, plus various set methods for binding oracle.sql.* objects.
OracleCallableStatement	Interface	Implements java.sql.CallableStatement . This interface contains various get and set methods for binding oracle.sql.* objects.
OracleResultSet	Interface	Implements java.sql.ResultSet . This interface contains various get methods for binding oracle.sql.* objects.
OracleResultSetMetaData	Interface	Implements java.sql.ResultSetMetaData . This interface contains methods for retrieving meta data about Oracle result sets, like the column names and types.
OracleDatabaseMetaData	Class	Implements java.sql.DatabaseMetaData . This class contains methods for retrieving meta data about the Oracle database, like the software version.
OracleTypes	Class	Defines integer constants that are used by JDBC to identify database types. This class duplicates the standard java.sql.Types class, along with the new constants for the Oracle types.

In the following sections, you ll learn how to use an OraclePreparedStatement object, add a row to the customers table, and use an OracleResultSet object to read that row.

Using an OraclePreparedStatement Object

The OraclePreparedStatement interface implements java.sql.PreparedStatement . I described the use of a java.sql.PreparedStatement object to add a row to a table earlier in the section Prepared SQL Statements. If you need a refresher on this use of such objects, I suggest you take a look at that section before proceeding.

In the previous section, you saw how to create the following four objects using the classes in the oracle.sql package:

• An oracle.sql.NUMBER object named customerId , which was set to 6

• An oracle.sql.CHAR object named firstName , which was set to Jason

• Another oracle.sql.CHAR object named lastName , which was set to Price

• An oracle.sql.DATE object named dob , which was set to 1969-02-22 13:54:12

To use these objects directly in a SQL DML statement, you must use an OraclePreparedStatement object, which contains set methods that are capable of handling oracle.sql.* objects. The following example creates an OraclePreparedStatement named myPrepStatement , which will be used to add a row to the customers table using the customerId , firstName , lastName , and dob objects:

 OraclePreparedStatement myPrepStatement =  (OraclePreparedStatement) myConnection.prepareStatement("INSERT INTO customers " +  "(customer_id, first_name, last_name, dob, phone) VALUES (" +   "?, ?, ?, ?, ?" +  ")"); 

Notice that I ve used the JDBC Connection object created earlier named myConnection , and I ve cast the JDBC PreparedStatement object returned by the prepareStatement() method to an OraclePreparedStatement object, which is stored in myPrepStatement . Also, I want to specify a value for the phone column using the fifth ? character, even though I haven t created a corresponding oracle.sql.* object. I m going to specify a database NULL value for this column shortly.

The next step is to bind the oracle.sql.* objects to myPrepStatement using the set methods. This involves assigning values to the placeholders marked by ? characters in myPrepStatement . Just as you use set methods like setInt() , setFloat() , setString() , and setDate() to bind Java variables to a PreparedStatement object, you also use set methods to bind oracle.sql.* objects to an OraclePreparedStatement object, such as setNUMBER() , setCHAR() , and setDATE() .

The following examples illustrate how to bind the customerId , firstName , lastName , and dob objects to myPrepStatement using the appropriate set methods:

 myPrepStatement.setNUMBER(1, customerId); myPrepStatement.setCHAR(2, firstName); myPrepStatement.setCHAR(3, lastName); myPrepStatement.setDATE(4, dob); 

To specify a database NULL value for the phone column (which corresponds to the fifth ? in myPrepStatement ), I will use the setNull() method:

 myPrepStatement.setNull(5, OracleTypes.CHAR); 

The int constant OracleTypes.CHAR is used to specify that the database column type is compatible with the oracle.sql.CHAR type. The phone column is defined as a database VARCHAR2 , which is compatible with oracle.sql.CHAR .

The only thing left to do now is to run the INSERT statement using the execute() method:

 myPrepStatement.execute(); 

This adds the row to the customers table.

Using an OracleResultSet Object

The OracleResultSet interface implements java.sql.ResultSet and contains get methods that are capable of handling oracle.sql.* objects. In this section, you ll see how to use an OracleResultSet object to retrieve the row previously added to the customers table.

The first thing needed is a JDBC Statement object through which a SQL statement may be run:

 Statement myStatement = myConnection.createStatement(); 

Next, the following example creates an OracleResultSet object named customerResultSet , which is populated with the ROWID , customer_id , first_name , last_dob , and phone columns for customer #6:

 OracleResultSet customerResultSet =  (OracleResultSet) myStatement.executeQuery("SELECT ROWID, customer_id, first_name, last_name, dob, phone " +  "FROM customers " +   "WHERE customer_id = 6"); 

I defined five oracle.sql.* objects earlier: rowid , customerId , firstName , lastName , and dob . These may be used to hold the first five column values. In order to store the phon e column, which contains a database NULL value. I ll create another oracle.sql.CHAR object using the myCharSet CharacterSet object created earlier:

 oracle.sql.CHAR phone = new oracle.sql.CHAR("", myCharSet); 

An OracleResultSet object contains a number of get methods to return the various oracl e.sql.* objects. You use getCHAR() to get an oracle.sql.CHAR , getNUMBER() to get an oracle.sql.NUMBER , getDATE() to get an oracle.sql.DATE , and so forth.

The following example uses a while loop, which uses the appropriate get methods to copy the column values into the rowid , customerId , firstName , lastName , dob , and phone objects. To display the values, the example uses calls to the stringValue() method to convert the rowid , customerId , and dob objects to Java String values. For the firstName , lastName , and phone objects, the example simply uses these objects directly in the System.out.println () calls:

 while (customerResultSet.next()) {  rowid = customerResultSet.getROWID("ROWID");  customerId = customerResultSet.getNUMBER("customer_id");  firstName = customerResultSet.getCHAR("first_name");  lastName = customerResultSet.getCHAR("last_name");  dob = customerResultSet.getDATE("dob");  phone = customerResultSet.getCHAR("phone");  System.out.println("rowid = " + rowid.stringValue());  System.out.println("customerId = " + customerId.stringValue());  System.out.println("firstName = " + firstName);  System.out.println("lastName = " + lastName);  System.out.println("dob = " + dob.stringValue());  System.out.println("phone = " + phone); } // end of while loop 

You have seen how to use the Oracle JDBC extension packages to add and retrieve a database row. The following section contains a complete program that illustrates the use of the Oracle JDBC extensions.

Example Program: BasicExample3.java

The program BasicExample3.java , shown in the following listing, is a complete Java program that uses the Oracle JDBC extensions to add a row to the customers table and retrieve and display that row s column values. The program performs the following tasks :

1. Imports the Oracle JDBC extension packages

2. Creates an oracle.sql.NUMBER object named customerId and sets it to 6

3. Creates two oracle.sql.CHAR objects named firstName and lastName and sets them to Jason and Price

4. Creates an oracle.sql.DATE object named dob and sets it to 1969-02-22 13:54:12

5. Creates an OraclePreparedStatement object named myPrepStatement , which contains an INSERT statement to add a row to the customers table

6. Binds the customerId , firstName , lastName , and dob objects to myPrepStatement , and sets the phone column to NULL using the setNull() method

7. Executes myPrepStatement , which adds the row to the customers table

8. Creates and populates an OracleResultSet object named customerResultSet with the ROWID , customer_id , first_name , last_name , dob , and phone columns for the new row retrieved from the customers table

9. Uses a while loop to retrieve the column values into the oracle.sql.* objects and displays their values

10. Closes the various JDBC objects

     /*  BasicExample3.java shows how to use the Oracle JDBC extensions  to add a row to the customers table, and then retrieve that row */ // import the JDBC packages import java.sql.*; // import the Oracle JDBC extension packages import oracle.sql.*; import oracle.jdbc.*; public class BasicExample3 {  public static void main (String args []) {  try {   // register the Oracle JDBC drivers   DriverManager.registerDriver(new oracle.jdbc.OracleDriver());   // EDIT IF NECESSARY   // create a Connection object, and connect to the database   // as store using the Oracle JDBC Thin driver    Connection myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",    "store",     "store_password");    // disable auto-commit mode    myConnection.setAutoCommit(false);    // create an oracle.sql.NUMBER object   oracle.sql.NUMBER customerId = new oracle.sql.NUMBER(6);   int customerIdInt = customerId.intValue();    System.out.println("customerIdInt = " + customerIdInt);   // create two oracle.sql.CHAR objects   oracle.sql.CharacterSet myCharSet =   CharacterSet.make(CharacterSet.US7ASCII_CHARSET);   oracle.sql.CHAR firstName = new oracle.sql.CHAR("Jason", myCharSet);   String firstNameString = firstName.stringValue();    System.out.println("firstNameString = " + firstNameString);    oracle.sql.CHAR lastName = new oracle.sql.CHAR("Price", myCharSet);   System.out.println("lastName = " + lastName);   // create an oracle.sql.DATE object   oracle.sql.DATE dob = new oracle.sql.DATE("1969-02-22 13:54:12");   String dobString = dob.stringValue();    System.out.println("dobString = " + dobString);   // create an OraclePreparedStatement object   OraclePreparedStatement myPrepStatement =   (OraclePreparedStatement) myConnection.prepareStatement("INSERT INTO customers " +    "(customer_id, first_name, last_name, dob, phone) VALUES (" +     "?, ?, ?, ?, ?" +     ")");   // bind the objects to the OraclePreparedStatement using the   // appropriate set methods   myPrepStatement.setNUMBER(1, customerId);  myPrepStatement.setCHAR(2, firstName);   myPrepStatement.setCHAR(3, lastName);   myPrepStatement.setDATE(4, dob);    // set the phone column to NULL   myPrepStatement.setNull(5, OracleTypes.CHAR);   // run the PreparedStatement  myPrepStatement.execute();   System.out.println("Added row to customers table");    // retrieve the ROWID, customer_id, first_name, last_name, dob, and    // phone columns for this new row using an OracleResultSet    // object   Statement myStatement = myConnection.createStatement();   OracleResultSet customerResultSet =    (OracleResultSet) myStatement.executeQuery("SELECT ROWID, customer_id, first_name, last_name, dob, phone " +    "FROM customers " +      "WHERE customer_id = 6");   System.out.println("Retrieved row from customers table");   // declare an oracle.sql.ROWID object to store the ROWID, and   // an oracle.sql.CHAR object to store the phone column    oracle.sql.ROWID rowid;    oracle.sql.CHAR phone = new oracle.sql.CHAR("", myCharSet);    // display the column values for row using the    // get methods to read the values   while (customerResultSet.next()) {    rowid = customerResultSet.getROWID("ROWID");   customerId = customerResultSet.getNUMBER("customer_id");    firstName = customerResultSet.getCHAR("first_name");    lastName = customerResultSet.getCHAR("last_name");    dob = customerResultSet.getDATE("dob");    phone = customerResultSet.getCHAR("phone");  System.out.println("rowid = " + rowid.stringValue());    System.out.println("customerId = " + customerId.stringValue());    System.out.println("firstName = " + firstName);    System.out.println("lastName = " + lastName);    System.out.println("dob = " + dob.stringValue());    System.out.println("phone = " + phone);    } // end of while loop    // close the OracleResultSet object using the close() method    customerResultSet.close();    // rollback the changes made to the database   myConnection.rollback();   // close the other JDBC objects  myPrepStatement.close();    myConnection.close();   } catch (SQLException e) {   System.out.println("Error code = " + e.getErrorCode());   System.out.println("Error message = " + e.getMessage());   System.out.println("SQL state = " + e.getSQLState());   e.printStackTrace();  }  } // end of main() } 

The output from this program is as follows :

 customerIdInt = 6 firstNameString = Jason lastName = Price dobString = 2/22/1969 13:54:12 Added row to customers table Retrieved row from customers table rowid = 414141494654414142414141504A70414146 customerId = 6 firstName = Jason lastName = Price dob = 2/22/1969 13:54:12 phone = null dobString2 = 2/22/1969 0:0:0