Getting Up To Date with JDBC API

The JDBC API is Java's way of accessing any tabular data source, typically a relational database. The API presents an object-oriented interface to the data and is made up of classes and interfaces in the java.sql package, with standard extensions in javax.sql. Since JDBC 1.0, a lot of functionality has been added to the API. JDBC 2.0 introduced scrollable result sets, updatable result sets, batch update capability and support for new SQL3 data types. The new JDBC 3.0 specification, in its proposed final draft form at this point, is promising to add more robust transactions by way of savepoints, a number of resource pooling enhancements, retrieval of auto-generated keys and a lot more. Through all of the additional functionality, the API has remained very straightforward and very easy to work with. This article discusses a number of these new areas of functionality.

A Quick JDBC Primer

The four basic steps involved with using JDBC to connect to a database are:

• Load the appropriate JDBC driver
• Request a connection to the database
• Send SQL to the database
• Process results, if appropriate

Loading the driver is accomplished either by setting the jdbc.drivers system property, or by dynamically loading the appropriate driver class with a call to Class.forName(). The following command line launches the com.ociweb.jdbc.MyApplicationName application and loads the sun.jdbc.odbc.JdbcOdbcDriver driver. This particular driver is used to connect to ODBC data sources, but the procedure is the same for all JDBC drivers.

java Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver com.ociweb.jdbc.MyApplicationName

The other approach is to load the driver by calling Class.forName().

try {

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

} catch (ClassNotFoundException exc) {

}

The appropriate driver must be loaded before a connection to the database can be established. Once the driver is loaded, a connection to the database can be established with a call to DriverManager.getConnection(). There are several versions of the getConnection method that accept different parameters. The simplest version accepts a single String argument that is a URL to a database. The format of the URL is:

jdbc:sub-protocol:sub-name

The sub-protocol portion of the URL is used by the DriverManager class to locate the appropriate driver. The sub-name portion of the URL is used by the driver to identify which database to connect to. The specifics of the sub-name vary from vendor to vendor so refer to the documentation for your specific JDBC driver. For example, a database URL to an ODBC data source might look like jdbc:odbc:HR. In this particular case, the sub-name is HR and this should be the name of an ODBC data source.

// Load the JDBC-ODBC bridge driver used

// for connecting to an ODBC data source...

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



// declare a URL to an ODBC data source named HR

String dbUrl = "jdbc:odbc:HR";



// Retrieve a connection to the database...

Connection conn = DriverManager.getConnection(dbUrl);

Once a connection to the database has been established, SQL can be sent to the database through a Statement object. The Connection interface defines a createStatement() method which returns a Statement that may be used to issue SQL commands on that Connection. There are two methods in the Statement interface for sending SQL, executeQuery() and executeUpdate(). Each of these methods accept a String argument. The executeQuery() method expects an SQL "select" statement as an argument and will return a ResultSet containing the results of the query.

The ResultSet interface defines the next() method which is used to iterate over the results. The ResultSet interface also defines numerous get methods for retrieving individual columns out of the results:

Statement stmt = conn.createStatement();

String sql = "select first_name, last_name from users";

ResultSet results = stmt.executeQuery(sql);



while(results.next()) {

    // the integer arguments to the getString() method 

    // here correspond with the columns specified in 

    // the select statement note that the integer 

    // arguments are NOT zero based

    // the first column is index 1

    String firstName = results.getString(1);

    String lastName = results.getString(2);

    // do something with the data...

}

The executeUpdate() method in the Statement interface expects an SQL statement that is updating the database, not selecting data from the database:

Statement stmt = conn.createStatement();
String sql = "update users set eligible_flag = 'Y' where age > 66";
// the return value of executeUpdate() indicates
// how many rows were affected by the update...
int n = stmt.executeUpdate(sql);
System.out.println(n + " rows were updated.");

Those are the very basics of using JDBC to issue simple selects and updates to the database. The JDBC API provides much more functionality.