Java Database Connectivity

JDBC is a standard Java class library to query and modify relational data
JDBC is modelled after ODBC (OpenDatabaseConnectivity?), but is fully Object-Oriented

The JDBC API makes it possible to do three things:

Establish a connection with a database or access any tabular data source
Send SQL statements
Process the results

Links

JDBC Architecture

From http://java.sun.com/products/jdbc/overview.html

The JDBC API contains two major sets of interfaces: the first is the JDBC API for application writers, and the second is the lower-level JDBC driver API for driver writers. JDBC technology drivers fit into one of four categories. Applications and applets can access databases via the JDBC API using pure Java JDBC technology-based drivers, as shown in the following figure:

Type 4 (left). Direct-to-Database Pure Java Driver: This style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

Type 3 (right).Pure Java Driver for Database Middleware: This style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.

The graphic below illustrates JDBC connectivity using ODBC drivers and existing database client libraries.

Type 1 (left). JDBC-ODBC Bridge plus ODBC Driver: This combination provides JDBC access via ODBC drivers. ODBC binary code - and in many cases, database client code - must be loaded on each client machine that uses a JDBC-ODBC Bridge. Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available.

Type 2 (right). A native-API partly Java technology-enabled driver: This type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

JDBC Classes, Interfaces and Methods

 DriverManager
  |
 Connection::getConnection()->DatabaseMetaData::getMetaData()
  |
 Statement::createStatement()
  |
 ResultSet::executeQuery()->ResultSetMetaData::getMetaData()
  |
 Relational DBMS

Registering a JDBC driver

JDBC drivers must register themselves with the DriverManager?
Drivers register themselves automatically when they are loaded
Use Class::forName(<package>.<driver class>) to load the JDBC driver:

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

Connecting to a database

Use DriverManager to open a connection to database
An URL specifies JDBC driver and database:

 Connection con = DriverManager.getConnection("jdbc:odbc:databasename", "username", "password");
 try {
   ...
 } finally {
   try { con.close(); } catch (Throwable ignore) [{}
 }

Statements

Three SQL statement interfaces:

Statement - Execute a SQL statement
PreparedStatement - Execute pre-compiled statements
CallableStatement? - Call a stored procedure

 Statement stmt = con.createStatement();
 try {
   ResultSet res = stmt.executeQuery("select * from sometable");
   try {   
     ...
   } finally {
     try { res.close(); } catch (Throwable ignore) [{}
   }
 } finally {
   try { stmt.close(); } catch (Throwable ignore) [{}
 }

Process query results

ResultSet holds a table of result data returned by an SQL query
Support for cursors, use ResultSet::next() to move to next record
Retrieve data using ResultSet::getXXX() methods

 ResultSet res = stmt.executeQuery("select name, salary from employee");
 try {
   while(res.next()) {
     String name = res.getString(1);
     BigDecimal salary = res.getBigDecimal(2, 2);
     ...
   } 
 } finally {
   try { res.close(); } catch (Throwable ignore) [{}
 }

Transactions

Set Connection::setAutoCommit(false) to take transaction control
Otherwise a transaction for each SQL statement

 boolean autoCommitDefault = con.getAutoCommit();
 try {
     con.setAutoCommit(false);

     /* You execute statements against conn here transactionally */

     con.commit();
 } catch (Throwable e) {
     try { con.rollback(); } catch (Throwable ignore) {}
     throw e;
 } finally {
     try { con.setAutoCommit(autoCommitDefault); } catch (Throwable ignore) {}
 }

JDBC 2.0

Scrollable and updatable result sets
Batch updates (requires a DBMS support)
SQL 3 data types: BLOB, CLOB, ARRAY, structured type
JDBC 2.0 standard extensions:
- DataSources? - Database connections named through JNDI
- ConnectionPooling? (requires DBMS support)
- RowSet?
- DistrubutedTransactionSupport?

Data Sources

 // register a data source
 DataSource?Class dso = new DataSource?Class(); // Implements DataSource?
 dso.setServerName("SOME_SERVER");
 dso.setDatabaseName("SOME_DATABASE");
 Context ic = new InitialContext();
 // Register dso as e named DataSource?
 ic.bind("jdbc/SOME_DATABASE", dso);
 ...
 // Querying for a DataSource?
 Context ic = new InitialContext();
 DataSource ds = (DataSource?) ic.lookup("jdbc/SOME_DATABASE");
 // make a connection
 Connection con = ds.getConnection("username", "pasword");

A useful tip... when setting a parameter in a PreparedStatement, if one sets the value to null when using JdbcOdbcBridge and SqlServer, the driver will throw an exception. Instead, use the setNull( int index, int type) method. Of course, this requires the mapping of types somewhere in the application.

Another tip... when a SQLException was thrown, most databases do not include the offending SQL in the exception message, making it quite difficult to trace the problem (especially if it is due to the values set in the PreparedStatement). It is a simple matter to write your own JDBC driver to pass all the work to a real JDBC driver, while capturing the values set in the PreparedStatement. When an exception is thrown, the append the SQL statement and all values to the exception message. This will help debugging a lot.

Careful here; it's considered a security risk in some quarters to display the underlying SQL in error messages. Include a "testing" versus "production" option flag somewhere, or write it to an internal log instead of displaying it.

CategoryJava CategoryDatabase