1 Introducing JDBC

Common Features of Oracle JDBC Drivers

The server-side and client-side Oracle JDBC drivers provide the same basic functionality.

The JDBC Thin and OCI drivers support the following versions of Java Development Kit (JDK): 1.2.x, 1.3.x and 1.4.x. The server-side Thin driver and server-side internal driver support JDK 1.4.1. All the JDBC drivers support the following standards and features:

• Same syntax and APIs

• Same Oracle extensions

• Full support for multithreaded applications

Oracle JDBC drivers implement the standard java.sql interfaces. You can access the Oracle-specific features, in addition to the standard features, by using the oracle.jdbc package.

JDBC Thin Driver

The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client side. The JDBC Thin driver communicates with the server using SQL*Net to access the Oracle Database.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of SQL*Net on top of Java sockets. The driver supports the TCP/IP protocol and requires a TNS listener on the TCP/IP sockets on the database server.

JDBC OCI Driver

The JDBC OCI driver is a Type II driver used with Java applications. It requires an Oracle client installation and, therefore, is Oracle platform-specific. It supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and Internetwork Packet Exchange/Sequenced Packet Exchange (IPX/SPX).

The JDBC OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to OCI, using native methods to call C-entry points. These calls communicate with the database using SQL*Net.

The JDBC OCI driver uses the OCI libraries, C-entry points, Oracle Net, core libraries, and other necessary files on the client computer where it is installed.

OCI is an API that enables you to create applications that use the native procedures or function calls of a third-generation language to access Oracle Database and control all phases of the SQL statement processing.

JDBC Server-Side Thin Driver

The JDBC server-side Thin driver offers the same functionality as the JDBC Thin driver that runs on the client side. However, the JDBC server-side Thin driven runs inside the Oracle Database and accesses a remote database or a different session on the same database.

This driver is useful in the following scenarios:

• Accessing a remote database server from an Oracle Database instance acting as a middle tier

• Accessing an Oracle Database session from inside another, such as from a Java stored procedure

The use of JDBC Thin driver from a client application or from inside a server does not affect the code.

Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user SCOTT:

CREATE ROLE jdbcthin;
CALL dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect');
GRANT jdbcthin TO SCOTT;

Note that JDBCTHIN in the grant_permission call must be in uppercase. The asterisk (*) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

JDBC Server-Side Internal Driver

The JDBC server-side internal driver supports any Java code that runs inside the Oracle Database, such as in a Java stored procedures or Enterprise JavaBean (EJB), and must access the same database. It lets the Java virtual machine (JVM) to communicate directly with the SQL engine. The driver supports only JDK 1.4.1.

The JDBC server-side internal driver, the Oracle JVM, the database, and the SQL engine all run within the same address space, and therefore, the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.

The JDBC server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions.

Choosing the Appropriate Driver

Consider the following when choosing a JDBC driver for your application or applet:

• In general, unless you need OCI-specific features, such as support for non-TCP/IP networks, use the JDBC Thin driver.

• If you want maximum portability and performance, then use the JDBC Thin driver. You can connect to the Oracle Database from either an application or an applet using the JDBC Thin driver.

• If you want to use Lightweight Directory Access Protocol (LDAP) over Secure Sockets Layer (SSL), then use the JDBC Thin driver.

• If you are writing a client application for an Oracle client environment and need OCI-driver-specific features, such as support for non-TCP/IP networks, then use the JDBC OCI driver.

• If you are writing an applet, then you must use the JDBC Thin driver.

• For code that runs in the database server acting as a middle tier, use the JDBC server-side Thin driver.

• If your code will run inside the target database server, then use the JDBC server-side internal driver to access that server.

Feature Differences Between JDBC OCI and Thin Drivers

Table 1-1 lists the features that are specific either to the JDBC OCI or JDBC Thin driver in Oracle Database 10g release 2 (10.2).

Applet Basics

You can use only the Oracle JDBC Thin driver for an applet.

Applets and Security

An applet can open network connections only to the host computer from which it was downloaded. Therefore, an applet can connect only to databases on the originating computer. If you want your applet to connect to a database running on a different computer, then you have the following options:

• Use the Oracle Connection Manager on the host computer. The applet can connect to the Connection Manager, which connects to a database on another computer.

• Use signed applets, which can request socket connection privileges to other computers.

Your applet can take advantage of the data encryption and integrity checksum features of the Oracle Advanced Security option.

Applets and Firewalls

An applet can connect to a database through a firewall.

Packaging and Deploying Applets

To package and deploy an applet, you must place the JDBC Thin driver classes and the applet classes in the same .zip file.

Oracle Extensions

A number of Oracle extensions are available, which can be used by Oracle JDBC application and applet programmers. These extensions include:

• Type extensions, such as ROWID and REF CURSOR types

• Wrapper classes for SQL types provided by the oracle.sql package

• Support for custom Java classes to map to user-defined types

• Extended large object (LOB) support

• Extended connection, statement, and result set functionality

• Performance enhancements

Supported JDK and JDBC Versions

In Oracle Database 10g release 2 (10.2), all the JDBC drivers are compatible with JDK 1.2.x and later. JDK 1.0.x and JDK 1.1.x are no longer supported, and therefore, the classes111.zip, classes111.jar, classes111_g.zip, classes111_g.jar, and nls_charset11.zip files are no longer provided.

JNI and Java Environments

The JDBC OCI driver uses the standard Java Native Interface (JNI) to call OCI C libraries. You can use the JDBC OCI driver with JVMs other than that of Sun Microsystems, in particular, with Microsoft and IBM JVMs.

JDBC and IDEs

The Oracle JDeveloper Suite provides developers with a single, integrated set of products to build, debug, and deploy component-based database applications for the Internet. The Oracle JDeveloper environment contains integrated support for JDBC, including the JDBC Thin driver and the native OCI driver. The database component of Oracle JDeveloper uses the JDBC drivers to manage the connection between the application running on the client and the server.

New Features

Oracle Database 10g release 2 (10.2) supports the following new features:

• DML returning

  The JDBC OCI and the JDBC Thin drivers support the data manipulation language (DML) returning feature. DML returning enables you to retrieve auto-generated keys along with other columns or values that your application may use.

  See Also:

  "DML Returning"

• JSR 114 RowSets

  Oracle Database 10g release 2 (10.2) provides support for all the RowSet implementations defined in the JDBC RowSet Implementations Specification (JSR-114). In particular, Oracle Database 10g release 2 (10.2) provides support for WebRowSet, FilteredRowSet, and JoinRowSet.

  See Also:

  Chapter 20, "JDBC RowSets"

• NCHAR literal support

  Oracle Database 10g release 2 (10.2) provides the NCHAR literal support. For further information, refer to the Note in "NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property".

• Proxy authentication

  In this release of Oracle Database, the proxy authentication feature is supported by both the JDBC Thin and JDBC OCI drivers.

  See Also:

  Chapter 12, "Proxy Authentication"

• Result set holdability

  Oracle JDBC drivers provide support for result set holdability, which is a feature of the JDBC 3.0 standard. This feature enables applications to decide whether the ResultSet objects should be open or closed, when an implicit or explicit commit operation is performed.

  See Also:

  "Result Set Holdability"

• Retrieval of auto-generated keys

  Oracle Database 10g release 2 (10.2) provides support for the retrieval of auto-generated keys JDBC 3.0 standard feature. This feature enables you to retrieve values that are generated by the database.

  See Also:

  "Retrieval of Auto-Generated Keys"

• Run-time connection load balancing

  The Oracle JDBC Thin and JDBC OCI drivers support the run-time connection load balancing feature. This feature enables routing of work requests to a database instance that offers the best performance, minimizing the need to relocate work.

  See Also:

  Chapter 24, " Run-Time Connection Load Balancing"

• SSL support

  Oracle Database 10g release 2 (10.2) provides support for the Secure Sockets Layer (SSL) Protocol.

  See Also:

  Chapter 11, "SSL Support"

• XAConnection caching

  The implicit connection caching feature supports the caching of XA connections. This feature is supported by both the JDBC Thin and JDBC OCI drivers.

  See Also:

  Chapter 23, "Implicit Connection Caching"

Desupported Features

No feature has been desupported in Oracle Database 10g release 2 (10.2). That is, Oracle Database 10g release 2 (10.2) supports all features that were supported in Oracle Database 10g release 1 (10.1).

Interface Changes

There are some changes in the setString, setCharacterStream, setAsciiStream, setBytes, and setBinaryStream methods of PreparedStatement in Oracle Database 10g release 2 (10.2).

There are three way to bind data for input:

• Direct binding where the data itself is placed in a bind buffer

• Stream binding where the data is streamed

• LOB binding where a temporary lob is created, the data placed in the LOB using the LOB APIs, and the bytes of the LOB locator are placed in the bind buffer

The three kinds of binding have some differences in performance and have an impact on batching. Direct binding is fast and batching is fine. Stream binding is slower, may require multiple round trips, and turns batching off. LOB binding is very slow and requires many round trips. Batching works, but might be a bad idea. They also have different size limits, depending on the type of the SQL statement.

For SQL parameters, the length of normal parameter types, such as RAW and VARCHAR2, is fixed by the size of the target column. For PL/SQL parameters, the size is limited to a fixed number of bytes, which is 32512.

The original behavior of the APIs were:

• setString: Direct bind of characters

• setCharacterStream: Stream bind of characters

• setAsciiStream: Stream bind of bytes

• setBytes: Direct bind of bytes

• setBinaryStream: Stream bind of bytes

In Oracle Database 10g release 2 (10.2), automatic switching between binding modes, based on the data size and on the type of the SQL statement is provided.

setBytes and setBinaryStream

For SQL, direct bind is used for size up to 2000 and stream bind for larger

For PL/SQL direct bind is used for size up to 32512 and lob bind is used for larger.

setString, setCharacterStream, and setAsciiStream

For SQL, direct bind is used up to 32766 Java characters and stream bind is used for larger. This is independent of character set.

For PL/SQL, you must be careful about the byte size of the character data in the database character set or the national character set depending on the setting of the form of use parameter. Direct bind is used for data where the byte length is less than 32512 and LOB bind for larger.

For fixed length character sets, multiply the length of the Java character data by the fixed character size in bytes and compare that to 32512. For variable length character sets, there are three cases based on the Java character length, as follows:

• If character length is less than 32512 divided by the maximum character size, then direct bind is used.

• If character length is greater than 32512 divided by the minimum character size, then LOB bind is used.

• If character length is in between and if the actual length of the converted bytes is less than 32512, then direct bind is used, else LOB bind is used.