|Oracle9i JDBC Developer's Guide and Reference
Release 1 (9.0.1)
Part Number A90211-01
This chapter provides an overview of the Oracle implementation of JDBC, covering the following topics:
This section presents a brief introduction to Oracle JDBC, including a comparison to SQLJ.
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.
This section has the following subsections:
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.
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 is effective in the following circumstances:
JDBC is effective in the following circumstances:
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:
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.
The server-side and client-side Oracle JDBC drivers provide the same basic functionality. They all support the following standards and features:
These two implementations use different sets of class files.
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.
|Use||External Libraries Needed||
|Performance||Completeness of Features|
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.
Using the Thin driver inside an Oracle server or middle tier is considered separately, under "JDBC Server-Side Thin Driver" below.
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.
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:
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:
There is no difference in your code between using the Thin driver from a client application or from inside a server.
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
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
create role jdbcthin; call dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect' ); grant jdbcthin to scott;
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.
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".
Consider the following when choosing a JDBC driver to use for your application or applet:
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.
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.
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".
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:
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".
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.
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".
A number of Oracle extensions are available to Oracle JDBC application and applet programmers, in the following categories:
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.
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
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
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
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".
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".
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
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".
This section provides a brief discussion of platform, environment, and support features of the Oracle JDBC drivers. The following topics are discussed:
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".
For information about supported combinations of driver versions, JDK versions, and database versions, see "Requirements and Compatibilities for Oracle JDBC Drivers".
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.
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.
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.