• JDBC Drivers
• Loading Drivers
• JDBC Connections
• SQL Tables
• Inserting Data
• Retrieving Data
• Other Information Sources

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 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

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:

• Connection creation (ie, the above code snipet) is typically costly (Oracle: high cost; MySQL: minimal). In general, do this as infrequently, and as late in your code, as possible.
• Do not store a JDBC Connection object in a website visitor's session object.
• Maintain a threadsafe Pool of ready Connections for reuse. Return Connections to the pool immediately after use. (Beware of pool Connection expiration.)
• These tips are not specific to JDBC Connections. Rather, they apply to database connections in general.

top

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

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

top

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

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.