JDBC (Java DataBase Connectivity)

JDBC (also known as the java.sql package is an API that allows a Java program to communicate with a database server using SQL commands. "The reason we think that Java and JDBC have an essential advantage over other database programming environments is that Java and JDBC offer platform and vendor independence." [Horstmann98, p187]

Note that JDBC is an SQL API, not an embedded SQL mechanism for Java. [Flanagan97b, p324]

The ultimate goal of JDBC is to make possible the following: [Horstmann98, p190]

Programmers can write applications in Java to access any database, using standard SQL statements (or even specialized extensions of SQL) while still following Java language conventions.
Database vendors and database tool vendors can supply the low-level drivers. Thus, they can optimize their drivers for their specific situation.

Microsoft's ODBC standard provided a vendor-independent, call-level interface to SQL for C. JavaSoft has offered the following response to the concern that they created a new model instead of adopting the ODBC model: [Horstmann98, p190]

ODBC is hard to learn.
ODBC has a few commands with lots of complex options. The Java style is to have simple and intuitive methods, but to have lots of them.
ODBC relies on the use of void* pointers and other C features that are not natural in Java.
It was felt to be too hard to map ODBC to Java because of the frequent use of multiple pointers and pointer indirection.

The java.sql package provides a fairly straightforward mechanism for sending SQL queries to a database and for receiving query results. If the programmer already has experience working with databases and SQL, this chapter is relatively intuitive. Before you can get started, you need a database and a JDBC driver that supports it. The database installed for this course is InstantDB.

InstantDB is a 100% Java, Relational Database Management System (RDBMS). It is free to non-commercial and non-government organizations. Features supported include joins, transactions, triggers, sub-selects, table aliasing, and much more than you would normally expect in a free RDBMS. InstantDB is accessed via its own JDBC driver. It can be downloaded at the first URL below. The second URL provides a list of JDBC-capable databases.

   http://www.instantdb.co.uk/frmindex.htm
   http://java.sun.com/products/jdbc/jdbc.drivers.html

The four main activities in JDBC programming are:

Registering a database driver (line 8)
Using the DriverManager class to obtain a Connection object that represents a database connection (line 10)
Sending an SQL statement to the database using a Statement object (line 20)
And retrieving the results of a query with a ResultSet object (line 22)

One of the interesting things about the java.sql package is that its most important members (Connection, Statement, ResultSet) are interfaces instead of classes. The whole point of JDBC is to hide the specifics of accessing particular databases, and these interfaces make that possible. A JDBC driver is a set of classes that implement the interfaces for a particular database. As an application programmer, all you have to worry about is writing code that uses the methods defined by the interfaces. [Flanagan97b, p325]

The DriverManager class is responsible for keeping track of all the JDBC drivers that are available on a system. The first task of a JDBC program is to load an appropriate driver for the installed database. The Class.forName() method provides one way of doing this. The string that is passed is the "fully qualified" class name of the driver (relative to the CLASSPATH environment variable. In this example, CLASSPATH was set with

   set CLASSPATH=%CLASSPATH%;c:\jdk1.2\InstantDB\classes

The "jdbc" substring on line 8 is a subdirectory in the "classes" directory above, and "idbDriver" is the name of a .class file in that subdirectory. When this driver class is loaded, a static initializer in the class takes care of registering the driver with the DriverManager. Note that this step simply loads and registers the driver, but "selecting" the driver happens in the next step. [The newInstance() call at line 9 was used in one author's discussion, but it does not seem to be necessary.]

The "selection of" (or "connection to") the database happens at line 10. A jdbc: URL is required. The general form (for Unix systems) is the first line below. The specific form for DOS and InstantDB are the last two lines.

   jdbc:subprotocol://host:port/databasename
   jdbc:idb:vlhdb.prp
   jdbc:idb:c:\\jdk1.2\\InstantDB\\vlh\\vlhdb.prp

The "subprotocol" is used by the DriverManager to search its internal dictionary of registered drivers. The host and port have been allowed to default. The remainder of the URL is the name of an InstantDB properties file. An InstantDB database is almost entirely defined by its properties file. The second line above assumes the file is in the current directory. The third line provides a "fully qualified" pathname. Line 11 creates an object that knows how to "execute" an SQL statement. There are three different "execute" methods:

executeUpdate() - for SQL statements that return how many rows were affected (insert, update, delete)
executeQuery() - for SQL statements that return a single ResultSet (select)
execute() - any kind of SQL statement (create table, insert, select, update, drop table)

This example exercises the last method and demonstrates how the return value and subsequent method invocations can be used to achieve the functionality of the other two. (lines 20-26)

The method printResultsTable() came from [Flanagan97b, p329] but is not reproduced here. The inner try / catch block allows SQL errors to be handled without shutting down the entire application.