JDBC Oracle Connect String

What are Oracle JDBC drivers ? [Using Service Name instead of SID]

JDBC is an API that allows Java programs to access any tabular data source.
In order to interact with an Oracle Database from a Java program using JDBC interface,
Oracle provides its own Oracle JDBC drivers.
Oracle provides drivers that enable users to make JDBC connections to Oracle databases.
The two most common methods of connecting to Oracle databases via JDBC are the Oracle Thin JDBC driver and the Oracle OCI JDBC driver.
The Oracle Thin driver requires no software other than the driver jar file. This driver connects to Oracle databases via TCP/IP.
The Oracle OCI (Oracle Call Interface) driver requires Oracle client software to be installed on the user's machine in order to connect to the database.
This driver uses native methods and is platform specific.
The Java classes to connect to Oracle are contained in the Oracle JDBC driver jar file.
For recent releases, these are numbered based on the Java version they are compiled for,
such as ojdbc14.jar (for Java 1.4), ojdbc15.jar (for Java 1.5), etc.
These drivers can be freely downloaded from Oracle's site (free registration is required).
You can tell the Oracle driver which method you wish to use to connect to the database (OCI or Thin) via the JDBC connection URL.

Oracle supplies 4 JDBC driver types

JDBC Thin client-side driver (Thin driver) JDBC OCI client-side driver (OCI driver) JDBC server-side Internal driver (KPRB driver) JDBC Thin server-side driver

JDBC Thin client-side driver (Thin driver)

The Oracle JDBC Thin driver is a JDBC Type 4 driver(written entirely in Java).
The JDBC Thin driver uses Java Sockets to connect directly to the Oracle Server.
The JDBC Thin driver provides its own implementation of a TCP/IP version of Oracle's SQL*Net/Net8.
As it is written entirely in Java, this driver is platform-independent.
The Thin driver is for client-side use and does not require Oracle software on the client side.

JDBC OCI client-side driver (OCI driver)

Oracle's JDBC OCI driver provides a JDBC Type 2 driver (native-API, partly Java) implementation
of the JDBC interfaces using OCI (Oracle Call Interface) to interact with an Oracle database.
As the JDBC OCI driver uses native methods to call C entry points in the OCI library, it is platform-specific.
It also requires an Oracle client (or Instant Client) installation including Net8.
Because the JDBC OCI driver interfaces to Oracle databases through OCI,
it is compatible with all Oracle database versions, and supports all installed Net8 adapters,
including IPC, named pipes, TCP/IP, and SPX/IPX among others.

JDBC server-side Internal driver (KPRB driver)

This JDBC Type 2 driver can be used by Java code that runs inside the oracle database server JVM,
such as Java stored procedures. The JDBC server driver allows Java programs to communicate with the internal
SQL engine inside Oracle and access the underlying database objects.

JDBC Thin server-side driver

This JDBC Type 4 driver allows Java program running inside an Oracle database server JVM to access remote databases.
The Oracle JDBC driver is shipped with the Oracle database server.
Starting with 9.2.0.1 database version, the Oracle JDBC driver versions are :

JDBC 9.2.0.X JDBC 10.1.0.X JDBC 10.2.0.X JDBC 11.1.0.X JDBC 11.2.0.X

JDBC connection to an Oracle database

Initially, after registering the Oracle JDBC driver, a JDBC connection can be opened by invoking method getConnection() of the class java.sql.DriverManager:

...
 DriverManager.registerDriver(new oracle.jdbc.OracleDriver() ); 
 Connection conn = DriverManager.getConnection( dbUrl, dbUser, dbPassword);
 ...

Using the getConnection method of OracleDataSource class allows you more flexibility:

...
 ds = new oracle.jdbc.pool.OracleDataSource();
 ds.setURL(dbUrl);
 Connection conn = ds.getConnection(dbUser,dbPassword);
 ...

This will allow you in to use the Implicit connection caching mechanism of the JDBC Driver.

The URL Oracle JDBC connection

The connection URL defines the address of the Oracle database server to connect:

jdbc:oracle::@

According to the Oracle driver type, the form of the URL is the following :

oci type

jdbc:oracle:oci:@

where database can be: An entry name defined in the tnsnames.ora This might require that you set the TNS_ADMIN environment variable.
or a descriptor that specifies the location of a listener and the service name

//[:]/

where host is the host name or the ip address of the Oracle database server port is the port number of the Oracle listener service
is Oracle service name of the database.

Thin type

jdbc:oracle:thin:@

where database can be: A descriptor that specifies the location of a listener and the service name

//[:]/

where host is the host name or the ip address of the Oracle database server port is the port number of the Oracle listener service

What is a service connect string

Oracle is replacing the SID mechanism for identifying databases with a services approach. This has been available in the database since 8.1.7. JDBC supports services in the connect URL. We strongly encourage everyone to transition from SIDs to services as quickly as possible as SIDs will be cease to be supported in one of the next few releases of the database.

Examples

jdbc:oracle:thin:@//192.168.2.1:1521/XE

jdbc:oracle:thin:@//myserver.com/customer_db

jdbc:oracle:oci:scott/tiger@//myserver.com:5521/customer_db

jdbc:oracle:thin:192.168.2.1:1521:X01A

jdbc:oracle:oci:@HR

jdbc:oracle:thin:@GL

jdbc:oracle:thin:@(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS =(PROTOCOL = TCP) (HOST = ) (PORT = )) 
(ADDRESS =(PROTOCOL = TCP) (HOST =) (PORT = )))
(CONNECT_DATA = (SERVICE_NAME = )))

jdbc:oracle:thin:@(DESCRIPTION= 
(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_scan) (PORT=1521)) 
(CONNECT_DATA=(SERVICE_NAME=GL)))

jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=off)
(ADDRESS=(PROTOCOL=TCP)(HOST=db7-vip)(PORT=1521))
(ADDRESS=(PROTOCOL =TCP)(HOST=db6-vip)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=db5-vip)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=GL)))

Note that the VIPs (Virtual IPs) for the 3 nodes are referenced explicitly in the connection string.

Note: Support for SID is being phased out. Oracle recommends that users switch over to using service names.

Connection Properties

The Oracle JDBC driver provides properties that can be specified when connecting to the database. Listed below are some examples of these properties.
To specify properties in the JDBC connection, you can use a Java Properties object, and pass that object into the JDBC getConnection method. For example:

java.util.Properties info = new java.util.Properties(); 
info.put("internal_logon", "sysdba"); 
Connection conn = DriverManager.getConnection (url, info); 

internal_logon

Use this property to connect as a sysoper or sysdba role.
When using this property, the user and password properties must be included in the properties object. For example:

Properties props = new Properties(); 
props.put("user", "scott"); 
props.put("password", "tiger"); 
props.put("internal_logon", "sysoper"); 
Connection conn = DriverManager.getConnection (url, props); 

defaultRowPrefetch

Oracle JDBC drivers allow you to set the number of rows to prefetch from the server while the result set is being populated during a query. Prefetching row data into the client reduces the number of round trips to the server. A typical value for this property is 10.

defaultBatchValue

Oracle JDBC drivers allow you to accumulate inserts and updates of prepared statements and send them to the server in batches once it reaches a specified batch value. This feature reduces round trips to the server. Use the value 1 if you don't want to use this feature.

processEscapes

"false" to disable escape processing for statements (Statement or PreparedStatement) created from this connection. Set this to "false" if you want to avoid many calls to Statement.setEscapeProcessing(false);. This is espcially usefull for PreparedStatement where a call to setEscapeProcessing(false) would have no effect. The default is "true".

JDBC - Version: 10.2.0.0.0 to 11.1.0.6.0

Information in this document applies to any platform.
How to connect via the JDBC Thin driver using an entry defined in a tnnames.ora file ?
JDBC Driver Thin supports tnsnames.ora file lookup support since 10g R2.
In order to use a tns entry defined in tnsnames.ora by a Java JDBC application, the following steps have to be done:

set the system property oracle.net.tns_admin defining the directory where the file tnsnames.ora is stored provide a jdbc connection string as "jdbc:oracle:thin:@{tns_entry_name}"

To illustrate this, here is a sequence of code to perform:

System.setProperty("oracle.net.tns_admin", "c:\\");
String url = "jdbc:oracle:thin:@mytns_alias";
Connection conn = DataSource.getConnection (url, userName, password); 

References

High-Performance Oracle JDBC Programmin
Oracle support : Starting With Oracle JDBC Drivers [ID 401934.1]