Data! You can't live without it. Wherever you go, whatever you touch, information is continually being flashed before us. It wasn't so long ago that people were complaining of not having enough information and now our medical experts are telling us to take days off due to the information overload some are experiencing.

However, the problem isn't the volume of data that we are presented with - it's the format. We seem to spend most of our time trying to sift out the useful information from the data noise that seems to interfere with our daily lives. It is easy to blame someone else for this and not take the responsibility ourselves, while in actual fact everyone has a duty to ensure data integrity and accuracy.

This miniseries of articles looked at different ways in which data can be manipulated by the Java developer. So far, we have looked at the storage and retrieval of simple one-off data values by presenting the implementation for a simple INI file structure. Next, we looked at storing more complicated data types using Java's Object Serialization interfaces and how easy it was to store our objects for use at a later date. This month we move the series on to the next level, which explores storing significant quantities of data, enough to warrant the use of an external database. This column will cover JDBC, the Java interface for databases. We will look at what it is, how to use it and where to use it. So without further ado, let us begin.

JDBC...
JDBC, or to give it its full name, Java Database Connectivity, is a set of classes that allows easy integration between Java classes and external databases. JDBC presents the user with a consistent interface to a database, irrespective of the data engine. For example, JDBC affords the developer the ability to test and develop using an Access database, and for their application to ship which then talks to an Oracle database, all without having to recompile a single line of code. It is this power that has made JDBC one of the most used APIs of the complete JDK package list, and once a developer has mastered the few classes that make up the JDBC package, there is no looking back. So how does it work?

JDBC can be broken into two distinct parts: the developers API and the service providers API. The developers API is what we are presented with in the JDK, and it is this one we use to insert and query data into our tables. The service providers API is the other side of the equation. It is this API that a service provider implements to develop a JDBC driver which sits in between JDBC and the actual database. For example, Oracle provides a free JDBC driver for use with their database, while Sybase provides one for use with theirs. When a developer wishes to change the database engine they are using, they merely replace the JDBC driver. The majority of mainstream databases have JDBC drivers available for them, including the standard ODBC driver that ships with the JDK.

JDBC uses the standard query language, or SQL, to facilitate the communication between the developer and the database, performing all translations transparently. For example, Microsoft uses a different flavor of SQL from Oracle. Some queries simply will not work in Access but will operate perfectly in Oracle. Fortunately, Oracle implements the true SQL interface, but as a developer you don't need to worry about these inconsistencies. Assuming your SQL is standard, JDBC will perform any conversion that may be necessary between different database standards.

We will look at the major stages in developing communications with a database: connection, querying and updating.

Connecting
Before you start using any tables, you must first make a connection to the driver, which in turn makes a connection to the actual database. The best way to illustrate this procedure is to use an example. The code below shows the basic stages in setting this up:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection con = DriverManager.getConnection( "jdbc:odbc:testdb", "nary", "ceri" );

The first call ensures that the class loader can find the JDBC driver that we intend to use. In this instance we will use the standard one that ships with the NT/95 version of the JDK, which uses the JDBC-ODBC bridge for the connection manager. Having successfully found the correct class, the DriverManager class attempts to retrieve a Connection instance to the specified database. This can be thought of as a login session to a given database. It is not uncommon for several connections, or sessions, to be open at once. Notice the parameters to the getConnection method; we have a URL to the database, a user name and password. One of the advantages of addressing the database in this manner is that it allows for the database to reside on a remote machine. Having retrieved the Connection, we can now start to query and use the database.

Querying data
One of the most common functions a database performs is that of holding data for possible future use; the way in which we retrieve data is through the use of SQL Select statements. We create the SQL statement using standard ASCII strings and then pass them to JDBC, which passes them on to the database for execution.

SQL presents the results from such queries as a table which can be read column by column, row by row. JDBC provides a class, ResultSet, to make reading this result table a trivial matter. Listing 1 is an example of reading all the results when the table is queried for all names beginning with 'A'.

The ResultSet is a class that maintains a cursor into the results table, which can be advanced using a call to the next() method. An important thing to remember is that once a row has been advanced, it can not be revisited. The query would have to be rerun in order to read the row again. Retrieving individual columns can be achieved using a variety of getXXX(...) type methods from the ResultSet class. For instance, in our example we use both the getString(...) and getInt(...) class to retrieve all the column values. The columns are retrieved using column indexes, starting at 1, and occurring in the same order they are specified in the SQL statement. It is important to use the correct getXXX(...) method for the column data, since a some castings will throw an exception. For example, if you try to use a getLong() method to retrieve a column with text in it, then an exception will most definitely occur. However, you could use a getString(...) method to retrieve a column that has nothing but integers in it. Just be careful when choosing your retrieval method.

Storing/Updating/Deleting data
Storing data is as straightforward as retrieving it. Using the SQL statement INSERT, we can pass data for inclusion via JDBC. We can build up the SQL INSERT statement, using standard ASCII strings, as in the example shown in Listing 2.

Once the statement has been constructed, it is run using the executeUpdate(...) method. This will return a variety of different results depending on the SQL being run. In this example, the method would return the row number where the insert took place.

Using these same procedures, we can use the executeUpdate(...) method for both the other popular SQL statements: UPDATE and DELETE.

Summary
This article took a whistle stop tour of the functionality provided by JDBC. The main reason for this was to introduce JDBC to you before we look at the final article in this miniseries, the database layer provided by Symantec's Visual Cafe. This mini-series explained the whole issue of data storage and the differing options depending on the volume of data being handled. As each article progressed, the level of complexity also increased, culminating to complete database access, using JDBC.

In the next column we will look closely at how Symantec has provided a complete set of wrapper classes that makes even the simple interface of JDBC even simpler to use.