1
Overview

This chapter provides an overview of the Oracle implementation of JDBC, covering the following topics:

• Introduction

• Overview of the Oracle JDBC Drivers

• Overview of Application and Applet Functionality

• Server-Side Basics

• Environments and Support

Introduction

This section presents a brief introduction to Oracle JDBC, including a comparison to SQLJ.

What is JDBC?

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers.

JDBC is based on the X/Open SQL Call Level Interface and complies with the SQL92 Entry Level standard.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance.

JDBC versus SQLJ

This section has the following subsections:

• Advantages of SQLJ over JDBC for Static SQL

• General Guidelines for Using JDBC and SQLJ

Developers who are familiar with the Oracle Call Interface (OCI) layer of client-side C code will recognize that JDBC provides the power and flexibility for the Java programmer that OCI does for the C or C++ programmer. Just as with OCI, you can use JDBC to query and update tables where, for example, the number and types of the columns are not known until runtime. This capability is called dynamic SQL. Therefore, JDBC is a way to use dynamic SQL statements in Java programs. Using JDBC, a calling program can construct SQL statements at runtime. Your JDBC program is compiled and run like any other Java program. No analysis or checking of the SQL statements is performed. Any errors that are made in your SQL code raise runtime errors. JDBC is designed as an API for dynamic SQL.

However, many applications do not need to construct SQL statements dynamically because the SQL statements they use are fixed or static. In this case, you can use SQLJ to embed static SQL in Java programs. In static SQL, all the SQL statements are complete or "textually evident" in the Java program. That is, details of the database object, such as the column names, number of columns in the table, and table name, are known before runtime. SQLJ offers advantages for these applications because it permits error checking at precompile time.

The precompile step of a SQLJ program performs syntax-checking of the embedded SQL, type checking against the database to assure that the data exchanged between Java and SQL have compatible types and proper type conversions, and schema checking to assure congruence between SQL constructs and the database schema. The result of the precompilation is Java source code with SQL runtime code which, in turn, can use JDBC calls. The generated Java code compiles and runs like any other Java program.

Although SQLJ provides direct support for static SQL operations known at the time the program is written, it can also interoperate with dynamic SQL through JDBC. SQLJ allows you to create JDBC objects when they are needed for dynamic SQL operations. In this way, SQLJ and JDBC can co-exist in the same program. Convenient conversions are supported between JDBC connections and SQLJ connection contexts, as well as between JDBC result sets and SQLJ iterators. For more information on this, see the Oracle9i SQLJ Developer's Guide and Reference.

The syntax and semantics of SQLJ and JDBC do not depend on the configuration under which they are running, thus enabling implementation on the client or database side or in the middle tier.

Advantages of SQLJ over JDBC for Static SQL

While JDBC provides a complete dynamic SQL interface from Java to relational databases, SQLJ fills a complementary role for static SQL.

Although you can use static SQL statements in your JDBC programs, they can be represented more conveniently in SQLJ. Here are some advantages you gain in using SQLJ over JDBC for static SQL statements:

• SQLJ source programs are more concise than equivalent JDBC programs, because SQLJ provides a shorter syntax.

• SQLJ provides strong typing of connections (and the sets of SQL entities that they access), query outputs, and return parameters.

• SQLJ can use database connections to type-check static SQL code at translation time. JDBC, being a completely dynamic API, does not perform any type-checking until run-time.

• SQLJ programs allow direct embedding of Java bind expressions within SQL statements. JDBC requires separate getter and/or setter call statements for each bind variable.

• SQLJ provides simplified rules for calling SQL stored procedures and functions.

General Guidelines for Using JDBC and SQLJ

SQLJ is effective in the following circumstances:

• You want to be able to check your program for errors at translation-time, rather than at run-time.

• You want to write an application that you can deploy to another database. Using SQLJ, you can customize the static SQL for that database at deployment-time.

• You are working with a database that contains compiled SQL. You will want to use SQLJ because you cannot compile SQL statements in a JDBC program.

JDBC is effective in the following circumstances:

• Your program uses dynamic SQL. For example, you have a program that builds queries in real-time or has an interactive query component.

• You do not want to have a SQLJ layer during deployment or development. For example, you might want to download only the JDBC Thin driver and not the SQLJ runtime libraries to minimize download time over a slow link.

  Note: You can intermix SQLJ code and JDBC code in the same source. This is discussed in the Oracle9i SQLJ Developer's Guide and Reference.

Overview of the Oracle JDBC Drivers

This section introduces the Oracle JDBC drivers, their basic architecture, and some scenarios for their use. This information describes the core functionality of all JDBC drivers. However, there is special functionality for the OCI driver, which is described Chapter 16, "JDBC OCI Extensions".

Oracle provides the following JDBC drivers:

• Thin driver, a 100% Java driver for client-side use without an Oracle installation, particularly with applets

• OCI driver for client-side use with an Oracle client installation

• server-side Thin driver, which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middle-tier scenarios

• server-side internal driver for code that runs inside the target server (that is, inside the Oracle server that it must access)

Figure 1-1 illustrates the driver-database architecture for the JDBC Thin, OCI, and server-side internal drivers.

The rest of this section describes common features of the Oracle drivers and then discusses each one individually, concluding with a discussion of some of the considerations in choosing the appropriate driver for your application.

Figure 1-1 Driver-Database Architecture

Text description of the illustration dbarch.gif

Common Features of Oracle JDBC Drivers

The server-side and client-side Oracle JDBC drivers provide the same basic functionality. They all support the following standards and features:

• either JDK 1.2.x / JDBC 2.0 or JDK 1.1.x / JDBC 1.22 (with Oracle extensions for JDBC 2.0 functionality)

  These two implementations use different sets of class files.

• same syntax and APIs

• same Oracle extensions

• full support for multi-threaded applications

Oracle JDBC drivers implement standard Sun Microsystems java.sql interfaces. Through the oracle.jdbc package, you can access the Oracle features in addition to the Sun features. This package is equivalent to the oracle.jdbc.driver package which is deprecated for Oracle9i.

Table 1-1 shows how the client-side drivers compare.

Table 1-1 JDBC Client-Side Drivers Compared at a Glance  

Driver	Type	Size	Protocol	100% Java	Use	External Libraries Needed	Platform- dependent	Performance	Completeness of Features
Thin	IV	Small	TTC	Yes	Applet and application	No	No	Better	Better
OCI	II	Large	TTC	No	Application	Yes	Yes	Best	Best

Note: Most JDBC 2.0 functionality, including that for objects, arrays, and LOBs, is available in a JDK 1.1.x environment through Oracle extensions.

JDBC Thin Driver

The Oracle JDBC Thin driver is a 100% pure Java, Type IV driver. It is targeted for Oracle JDBC applets but can be used for applications as well. Because it is written entirely in Java, this driver is platform-independent. It does not require any additional Oracle software on the client side. The Thin driver communicates with the server using TTC, a protocol developed by Oracle to access the Oracle Relational Database Management System (RDBMS).

For applets it can be downloaded into a browser along with the Java applet being run. The HTTP protocol is stateless, but the Thin driver is not. The initial HTTP request to download the applet and the Thin driver is stateless. Once the Thin driver establishes the database connection, the communication between the browser and the database is stateful and in a two-tier configuration.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of TCP/IP that emulates Oracle Net and TTC (the wire protocol used by OCI) on top of Java sockets. Both of these protocols are lightweight implementation versions of their counterparts on the server. The Oracle Net protocol runs over TCP/IP only.

The driver supports only TCP/IP protocol and requires a TNS listener on the TCP/IP sockets from the database server.

Note: When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.

Using the Thin driver inside an Oracle server or middle tier is considered separately, under "JDBC Server-Side Thin Driver" below.

JDBC OCI Driver

The JDBC OCI driver is a Type II driver for use with client-server Java applications. This driver requires an Oracle client installation, and therefore is Oracle platform-specific and not suitable for applets.

Note: In Oracle9i, the OCI driver is a single OCI driver for use with all database versions. It replaces the distinct OCI8 and OCI7 drivers of previous releases. While the OCI8 and OCI7 drivers are deprecated for Oracle9i, they are still supported for backward compatibility.

The JDBC OCI driver provides OCI connection pooling functionality, which can either be part of the JDBC client or a JDBC stored procedure. OCI driver connection pooling requires fewer physical connections than standard connection pooling, it also provides a uniform interface, and allows you to dynamically configure the attributes of the connection pool. For a complete description of OCI driver connection pooling, see "OCI Driver Connection Pooling".

The OCI driver supports Oracle7, Oracle8/8i, and Oracle9i with the highest compatibility. It also supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

The OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to the Oracle Call Interface (OCI), using native methods to call C-entry points. These calls are then sent over Oracle Net to the Oracle database server. The OCI driver communicate with the server using the Oracle-developed TTC protocol.

The OCI driver uses the OCI libraries, C-entry points, Oracle Net, CORE libraries, and other necessary files on the client machine on which it is installed.

The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to create applications that use the native procedures or function calls of a third-generation language to access an Oracle database server and control all phases of SQL statement execution. The OCI driver is designed to build scalable, multi-threaded applications that can support large numbers of users securely.

The Oracle9i JDBC OCI driver has the following functionality:

• Uses OCI

• Connection Pooling

• OCI optimized fetch

• Prefetching

• Fastest LOB access

• Client-side object cache

• Transparent Application Failover (TAF)

• Middle-tier authentication

JDBC Server-Side Thin Driver

The Oracle JDBC server-side Thin driver offers the same functionality as the client-side Thin driver, but runs inside an Oracle database and accesses a remote database.

This is especially useful in two situations:

• to access a remote Oracle server from an Oracle server acting as a middle tier

• more generally, to access one Oracle server from inside another, such as from any Java stored procedure or Enterprise JavaBean

There is no difference in your code between using the Thin driver from a client application or from inside a server.

Note: Statement cancel() and setQueryTimeout() methods are not supported by the server-side Thin driver.

About Permission for the Server-Side Thin Driver

The thin driver opens a socket to use for its connection. Because the Oracle server is enforcing the Java security model, this means that a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted with the appropriate permission. This is an example of how the permission can be granted for 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 upper case. The '*' is a pattern. It is possible to limit the permission to allow connecting to specific machines or ports. See the Javadoc for complete details on the java.net.SocketPermission class. Also, refer to the Oracle9i Java Developer's Guide for further discussion of Java security inside the Oracle server.

JDBC Server-Side Internal Driver

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

The server-side internal driver, the JVM, the database, KPRB (server-side) C library, and the SQL engine all run within the same address space, so the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.

The server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions. For more information on the server-side internal driver, see "JDBC in the Server: the Server-Side Internal Driver".

Note: The server-side internal driver supports only JDK 1.2.x.

Choosing the Appropriate Driver

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

• If you are writing an applet, you must use the JDBC Thin driver. JDBC OCI-based driver classes will not work inside a Web browser, because they call native (C language) methods.

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

• If you are writing a client application for an Oracle client environment and need maximum performance, then choose the JDBC OCI driver.

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

• If your code will run inside the target Oracle server, then use the JDBC server-side internal driver to access that server. (You can also access remote servers using the server-side Thin driver.)

• If performance is critical to your application, you want maximum scalability of the Oracle server, or you need the enhanced availability features like TAF or the enhanced proxy features like middle-tier authentication, then choose the OCI driver.

Overview of Application and Applet Functionality

This section compares and contrasts the basic functionality of JDBC applications and applets, and introduces Oracle extensions that can be used by application and applet programmers.

Application Basics

You can use either the Oracle JDBC Thin or OCI driver for a client application. Because the JDBC OCI driver uses native methods, there can be significant performance advantages in using this driver for your applications.

An application that can run on a client can also run in the Oracle server, using the JDBC server-side internal driver.

If you are using a JDBC OCI driver in an application, then the application will require an Oracle installation on its clients. For example, the application will require the installation of Oracle Net and client libraries.

The JDBC Thin and OCI drivers offer support for data encryption and integrity checksum features of the Oracle Advanced Security option (formerly known as ANO or ASO). See "JDBC Client-Side Security Features". Such security is not necessary for the server-side internal driver.

Applet Basics

This section describes the issues you should take into consideration if you are writing an applet that uses the JDBC Thin driver.

For more about applets and a discussion of relevant firewall, browser, and security issues, see "JDBC in Applets".

Applets and Security

Without special preparations, an applet can open network connections only to the host machine from which it was downloaded. Therefore, an applet can connect to databases only on the originating machine. If you want to connect to a database running on a different machine, you have two options:

• Use Oracle8 Connection Manager on the host machine. The applet can connect to Oracle8 Connection Manager, which in turn connects to a database on another machine.

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

Both of these topics are described in greater detail in "Connecting to the Database through the Applet".

The Thin driver offers support for data encryption and integrity checksum features of the Oracle Advanced Security option. See "JDBC Client-Side Security Features".

Applets and Firewalls

An applet that uses the JDBC Thin driver can connect to a database through a firewall. See "Using Applets with Firewalls" for more information on configuring the firewall and on writing connect strings for the applet.

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. This is described in detail in "Packaging Applets".

Oracle Extensions

A number of Oracle extensions are available to Oracle JDBC application and applet programmers, in the following categories:

• type extensions (such as ROWIDs and REF CURSOR types)

• wrapper classes for SQL types (the oracle.sql package)

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

• extended LOB support

• extended connection, statement, and result set functionality

• performance enhancements

See Chapter 5, "Overview of Oracle Extensions" for an overview of type extensions and extended functionality, and succeeding chapters for further detail. See Chapter 12, "Performance Extensions" regarding Oracle performance enhancements.

Package oracle.jdbc

Beginning in Oracle9i, the Oracle extensions to JDBC are captured in the package oracle.jdbc. This package contains classes and interfaces that specify the Oracle extensions in a manner similar to the way the classes and interfaces in java.sql specify the public JDBC API.

Your code should use the package oracle.jdbc instead of the package oracle.jdbc.driver used in earlier versions of Oracle. Use of the package oracle.jdbc.driver is now deprecated, but will continue to be supported for backwards compatibility.

All that is required to covert your code is to replace "oracle.jdbc.driver" with "oracle.jdbc" in the source and recompile. This cannot be done piece-wise. You must convert all classes and interfaces that are referenced by an application. Conversion is not required, but is highly recommended. Future releases of Oracle may have features that are incompatible with use of the package oracle.jdbc.driver.

The purpose of this change is to enable the Oracle JDBC drivers to have multiple implementations. In all releases up to and including Oracle9i, all of the Oracle JDBC drivers have used the same top level implementation classes, the classes in the package oracle.jdbc.driver. By converting your code to use oracle.jdbc, you will be able to take advantage of future enhancements that use different implementation classes. There are no such enhancements in Oracle9i, but there are plans for such enhancements in the future.

Additionally, these interfaces permit the use of some code patterns that are difficult to use when your code uses the package oracle.jdbc.driver. For example, you can more easily develop wrapper classes for the Oracle JDBC classes. If you wished to wrap the OracleStatement class in order to log all SQL statements, you could easily do so by creating a class that wraps OracleStatement. That class would implement the interface oracle.jdbc.OracleStatement and hold an oracle.jdbc.OracleStatement as an instance variable. This wrapping pattern is much more difficult when your code uses the package oracle.jdbc.driver as you cannot extend the class oracle.jdbc.driver.OracleStatement.

Once again, your code should use the new package oracle.jdbc instead of the package oracle.jdbc.driver. Conversion is not required as oracle.jdbc.driver will continue to be supported for backwards compatibility. Conversion is highly recommended as there may in later releases be features that are not supported if your code uses oracle.jdbc.driver.

Server-Side Basics

By using the Oracle JDBC server-side internal driver, code that runs in an Oracle database, such as in Java stored procedures or Enterprise JavaBeans, can access the database in which it runs.

For a complete discussion of the server-side driver, see "JDBC in the Server: the Server-Side Internal Driver".

Session and Transaction Context

The server-side internal driver operates within a default session and default transaction context. For more information on default session and transaction context for the server-side driver, see "Session and Transaction Context for the Server-Side Internal Driver".

Connecting to the Database

The server-side internal driver uses a default connection to the database. You can connect to the database with either the DriverManager.getConnection() method or the Oracle-specific OracleDriver class defaultConnection() method. For more information on connecting to the database with the server-side driver, see "Connecting to the Database with the Server-Side Internal Driver".

Environments and Support

This section provides a brief discussion of platform, environment, and support features of the Oracle JDBC drivers. The following topics are discussed:

• Supported JDK and JDBC Versions

• JNI and Java Environments

• JDBC and the Oracle Application Server

• JDBC and IDEs

Supported JDK and JDBC Versions

Oracle has two versions of the Thin and OCI drivers--one that is compatible with JDK 1.2.x and one that is compatible with JDK 1.1.x. The JDK 1.2.x versions support standard JDBC 2.0. The JDK 1.1.x versions support most JDBC 2.0 features, but must do so through Oracle extensions because JDBC 2.0 features are not available in JDK 1.1.x versions.

Very little is required in migrating from a JDK 1.1.x environment to a JDK 1.2.x environment. For information, see "Migration from JDK 1.1.x to JDK 1.2.x".

Notes: The server-side internal driver supports only JDK 1.2.x. JDK 1.0.2 is no longer supported in the current drivers. Each driver implementation uses its own JDBC classes ZIP file--classes12.zip for JDK 1.2.x versions, and classes111.zip for JDK 1.1.x versions.

For information about supported combinations of driver versions, JDK versions, and database versions, see "Requirements and Compatibilities for Oracle JDBC Drivers".

JNI and Java Environments

The Oracle JDBC OCI driver uses the standard JNI (Java Native Interface) to call Oracle OCI C libraries. Prior to 8.1.6, when the OCI drivers supported JDK 1.0.2, they used NMI (Native Method Interface) for C calls. NMI was an earlier specification by Sun Microsystems and was the only native call interface supported by JDK 1.0.2.

Because JNI is now supported by Oracle JDBC, you can use the OCI driver with Java virtual machines other than that of Sun Microsystems--in particular, with Microsoft and IBM JVMs. These JVMs support only JNI for native C calls.

JDBC and the Oracle Application Server

The Oracle Application Server (OAS) is a collection of middleware services and tools that provide a scalable, robust, secure, and extensible platform for distributed, object-oriented applications. The OAS supports access to applications from both Web clients (browsers) using the Hypertext Transfer Protocol (HTTP), and CORBA clients, which use the Common Object Request Broker Architecture (CORBA) and the Internet Inter-ORB Protocol (IIOP).

You can use the JDBC OCI drivers on a middle tier in conjunction with OAS (formerly Web Application Server, or WAS) versions 3.0 and higher--the OAS bundles JDBC with its distribution. For more information about the use of JDBC with the OAS, refer to the Oracle Application Server documentation.

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 Oracle Internet platform. The Oracle JDeveloper environment contains integrated support for JDBC and SQLJ, including the 100% pure JDBC Thin driver and the native OCI drivers. The database component of Oracle JDeveloper uses the JDBC drivers to manage the connection between the application running on the client and the server. See your Oracle JDeveloper documentation for more information.