Accessing a database through JDBC

What is JDBC?

If you have already been through the "Using JDBC to insert XML data into a database" tutorial, feel free to skip ahead to Anatomy of a SELECT statement.

It was not very long ago that in order to interact with a database, a developer had to use the specific API for that database. This made creating a database-independent application difficult, if not impossible.

JDBC is similar to ODBC (or Open Database Connectivity), which provides a standard API intermediary for accessing a database. As seen on the left, standard JDBC commands can be used, and the JDBC driver translates them into the native API for the database.

Nowhere in this tutorial is a specific database mentioned, because the choice is largely irrelevant. All commands are standard JDBC commands, which are translated by the driver into native commands for whatever database you choose. Because of this API independence, of sorts, you can easily use another database without changing anything in your application except the driver name, and possibly the URL of the connection, used when you Create the connection.

See Resources for information on downloading the appropriate JDBC drivers for your database. Over 150 JDBC drivers are available, and for virtually any database.

There are even options for databases that do not have an available JDBC driver.

Back to top

The JBDC-ODBC bridge

It is not necessary to have a specific JDBC driver for a database as long as an ODBC driver is available; you can use a JDBC-ODBC bridge instead. The application calls the bridge, which translates the commands into ODBC, and the ODBC driver translates them into the native API.

The JDBC-ODBC bridge is not the recommended way to access a database for various reasons, involving both performance and configuration -- commands must pass through two API's, and the ODBC driver must be installed and configured on every client -- but it is acceptable for testing and development if there is no pure JDBC driver available.

If you choose to use the bridge, create a System Data Source Name (DSN) on a Windows system by choosing Start>Settings>Control Panel>ODBC Data Sources . Make note of the name of the DSN, as it will be referenced when you Create the connection.

Back to top

Set up the database and driver

create table products (
   product_id   numeric primary key,
   product_name varchar(50),
   base_price   numeric,
   size         numeric,
   unit         varchar(10),
   lower        numeric,
   upper        numeric,
   unit_price   numeric )

First, make sure that whatever database you choose to use is installed and running, and that the driver is available. JDBC drivers can be downloaded from http://industry.java.sun.com/products/jdbc/drivers.

Once you have created the database, create the necessary tables. This tutorial uses only one table, products. Its structure is at the left. Create the table with the appropriate steps for your database.

Note: Under normal circumstances this data would be broken out into at least two related tables; for simplicity's sake, the example represents only one.

Back to top

The process of accessing a database

Interacting with a database using Java usually consists of the following steps:

1. Load the database driver. This can be a JDBC driver or the JDBC-ODBC bridge.
2. Create a Connection to the database.
3. Create a Statement object. This object actually executes the SQL or stored procedure.
4. Create a ResultSet and populate it with the results of an executed query (if the goal is to retrieve or directly update data).
5. Retrieve or update the data from the ResultSet.

The java.sql package contains the JDBC 2.0 Core API for accessing a database as part of the JavaTM 2 SDK, Standard Edition distribution. The javax.sql package that is distributed as part of the Java 2 SDK, Enterprise Edition, contains the JDBC 2.0 Optional Package API.

This tutorial uses only classes in the JDBC 2.0 Core API.

Back to top

Instantiate the driver

To access the database, first load the JDBC driver. A number of different drivers may be available at any given time; it is the DriverManager that decides which one to use by attempting to create a connection with each driver it knows about. The first one that successfully connects will be used by the application. There are two ways that the DriverManager can know a driver exists.

The first way is to load it directly using Class.forName(), as shown in this example. When the driver class is loaded, it registers with the DriverManager, as shown here:

public class Pricing extends Object {

   public static void main (String args[]){

      //For the JDBC-ODBC bridge, use
      //driverName = "sun.jdbc.odbc.JdbcOdbcDriver"
      String driverName = "JData2_0.sql.$Driver";

      try {
         Class.forName(driverName);
      } catch (ClassNotFoundException e) {
         System.out.println("Error creating class: "+e.getMessage());
      }
   }
}

With a successfully loaded driver, the application can connect to the database.

The second way the DriverManager can locate a driver is to cycle through any drivers found in the sql.drivers system property. The sql.drivers property is a colon-delimited list of potential drivers. This list is always checked prior to classes being loaded dynamically, so if you want to use a particular driver, make sure that the sql.drivers property is either empty or starts with your desired driver .

Back to top

Create the connection

Once you load the driver, the application can connect to the database.

The DriverManager makes a connection through the static getConnection() method, which takes the URL of the database as an argument. The URL is typically referenced as:

jdbc:<sub-protocol>:databasename

However, the reference URL can be written in any format the active driver understands. Consult the documentation for your JDBC driver for the URL format.

One occasion where the subprotocol comes into play is in connecting via ODBC. If the sample database, with its DSN pricing, were accessed directly via ODBC the URL might be:

odbc:pricing

This means that to connect via JDBC, the URL would be:

jdbc:odbc:pricing

The actual connection is created below:

                    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Pricing extends Object {

   public static void main (String args[]){

      //For the JDBC-ODBC bridge, use
      //driverName = "sun.jdbc.odbc.JdbcOdbcDriver"
      //and
      //connectURL = "jdbc:odbc:pricing"
      String driverName = "JData2_0.sql.$Driver";
      String connectURL = "jdbc:JDataConnect://127.0.0.1/pricing";
      Connection db = null;	   
      try {
         Class.forName(driverName);
         db = DriverManager.getConnection(connectURL);
      } catch (ClassNotFoundException e) {
         System.out.println("Error creating class: "+e.getMessage());
      } catch (SQLException e) {
         System.out.println("Error creating connection: "+e.getMessage());
      }
   }
}

Once a connection is successfully made, it is possible to perform any required database operations (for example, inserting or updating data).

Back to top

Closing the connection

Because the Statement and Connection are objects, Java will garbage collect them, freeing up the database resources they take up. This may lull you into thinking that this means you don't have to worry about closing these objects, but it's not true.

It is entirely possible that the Java application itself has plenty of resources available, which means less-frequent garbage collection. It is also possible that while the Java application has plenty of resources, the available database resources are limited. Many of the database resources may be taken up by Java objects that could just as easily have been closed by the application.

It's important to make sure that these objects are closed whether or not there are any errors, so add a finally block to the try-catch block that's already in place:

...
      Connection db = null;	  
      try {
         Class.forName(driverName);
         db = DriverManager.getConnection(connectURL);
      } catch (ClassNotFoundException e) {
         System.out.println("Error creating class: "+e.getMessage());
      } catch (SQLException e) {
         System.out.println("Error creating connection: "+e.getMessage());
      } finally {
         System.out.println("Closing connections...");
         try {
            db.close();
         } catch (SQLException e) {
            System.out.println("Can't close connection.");
         }
      }
   }
}

Ironically, the close() method itself can throw a SQLException, so it needs its own try-catch block.

2 of 9 | Previous | Next

Comments

Back to top

Table of contents

• Introduction
• Accessing a database through JDBC
• Extracting information from a database via JDBC
• Setting up the mapping
• Creating an XML document using SQL results
• JDBC data extraction summary
• Download
• Resources
• About the author

---

2 of 9 | Previous | Next

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into XML on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.