• ORACLE_HOME/jdbc

• ORACLE_HOME /jlib

Check whether or not the following directories and files have been created and populated in the ORACLE_HOME/jdbc directory:

• doc

  This directory contains the javadoc.zip file, which is the Oracle JDBC application programming interface (API) documentation.

• lib

  The lib directory contains the following required Java classes:

  • ojdbc10.jar and ojdbc10_g.jar

    Contain the JDBC driver classes for use with JDK 8, JDK 9, and JDK 11

  • ojdbc8.jar and ojdbc8_g.jar

    Contain the JDBC driver classes for use with JDK 8

• orai18n.jar and orai18n-mapping.jar

  Contain classes for globalization and multibyte character sets support

• Readme.txt

  This file contains late-breaking and release-specific information about the drivers, which may not have been included in other documentation on the product.

Check whether or not the following directories have been created and populated in the ORACLE_HOME /jlib directory:

• jta.jar and jndi.jar

  These files contain classes for the Java Transaction API (JTA) and the Java Naming and Directory Interface (JNDI). These are required only if you are using JTA features for distributed transaction management or JNDI features for naming services.

• ons.jar

  This JAR file contains classes for Oracle RAC Fast Application Notification. It is also required for Universal Connection Pool (UCP) features like Fast Connection Failover, Run-time Load Balancing, Web Session Affinity, and Transaction Affinity.

You must set the CLASSPATH environment variable for JDBC OCI or Thin driver. Include the following in the CLASSPATH environment variable:

ORACLE_HOME/jdbc/lib/ojdbc10.jar
ORACLE_HOME/jdbc/lib/ojdbc8.jar
ORACLE_HOME/jlib/orai18n.jar

JDBC OCI Driver

To use the JDBC OCI driver, you must also set the following value for the library path environment variable:

• On Solaris or Linux, set the LD_LIBRARY_PATH environment variable as follows:

  ORACLE_HOME/lib
  

  This directory contains the libocijdbc19.so shared object library.

• On Microsoft Windows, set the PATH environment variable as follows:

  ORACLE_HOME\bin
  

  This directory contains the ocijdbc19.dll dynamic link library.

All of the JDBC OCI demonstration programs can be run in the Instant Client mode by including the JDBC OCI Instant Client data shared library on the library path environment variable.

JDBC Thin Driver

To use the JDBC Thin driver, you do not have to set any other environment variables. However, to use the JDBC server-side Thin driver, you need to set permission.

Setting Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. As Oracle Database enforces the Java security model, a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user HR:

CREATE ROLE jdbcthin;
CALL dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect');
GRANT jdbcthin TO HR;

Note that JDBCTHIN in the grant_permission call must be in uppercase. The asterisk (*) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

To further ensure that Java is set up properly on your client system, go to the samples directory under the ORACLE_HOME/jdbc/demo directory. Now, type the following commands on the command line, one after the other, to see if the Java compiler and the Java interpreter run without error:

javac

java

Each of the preceding commands should display a list of options and parameters and then exit. Ideally, verify that you can compile and run a simple test program, such as jdbc/demo/samples/generic/SelectExample.

To determine the version of the JDBC driver, call the getDriverVersion method of the OracleDatabaseMetaData class as shown in the following sample code:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class JDBCVersion
{
  public static void main (String args[]) throws SQLException
  {
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:HR/hr@<host>:<port>:<service>");
    Connection conn = ods.getConnection();

    // Create Oracle DatabaseMetaData object
    DatabaseMetaData meta = conn.getMetaData();

    // gets driver info:
    System.out.println("JDBC driver version is " + meta.getDriverVersion());
  }
}

You can also determine the version of the JDBC driver by executing the following commands:

• java -jar ojdbc8.jar

• java -jar ojdbc10.jar

The samples directory contains sample programs for a particular Oracle JDBC driver. One of the programs, JdbcCheckup.java, is designed to test JDBC and the database connection. The program queries for the user name, password, and the name of the database to which you want to connect. The program connects to the database, queries for the string "Hello World", and prints it to the screen.

Go to the samples directory, and compile and run the JdbcCheckup.java program. If the results of the query print without error, then your Java and JDBC installations are correct.

Although JdbcCheckup.java is a simple program, it demonstrates several important functions by performing the following:

• Imports the necessary Java classes, including JDBC classes

• Creates a DataSource instance

• Connects to the database

• Runs a simple query

• Prints the query results to your screen

The JdbcCheckup.java program, which uses the JDBC OCI driver, is as follows:

/*
 * This sample can be used to check the JDBC installation.
 * Just run it and provide the connect information. It will select
 * "Hello World" from the database.
 */

// You need to import the java.sql and JDBC packages to use JDBC
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup
{
  public static void main(String args[]) throws SQLException, IOException
  {

    // Prompt the user for connect information
    System.out.println("Please enter information to test connection to 
                          the database");
    String user;
    String password;
    String database;

    user = readEntry("user: ");
    int slash_index = user.indexOf('/');
    if (slash_index != -1)
    {
      password = user.substring(slash_index + 1);
      user = user.substring(0, slash_index);
    }
    else
      password = readEntry("password: ");
    database = readEntry("database(a TNSNAME entry): ");

    System.out.print("Connecting to the database...");
    System.out.flush();
    System.out.println("Connecting...");
    // Open an OracleDataSource and get a connection
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:oci:@" + database);
    ods.setUser(user);
    ods.setPassword(password);
    Connection conn = ods.getConnection();
    System.out.println("connected.");

    // Create a statement
    Statement stmt = conn.createStatement();

    // Do the SQL "Hello World" thing
    ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");

    while (rset.next())
      System.out.println(rset.getString(1));
    // close the result set, the statement and the connection
    rset.close();
    stmt.close();
    conn.close();
    System.out.println("Your JDBC installation is correct.");
  }

  // Utility function to read a line from standard input
  static String readEntry(String prompt)
  {
    try
    {
      StringBuffer buffer = new StringBuffer();
      System.out.print(prompt);
      System.out.flush();
      int c = System.in.read();
      while (c != '\n' && c != -1)
      {
        buffer.append((char)c);
        c = System.in.read();
      }
      return buffer.toString().trim();
    }
    catch(IOException e)
    {
      return "";
    }
  }
}

Regardless of which Oracle JDBC driver you use, include the import statements shown in Table 2-1 at the beginning of your program using the following syntax:

import <package_name>;

The Oracle packages listed as optional provide access to the extended functionality provided by Oracle JDBC drivers, but are not required for the example presented in this section.

First, you must create an OracleDataSource instance. Then, open a connection to the database using the OracleDataSource.getConnection method. The properties of the retrieved connection are derived from the OracleDataSource instance. If you set the URL connection property, then all other properties, including TNSEntryName, DatabaseName, ServiceName, ServerName, PortNumber, Network Protocol, and driver type are ignored.

Specifying a Database URL, User Name, and Password

The following code sets the URL, user name, and password for a data source:

OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setUser(user);
ods.setPassword(password);

The following example connects user HR with password hr to a database with service orcl through port 5221 of the host myhost, using the JDBC Thin driver:

OracleDataSource ods = new OracleDataSource();
String url = "jdbc:oracle:thin:@//myhost:5221/orcl";
ods.setURL(url);
ods.setUser("HR");
ods.setPassword("hr");
Connection conn = ods.getConnection();

Specifying a Database URL that Includes User Name and Password

The following example connects user HR with password hr to a database host whose Transparent Network Substrate (TNS) entry is myTNSEntry, using the JDBC Oracle Call Interface (OCI) driver. In this case, the URL includes the user name and password and is the only input parameter.

String url = "jdbc:oracle:oci:HR/hr@myTNSEntry");
ods.setURL(url);
Connection conn = ods.getConnection();

If you want to connect using the Thin driver, then you must specify the port number. For example, if you want to connect to the database on the host myhost that has a TCP/IP listener on port 5221 and the service identifier is orcl, then provide the following code:

String URL = "jdbc:oracle:thin:HR/hr@//myhost:5221/orcl");
ods.setURL(URL);
Connection conn = ods.getConnection();

Once you connect to the database and, in the process, create a Connection object, the next step is to create a Statement object. The createStatement method of the JDBC Connection object returns an object of the JDBC Statement type. To continue the example from the previous section, where the Connection object conn was created, here is an example of how to create the Statement object:

Statement stmt = conn.createStatement();

To query the database, use the executeQuery method of the Statement object. This method takes a SQL statement as input and returns a JDBC ResultSet object.

To continue the example, once you create the Statement object stmt, the next step is to run a query that returns a ResultSet object with the contents of the first_name column of a table of employees named EMPLOYEES:

ResultSet rset = stmt.executeQuery ("SELECT first_name FROM employees");

Once you run your query, use the next() method of the ResultSet object to iterate through the results. This method steps through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the appropriate getXXX methods of the ResultSet object, where XXX corresponds to a Java data type.

For example, the following code will iterate through the ResultSet object, rset, from the previous section and will retrieve and print each employee name:

while (rset.next())
   System.out.println (rset.getString(1));

The next() method returns false when it reaches the end of the result set. The employee names are materialized as Java String values.

You must explicitly close the ResultSet and Statement objects after you finish using them. This applies to all ResultSet and Statement objects you create when using Oracle JDBC drivers. The drivers do not have finalizer methods. The cleanup routines are performed by the close method of the ResultSet and Statement classes. If you do not explicitly close the ResultSet and Statement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing both the result set and the statement releases the corresponding cursor in the database. If you close only the result set, then the cursor is not released.

For example, if your ResultSet object is rset and your Statement object is stmt, then close the result set and statement with the following lines of code:

rset.close();
stmt.close();

When you close a Statement object that a given Connection object creates, the connection itself remains open.

DML Operations

To perform DML (Data Manipulation Language) operations, such as INSERT or UPDATE operations, you can create either a Statement object or a PreparedStatement object. PreparedStatement objects enable you to run a statement with varying sets of input parameters. The prepareStatement method of the JDBC Connection object lets you define a statement that takes variable bind parameters and returns a JDBC PreparedStatement object with your statement definition.

Use the setXXX methods on the PreparedStatement object to bind data to the prepared statement to be sent to the database.

The following example shows how to use a prepared statement to run INSERT operations that add two rows to the EMPLOYEES table.

    // Prepare to insert new names in the EMPLOYEES table
PreparedStatement pstmt = null;
try{
    pstmt = conn.prepareStatement ("insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME) values (?, ?)");

    // Add LESLIE as employee number 1500
    pstmt.setInt (1, 1500);          // The first ? is for EMPLOYEE_ID
    pstmt.setString (2, "LESLIE");   // The second ? is for FIRST_NAME
    // Do the insertion
    pstmt.execute();

    // Add MARSHA as employee number 507
    pstmt.setInt (1, 507);           // The first ? is for EMPLOYEE_ID
    pstmt.setString (2, "MARSHA");   // The second ? is for FIRST_NAME
    // Do the insertion
    pstmt.execute();
}

finally{
		if(pstmt!=null)

    // Close the statement
    pstmt.close();
}

DDL Operations

To perform data definition language (DDL) operations, you must create a Statement object. The following example shows how to create a table in the database:

//create table EMPLOYEES with columns EMPLOYEE_ID and FIRST_NAME
String query;
Statement stmt=null;

try{
    query="create table EMPLOYEES " +
          "(EMPLOYEE_ID int, " +
          "FIRST_NAME varchar(50))";
    stmt = conn.createStatement();
    stmt.executeUpdate(query);
    }
finally{
     //close the Statement object
     stmt.close();
    }

The following example shows how to prepare your DDL statements before any reexecution:

//
Statement stmt = null;
PreparedStatement pstmt = null;
try{
    pstmt = conn.prepareStatement ("insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME) values (?, ?)");
    stmt = conn.createStatement("truncate table EMPLOYEES"); 
 
    // Add LESLIE as employee number 1500
    pstmt.setInt (1, 1500);          // The first ? is for EMPLOYEE_ID
    pstmt.setString (2, "LESLIE");   // The second ? is for FIRST_NAME
    pstmt.execute();
    stmt.executeUpdate();
 
    // Add MARSHA as employee number 507
    pstmt.setInt (1, 507);           // The first ? is for EMPLOYEE_ID
    pstmt.setString (2, "MARSHA");   // The second ? is for FIRST_NAME
    pstmt.execute();
    stmt.executeUpdate();
    }
finally{
if(pstmt!=null)
 
    // Close the statement
     pstmt.close();
}

You must close the connection to the database after you have performed all the required operations and no longer require the connection. You can close the connection by using the close method of the Connection object, as follows:

conn.close();

JDBC supports the invocation of PL/SQL procedures/functions and anonymous blocks, using either JDBC escape syntax or PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

// JDBC escape syntax
CallableStatement cs1 = conn.prepareCall
                       ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                       ( "{? = call func (?,?)}" ) ; // stored func
// PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                       ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                       ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using the Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:

create or replace function foo (val1 char)
return char as
begin
   return val1 || 'suffix';
end;

The function invocation in your JDBC program should look like the following:

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@<hoststring>");
ods.setUser("HR");
ods.setPassword("hr");
Connection conn = ods.getConnection();

CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.execute();
String result = cs.getString(1);

You can use JDBC to call Java stored procedures through the SQL interface. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly published. That is, you have written call specifications to publish them to the Oracle data dictionary. Applications can call Java stored procedures using the Native Java Interface for direct invocation of static Java methods.