JDBC (Java Database Connectivity) API enables Java applications to connect to databases and retrieve data. JDBC is one of the most popular database APIs in use today. This is partly due to the popularity of Java and partly to the elegant and user friendly design of JDBC itself. JDBC is vendor neutral. The API is not specific to any database vendor. Common JDBC feaures seamlessly work with any database and JDBC applications are portable across databases.

Ever since Java became the choice of enterprise software developers, database connectivity was a hot subject. JDBC was accepted by many database vendors and JDBC drivers were released quickly. Today, applications servers wuch as WebSphere, WebLogic, JBoss, Geronimo, Tomcat all support JDBC.

In order to use JDBC, an application needs to open a JDBC connection first. In a standalone application, this is typically done using DriverManager.getConnection. Prior to this call, a JDBC driver must have been loaded using the Class.forName(driverName) method. When a connection is requested from DriverManager, all loaded drivers are checked to see whether they can provide a connection to the requested database based on the JDBC url. The correct driver responds by handing out a connection. Each database driver has it's own JDBC url pattern. The JDBC url is typically in the form of jdbc:dbname://host:port/dbname:key=value;

It is usually expensive to create a connection. Every connection must be closed after it is used. If connections will be created and closed frequently, this can have adverse effect on application performance. There are libraries that pool (cache) JDBC connections seamlessly. This is called connection pooling.

Connection pooling is one of the services typically provided by application servers and web containers. The application defines a datasource (driver) to use in the application server. Application server makes sure that every time a connection is closed, it is placed in a pool in idle state and when a connection is requested again, a connection is returned from the pool if it is available. If a connection is not available in the pool, and if the connection pool has reached maximum capacity, then it will wait for another connection to be returned to the pool before handing out a connection.

Once the application receives the connection, it can create or prepare a statement object using it.

In JDBC, there are 3 types of statements: Statement, PreparedStatement, CallableStatement. A statement object represents an sql statement that will be executed. It typically does not have parameters. Usually the database server and the application server cannot cache the statement and the associated data access plan. On the other hand, PreparedStatement objects are parametric such that using question marks they allow parameters to be passed to them so that they can return different results when different parameters are given. For example, a website that uses a login page can use a prepared statement like this:

select userid from users where userid=? and password=?

as you can see, once prepared, this statement can be re-used for many userid / password pairs. Thus prepared statements are good candidates for caching to improve performance. The database server can also cache prepared access plans. This provides higher scalability and speed.

CallableStatement object is similar to PreparedStatement objects except that instead of SQL statements, they represent stored procedure calls. Stored procedures can return zero or more resultsets where as a Statement or a PreparedStatement can only return one resultset. A stored procedure can also return output parameters via CallableStatement object.

For more information about JDBC Java database connectivity, visit jdbcurl.com