JDBC - Java Database Connectivity, an SQL Encapsulation

John M. Thompson


Introduction

JDBC Drivers

JDBC requires a tailored driver for each database server.  JDBC drivers of four types exist.

Type 1: JDBC-ODBC Bridge
Type 1 Drivers provide database access using an ODBC driver in between the JDBC and the server.  These give near universal access at the expense of inefficiency.

Type 2: Partial Java Driver
An example is the Oracle driver that connects using Oracle's existing C-based drivers.  While faster, they are not portable.

Type 3: Pure Java Driver
These drivers are pure Java, but will go through a vendor's middleware to the database server.  The server-side can be optimized for a specific OS improving performance.  For best portability, a Type 4 driver may be a better choice.

Type 4: Direct-to-Database Pure Java Driver
JDBC calls are converted to database-specific network packets.  Since they can be downloaded dynamically, they are a good choice for Applet use.  These drivers are optimized for a database server, but not necessarily for a specific OS.

top


Loading Drivers

Loading a Type 1 Driver:

  
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  
  

The documentation for your chosen driver will give the required class name. For example, for class name jdbc.mmDriver, use the following code:

  
  Class.forName("jdbc.mmDriver");
  
  

No need to create an instance of a driver and register it with the DriverManager - calling Class.forName() will do that for you.

top


JDBC Connections

A connection is needed to communicate with the database server:


Connection dbCon = 
   DriverManager.getConnection( someUrl,
                                "dbUsername", 
                                "dbPassword");

Some notes on Connections: there are a host of performance-related issues surrounding Connections. Application requirements may dictate your approach, but here are some rules of thumb:

top


SQL Tables

A new goAskem table:

IAdminAction
Id
TestAdminId
ActionDate
ActionType
Notes

The following SQL statement will create this table:


CREATE TABLE IAdminActions
   ( Id INTEGER,
     TestAdminId INTEGER,
     ActionDate DATE,
     ActionType INTEGER,
     Notes VARCHAR(100) 
   )

This SQL statement can be used to read some of the table's columns, for one (?) row:


SELECT ActionType, ActionDate, Notes FROM IAdminActions 
   WHERE TestAdminId=1752
      ORDER BY ActionDate

To create this table with JDBC:


String createTable = "CREATE TABLE IAdminActions" +
                     "(Id INTEGER, TestAdminId INTEGER," +
                     "ActionDate DATE, ActionType INTEGER," +
                     "Notes VARCHAR(100) )";
Statement stmt = dbCon.createStatement();
stmt.executeUpdate( createTable );

top


Inserting Data

Statement stmt = dbCon.createStatement();
stmt.executeUpdate( "INSERT INTO IAdminAction " + 
                    "VALUES (444, 1545, '2001-04-07 13:44:07'," +
                    "7, 'Sample action.')");

top


Retrieving Data

ResultSet result = 
   stmt.executeQuery( "SELECT TestAdminId, ActionType FROM IAdminActions" );
int admin, action;
while (result.next()) {
   admin  = result.getInt( 1 /* 1st Column */ );
   action = result.getInt( 2 /* 2nd Column */ );
   System.out.println( "For Admin " + admin + ", Action = " + action );
}

top


Other Information Sources

For full specifications including the upcoming JDBC Version 3.0 spec, downloads, recommended reading, FAQ, and an on-line tutorial, go to:

http://java.sun.com/products/jdbc

top


TOC

Originals are accessible from http://www.iwaytechnology.com/jt/index.html.


Copyright 2001
by John M. Thompson
Boulder, Colorado USA
jt@iwaytechnology.com
A limited right to copy this page for
individual (non-commercial)
educational use only
is hereby granted.