Home > Articles > Programming > Java

Using Java Database Connectivity (JDBC) with Oracle

Use JDBC to access a relational database from a Java application, no matter where the application is running or where the database is. Bulusu Lakshman describes Oracle database access in Java using JDBC. Learn the the details, from the querying and returning of resultsets to executing DML from the Oracle 8i database. Oracle JDBC extensions are also discussed, and a case study is presented to illustrate the concepts.

The following is an excerpt from "Oracle and Java Development" ©2002.

Like this article? We recommend
  • 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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020