Accessing a database through JDBC

What is JDBC?

(If you have already been through the "Using JDBC to extract data into XML" tutorial, feel free to skip ahead to The orders.xml file.)

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, in that it provides a standard API intermediary for accessing a database. As seen on the left, the JDBC driver translates standard JDBC commands into the native API for the database.

You will notice that 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 (which you will use when you Create the connection).

See Resources for information on downloading the appropriate JDBC driver for your database. Over 150 JDBC drivers are available, covering virtually every database.

There are even options for databases that do not have an available JDBC driver, as you will see in the next panel.

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; a JDBC-ODBC bridge can be used instead. As you can see on the left, the application accesses the bridge via JDBC calls. The bridge then translates the commands into ODBC, whereupon the ODBC driver translates them into the native API.

The JDBC-ODBC bridge is not the recommended way to access a database for reasons involving both performance and configuration -- commands must pass through two APIs, 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 on a Windows system, create a System Data Source Name (DSN) by choosing Start>Settings>Control Panel>ODBC Data Sources . Note 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 orders (
   orderid       numeric 
                   primary key,
   userid        varchar(50),
   productid     varchar(10),
   quantity      numeric,
   unitprice     numeric )
   
create table productOrders (
   productid     varchar(10),
   quantity      numeric,
   revenue       numeric )

First, make sure your database of choice is installed and running, and that the JDBC driver is available. You can download drivers from http://industry.java.sun.com/products/jdbc/drivers.

Once you have created the database, you will need to create the necessary tables. Ultimately, the structure will be unimportant because it can be determined by the XML mapping file. This tutorial assumes the presence of two tables, orders and productOrders, detailed at the left. Create the tables using the appropriate steps for your database.

Back to top

The process of accessing a database

Using the Java language to interact with a database 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 Java 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 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 SaveOrders 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 orders, were accessed directly via ODBC, the URL might be:

odbc:orders

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

jdbc:odbc:orders

The actual connection is created below:

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

public class SaveOrders extends Object {

   public static void main (String args[]){

      //For the JDBC-ODBC bridge, use
      //driverName = "sun.jdbc.odbc.JdbcOdbcDriver"
      //and
      //connectURL = "jdbc:odbc:orders"
      String driverName = "JData2_0.sql.$Driver";
      String connectURL = "jdbc:JDataConnect://127.0.0.1/orders";
      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 the database resources they take up. This may lull you into thinking you do not have to worry about closing these objects, but that is not true.

It is entirely possible that the Java application itself has plenty of resources available, which means less-frequent garbage collection. At the same time, there may be limited database resources available, many of which may be taken up by Java objects that can easily be closed by the application.

It is 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 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 10 | Previous | Next

Comments

Back to top

Table of contents

• Introduction
• Accessing a database through JDBC
• Reading the XML file
• Updating the database using SQL
• Using an XML mapping file
• Using transactions and batches
• Updatable ResultSets
• JDBC/XML data insertion summary
• Resources
• About the author

---

2 of 10 | 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.