Connect using the Oracle JDBC driver

Following code snippet shows how to connect to the Oracle Database using oracle.jdbc.pool.OracleDataSource


    String url = "jdbc:oracle:thin:@tcp://my-host:1522/my-service";
    OracleDataSource ods = new OracleDataSource();
    ods.setUser(userName);
    ods.setPassword(password);
    ods.setURL(url);
    Connection con = ods.getConnection();

URL Formats

The Oracle JDBC Driver supports the following URL formats.

EZConnect Format

In EZConnect format Hostname, Port and Service Name can be configured using a simple syntax.
Since 19c, the connection properties can be added at the end of the URL.
The syntax uses '?' to indicate start of conection properties and '&' as a delimiter between each property.

Syntax :

jdbc:oracle:thin:@[[protocol:]//]host1[,host2,host3][:port1][,host4:port2] [/service_name][:server_mode][/instance_name][?connection properties]

Examples:

  • jdbc:oracle:thin:@mydbhost:1521/mydbservice
  • jdbc:oracle:thin:@tcp://mydbhost:1521/mydbservice
  • jdbc:oracle:thin:@tcp://mydbhost1,mydbhost2:1521/mydbservice
  • jdbc:oracle:thin:@tcp://mydbhost1:5521,mydbhost2:1521/mydbservice
  • jdbc:oracle:thin:@tcp://mydbhost1:5521/mydbservice:dedicated
  • jdbc:oracle:thin:@mydbhost1:5521/mydbservice?oracle.net.httpsProxyHost=myproxy&oracle.net.httpsProxyPort=80
  • jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet
  • jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet&ssl_server_cert_dn="Server DN"

TNS URL Format

This format is more structured way of specifying the connect options and more descriptive.

Syntax :

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=<protocol>) (HOST=<dbhost>)(PORT=<dbport>)) (CONNECT_DATA=(SERVICE_NAME=<service-name>))

Examples:

  • jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=mydbhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=mydbservice))
  • jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=5221))) (CONNECT_DATA=(SERVICE_NAME=orcl)))
  • jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS)(PORT=1522)(HOST=myhost)) (CONNECT_DATA=(SERVICE_NAME=dbservicename)) (SECURITY=(ssl_server_cert_dn="CN=testcert.test.com, O=org,L=somecity,ST=state,C=US")))

TNS Alias Format

To use the alias configured in the tnsnames.ora, use the below format. The location of the tnsnames.ora file can be configured using TNS_ADMIN parameter.

Syntax :

jdbc:oracle:thin:@<alias_name>

Examples:

  • jdbc:oracle:thin:@prod_db?TNS_ADMIN=/work/tns/
  • jdbc:oracle:thin:@inst1?TNS_ADMIN=/work/tns/
Packages 
Package Description
oracle.jdbc
Beginning in Oracle9i, the Oracle extensions to JDBC are captured in the package oracle.jdbc.
oracle.jdbc.aq
This package is an Oracle JDBC extension that provides interfaces to access the Advanced Queuing feature of Oracle for enterprise messaging applications.
oracle.jdbc.babelfish
Provides the classes necessary for the support for application migration feature.
oracle.jdbc.datasource
Beginning in Oracle Release 12.2, some of the Oracle extensions to JDBC are captured in the package oracle.jdbc.datasource.
oracle.jdbc.dcn
This package is an Oracle JDBC extension that provides interfaces to access the Database Change Notification feature of Oracle.
oracle.jdbc.pool
A package of connection cache and pooling related classes.
oracle.jdbc.replay
Provides interfaces and factory methods for Application Continuity.
oracle.jdbc.xa
Provides classes that implement the JDBC XA APIs.
oracle.jdbc.xa.client
Provides classes that implement the JDBC XA APIs.
oracle.sql
A package of classes that represent java SQL types and Oracle specific SQL types.
oracle.sql.json
The API for JSON type in Oracle Database.