3 JDBC Standards Support

Data Type Support

Oracle JDBC fully supports JDK 6 and JDK 7, which includes standard JDBC 2.0 functionality through implementation of interfaces in the standard java.sql package. These interfaces are implemented as appropriate by classes in the oracle.sql and oracle.jdbc packages.

Standard Feature Support

In a JDK 6.0 environment, using the JDBC classes in ojdbc6.jar, JDBC 2.0 features, such as scrollable result sets, updatable result sets, and update batching, are supported through methods specified by standard JDBC 2.0 interfaces.

Extended Feature Support

Features of the JDBC 2.0 optional package, including data sources, connection pooling, and distributed transactions, are supported in a JDK 1.2.x or later environment.

The standard javax.sql package and classes that implement its interfaces are included in the Java Archive (JAR) files packaged with Oracle Database.

Standard versus Oracle Performance Enhancement APIs

The following performance enhancements are available under JDBC 2.0, which had previously been available only as Oracle extensions:

• Update batching

• Fetch size or row prefetching

In each case, you have the option of using the standard model or the Oracle model. Oracle recommends that you use the JDBC standard model whenever possible. Do not, however, try to mix usage of the standard model and Oracle model within a single application for either of these features.

Transaction Savepoints

The JDBC 3.0 specification supports savepoints, which offer finer demarcation within transactions. Applications can set a savepoint within a transaction and then roll back all work done after the savepoint. Savepoints relax the atomicity property of transactions. A transaction with a savepoint is atomic in the sense that it appears to be a single unit outside the context of the transaction, but code operating within the transaction can preserve partial states.

Retrieval of Auto-Generated Keys

Many database systems automatically generate a unique key field when a row is inserted. Oracle Database provides the same functionality with the help of sequences and triggers. JDBC 3.0 introduces the retrieval of auto-generated keys feature that enables you to retrieve such generated values. In JDBC 3.0, the following interfaces are enhanced to support the retrieval of auto-generated keys feature:

• java.sql.DatabaseMetaData

• java.sql.Connection

• java.sql.Statement

These interfaces provide methods that support retrieval of auto-generated keys. However, this feature is supported only when INSERT statements are processed. Other data manipulation language (DML) statements are processed, but without retrieving auto-generated keys.

/** SQL statements for creating an ORDERS table and a sequence for generating the
  * ORDER_ID.
  *
  * CREATE TABLE ORDERS (ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ISBN NUMBER,
  * DESCRIPTION NCHAR(5))
  *
  * CREATE SEQUENCE SEQ01 INCREMENT BY 1 START WITH 1000
  */

...
String cols[] = {"ORDER_ID", "DESCRIPTION"};

// Create a PreparedStatement for inserting a row into the ORDERS table.
OraclePreparedStatement pstmt = (OraclePreparedStatement)
conn.prepareStatement("INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ISBN,  DESCRIPTION) VALUES (SEQ01.NEXTVAL, 101,
 966431502, ?)", cols);
char c[] = {'a', '\u5185', 'b'};
String s = new String(c);
pstmt.setNString(1, s);
pstmt.executeUpdate();
ResultSet rset = pstmt.getGeneratedKeys();
...

JDBC 3.0 LOB Interface Methods

Table 3-2 and Table 3-3 show the conversions between Oracle proprietary methods and JDBC 3.0 standard methods.

Result Set Holdability

Result set holdability was introduced since JDBC 3.0. This feature enables applications to decide whether the ResultSet objects should be open or closed, when a commit operation is performed. The commit operation could be either implicit or explicit.

Oracle Database supports only HOLD_CURSORS_OVER_COMMIT. Therefore, it is the default value for Oracle JDBC drivers. Any attempt to change holdability will throw a SQLFeatureNotSupportedException exception.

Wrapper Pattern Support

Wrapper pattern is a common coding pattern used in Java applications to provide extensions beyond the traditional JDBC API that are specific to a data source. You may need to use these extensions to access the resources that are wrapped as proxy class instances representing the actual resources. JDBC 4.0 introduces the Wrapper interface that describes a standard mechanism to access these wrapped resources represented by their proxy, to permit direct access to the resource delegates.

The Wrapper interface provides the following two methods:

• public boolean isWrapperFor(Class<?> iface) throws SQLException;

• public <T> T unwrap(Class<T> iface) throws SQLException;

The other JDBC 4.0 interfaces, except those that represent SQL data, all implement this interface. These include Connection, Statement and its subtypes, ResultSet, and the metadata interfaces.

SQLXML Type

One of the most important updates in JDBC 4.0 standard is the support for the XML data type, defined by the SQL 2003 standard. Now JDBC offers a mapping interface to support the SQL/XML database data type, that is, java.sql.SQLXML. This new JDBC interface defines Java native bindings for XML, thus making handling of any database XML data easier and more efficient.

You can create an instance of XML by calling the createSQLXML method in java.sql.Connection interface. This method returns an empty XML object.

The PreparedStatement, CallableStatement, and ResultSet interfaces have been extended with the appropriate getter and setter methods in the following way:

• PreparedStatement: The method setSQLXML have been added

• CallableStatement: The methods getSQLXML and setSQLXML have been added

• ResultSet: The method getSQLXML have been added

Example

import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;
 
public class SQLXMLTest
 {
 
  public static void main(String[] args) 
  {
  
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  PreparedStatement ps = null;
  
  String xml = "<?xml version=\"1.0\"?>\n" +
    "<oldjoke>\n" +
    "<burns>Say <quote>goodnight</quote>, Gracie.</burns>\n" +
    "<allen><quote>Goodnight, Gracie.</quote></allen>\n" +
    "<applause/>\n" +
    "</oldjoke>";
 
  try
  {
 
     OracleDataSource ods = new OracleDataSource();
     ods.setURL("jdbc:oracle:thin:@//localhost:5221/orcl");
     ods.setUser("HR");
     ods.setPassword("hr");
     conn = ods.getConnection();
 
     ps = conn.prepareStatement("insert into x values (?, ?)");
     ps.setString(1, "string to string");
     SQLXML x = conn.createSQLXML();
     x.setString(xml);
     ps.setSQLXML(2, x);
     ps.execute();
     stmt = conn.createStatement();
     rs = stmt.executeQuery("select * from x");
     while (rs.next()) 
     {
       x = rs.getSQLXML(2);
       System.out.println(rs.getString(1) + "\n" + rs.getSQLXML(2).getString());
       x.free();
     }
 
     rs.close();
     ps.close();    
  }
 
  catch (SQLException e){e.printStackTrace ();}
 
  }
}

Enhanced Exception Hierarchy and SQLException

JDBC 3.0 defines only a single exception, SQLException. However, there are large categories of errors and it is useful to distinguish them. This feature provides subclasses of the SQLException class to identify the different categories of errors. The primary distinction is between permanent errors and transient errors. Permanent errors are a result of the correct operation of the system and will always occur. Transient errors are the result of failures, including timeouts, of some part of the system and may not reoccur.

JDBC 4.0 adds additional exceptions to represent transient and permanent errors and the different categories of these errors.

Also, the SQLException class and its subclasses are enhanced to provide support for the J2SE chained exception functionality.

The RowId Data Type

JDBC 4.0 provides the java.sql.RowId data type to represent SQL ROWID values. You can retrieve a RowId value using the getter methods defined in the ResultSet and CallableStatement interfaces. You can also use a RowId value in a parameterized PreparedStatement to set a parameter with a RowId object or in an updatable result set to update a column with a specific RowId value.

A RowId object is valid until the identified row is not deleted. A RowId object may also be valid for the following:

• The duration of the transaction in which it is created

• The duration of the session in which it is created

• An undefined duration where by it is valid forever

The lifetime of the RowId object can be determined by calling the DatabaseMetaData.getRowIdLifetime method.

LOB Creation

In JDBC 4.0, the Connection interface has been enhanced to provide support for the creation of BLOB, CLOB, and NCLOB objects. The interface provides the createBlob, createClob, and createNClob methods that enable you to create Blob, Clob, and NClob objects.

The created large objects (LOBs) do not contain any data. You can add or retrieve data to or from these objects by calling the APIs available in the java.sql.Blob, java.sql.Clob, and java.sql.NClob interfaces. You can either retrieve the entire content or a part of the content from these objects. The following code snippet illustrates how to retrieve 100 bytes of data from a BLOB object starting at offset 200:

...
Connection con = DriverManager.getConnection(url, props);
Blob aBlob = con.createBlob();
// Add data to the BLOB object.
aBlob.setBytes(...);
...
// Retrieve part of the data from the BLOB object.
InputStream is = aBlob.getBinaryStream(200, 100);
...

You can also pass LOBs as input parameters to a PreparedStatement object by using the setBlob, setClob, and setNClob methods. You can use the updateBlob, updateClob, and updateNClob methods to update a column value in an updatable result set.

These LOBs are temporary LOBs and can be used for any purpose for which temporary LOBs should be used. To make the storage permanent in the database, these LOBs must be written to a table.

Temporary LOBs remain valid for at least the duration of the transaction in which they are created. This may result in unwarranted use of memory during a long running transaction. You can release LOBs by calling their free method, as follows:

...
Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();
...

National Language Character Set Support

JDBC 4.0 introduces the NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB JDBC types to access the national character set types. These types are similar to the CHAR, VARCHAR, LONGVARCHAR, and CLOB types, except that the values are encoded using the national character set.

setClientInfo Method

The setClientInfo method sets the value of the property providing client information. This method accepts keys of the form <namespace>.<keyname>. The JDBC driver supports any <namespace>.<keyname> combination.

The setClientInfo method checks the Java permission oracle.jdbc.clientInfo and if the security check fails, then it throws a SecurityException. It supports permission name patterns of the form <namespace>.*. The setClientInfo method either sets or clears all pairs, so it requires that the permission name must be set to an asterisk (*).

The setClientInfo method is backward compatible with the setEndToEndMetrics and the setClientIdentifier methods, and can use DMS to set client tags.

Monitoring Database Operations

Many Java applications do not have a database connection, but they need to track database activities on behalf of their functionalities. For such applications, Oracle Database 12c Release 1 (12.1) introduces the DBOP tag that can be associated with a thread in the application when the application does not have explicit access to a database. The DBOP tag is associated with a thread through the invocation of DMS APIs, without requiring an active connection to the database. When the thread sends the next database call, then DMS propagates these tags through the connection along with the database call, without requiring an extra round trip. In this way, applications can associate their activity with database operations while factorizing the code in the Application layer.The DBOP tag composes of the following:

• Database operation name

• The execution ID

• Operation attributes

The setClientInfo method supports the DBOP tag. The setClientInfo method sets the value of the tag to monitor the database operations. When the JDBC application connects to the database and a database round-trip is made, the database activities can be tracked. For example, you can set the value of the DBOP tag to foo in the following way:

...
Connection conn = DriverManager.getConnection(myUrl, myUsername, myPassword);
conn.setClientInfo("E2E_CONTEXT.DBOP", "foo");
Statement stmt = conn.createStatement();
stmt.execute("select 1 from dual");  // DBOP tag is set after this
...

getObject Method

The getObject method retrieves an object, based on the parameters passed. Oracle Database 12c Release 1 (12.1) supports the following two new getObject methods:

Method 1

<T> T getObject(int parameterIndex,
                java.lang.Class<T> type)
            throws SQLException

Method 2

<T> T getObject(java.lang.String parameterName,
                java.lang.Class<T> type)
            throws SQLException

These methods support the conversions listed in the JDBC specification and also the additional conversions listed in Table A-1. The Oracle Database 12c Release 1 (12.1) drivers also support conversions to some additional classes, which implement one or more static valueOf methods, if any of the following criteria is met:

• No other conversion is specified in JDBC specification or Table A-1

• The type argument defines one or more public static single argument methods named valueOf

• One or more of the valueOf methods take an argument that is a value of a type supported because of JDBC specification or Table A-1

This release of JDBC drivers convert the value to a type specified in the JDBC specification, or in Table A-1 and then call the corresponding valueOf method with the converted value as the argument. If there is more than one appropriate valueOf method, then the JDBC driver chooses one valueOf method in an unspecified way.

Example

ResultSet rs = . . . ;
Character c = rs.getObject(1, java.lang.Character.class);

The Character class defines the following valueOf method:

public static Character valueOf(char c);

Table A-1 specifies that NUMBER can be converted to char. So, if the first column of the ResultSet is a NUMBER, then the getObject method converts that NUMBER value to a char and passes the char value to the valueOf(char) method and returns the resulting Character object.