Using Java Database Connectivity (JDBC) with Oracle

Like this article? We recommend

Core Java, Vol. II-Advanced Features, 12th Edition

Learn More Buy

Like this article? We recommend

Like this article? We recommend 

Core Java, Vol. II-Advanced Features, 12th Edition

Learn More Buy

• Java Database Connectivity (JDBC) is a method of Java calling SQL and PL/SQL. The DML operations of SELECT, INSERT, UPDATE, and DELETE as well as calling PL/SQL procedures and returning of resultsets can be done using JDBC.

• Oracle8i provides JDBC drivers for Java programs to interface with the database.

• Java programs can call SQL and PL/SQL using the classes in a standard package java.sql.* and two other Oracle supplied packages oracle.sql.* and oracle.jdbc.driver.*.

• The basic steps in creating a JDBC application involve importing JDBC packages, loading and registering the JDBC driver, opening a connection to the database, creating a statement object to perform a query, executing the statement object, returning a query resultset, processing the resultset, closing the resultset and statement objects, and finally closing the connection.

• To process INSERTS, UPDATES, and DELETES, create a PreparedStatement object.

• To invoke PL/SQL subprograms create a CallableStatement object.

• Transaction processing involving COMMIT and ROLLBACK is done by setting the auto-commit mode to OFF and then using the commit() and rollback() methods on the Connection object.

• To manipulate ROWIDS and REF CURSORS use the Oracle Extensions supplied by the classes in the oracle.sql.* package.

This chapter explains the use of Java Database Connectivity for database access in Java. It highlights the method to incorporate Java in the Oracle 8i database using JDBC. The various methods of using JDBC starting from the querying and returning of resultsets to executing DML from the Oracle 8i database are described in detail. The Oracle JDBC extensions are discussed. A case study is presented to illustrate the concepts.

Overview of JDBC—Java Calling SQL and PL/SQL

This section gives a brief outline of JDBC and the various JDBC drivers. It also highlights the JDBC 2.0 features. The details of the case study used to illustrate the various JDBC concepts throughout are presented.

About JDBC

JDBC provides a standard interface for accessing a relational database from a Java application regardless of where the application is running and where the database is. From an Oracle 8i perspective it provides a way for Java applications to call SQL and PL/SQL. In other words it is a way to execute SQL statements and also call stored database procedures. One important feature of JDBC is location independence. Java programs with database access can be written and deployed as an application or as a Web-based applet. The ease of development, robustness, and security of Java programs makes it a good choice for writing database applications in Java. Moreover, the early compile-time checking and dynamic runtime checking goes in sync with the dynamic SQL interface of JDBC for Java. JDBC consists of a high-level "thin" API and multiple low-level drivers for connecting to different databases. There are four types of drivers defined by JDBC as follows:

• Type 1: JDBC/ODBC—These require an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. It provides no host redirection capability.

• Type2: Native API—This type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection.

• Type 3: Open Protocol-Net—This is not vendor specific and works by forwarding database requests to a remote database source using a net server component. How the net server component accesses the database is transparent to the client. The client driver communicates with the net server using a database-independent protocol and the net server translates this protocol into database calls. This type of driver can access any database.

• Type 4: Proprietary Protocol-Net—This has a same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client.

Figure 3.1 shows a typical implementation of a JDBC application.

Figure 3.1 A typical JDBC application.

Basically a Java program implementing JDBC performs the following functions:

• Load a JDBC driver.

• Establish a database connection.

• Optionally interrogate the database for capability subset.

• Optionally retrieve schema metadata information.

• Construct a SQL or callable statement object and send queries or database tasks.

• Execute the database tasks or process resultsets.

• Close the statement object and resultset.

• Close the connection.

Oracle JDBC Drivers

Oracle 8i provides four types of JDBC drivers, namely, thin drivers, OCI drivers, server-side thin drivers, and server-side internal drivers. These client-side and server-side drivers provide the same functionality and have the same syntax and APIs and they share the same Oracle extensions. The difference lies in how they connect to the database and how they transfer data. The server-side internal driver supports JDK 1.2.x/JDBC 2.0 whereas the other drivers support JDK 1.1.x/JDBC 1.22 with Oracle extensions for JDBC 2.0. The following gives a detailed description of each of these drivers:

Client-side Oracle JDBC Thin Driver

This driver is a Type 4 (Proprietary Protocol-Net) driver and is written in 100% pure Java making it platform independent. It allows a direct connection to the database. It implements the TCP/IP protocol that emulates Oracle's Net8 and TTC (the wire protocol of OCI) on top of Java sockets. Java applets are good candidates that make use of this driver. This driver gives the maximum portability. Figure 3.2 shows a client-side JDBC thin driver.

Figure 3.2 A configuration of an Oracle client-side JDBC thin driver.

Client-side Oracle JDBC OCI Driver

This is a native-API Type 2 driver that is suited for client-server Java applications. It is Oracle platform-specific and requires an Oracle client installation. This driver converts JDBC calls into calls to the Oracle Call Interface using native methods. These calls are then sent to the Oracle database server using Net8. These drivers support protocols such as IPC, named pipes, TCP/IP, and IPX/SPX. This driver gives the maximum performance for an Oracle client application.

Server-side Oracle JDBC Thin Driver

This driver has the same functionality as the client-side thin driver except that it runs inside Oracle 8i and accesses a remote database. This driver can also be used to access data in the same database as the database connection. An example of using this driver is when accessing an Oracle server from inside of a Java stored procedure.

Server-side Oracle JDBC Internal Driver

This driver supports any Java code that runs inside a target Oracle database such as a Java stored procedure and must access the same database.

JDBC 2.0 Support

Oracle 8i Release 2 provides JDBC drivers that are fully compliant with Java 2 and JDBC 2.0. Applications can use these features in exactly the same way as with earlier versions of JDBC. All four Oracle JDBC drivers support these changes. We will discuss JDBC2.0 support under the following headings:

• JDK Support

• JDBC 2.0 Features

JDK Support

Oracle 8i Release 2 supports JDK 1.2 and JDK 1.1. As regards the former, the JDK 1.2 versions of the JDBC drivers are fully compliant with JDBC 2.0. The implementation is by means of the interfaces in the standard java.sql package included in the file classes12.zip. On the Oracle side, these interfaces are implemented as appropriate by classes in the oracle.sql and oracle.jdbc.driver packages. The files java.sql.* and oracle.sql.* need to be imported for _JDK 1.2.

As regards JDK 1.1.x, the file classes111.zip is needed. The implementation of JDBC2.0 functionality is by means of the package oracle.jdbc2 still included in classes111.zip. Features such as objects, object references, arrays, and LOBS can be used by importing this package. The files java.sql.*, oracle.jdbc2.*, and oracle.sql.* need to be imported for JDK 1.1.x.

The package java.sql.* comes with JDK; the packages oracle.sql.*, oracle.jdbc.driver, and oracle.jdbc2 come with Oracle's drivers.

JDBC 2.0 Features

The following are the JDBC 2.0 features supported by Oracle 8i Release 2:

• Resultset enhancements

  JDBC 2.0 supports scrollable resultset capability under three major headings, namely, forward-only, scroll-insensitive, and scroll-sensitive. Each of these resultsets can in turn be Read_only or Updatable. Forward_only/Read_only is feature of JDBC 1.0. The remaining five kinds are additions in JDBC 2.0. All these kinds are supported in JDK 1.2 and JDK 1.1 through an Oracle extension.

• Batch Updates

  Oracle 8i Release 2 supports two types of batch updates, namely, automatic batch update of Oracle style and explicit batch update of JDBC 2.0 style.

• Advanced Data Types

  The advanced data types such as objects, object references, arrays, LOBS, SQL Data, and Struct are now supported by JDBC 2.0. With JDK1.2 compatible drivers, these are available as part of the java.sql package. While porting code from JDK1.1 to JDK 1.2 drivers, oracle.jdbc2 should be replaced with java.sql in the source and then recompiled.

• JNDI

  The Java Naming and Directory Interface (JNDI) is an interface to obtain network resources in a vendor independent fashion. This is available as part of the JDBC2.0 Optional Package (JDBC 2.0 Standard Extension API) and implemented by means of javax.sql package. As of Oracle 8i Release 2, this is available for both JDK1.2 and JDK1.1.x.

• Connection Pooling

  Connection pooling is a method where multiple consumers share a limited set of connections instead of each having to create new connections. This also includes connection caching. This is implemented by means of javax.sql package. This is available for both JDK1.1.x and JDK1.2 drivers but only in the OCI and thin drivers. The server-side Oracle JDBC driver does not have connection pooling as the server driver can have only one connection which is to the logged-in session.

• Distributed Transactions

  A distributed transaction is a combination of two or more related transactions that execute in a coordinated manner. All the individual transactions might take place in the same database, but typically, the individual transactions are in different databases and often in different locations. For example, a distributed transaction might involve a change in a transaction record in one table and an automatic creation of an audit record in another table in a different database located in a second location. Distributed transactions are defined as a JDBC2.0 standard extensions API and are also implemented by means of javax.sql package. On the Oracle side, support for distributed transactions is provided by means of two packages: oracle.jdbc.xa.client for client-side OCI and thin drivers and the server-side thin driver; and oracle.jdbc.xa.server package for server-side internal driver.

• Other Features

  Other features include fetch size/row prefetching. Fetch size is part of JDBC2.0 and includes methods to define the number of rows retrieved by each database fetch. Oracle row-prefetching can also be used as an equivalent of JDBC2.0 fetch size. These features can be used in Oracle 8i Release 2 and JDK 1.2 and JDK 1.1.x as an Oracle extension.

Case Study

We will use the schema oratest/oratest@oracle (where oracle is the tnsnames alias in tnsnames.ora). This is for tools such as SQL*Plus. As part of the case study we will use the same schema objects as defined in case study of Chapter 2. These are reproduced here for convenience:

Database objects (tables and object types):

CREATE TABLE dept (deptno number(4) primary key,
     dname varchar2(20) not null,
     loc varchar2(15) not null);

CREATE TABLE emp (empno number(10) primary key,
     ename varchar2(40) not null,
     job varchar2(15) not null,
     mgr number(10) references emp(empno),
     hiredate date not null,
     sal number(12,2) not null,
     comm number(4),
     deptno number(4) references dept(deptno));

CREATE TABLE dept_audit (deptno number(4) primary key references 
dept(deptno),
       cnt_emp number(10) not null);

CREATE OR REPLACE TYPE address AS OBJECT
        (add_id number(10),
        line1 varchar2(20),
        line2 varchar2(20),
        city varchar2(15),
        state varchar2(2),
        zip varchar2(11));
/

(This type is later changed in Chapter 4, "Advanced JDBC Programming," to include member methods.)

CREATE TABLE addresses OF address;

In addition, the following schema objects are also used and should be defined in the above schema:

• Database tables and objects

  CREATE TABLE emp_with_type   
       (empno number(10) primary key,
       ename varchar2(40) not null,
       etype varchar2(10) not null,
       job varchar2(15) not null,
       mgr number(10) references emp(empno),
       hiredate date not null,
       sal number(12,2) not null,
       comm number(4),
       deptno number(4) references dept(deptno));

  CREATE TABLE emp_with_addr (empno number(10) primary key,
       ename varchar2(40) not null,
       job varchar2(15) not null,
       mgr number(10) references emp(empno),
       hiredate date not null,
       sal number(12,2) not null,
       comm number(4),
       deptno number(4) references dept(deptno),
       emp_addr REF address);

  This is used for describing Object REFS in Chapter 4.

  CREATE TYPE address_id_varray IS VARRAY(20) OF NUMBER;

  CREATE TABLE address_id_list(address_id_list address_id_varray);

  The above created type and table are used in describing collections in Chapter 4.

  CREATE TABLE lob_tab
    (id NUMBER PRIMARY KEY,
    blob_data BLOB,
    clob_data CLOB,
    bfile_data BFILE);

  This table is used in describing large objects in Chapter 4.

  CREATE TABLE tab1 (col1 NUMBER);

• PLSQL packages and procedures:

  CREATE OR REPLACE PACKAGE pkg_refcur IS
     TYPE bonus_refcur IS REF CURSOR;
     FUNCTION f_refcur(ip_etype VARCHAR2) RETURN pkg_refcur.bonus_refcur;
    END pkg_refcur;
    /

This package is used in describing Oracle REF CURSOR in the section "Features of Oracle Extensions."

A procedure p_highest_paid_emp is used in the section "Calling PL/SQL Stored Procedures," later in this chapter.

The above schema objects are used to describe the ins and outs of JDBC programs.