Accessing Relational Data with JDBC

Accessing Relational Data with JDBC

By Weiqi Gao, OCI Senior Software Engineer

December 1999

The Java Database Connectivity (JDBC) API is a Java API for accessing relational (tabular) data, especially those stored in a relational database. JDBC has been an integral part of Sun's Java Development Kit since 1.1 and is supported by virtually all relational database vendors.

Pieces of the puzzle

To use JDBC API, several pieces of software must be installed:

1. The Java Development Kit (JDK), which contains the JDBC API interfaces, as well as the JDBC driver manager
2. The JDBC driver for a particular brand of relational database

The JDBC API interfaces allow Java applications to access data in a uniform, vendor-independent fashion.

The JDBC driver manager is responsible for finding and loading the correct JDBC driver based on the connection string.

The JDBC driver contains classes that implement the standard JDBC interfaces. The JDBC driver is responsible for actually communicating with the database and carrying out SQL commands and queries. 

JDBC drivers can be implemented in four ways:

1. JDBC-ODBC bridge plus ODBC driver. The JDK contains a JDBC-ODBC bridge, which takes JDBC calls from a Java application and turns them into ODBC calls, utilizing the ODBC driver that is installed on the same machine.
2. Native-API partly-Java driver. This kind of driver takes JDBC calls from a Java application and turns them into calls to the database vendor's native client access software that is installed on the same machine.
3. JDBC-Net pure Java driver. This kind of driver takes JDBC calls from a Java application and turns them into a vendor independent net protocol. At the other end of the network, the protocol is translated by a middleware server into a vendor specific protocol.
4. Native-protocol pure Java driver. This kind of driver takes calls from Java applications and turns them into vendor specific protocols.

The JDBC API is easy to use

The JDBC API gives database access code an object-oriented feel. 

Here's a snippet of very simple JDBC code that queries a database with an employee table for the last_name and hobby fields and prints out the result (we used the Connection, Statement, ResultSet classes):

import java.sql.*;
 
public class employee {
  public static void main(String[] args) {
    try {
      // Load the driver class
      Class.forName("postgresql.Driver");
 
      // Make a connection
      Connection conn = DriverManager.getConnection
        ("jdbc:postgresql://myhost/mydatabase", "myuserid", "mypassword");
 
      // Create a SQL statement
      Statement stmt = conn.createStatement();
 
      // Execute a query
      ResultSet rs = stmt.executeQuery
        ("select first_name, hobby from employee");
 
      // Fetch the data from the query result set
      while (rs.next()) {
        System.out.println("first_name: " + rs.getString(1));
        System.out.println("hobby: " + rs.getString(2));
      }
    } catch (Exception e) {
      System.out.println("Exception: " + e.getMessage());
    }
  }
}

Benefits of JDBC

Aside from being easy to use, the JDBC API also offers the following benefits:

1. Wide availability. Since JDBC is included with the Java Platform, it is available everywhere Java is available.  This makes the Java application that uses the JDBC API portable to many platforms.
2. Vendor independence. Since Java applications that use the JDBC API only use interfaces from the JDBC packages, the code has a high likelihood of being portable from one vendor's database to another.
3. Multi-tiered applications. The JDBC API encourages Java applications to be designed into multiple tiers, separating business logic from presentation logic. This aids the scalability, reliability, and maintainability of the application tremendously.
4. Robustness, security, automatically downloadable code, and other Java pluses. By virtue of being written in Java, the JDBC application automatically enjoys these benefits that Java offers.

JDBC is the basis for other technologies

The JDBC API offers the Java platform a standard way of accessing relational data. Many other technologies use the JDBC API as a low level tool to build higher-level abstractions. Enterprise JavaBeans (EJB) servers usually use JDBC API for their persistence mechanism.

JDBC 2.0

With the release of the Java 2 Platform comes the JDBC API 2.0. This new version of the JDBC API offers many more features in addition to the ones offered in JDBC API 1.x. Most noticable are scrollable result sets, batch updates, and advanced data types.

The JDBC API 2.0 standard extension (javax.sql.*) offers JNDI support, connection pooling, and distributed transactions.

More information

The following web resource has more information about JDBC:  http://java.sun.com/products/jdbc/: (the official Sun Java JDBC site)