Connect to an Oracle Database using Java and JDBC

This document will detail establishing a connection to an Oracle Database using Java and JDBC. Errors that could arise in making JDBC connections will be examined at the end of the document.

Below is complete program to connect to an Oracle Database using Java and JDBC.

import oracle.jdbc.OracleConnection;  
import oracle.jdbc.pool.OracleDataSource;  
import java.sql.SQLException;   

public class SimpleConnection {

  public static void main(String[] args) {
    
    try {
      // Create the data source
      OracleDataSource ods = new OracleDataSource();
      
      // set connection properties
      ods.setDriverType("thin");
      ods.setNetworkProtocol("tcp");
      ods.setDatabaseName("scratch");
      ods.setServerName("wrath");
      ods.setPortNumber(1521);
      ods.setUser("ejenkinson");
      ods.setPassword("ejenkinson");
      
      // open the connection to the database
      OracleConnection ocon = (OracleConnection)ods.getConnection();
      
      // close the connection the database and the close the datasource
      ocon.close();
      ods.close();
    } catch(SQLException e) {
      System.out.println(e.getMessage());
    }
                                                                                                          
  }

It is not a very interesting program in that no output is displayed unless there is an error in connecting to the database. While uninteresting the program does demonstrate the process in which to connect to an Oracle Database using JDBC. Create the OracleDataSource object, set the OracleDataSource connection properties and create the OracleConnection object to the database and then close the OracleConnection and OracleDatabase objects.

The vast majority of the code shown above is setting connection properties for the OracleDataSource via setter methods. While this is a valid way to set the connection properties it is more common to use a JDBC URL to set connection properties.

JDBC URL Format

The JDBC URL provides a compact way in which to provide the connection properties the DataSource object. The basic format for Oracle JDBC URL jdbc:oracle:< driver_type >:@database or if you would like to include the user name and password you can use the following format of jdbc:oracle:< driver_type >:< username/password >@database.

– thin or OCI. For the thin driver all that is needed in order to connect to an Oracle Database is the Oracle JDBC Jar files. A Oracle Client or Instant Client install is needed for the OCI driver.

< username/password > – The database user name and password. The / must be present between the username and password.

database – can be specified in one of three ways: When using the thin driver the string host:port:dbsid can be used. Where host is the server name hosting the database, port is the port number of the listener and dbsid is the database service ID. When using the OCI driver a net service name can be used. A Connect Descriptor can be used for either the OCI or thin driver.

Below are some examples of an Oracle JDBC URL. The host name is wrath, the listener port is 1521 and the database service ID is scratch.

jdbc:oracle:thin:@wrath:1521:scratch

jdbc:oracle:thin:ejenkinson/ejenkinson@wrath:1521:scratch

jdbc:oracle:thin:ejenkinson/ejenkinson@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wrath.oracledistilled.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=scratch)))

jdbc:oracle:oci:ejenkinson/ejenkinson@scratch

jdbc:oracle:thin:ejenkinson/ejenkinson@wrath:1521/scratch

The last example given details the use of the EZ-Connect style of the Connect Descriptor.

Below is another example of connection to an Oracle Database using a JDBC URL to set the connection properties of the OracleDataSource object.

import oracle.jdbc.OracleConnection;  
import oracle.jdbc.pool.OracleDataSource;  
import java.sql.SQLException;   

public class SimpleConnectionURL {

  public static void main(String[] args) {
    
    try {
      // Create the data source
      OracleDataSource ods = new OracleDataSource();
      
      // set connection properties
      String dbURL = "jdbc:oracle:thin:ejenkinson/ejenkinson@wrath:1521/scratch";
      ods.setURL(/java/jdbc/connect-to-an-oracle-database-using-java-and-jdbc/dbURL);
      
      // open the connection to the database
      OracleConnection ocon = (OracleConnection)ods.getConnection();
      
      // close the connection the database and the close the datasource
      ocon.close();
      ods.close();
    } catch(SQLException e) {
      System.out.println(e.getMessage());
    }
                                                                                                          
  }
}

Below are some possible errors that you might receive when attempting to connect to an Oracle Database using Oracle JDBC and Java along with some possible solutions.

Invalid Oracle URL specified

1. Verify that the JDBC URL specified meets the specifications described in this document.

The Network Adapter could not establish the connection

1. Verify that the database Listener is running.
2. Verify that port number provided in the JDBC URL is correct

Unknown host specified

1. Verify that the host name provided in the JDBC URL is correct

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

1. Verify that the database name provided in the JDBC URL is correct.