Blog Archives

Autogenerated Keys with Oracle JDBC Driver

JDBC 3.0’s autogenerated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. The method Statement.getGeneratedKeys can be called to retrieve the value of such a column. This method returns a ResultSet object with a column for each automatically generated key. Calling ResultSet.getMetaData on the ResultSetobject returned by getGeneratedKeys produces a ResultSetMetaData object that can be used to determine the number, type, and properties of the generated keys. A flag indicating that any auto-generated columns should be returned is passed to the methods executeexecuteUpdate, or prepareStatement when the statement is executed or prepared.

Read the rest of this entry

How Does OCI JDBC Application Communicate With Oracle Database Server

This post details out on what happens behind the scenes when an JDBC application using OCI driver communicates with the oracle database server.

A sample jdbc program looks like

import java.sql.*;
class DatabaseAccess{
  public static void main (String args []) throws Exception
  {
        Class.forName ("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:oci:@hostname:1521:service_name", "scott", "tiger");

        ....
        ....
  }
}

As you can see, the program uses the JDBC OCI driver (a type 2 JDBC driver) to create a database connection. The Oracle JDBC OCI driver converts the JDBC invocations to calls to OCI (Oracle Call Interface) which are then sent over by Oracle Net to the Oracle database server. Read the rest of this entry

JDBC Driver Types & Architecture

The Data Access Handbook explains two major reasons a database driver can degrade the performance.

  1. The architecture of the driver is not optimal.
  2. The driver is not tunable. It does not have runtime performance tuning options that allow you to configure the driver for optimal performance. The type of options that we are talking about are ones that you can adjust to match your application and environment. For example, if your application retrieves large objects, look for a driver option that allows you to configure how much active memory the driver uses to cache a large object.

This post details on the different database driver architectures.

Four distinct architectures exist for database drivers: bridge, client-based, database wire protocol, and independent protocol. Choose a database driver that is implemented with an architecture that provides the best performance for your application.

Read the rest of this entry

Database Connections Are Performance Expensive

While reading the Data Access Handbook I came across some facts about database connections. These connections are established by any JEE application to the database server.

  • Creating a connection is performance-expensive compared to all other tasks a database application can perform.
  • Open connections use a substantial amount of memory on both the database server and database client machines.
  • Establishing a connection takes multiple network round trips to and from the database server.
  • Opening numerous connections can contribute to out-of-memory conditions, which might cause paging of memory to disk and, thus, overall performance degradation.
  • In today’s architectures, many applications are deployed in connection pooled environments, which are intended to improve performance.However, many times poorly tuned connection pooling can result in performance degradation. Connection pools can be difficult to design, tune, and monitor.

Read the rest of this entry

Oracle JDBC driver and connection thread safety

Oracle JDBC developer guide recommends not to share connections across multiple threads. Here is small excerpt from the Oracle guide

“Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.”