Oracle JDBC Programming

In this article, we'll show how to create a JDBC connection to Oracle database and retrieve data from it using Eclipse.

Prerequisites

You can get Oracle Database 11g Release 2 JDBC Drivers here.

• Contents of this release includes:
  • ojdbc5.jar
    • Classes for use with JDK 1.5. It contains the JDBC driver classes, except classes for NLS support in Oracle Object and Collection types.
  • ojdbc5_g.jar
    • Same as ojdbc5.jar, except that classes were compiled with "javac -g" and contain tracing code.
  • ojdbc5dms.jar
    • Same as ojdbc5.jar, except that it contains instrumentation to support DMS (i.e., Oracle Dynamic Monitoring Service) and limited java.util.logging calls.
  • ojdbc5dms_g.jar
    • Same as ojdbc5_g.jar, except that it contains instrumentation to support DMS.
  • ojdbc6.jar
    • Classes for use with JDK 1.6. It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.
  • ojdbc6_g.jar
    • Same as ojdbc6.jar except compiled with "javac -g" and contains tracing code.
  • ojdbc6dms.jar
    
    • Same as ojdbc6.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls.
  • ojdbc6dms_g.jar
    
    • Same as ojdbc6_g.jar except that it contains instrumentation to support DMS.

In this project, we have downloaded ojdbc6_g.jar and included it in Eclipse's Java Build Path.

Sample Code

If you are using ojdbc6.jar and JSE 6 or later, you don't have to register the driver at all no matter which driver you are using. As of JSE 6, the standard Java Service Provider Interface registers the drivers automatically. Just call DriverManager.getConnection and the runtime will find the driver and register it for you.

There are four types of drivers supported by Oracle. One of them is thin driver which is a 100% Java driver for client-side use without an Oracle installation, particularly with applets. The Thin driver type is thin.

When the method getConnection is called, the DriverManager will attempt to locate a suitable driver from amongst those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application. In ojdbc6.jar, there is a driver named oracle.jdbc.driver.OracleDriver that implements the java.sql.Driverinterface.

        Connection con = DriverManager.getConnection
        ( "jdbc:oracle:thin:@xxx.us.oracle.com:1521/XE", "username","password");

        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(
          "SELECT USER_EMAIL_ADDRESS, POST_BODY, FORUM_POST_DATE FROM xtm_tc_forum_posts");
        while (rs.next()) {
            String fromUser = rs.getString("USER_EMAIL_ADDRESS");
            if (fromUser == null  fromUser.length() == 0)
                continue;
            byte[] b = rs.getBytes("POST_BODY");
            try {
                date = sdf.parse(rs.getString("FORUM_POST_DATE"));
            } catch (ParseException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            cal.setTime(date);
            builder.ProfileContent(b, "text/plain", cal, fromUser);
        }

You can open a connection to the database with the static getConnection() method of the java.sql.DriverManager class. The type of the object returned is java.sql.Connection. The form of getConnection() method we use in the example requires us to specify a database URL , user name and password. If you want to connect with the Thin driver, you must specify the port number and SID. For example, to connect user scott with password tiger to a database with SID (system identifier) XE through port 1521 of host xxx.us.oracle.com, using the Thin driver, you would write :

Connection con = DriverManager.getConnection
        ( "jdbc:oracle:thin:@xxx.us.oracle.com:1521/XE", "scott","tiger");

Note that although the method is not creating a new physical connection (only a single implicit connection is used), it is returning a new object.

Connection object represents a connection (session) with a specific database. You can use it to query the information of the database by using getMetaData method. For example, you can query its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. You can also use createStatement method to create a Statement object for sending SQL statements to the database:

Statement stmt = con.createStatement();

Statement object provides an executeQuery method that executes the given SQL statement, which returns a single ResultSet object:

ResultSet rs = stmt.executeQuery("SELECT USER_EMAIL_ADDRESS, POST_BODY, FORUM_POST_DATE FROM xtm_tc_forum_posts");

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set:

     while (rs.next()) {
            String fromUser = rs.getString("USER_EMAIL_ADDRESS");
            if (fromUser == null  fromUser.length() == 0)
                continue;
            byte[] b = rs.getBytes("POST_BODY");
            try {
                date = sdf.parse(rs.getString("FORUM_POST_DATE"));
            } catch (ParseException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            cal.setTime(date);
            builder.ProfileContent(b, "text/plain", cal, fromUser);
        }

DriverManager vs. DataSource

The DataSource interface, new in the JDBC 2.0 API, provides another way to connect to a data source. The use of a DataSource object is the preferred means of connecting to a data source.

The following describes the steps of connecting to a data source (i.e., OracleDataSource):

1. Import the necessary JDBC classes in your programs that use JDBC.
   For example:

   import java.sql.*;
   import java.math.*; // if needed

   To use OracleDataSource, you need to do:
   

   import oracle.jdbc.pool.OracleDataSource;

2. Create an OracleDataSource instance.

   OracleDataSource ods = new OracleDataSource();

3. Set the desired properties if you don't want to use the default properties. Different connection URLs should be used for different JDBC drivers.
   
   

   ods.setUser("my_user");
   ods.setPassword("my_password");

   
   
   • For the JDBC OCI Driver:
     To make a bequeath connection, set URL as:
     

     ods.setURL("jdbc:oracle:oci:@");

     To make a remote connection, set URL as:
     

     ods.setURL("jdbc:oracle:oci:@<database>");

     where <database> is either a TNSEntryName or a SQL*net name-value pair defined in tnsnames.ora.
   • For the JDBC Thin Driver, or Server-side Thin Driver:

     ods.setURL("jdbc:oracle:thin:@<database>");

     
     where <database> is either a string of the form //<host>:<port>/<service_name>, or a SQL*net name-value pair, or a TNSEntryName.
   • For the JDBC Server-side Internal Driver:

     ods.setURL("jdbc:oracle:kprb:");

     
     Note that the trailing ':' is necessary. When you use the Server-side Internal Driver, you always connect to the database you are executing in. You can also do this:
     
     

     Connection conn = new oracle.jdbc.OracleDriver().defaultConnection();

   
   
4. Open a connection to the database with getConnection() methods defined in OracleDataSource class.
   
   

   Connection conn = ods.getConnection();