TABLE OF CONTENTS (HIDE)

Java Database Programming (JDBC)

An Intermediate Tutorial

Introduction

JDBC (Java Database Connectivity) is the Java's Application Programming Interface (API) for interfacing with relational databases. The API defines how to connect to the database, execute SQL statements, return the results, among others. JDBC defines the API, while the database vendors develop their specific drivers that meets the JDBC specification.

JDBC is divided into two parts: Core API and Optional API. Core API is implemented in java.sql package, while optional API in javax.sql package.

Core API (java.sql) interfaces include:

  • DriverManager, Connection, Statement,PreparedStatement, CallableStatement, ResultSet,
  • ResultSetMetaData, DatabaseMetaData.
  • Types, Blob, Clob, Array, Struct, Date, Time, Timestamp.

Optional API (javax.sql) interfaces include:

  • DataSource, PooledConnection, RowSet, and etc.

JDBC has the following versions:

  1. Version 1.0 (1997): Include how to create a driver instance, create a database connection (via Connection object) execute SQL statements (via Statement object), return the results (through a ResultSet object), and metadata of the database and the ResultSet.
  2. Version 2.0/2.1 (integrated into JDK 1.2): JDBC 2.0 introduces many new features to improve the performance and functionality.  For example, it introduces scrollable and updatable ResultSet, batching processing.  It also introduces a new RowSet interface and DataSource interfaces to support connection pooling and distributed transaction.
  3. Version 3.0 (integrated into JDK 1.4): [TODO]
  4. Version 4.0 (integrated into JDK 6): No need to load the database driver explicitly, as DriverManager can locate the appropriate database driver in the classpath. [TODO more]

I assume that you have basic knowledge of JDBC programming; otherwise, read "Introduction to Java Database Programming (JDBC) by Examples".

Handling SQLException

Many methods in java.sql throw a SQLException. You can use these methods:

public String getSQLState()
// Retrieves the SQLState (a five alphanumeric characters) for this SQLException object.
 
public int getErrorCode()
// Retrieves the vendor-specific exception code for this SQLException object.
 
public String getMessage()

public Iterator<Throwable> iterator()
// Returns an iterator over the chained SQLExceptions.
// You can instead use a for-each loop:
// for (Throwable t : ex) { t.getCause(); }

Example: [TODO]

ResultSet Interface

The java.sql.ResultSet interface, which models the resultant table of a SELECT query, provides methods for retrieving and manipulating the results of SELECT queries. As seen in all the earlier example, the Statement.executeQuery("SELECT ... FROM ...") method returns the query result in a ResultSet object. A ResultSet object maintains a row cursor. The row cursor initially positions before the first row in the ResultSet. ResultSet.next() moves the cursor to the next (first) row. You can then use ResultSet.getXxx(columnName) or ResultSet.getXxx(columnNumber) to retrieve the value of a column for the current row.

The default ResultSet object is not updatable and has a row cursor that moves forward only (via the next() method) to move from the first row to the last row). JDBC 2.0 introduces more functions to ResultSet, such as updatable and scrollable ResultSet. To enable these new functions, pass the desired parameters into the Connection.createStatement() method, e.g.,

Connection conn = DriverManager.getConnection(database-URL, username, password);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rset = stmt.executeQuery("SELECT ... FROM ...");

The syntax is as follows:

// In Connection Interface
Statement createStatement(
   int resultSetType, 
   int resultSetConcurrency) throws SQLException // JDBC 2.0 (JDK 1.2)
   
Statement createStatement(
   int resultSetType,
   int resultSetConcurrency,
   int resultSetHoldability) throws SQLException // JDBC 3.0 (JDK 1.4)

ResultSet's Type: The possible values are:

  • ResultSet.TYPE_FORWARD_ONLY (default): The ResultSet is not scrollable, but moves forward only from the first row to the last row via the next() method.
  • ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE: The ResultSet is scrollable, i.e., the row cursor can move forward, backward or to an absolute or relative row, via methods such as first(), last(), previous(), next(), absolute(rowNumber), relative(rowNumber), beforeFirst(), afterLast(), moveToCurrentRow(), moveToInsertRow().
    • ResultSet.TYPE_SCROLL_INSENSITIVE: The ResultSet does NOT reflect change made by others to the underlying data source while it is opened, hence, insensitive to changes.
    • ResultSet.TYPE_SCROLL_SENSITIVE: The ResultSet reflects changes made by others to the underlying data source while it remains opened.

ResultSet's Concurrency: The possible values are:

  • ResultSet.CONCUR_READ_ONLY (default): The ResultSet is read-only. You cannot update the underlying database via the ResultSet.
  • ResultSet.CONCUR_UPDATABLE: The ResultSet object can be updated via the updateXxx() methods. Change in ResultSet is reflected in the underlying database.

ResultSet's Cursor Holdability (JDBC 3.0/JDK 1.4): The Connection.commit() command may close the ResultSet that have been created during the current transaction. You can control this behavior via:

  • HOLD_CURSORS_OVER_COMMIT: ResultSet cursors are NOT closed but held open when the Connection.commit() is called. Holdable cursors are useful if your application uses mostly read-only ResultSet objects.
  • CLOSE_CURSORS_AT_COMMIT: ResultSet objects (and cursors) are closed when the commit() method is called.

Not all databases and JDBC drivers support these ResultSet's types. You can verify via method DatabaseMetaData.supportsResultSetType(resultSetType). For example,

import java.sql.*;
 
public class SupportedResultSetTypeTest {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx");  // MySQL
      ) {
         DatabaseMetaData md = conn.getMetaData();   // Get the database metadata
         // Verify ResultSet's type
         System.out.println("-- ResultSet Type --");
         System.out.println("Supports TYPE_FORWARD_ONLY: "
                 + md.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
         System.out.println("Supports TYPE_SCROLL_INSENSITIVE: "
                 + md.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
         System.out.println("Supports TYPE_SCROLL_SENSITIVE: "
                 + md.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
 
         // Verify ResultSet's concurrency
         System.out.println("-- ResultSet Concurrency --");
         System.out.println("Supports CONCUR_READ_ONLY: "
                 + md.supportsResultSetType(ResultSet.CONCUR_READ_ONLY));
         System.out.println("Supports CONCUR_UPDATABLE: "
                 + md.supportsResultSetType(ResultSet.CONCUR_UPDATABLE));
 
         // Verify ResultSet's cursor holdability
         System.out.println("-- ResultSet Cursor Holdability --");
         int holdability = md.getResultSetHoldability();  // Get the default holdability
         if (holdability == ResultSet.CLOSE_CURSORS_AT_COMMIT) {
            System.out.println("Default: CLOSE_CURSORS_AT_COMMIT");
         } else if (holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT) {
            System.out.println("Default: ResultSet.HOLD_CURSORS_OVER_COMMIT");
         }
         System.out.println("Supports CLOSE_CURSORS_AT_COMMIT: "
                 + md.supportsResultSetType(ResultSet.CLOSE_CURSORS_AT_COMMIT));
         System.out.println("Supports HOLD_CURSORS_OVER_COMMIT: "
                 + md.supportsResultSetType(ResultSet.HOLD_CURSORS_OVER_COMMIT));
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
   }
}
Example: Scrollable ResultSet

Set the ResultSet’s type to TYPE_SCROLL_INSENSITIVE, as follows. You could then use methods such as first(), last(), previous(), next(), absolute(rowNumber), relative(rowNumber), beforeFirst(), afterLast(), moveToCurrentRow(), moveToInsertRow() to move or position the cursor. You can use getRow() to get the current row number. Take note that row number starts at 1.

import java.sql.*;
 
public class ScrollableResultSetTest {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx"); // MySQL
 
         //  TYPE_SCROLL_INSENSITIVE: ResultSet is scrollable and does not
         //    reflect changes by others while it is opened for processing.
         //  CONCUR_READ_ONLY: ResultSet is not updatable
         Statement stmt = conn.createStatement(
               ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      ) {
         ResultSet rset = stmt.executeQuery("select * from books");
 
         rset.last();
         System.out.println("-- LAST ROW --");
         System.out.println(
                 rset.getRow() + ": " + + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
 
         rset.beforeFirst();
         System.out.println("-- ALL ROWS --");
         while(rset.next()) {
            System.out.println(
                    rset.getRow() + ": " + rset.getInt("id") + ", "
                    + rset.getString("title") + ", " + rset.getString("author") + ", "
                    + rset.getDouble("price") + ", " + rset.getInt("qty"));
         }
 
         rset.absolute(3);  // Row number starts at 1
         System.out.println("-- ABSOLUTE ROW 3 --");
         System.out.println(
                 rset.getRow() + ": " + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
 
         rset.relative(-2);
         System.out.println("-- RELATIVE ROW -2 --");
         System.out.println(
                 rset.getRow() + ": " + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
   }
}
Example: Updatable ResultSet

Set the ResultSet’s concurrency to CONCUR_UPDATABLE as follows. You can use UpdateXxx() methods to set the new content of a particular cell of the current row. You can then use updateRow() or deleteRow() to update or delete the current row; or insertRow() to insert the staging row.

import java.sql.*;
 
public class JdbcUpdatableResultSetJDK7 {  // JDK 7 and above
   public static void main(String[] args) {
      try (
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx"); // MySQL
 
         //  TYPE_SCROLL_INSENSITIVE: ResultSet is scrollable and does not
         //    reflect changes by others while it is opened for processing.
         //  CONCUR_UPDATABLE: ResultSet is updatable
         Statement stmt = conn.createStatement(
               ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ) {
         conn.setAutoCommit(false);  // Disable auto-commit
 
         ResultSet rset = stmt.executeQuery("select * from books");
 
         // Update a row
         rset.last();
         System.out.println("-- Update a row --");
         System.out.println(
                 rset.getRow() + ": " + + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
         rset.updateDouble("price", 99.99);   // update cells via column name
         rset.updateInt("qty", 99);
         rset.updateRow();  // update the row in the data source
         System.out.println(
                 rset.getRow() + ": " + + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
 
         // Delete a row
         rset.first();
         System.out.println("-- Delete a row --");
         System.out.println(
                 rset.getRow() + ": " + + rset.getInt("id") + ", "
                 + rset.getString("title") + ", " + rset.getString("author") + ", "
                 + rset.getDouble("price") + ", " + rset.getInt("qty"));
         rset.deleteRow();  // delete the current row
 
         // A updatable ResultSet has a special row that serves as a staging area
         // for building a row to be inserted.
         rset.moveToInsertRow();
         rset.updateInt(1, 8001);  // Update cells via column number (starts at 1)
         rset.updateString(2, "Even More Programming");
         rset.updateString(3, "Kumar");
         rset.updateDouble(4, 77.88);
         rset.updateInt(5, 77);
         rset.insertRow();
         // After inserting a row, you need to move the cursor to another row, to avoid
         //  unexpected error if another part of the program also accesses this ResultSet.
         rset.beforeFirst();
 
         conn.commit();  // commit changes
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
   }
}

How to Detect Empty ResultSet

The first invocation of rset.next() shall return false, indicating empty ResultSet. You can re-write the ResultSet processing codes as follows:

if (!rset.next()) {
   // ResultSet is empty
   ......
} else {
   // Process the ResultSet. Cursor pointing at first row
   do {
      ......
   } while (rset.next());
}

How to Get the Number of Rows of a ResultSet

Unfortunately, JDBC's ResultSet does not offer any method to return the number of rows in the ResultSet. (It does provide the number of columns in the ResultSetMetaData.) This is because most DBMS starts sending the data back to the client (once the buffer is filled) before it knows how many rows qualified.

These are the suggestions:

  1. Issue a prior query "SELECT COUNT(*) FROM tableName WHERE ..." to get the number of rows, before issuing the proper query. The drawback is two queries are needed.
  2. For JDBC 2.0's scrollable ResultSet, you could move the cursor to the last row (via rset.last()) and use the rset.getRow() to get the row number (row number starts at 1). Take note that rset.last() returns false for an empty ResultSet; rset.getRow() returns 0 if there is no current row. You can then use rset.beforeFirst() to rewind the ResultSet. 
    int rowCount = rset.last() ? rset.getRow() : 0;
rset.beforeFirst();  // so that you can process your ResultSet
    Drawback: the method last() is an expensive operation, which forces the DBMS to retrieve the entire ResultSet. The previous query could be less expensive! Furthermore, you need a JDBC's driver that supports scrollable ResultSet.

More on Transaction

As discussed earlier, you could disable the auto-commit, and use commit/rollback to group a set of SQL statements as a transaction.

Transaction Isolation Level

[TODO]

Savepoints

Within a transaction, you can use conn.setSavepoint() to set a particular Savepoint. You could then rollback to the Savepoint, via rollback(savePoint).

Example [TODO]

RowSet Interface

So far, we have been using ResultSet to store the SELECT query result. Use of ResultSet requires programmers to manage the Connection and Statement explicitly. JDBC 2.0 introduces a new RowSet interface, which manages Connection and Statement for you automatically and greatly simplifies your programming for enterprise web applications.

To use a RowSet:

  1. Construct an instance of RowSet;
  2. Set the properties and query statement of the RowSet;
  3. Invoke execute() to execute the query.

You do not have to manage the Connection and Statement. For example,

JdbcRowSet rowSet = new JdbcRowSetImpl();
rowSet.setUrl(databaseUrl);
rowSet.setUsername(username);
rowSet.setPassword(password);
rowSet.setCommand("SELECT * FROM books"); // Set query command
rowSet.execute();                         // Run command

RowSet implements ResultSet interface and therefore share its capabilities. RowSet adds these new capabilities:

  1. A RowSet object is scrollable and updatable by default.
  2. RowSet support JavaBeans component model and can be used in a visual environment. It also supports JavaBeans Event model for events such as cursor movement; update, insertion, or deletion of a row; and change to the entire RowSet contents.
JDBC_RowSet_Classes.png

There are two types of RowSet: connected and unconnected. A connected RowSet remains connected to the database, until the application terminates. Changes to a connected RowSet are reflected in the underlying data source. An unconnected RowSet closes the connection after the query. You may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated into the data source after a disconnected RowSet reestablished the connection with the data source.

There are 5 implementations of RowSet as illustrated:

  1. JdbcRowSet: a connected RowSet, which can be considered as a wrapper to a scrollable and updatable ResultSet.
  2. CachedRowSet: defines the basic capabilities of disconnected RowSet, which can re-connect to the data source to update the changes made while disconnected.
  3. WebRowSet: sub-interface of CachedRowSet, which uses XML documents.
  4. JoinRowSet: sub-interface of WebRowSet, which can form the equivalent of a SQL JOIN without having to connect to a data source.
  5. FilteredRowSet: sub-interface of WebRowSet, which can perform query on a RowSet object without having to connect to a data source.
Example: JdbcRowSet

JdbcRowSet is a connected RowSet that wraps around a scrollable and updatable ResultSet (TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE). An implementation is provided in com.sun.rowset.JdbcRowSetImpl. For example,

import java.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.JdbcRowSetImpl;
 
public class JdbcRowSetTest {
   public static void main(String[] args) {
      String databaseUrl = "jdbc:mysql://localhost:8888/ebookshop";
      String username = "myuser";
      String password = "xxxx";
      JdbcRowSet rowSet = null;
 
      try {
         // No need to allocate Connection and Statement,
         // Simply allocate a RowSet instance
         rowSet = new JdbcRowSetImpl();
         rowSet.setUrl(databaseUrl);
         rowSet.setUsername(username);
         rowSet.setPassword(password);
         rowSet.setCommand("SELECT * FROM books");
         rowSet.execute();
 
         // RowSet is scrollable and updatable
         // Update a row
         rowSet.last();
         System.out.println("-- Update a row --");
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
         rowSet.updateDouble("price", 99.99);   // update cells
         rowSet.updateInt("qty", 99);
         rowSet.updateRow();  // update the row in the data source
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
 
         // Delete a row
         rowSet.first();
         System.out.println("-- Delete a row --");
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
         rowSet.deleteRow();  // delete the current row
 
         // A updatable ResultSet has a special row that serves as a staging area
         // for building a row to be inserted.
         rowSet.moveToInsertRow();
         rowSet.updateInt(1, 8104);  // Use column number
         rowSet.updateString(2, "Even More Programming");
         rowSet.updateString(3, "Kumar");
         rowSet.updateDouble(4, 77.88);
         rowSet.updateInt(5, 77);
         rowSet.insertRow();
         rowSet.beforeFirst();
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      } finally {
         try {
            rowSet.close();
         } catch (SQLException ex) {
            ex.printStackTrace();
         }
      }
   }
}

In JDK 7 (with RowSet 1.1), you can use a RowSetFactory to create a JdbcRowSet object. For example,

import java.sql.*;
import javax.sql.rowset.*;
 
public class JdbcRowSetTestJDK7 {  // JDK 7
   public static void main(String[] args) {
      String databaseUrl = "jdbc:mysql://localhost:8888/ebookshop";
      String username = "myuser";
      String password = "xxxx";
 
      // Get an instance of RowSetFactory
      RowSetFactory rowSetFactory = null;
      try {
         rowSetFactory = RowSetProvider.newFactory();
      } catch (SQLException ex) {
         ex.printStackTrace();
         return;
      }
 
      try (
         // Use RowSetFactory to allocate a RowSet instance.
         JdbcRowSet rowSet = rowSetFactory.createJdbcRowSet();
      ) {
         rowSet.setUrl(databaseUrl);
         rowSet.setUsername(username);
         rowSet.setPassword(password);
         rowSet.setCommand("SELECT * FROM books");
         rowSet.execute();
 
         // RowSet is scrollable and updatable
         // Update a row
         rowSet.last();
         System.out.println("-- Update a row --");
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
         rowSet.updateDouble("price", 99.99);   // update cells
         rowSet.updateInt("qty", 99);
         rowSet.updateRow();  // update the row in the data source
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
 
         // Delete a row
         rowSet.first();
         System.out.println("-- Delete a row --");
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
         rowSet.deleteRow();  // delete the current row
 
         // A updatable ResultSet has a special row that serves as a staging area
         // for building a row to be inserted.
         rowSet.moveToInsertRow();
         rowSet.updateInt(1, 8303);  // Use column number
         rowSet.updateString(2, "Even More Programming");
         rowSet.updateString(3, "Kumar");
         rowSet.updateDouble(4, 77.88);
         rowSet.updateInt(5, 77);
         rowSet.insertRow();
         rowSet.beforeFirst();
 
      } catch(SQLException ex) {
         ex.printStackTrace();
      }
   }
}
Example: CachedRowSet

CachedRowSet is a unconnected RowSet that wraps around a scrollable and updatable ResultSet. CachedRowSet disconnects from the data source after an SELECT query and caches the query result in memory. For unconnected RowSet, a call to acceptChanges() reconnected to the data source to apply changes in the RowSet. An implementation is provided in com.sun.rowset.CachedRowSetImpl.

For example,

import java.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.CachedRowSetImpl;
 
public class CachedRowSetTest {
   public static void main(String[] args) {
      String databaseUrl = "jdbc:mysql://localhost:8888/ebookshop";
      String username = "myuser";
      String password = "xxxx";
      Connection conn = null;
      CachedRowSet rowSet = null;
 
      try {
         conn = DriverManager.getConnection(databaseUrl, username, password);
         conn.setAutoCommit(false);  // Need to disable auto-commit for CachedRowSet
 
         rowSet = new CachedRowSetImpl();
         rowSet.setUrl(databaseUrl);
         rowSet.setUsername(username);
         rowSet.setPassword(password);
         rowSet.setCommand("SELECT * FROM books");
         int [] keys = {1};  // Set column 1 as the key column in the RowSet
         rowSet.setKeyColumns(keys);
         rowSet.execute(conn);  // execute on the Connection
 
         // RowSet is scrollable and updatable
         // Update a row
         rowSet.first();
         System.out.println("-- Update a row --");
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
         rowSet.updateDouble("price", 99.99);   // update cells
         rowSet.updateInt("qty", 99);
         rowSet.updateRow();  // update the row in the data source
         System.out.println(
                 rowSet.getRow() + ": " + + rowSet.getInt("id") + ", "
                 + rowSet.getString("title") + ", " + rowSet.getString("author") + ", "
                 + rowSet.getDouble("price") + ", " + rowSet.getInt("qty"));
 
         // A updatable ResultSet has a special row that serves as a staging area
         // for building a row to be inserted.
         rowSet.moveToInsertRow();
         rowSet.updateInt(1, 8909);  // Use column number
         rowSet.updateString(2, "Even More Programming");
         rowSet.updateString(3, "Kumar");
         rowSet.updateDouble(4, 77.88);
         rowSet.updateInt(5, 77);
         rowSet.insertRow();
         // need to move away from insert row before apply changes
         rowSet.moveToCurrentRow();
 
         // Reconnect to data source to apply change in the RowSet.
         rowSet.acceptChanges(conn);  // On non-autocommit Connection
      } catch(SQLException ex) {
         ex.printStackTrace();
      } finally {
         try {
            rowSet.close();
            conn.close();
         } catch (SQLException ex) {
            ex.printStackTrace();
         }
      }
   }
}

For CachedRowSet, we need to set a key column for the RowSet. Furthermore, the applyChanges(conn) must be run on a non-autocommit Connection.

Connecting with DataSource

A JDBC program can connect to a target data source using one of two classes:

  • DriverManager: It connects an application to a data source, via a database URL specified in its static method getConnection(). For example, 
    // MySQL
Connection conn = DriverManager.getConnection("jdbc:mysql://{host}:{port}/{db-name}", "{user}", "{password}");
// MS Access
Connection conn = DriverManager.getConnection("jdbc:odbc:{odbc-name}");
    Since JDK 6 (with JDBC 4.0), DriverManager automatically loads the JDBC driver found in the classpath. [But You need to manually load the JDBC driver prior to JDK 6.]
  • DataSource: DriverManager is much easier to use. But the DataSource interface makes available the details of the underlying data source to your application. DataSource can provide connection pooling (re-using a pool of connections, instead of opening/closing each connection, due to its high overhead) and distributed transactions (on multiple database systems), which are essential for Java EE applications, in particular EJB (Enterprise JavaBeans).

To set up a DataSource:

  1. Create an instance of the DataSource.
  2. Set its properties.
  3. Register it with a naming service that uses the Java Naming and Directory Interface (JNDI).

DataSource for Connection Pooling: [TODO]

REFERENCES & RESOURCES

  1. JDBC Online Tutorial @ http://download.oracle.com/javase/tutorial/jdbc/index.html.
  2. JDBC Home Page @ http://java.sun.com/products/jdbc/overview.html.
  3. JDBC API Specifications 1.2, 2.1, 3.0, and 4.0 @ http://java.sun.com/products/jdbc.
  4. White Fisher, et al., "JDBC API Tutorial and Reference", 3rd eds, Addison Wesley, 2003.
  5. MySQL Home Page @ http://www.mysql.org, and documentation.
  6. MySQL 5.5 Reference Manual @ http://dev.mysql.com/doc/refman/5.5/en/index.html.
  7. SQL.org @ http://www.sql.org.

Latest version tested: JDK 1.7.0/JDBC 4.0, MySQL 5.5
Last modified: October, 2011