Databases (Java Distributed Computing)

This chapter is a brief introduction to the topic of integrating databases into networked Java applications. As with many of the topics in this book, the development of database applications is a subject that can fill a book of its own. And it has, many times over. For the purposes of this book, rather than trying to cover the gamut of database design and development in depth, we will restrict this chapter to a discussion of the following topics:

We assume that the reader has a basic understanding of general relational database topics, such as SQL and the layout of data into tables and records. For more in-depth coverage of the JDBC package than we will be able to provide here, refer to George Reese's book, Database Programming with JDBC and Java (O'Reilly & Associates).

7.1. An Overview of JDBC

JDBC is the database connectivity package included in the core Java API.[1] JDBC gives you a database-independent interface for opening a connection to a relational database, issuing SQL calls to the database, and receiving a set of data as the result. In more technical terms, JDBC acts as a Java implementation of the standard SQL call-level interface (CLI) defined by X/Open and supported by most major relational database vendors. In order to perform transactions with a specific type of database, you need to have a JDBC driver that acts as a bridge between the JDBC method calls and the native database interface.

7.1.1. Data Retrieval Example

Perhaps the easiest way to get started with JDBC is to see a simple example of the API in action. Example 7-1 shows a Java code segment that opens a database connection, executes a query, and iterates through the results.

Example 7-1. A Simple JDBC Database Query

// Construct the database address
String dbaseURL = "jdbc:mysubprotocol://dbasehost/dbasename";
// Make the database connection
Connection dbConnection =
  DriverManager.getConnection(dbaseURL, "dbaseuser", "dbasepasswd");
// Create a statement and execute the SQL query
Statement query = dbConnection.getStatement();
ResultSet results =
  query.executeQuery("SELECT first_name, last_name from user_table");

// Iterate through the results and print them to standard output
while (results.next()) {
  String fname = results.getString("first_name");
  String lname = results.getString("last_name");
  System.out.println("Found user \"" + fname + " " + lname + "\"");
}

This URL is passed to the JDBC DriverManager's getConnection() method, along with an account name and password, to open a connection to the database:

Once the connection is created, we construct a Statement object, which is used to issue an SQL query. In this case, we're retrieving all of the first and last names (user_fname and user_lname) from a table of user information (user_table). The results of the query are returned by JDBC as a ResultSet object:

Finally, we can iterate over the results of the query and present them to the user:

In the following sections, we'll examine JDBC in more detail to see how this and other database interactions are supported.

7.1.2. The API at a Glance

The JDBC API offers you interfaces that mirror the basic concepts surrounding relational databases. These interfaces, all part of the java.sql package, include interfaces for a DriverManager, a Connection, a Statement, and a ResultSet.

7.1.2.1. DriverManager

The DriverManager class provides the means to load database drivers into a Java application or applet; it is the primary way in JDBC to establish a connection to a database. A Java application first creates a DriverManager instance, then connects to a database by calling the DriverManager's static getConnection() method, passing a URL-like reference to the database as a method argument. The DriverManager searches the set of available drivers for one that can support a connection to the referenced database. If it finds one, it passes the database address to the driver and asks it to create a connection. The connection to the database is returned in the form of a Connection object (described later).

All JDBC drivers provide an implementation of the java.sql.Driver interface. When a DriverManager is created, it attempts to load a set of drivers specified by the sql.Driver's Java property, which can be set to a list of colon-delimited Driver class names. Additional drivers can also be loaded explicitly in the Java application as needed. When a Driver class is loaded into the Java runtime, it's responsible for creating an instance of itself and registering this instance with the resident DriverManager. So any additional drivers needed by an application can be loaded explicitly by using the Class.forName() method:

Since the Driver class automatically registers itself with the DriverManager, there really isn't any reason to keep the reference to the Driver. You'll often see drivers loaded by just calling the forName() method and later referencing the driver by name when a database connection is made.

7.1.2.2. Connection

Once the necessary drivers have been loaded by the DriverManager, a connection to a database can be made through the getConnection() method of the DriverManager class. The desired database is specified with a String argument that acts as a URL-like address to the database. There is no standard format for this database address string; the DriverManager simply passes it to each loaded JDBC driver in turn and asks if it understands and supports the type of database being addressed. Typically, the database address will include explicit information about the type of driver to be used to make the connection. For example, JDBC drivers using ODBC protocol to establish database connections usually use addresses of the form:

where financedata is a local data source. Access to a remote database from a local client may involve an address of a slightly different form:

where <sub-protocol> specifies a database connection service and <sub-name> provides all of the information that the underlying service will need to find the database and connect to it. So in the remote database URL shown above, drvr is the sub-protocol, specifying a specific driver to use to connect to our database. The dataserver.foobar.com:500/financedata portion of the URL acts as the sub-name, and gives the information the driver needs to find our database. Other drivers may require you to specify sub-protocols and sub-names differently. You should consult the documentation for the JDBC drivers you're using to find out what form your database URLs should take.

The getConnection() method on DriverManager either returns a Connection object that represents the connection to the named database, or throws an exception if the connection couldn't be established.

7.1.2.3. Statement

The Connection interface allows the user to create query statements to the database. Query statements are represented as Statement objects or subclasses. The Connection interface provides three methods for creating database query statements: createStatement(), prepareStatement(), and prepareCall(). The createStatement() method is used for simple SQL statements that don't involve any parameters. This returns a Statement object that can be used to issue SQL queries to the database, normally using its executeQuery() method. This method accepts an SQL statement as a string argument, and the results of the statement are returned in the form of a ResultSet object (described later). Other methods available in the Statement interface for issuing SQL statements to the database are execute(), which is used for SQL queries that can return multiple result sets, and executeUpdate(), which can be used to issue INSERT, UPDATE, or DELETE statements.

In addition to the basic Statement interface, a Connection object can be used to create precompiled PreparedStatement s, and CallableStatements that represent stored procedures in the database. An SQL statement involving input parameters, or a statement that you want to execute multiple times, can be created using the prepareStatement() method on a Connection object, which returns a PreparedStatement object. The SQL statement passed into the prepareStatement() method is precompiled so that multiple executions of the statement will be more efficient. This subclass of Statement supports setting the values of precompiled input parameters through a series of setXXX() methods. The PreparedStatement object has an executeQuery() method that requires no arguments, and instead executes the precompiled SQL statement on the database. Note that not all database vendors or JBC drivers support precompiled statements, so check your DBMS documentation and JDBC driver specifications to see if you can use PreparedStatements.

A stored SQL procedure can be accessed through an SQL statement created through the prepareCall() method on a Connection object. This method returns a CallableStatement object, which lets you set input parameters and get output parameters from the statement.

By default, the JDBC package is configured to commit each Statement issued through a Connection. If you need to do rollbacks of transactions, or you want to commit multiple statements as a single transaction, or both, you can disable the autocommit feature by calling Connection.setAutoCommit(false) . Then a sequence of Statements can be created from a Connection object, executed against the database, and the entire transaction can be committed as one transaction by calling the Connection's commit() method. If you want to abort the transaction, you can call the Connection's rollback() method.

7.1.2.4. ResultSet

Rows of data returned from the execution of a statement against a database are represented as ResultSet objects in JDBC. For example, the executeQuery() method of the Statement interface returns a ResultSet object. A ResultSet object provides ways to iterate through the rows of data returned as the result of an SQL query, through its next() method; data fields within each row can be retrieved by name or by column index number using its getXXX() methods. The user needs to know the type of data to expect in each column of returned data, since each data item is retrieved through type-specific getXXX() methods.

Depending on how your JDBC driver is implemented, iterating through the rows of data in a ResultSet may cause individual data fetches from the database, or it may simply pull each row of data from a local cache. If the performance of your data transactions is an issue in your application, you should determine how returned data is handled by your vendor's drivers.

Chapter 7. Databases

Contents: