Java Database Connectivity (JDBC) drivers

This paper provides an introduction to Java Database Connectivity (JDBC) drivers, an overview of the JDBC drivers Oracle provides, guidelines on how to choose a JDBC driver and how to achieve the best performance with JDBC.

Introduction

This paper provides an introduction to Java Database Connectivity (JDBC) drivers, an overview of the JDBC drivers Oracle provides, guidelines on how to choose a JDBC driver and how to achieve the best performance with JDBC. This paper is written for a wide audience, from the beginner to more advanced users of JDBC. Developers just starting out with Java and database connectivity will get an overview of the JDBC driver types and how they should be used. For more advanced developers, the second half of the paper focuses on how to improve performance of your database-centric applications.

JDBC was created by Sun to provide a platform-neutral, universal framework between databases and Java. The JDBC API is a set of interfaces that abstract database functionality, such as running queries and processing results. A database or third-party vendor creates a JDBC driver that implements these interfaces. With the JDBC API, developers can take advantage of the Java platform's "Write Once, Run Anywhere" capabilities.

JDBC clients

JDBC drivers allow database clients to access the database server. A database client may be an applet, application or with J2EE a servlet or EJB. Since servlets and EJBs run in application server containers, the application server actually becomes a client to the database server.

JDBC driver types

A JDBC driver is needed for a Java application to communicate with a database using JDBC. JDBC drivers are categorized into four types. The architecture differences between these categories include how the driver makes a connection to the database and whether the driver is implemented in native or Java code.

Type 1: JDBC-ODBC bridge
The type 1 driver provides JDBC access to most Open Database Connectivity (ODBC) drivers. Type 1 drivers are "JDBC-ODBC bridge" drivers provided by Sun and are meant to support legacy products. The bridge converts JDBC calls into ODBC and passes them to the appropriate ODBC driver for the backend database. The bridge driver needs to be installed and configured on the client side before it can be used. The use of two interfaces (JDBC and ODBC) makes this driver type the least performing. The functionality of this driver is also limited to the underlying ODBC driver, which usually does not support all the features of Java. It is recommended to use this driver for development only and not in a production environment. Type 2: Native API driver
The type 2 driver converts JDBC to native proprietary database calls. This driver is partially native and partially Java. The JDBC manager, driver and native database libraries are required on the client. Type 2 drivers are faster than type 1 because the extra layer of translation to ODBC is removed.

Oracle Type 2 Driver
Oracle provides a type 2 driver called the JDBC Oracle Call Interface (OCI) driver. It is written in a combination of Java and C and converts JDBC to OCI using JNI calls. These calls are then sent over Oracle Net to the Oracle database server. This driver is Oracle platform-specific because it requires an Oracle client installation.

Type 3: JDBC Network-protocol driver
The type 3 driver uses a database-independent network protocol to communicate with a middle-tier JDBC server. The JDBC server translates these calls into the database-specific API. The driver is database independent and does not require any native binary code on the client. It only needs to pass SQL commands over the network to the server and receive data back. The JDBC server can be implemented in Java or as a native component.

Type 4: Native protocol (100% Java driver)
The type 4 driver translates JDBC calls into the DBMS specific protocol directly. Similar to the type 3 driver, the type 4 drivers do not require any code on the client.

Oracle Type 4 Driver
Oracle provides a type 4 driver called the JDBC thin driver, which is a 100 percent Java, platform-independent. The JDBC thin driver allows a direct connection to the database by providing an implementation of TCP/IP that emulates Oracle Net and TTC (the wire protocol used by OCI) on top of Java sockets.

How to choose a driver for your application

There are some cases where there will be no option about which driver to use. Applications that will be running on many clients, such as an applet, will need to use the JDBC thin driver.

    Use the thin driver if you are writing an applet. The OCI driver will not work inside a Web browser because it makes native method calls.
    Use the thin driver for applications where you have no control over the client platform.

OCI vs. thin driver:

For applications that will be deployed in a controlled environment where you have the opportunity to install the Oracle client software on every platform, you have a choice between the type 2 OCI driver and type 4 thin driver. There are a few things to keep in mind when choosing between these two drivers. These include: performance, scalability, portability and cost.

Performance
Type 2 drivers are generally faster than type 4 drivers because they are directly executed by the computer's CPU. The type 4 driver's are compiled into byte code and are executed on the Java Virtual Machine, which is then executed by the computer's CPU. The JVM adds an additional layer that affects its performance. Because the OCI driver uses native code libraries, it executes faster than the thin driver.

There is a significant performance difference between the two drivers when stored procedures are used. The thin driver can take up to twice as long as the OCI driver to execute a stored procedure. Response time can deteriorate quickly if multiple stored procedures are called.

Use the OCI driver if performance is critical to your application or you make heavy use of stored procedures.

Scalability
The type 2 OCI driver has several extensions that can improve the scalability and availability of your applications. These include connection pooling and transparent application failover.

OCI driver connection pooling
Connection pooling is a mechanism to reuse physical connections and minimize expensive operations in the creation and closing of database sessions. Both the thin and OCI drivers support connection pooling, however, the OCI driver has some enhanced features. The OCI driver allows applications to have many logical connections to the database, all using a small set of physical connections. The pooling of the incoming connections to the Oracle database is controlled by the OCI connection pool manager on the middle tier. Many more Oracle sessions can be multiplexed over this pool of fewer shared connections and backend databases.

Transparent Application Failover
Transparent Application Failover (TAF) is another feature of the OCI driver. It allows the driver to automatically reconnect to a database if the database instance to which the connection is made goes down. When this occurs, the active transactions are rolled back and the last committed transaction is restored. The new database connection, though created by a different node, is identical to the original regardless of how the connection was lost.

    Use the OCI driver if you want the maximum scalability of the Oracle server or you need the enhanced availability features such as Transparent Application Failover.

Portability and cost
The JDBC OCI driver requires the presence of the OCI libraries, Net8, CORE libraries and other files on each client machine on which it is installed. The time (and therefore cost) of installing, configuring and maintaining the client software needs to be taken into account when using the OCI driver.

    Use the thin driver for maximum portability and lower cost.

The following summarizes the types of drivers to use in different situations.

Performance with stored procedures: Type 2
Scalability: Type 3, Type 4
Transparent Application Failover: Type 2
Legacy system with ODBC driver: Type 2
Portability: Type 3, Type 4

Performance
Current Web applications are still very database-centric. From 60 percent to 90 percent of the execution time on a Web application can be spent in accessing the database. To improve performance of these applications, it is very important to tune database access in your applications. The following section outlines the best practices for database access including:

  • Disabling auto-commit mode

  • Defining column types

  • Prefetching rows

  • Using update batching

  • Using JDBC connection pooling and caching

  • Using JDBC statement caching

  • Avoid using more than one database connection simultaneously

Disabling auto-commit mode
Auto-commit mode indicates to the database whether to issue an automatic commit operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.

By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the setAutoCommit() method of the connection object (either java.sql.Conection or oracle.jdbc.OracleConnection).

For better application performance, disable auto-commit mode and use the commit() or rollback() method of the connection object to manually commit or rollback your transaction.

Example: Disabling auto-commit mode
The following example illustrates how to do this. It assumes you have imported the oracle.jdbc.* and java.sql.* interfaces and classes.

//ds is a DataSource object
Connection conn = ds.getConnection();

// It's faster when auto commit is off
conn.setAutoCommit (false);

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

Defining Column Types
Defining column types improves performance by saving a roundtrip to the DB server. It allows the application code to define the datatype for every column of the expected result set and for VARCHAR, VARCHAR2, CHAR and CHAR2 it specifies the maximum length.

Example: Defining column types
The following example illustrates the use of this feature. It assumes you have imported the oracle.jdbc.* and java.sql.* interfaces and classes.

//ds is a DataSource object
Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement("select empno, ename, hiredate from emp");

//Avoid a roundtrip to the database and describe the columns
((OraclePreparedStatement)pstmt).defineColumnType(1,Types.INTEGER);

//Column #2 is a VARCHAR, we need to specify its max length
((OraclePreparedStatement)pstmt).defineColumnType(2,Types.VARCHAR,12);
((OraclePreparedStatement)pstmt).defineColumnType(3,Types.DATE);
ResultSet rset = pstmt.executeQuery();
while (rset.next())
System.out.println(rset.getInt(1)+","+rset.getString(2)+","+rset.getDate(3));
pstmt.close();
...

Prefetching rows
Row prefetching improves performance by reducing the number of round trips to a database server. For most database-centric applications, row prefetching should be used as much as possible. The recommended prefetch size is 10.

Example: Row prefetching
The following example illustrates the use of row prefetching. It assumes you have imported the oracle.jdbc.* and java.sql.* interfaces and classes.

//ds is a DataSource object
Connection conn = ds.getConnection();

//Set the default row-prefetch setting for this connection
((OracleConnection)conn).setDefaultRowPrefetch(7);

//The following statement gets the default row-prefetch value for
//the connection, that is, 7
Statement stmt = conn.createStatement();

//Subsequent statements look the same, regardless of the row
//prefetch value. Only execution time changes.
ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
System.out.println( rset.next () );
while( rset.next () )
System.out.println( rset.getString (1) );

//Override the default row-prefetch setting for this statement
( (OracleStatement)stmt ).setRowPrefetch (2);
ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");
System.out.println( rset.next () );
while( rset.next() )
System.out.println( rset.getString (1) );
stmt.close();

Using update batching
Update batching sends a batch of operations to the database in one trip. When using it:

  • Always disable auto-commit mode with update batching.

  • Use a batch size of around 10.

  • Don't mix the standard and Oracle models of update batching.

Example: Oracle update batching
The following example illustrates how you use the Oracle update batching feature. It assumes you have imported the oracle.driver.* interfaces.

//ds is a DataSource object
Connection conn = ds.getConnection();

//Always disable auto-commit when using update batching
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("insert into dept values
(?, ?, ?)");

//Change batch size for this statement to 3
((OraclePreparedStatement)ps).setExecuteBatch (3);

//--------#1------------
ps.setInt(1, 23);
ps.setString(2, "Sales");
ps.setString(3, "USA");
ps.executeUpdate(); //JDBC queues this for later execution
//--------#2------------
ps.setInt(1, 24);
ps.setString(2, "Blue Sky");
ps.setString(3, "Montana");
ps.executeUpdate(); //JDBC queues this for later execution

//--------#3------------
ps.setInt(1, 25);
ps.setString(2, "Applications");
ps.setString(3, "India");
ps.executeUpdate(); //The queue size equals the batch value of 3
//JDBC sends the requests to the database

//--------#1------------
ps.setInt(1, 26);
ps.setString(2, "HR");
ps.setString(3, "Mongolia");
ps.executeUpdate(); //JDBC queues this for later execution
((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the

//queued request
conn.commit();
ps.close();
...

Example: Standard update batching
This example uses the standard update batching feature. It assumes you have imported the oracle.driver.* interfaces.

//ds is a DataSource object
Connection conn = ds.getConnection();

//Always disable auto-commit when using update batching
conn.setAutoCommit(false);
Statement s = conn.createStatement();
s.addBatch("insert into dept values ('23','Sales','USA')");
s.addBatch("insert into dept values ('24','Blue Sky','Montana')");
s.addBatch("insert into dept values ('25','Applications','India')");

//Manually execute the batch
s.executeBatch();
s.addBatch("insert into dept values ('26','HR','Mongolia')");
s.executeBatch();
conn.commit();
ps.close();
...

Using JDBC connection pooling and caching
Constant creation and destruction of resource objects can be very expensive in Java. We suggest using a resources pool to share resources that are expensive to create. The JDBC connections are one of the most common resources used in any Web application that requires database access. They are also very expensive to create. We have seen overhead from hundreds of milliseconds to seconds (depending on the load) in establishing a JDBC connection on a mid-size system with 4 CPUs and 2 GB memory.

In JDBC 2.0, a connection-pooling API allows physical connections to be reused. A pooled connection represents a physical connection, which can be reused by multiple logical connections. When a JDBC client obtains a connection through a pooled connection, it receives a logical connection. When the client closes the logical connection, the pooled connection does not close the physical connection. It simply frees up resources, clears the state and closes any statement objects associated with the instance before the instance is given to the next client. The physical connection is released only when the pooled connection object is closed directly.

The term pooling is extremely confusing and misleading in this context. It does not mean there is a pool of connections. There is just one physical connection, which can be serially reused. It is still up to the application designer to manage this pooled connection to make sure it is used by only one client at a time.

To address this management challenge, Oracle's extension to JDBC2.0 also includes connection caching, which helps manage a set of pooled connections. It allows each connection cache instance to be associated with a number of pooled connections, all of which represent physical connection to the same database and schema. You can use one of Oracle's JDBC connection caching schemes (dynamic, fixed with no wait or fixed wait) to determine how you want to manage the pooled connections, or you can use the connection caching APIs to implement your own caching mechanisms.

Using JDBC statement caching
Use JDBC statement caching to cache a JDBC PreparedStatement or OracleCallableStatement that is used repeatedly in the application to:

  • prevent repeated statement parsing and recreation

  • reduce the overhead of repeated cursor creation

The performance gain will depend on the complexity of the statement and how often the statement has to be executed. Since each physical connection has its own statement cache, the advantage of using statement caching with a pool of physical connections may vary. That is, if you execute a statement in a first connection from a pool of physical connections, it will be cached with that connection. If you later get a different physical connection and want to execute the same statement, then the cache does you no good.

Avoid using more than one database connection simultaneously
Using more than one database connection simultaneously in a request can cause a deadlock in the database. This is most common in JSPs. First, a JSP will get a database connection to do some data accessing. But then, before the JSP commits the transaction and releases the connection, it invokes a bean which gets its own connection for its database operations. If these operations are in conflict, they can result in a deadlock.

Furthermore, you cannot easily roll back any related operations if they are done by two separate database connections in case of failure. Unless your transaction spans multiple requests or requires some complex distributed transaction support, you should try to use just one connection at a time to process the request.

Summary

This paper provides an overview of the JDBC driver types, guidelines on how to choose a JDBC driver, and how to improve the performance of database applications. Of the 4 JDBC driver types, Oracle provides a type 2 OCI driver and a type 4 thin driver. Advantages of the type 2 OCI driver include Transparent Application Failover and improved performance when stored procedures are used. The type 4 thin driver should be used for maximum portability and lowest cost. To achieve the best possible performance from the applications and JDBC drivers, several performance-tuning best practices should be followed including: disabling auto-commit mode, defining column types, prefetching rows, using update batching and using JDBC statement caching.


About the authors:

Anjali Dhond is a product manager at Oracle, working in the Oracle Application Server technology group. She's been with the group since its inception and works closely with customers. Before joining Oracle, Anjali was a research assistant in the Computer Science Department at the Massachusetts Institute of Technology. In addition, Anjali worked as a developer at Sapient Corporation, a Web consulting firm.

Jean de Lavarene is a senior member of technical staff, focused on the performance of the JDBC drivers. He works closely with the Oracle Application Server technology team on the performance benchmarks and publications. Jean holds an MS in Computer Science from the Ecole des Mines in Paris.

Kuassi Mensah is group product manager within the Java, J2EE and Web services products group. His previous areas of focus have included setting up a sales consulting unit specializing in system and database performance and working with the Center of Expertise specializing in application architecture, performance, massively parallel systems and Java. Kuassi holds an MS and post-graduate degree (DEA) in computer sciences from the Programming Institute of University of Paris VI.

Ronald J. Peterson is a consulting member of the technical staff, Oracle XDB development. Prior to joining Oracle, he spent 23 years at IBM, 15 of those in DB2 development. He designed the DB2 JDBC and SQLJ drivers for DB2 Version 7 and, after joining Oracle, focused on the Oracle JDBC drivers.


Appendix
There are five basic steps in using JDBC to connect and receive data from the database:

1. Load the JDBC driver
2. Get a connection to the database
3. Create a statement
4. Execute the statement
5. Process the results

Depending on whether your application is a standalone Java program or a J2EE application that will be running inside a container will determine how the JDBC driver is loaded. J2EE applications can use datasources to specify databases or other resources to use. Datasources use the Java Naming and Directory Interface(JNDI) so that databases can be accessed by logical names, for convenience and portability.

For a standalone Java program, the following code loads the driver, gets a connection to the database, creates and executes a statement, and processes the results. The Oracle JDBC driver (in this example the thin driver) classes12.zip needs to be in the classpath.

Example: JDBC example
The following example illustrates how to load the driver, get a connection to the database, create and execute a statement, and process the results. The Oracle JDBC driver (in this example the thin driver) classes12.zip needs to be in the classpath.

//Load the JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

//Get the connection
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@ : : "," "," ");

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

//Execute the statement
ResultSet rs= stmt.executeQuery("select * from EMP");

//Process the results
while (rs.next()) {
String name= rs.getString(1);
System.out.println("Employee Name: "+name);
}

For a J2EE application running on Oracle Containers for J2EE, datasources can be used to specify the JDBC connection. The advantage of using a datasource is that the database connection information is stored in a configuration file rather than in the code itself. If the database location changes in the future, only the datasources.xml file needs to be modified.

Example: Datasources
To use a datasource, the following needs to be added to the datasrources.xml file.

data-source class="oracle.jdbc.pool.OracleDataSource"
name="jdbc/pool/OracleDS"
location="jdbc/pool/OracleDS"
url="jdbc:oracle:thin:@
 
  :
  
   :
   
    "
username="
    
     "
password="
     
      " 

     
    
   
  
 

In this example, the data source created is of type oracle.jdbc.pool.OracleDataSource and the connection created when getConnection() is called is of type oracle.jdbc.driver.OracleConnection.

Example: Datasource lookup
The following Java code creates an initial context for the JNDI connection, looks up the JDBC datasource, and opens a connection.

javax.naming.InitialContext ic = new javax.naming.InitialContext(); 
oracle.jdbc.pool.OracleDataSource dataSource = (oracle.jdbc.pool.OracleDataSource)ic.lookup("jdbc/pool/OracleDS"); 
oracle.jdbc.driver.OracleConnection connection = dataSource.getConnection(); 

The code to create and execute a statement and process the results is the same as with the standalone JDBC connection.

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

//Execute the statement
ResultSet rs= stmt.executeQuery("select * from EMP");

//Process the results
while (rs.next()) { 
String name= rs.getString(1); 
System.out.println("Employee Name: "+name); 
} 

References

Java Programming with Oracle JDBC, Donald Bales, January 2002.

Oracle9i Java Programming, John Carnell, March 2002.

Oracle9i JDBC Developer's Guide and Reference, Release 2, Elizabeth Perry, March 2002.

Dig deeper on Oracle strategy and product roadmap

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close