by George Reese
You have very likely used the file system of your applications' host operating system to save important information related to the work your applications do. For applications such as word processors and graphics applications, storing user data in files works very well. In fact, most traditional PC applications work fine using file-based data storage.
As your applications grow in complexity, however, your data needs can increase in complexity as well. A file system allows an application to save information in a very static manner. If, for example, you want to save information about your compact disc collection, you may choose to store it in a spreadsheet. Your spreadsheet columns would contain information such as artist, title, and release date. You would then save that spreadsheet to a file. But what would you do if you wanted to store a list of songs for each album? How would you find all the songs with the word rain in them released since 1979?
A file system does not work well for complex data storage needs such as the one just described. More complex applications, such as those common to the business and research worlds, require the power of a database. A database is an application that specializes in providing other applications with access to data in a more complex manner than is allowed by file systems. The only request your application can make of a file system is for it to hand your application a file with a given name. You can ask a database, however, for all the songs with the word rain in them released since 1979.
In addition to enabling an application to access data in a more complex way, a database can be used to create a central information store for networked computers. Although the content of such information may not be complex enough to stretch the capabilities of a file system, file systems do not work well on the Internet, where different computers with very diverse operating systems interact.
The original release of Java provided only file system access,
with no provisions for database access. In March 1996, JavaSoft
began to address the need for database access with the draft release
of the Java Database Connectivity (JDBC) specification. This chapter
addresses the use of JDBC to give your applications access to
databases.
| Note |
This chapter focuses directly on the Java JDBC API. If you are not familiar with databases, take a look at Chapter 43, "Developing Your Own Database Application," which describes in detail the development of a database application. |
To provide a common base API for accessing data, Sun (with support from a number of independent software vendors) developed JDBC. JDBC defines a number of Java interfaces to enable developers to access data independently of the actual database product being used to store the data. In theory, an application written against the basic JDBC API using only SQL-2 can function against any database technology that supports SQL-2.
Data can be stored in a wide variety of formats using various technologies. Most systems currently use one of three major database management systems:
Relational databases are overwhelmingly the most common. In addition to these systems, there are other things to consider, such as hierarchical databases and file systems. Any low-level API trying to find a least-common denominator among these data storage methods would end up with the null set. JDBC, however, mandates no specific requirements on the underlying DBMS. Rather than dictating what sort of DBMS an application must have to support JDBC, the JDBC specification places all its requirements on the JDBC implementation.
The JDBC specification primarily mandates that a JDBC implementation support at least ANSI SQL-2 Entry Level. Because most common RDBMS and OORDBMS systems support SQL-2, this requirement provides a reasonable baseline from which to build database access. In addition, because SQL-2 is required only at the JDBC implementation level, that implementation can provide its own SQL-2 wrapper around non-SQL data stores. Writing such a wrapper, however, would likely be a huge task.
JDBC defines eight interfaces that must be implemented by a driver in order to be JDBC-compliant:
Figure 31.1 shows these interfaces and how they interact in the full JDBC object model.
Figure 31.1: The JDBC object model.
The central object around which the whole concept revolves is
the java.sql.DriverManager
object. It is responsible for keeping track of the various JDBC
implementations that may exist for an application. If, for example,
a system were aware of Sybase and Oracle JDBC implementations,
the DriverManager would be
responsible for tracking those implementations. Any time an application
wants to connect to a database, it asks the DriverManager
to give it a database connection, using a database URL through
the DriverManager.getConnection()
method. Based on this URL, the DriverManager
searches for a Driver implementation
that accepts the URL. It then gets a Connection
implementation from that Driver
and returns it to the application.
| What is a databse url? |
To enable an application to specify the database to which it wants to connect, JDBC uses the Internet standard Uniform Resource Locator system. A JDBC URL consists of the following pieces: jdbc:<subprotocol>:<subname> As with URLs you have seen all over the Internet, the first element is the resource protocol-in this case, a JDBC data source. The subprotocol is specific to the JDBC implementation. In many cases, it is the DBMS name and version; for example, syb10 indicates Sybase System 10. The subname element is any information specific to the DBMS that tells it where it needs to connect. For mSQL, the JDBC URL is in this format: jdbc:msql://hostname:port/database JDBC itself does not care what a database URL looks like. The important thing is simply that a desired JDBC implementation can recognize the URL and get the information it needs to connect to a database from that URL. |
The DriverManager is the only instantiated class provided by JDBC other than exception objects and a few specialized subclasses of java.util.Date. Additional calls made by an application are written against the JDBC interfaces that are implemented for specific DBMSs.
A Driver is essentially a Connection factory. The DriverManager uses a Driver to determine whether it can handle a given URL. If one of the Drivers in its list can handle the URL, that Driver should create a Connection object and return it to the DriverManager. Because an application only indirectly references a Driver through the DriverManager, applications are rarely concerned with this interface.
A Connection is a single database session. As such, it stores state information about the database session it manages and provides the application with Statement, PreparedStatement, or CallableStatement objects for making calls during the session.
A Statement is an unbound SQL call to the database. It is generally a simple UPDATE, DELETE, INSERT, or SELECT statement in which no columns must be bound to Java data. A Statement provides methods for making such calls and returns to the application the results of any SELECT statement or the number of rows affected by an UPDATE, DELETE, or INSERT statement.
Statement has the subclass PreparedStatement, which is in turn subclassed by CallableStatement. A PreparedStatement is a precompiled database call that requires parameters to be bound. An example of a PreparedStatement is a stored procedure call that has no OUT or INOUT parameters. For stored procedures with OUT or INOUT parameters, an application should use the CallableStatement interface.
An application gets data returned by a SELECT query through the implementer of the java.sql.ResultSet interface. Specifically, the ResultSet object enables an application to retrieve sequential rows of data returned from a previous SELECT call. The ResultSet provides a multitude of methods that enable you to retrieve a given row as any data type to which it makes sense to convert it. For example, if you have a date stored in the database as a datetime, you can retrieve it through the getString() method and use it as a String.
Meta-data is data about data. Specifically, it is a set of data that gives you information on the database and data retrieved from the database. Java provides two meta-data interfaces: java.sql.ResultSetMetaData and java.sql.DatabaseMetaData. The ResultSetMetaData interface provides a means for getting information about a particular ResultSet. For example, among other things, ResultSetMetaData provides information on the number of columns in the result set, the name of a column, and its type. The DatabaseMetaData interface, on the other hand, gives the application information on the database in general, such as what levels of support it has, its name, version, and other bits.
An application for which database independence is paramount should be written to the JDBC specification, using no database-specific calls and making use of no SQL that is not part of the ANSI SQL-2 standard. In such code, no reference should be made to a specific implementation of JDBC. Writing a simple database application using only JDBC calls involves the following steps:
A simple sample applet that demonstrates bare database connectivity is a common Web counter. A Web counter is an applet that keeps track of how many times a given Web page has been "hit," or accessed. Using the JDBC interfaces, this applet connects to a database, determines how many times the page on which it appears has been hit, updates the page to reflect the new hit, and finally displays the number of hits. To use this example, you need a database engine to run your database and a JDBC driver to access that database engine. If you do not have a database engine, download mSQL and JDBC, which are both free for noncommercial use and are provided on the CD-ROM that accompanies this book. Links to mSQL and the JDBC class may be found through http://www.imaginary.com/Java/. In addition, you need to create a table called t_counter with the fields counter_file (CHAR(100), PRIMARY KEY) and counter_num (INT, NOT NULL). The following mSQL script creates the table:
DROP TABLE t_counter\p\g
CREATE TABLE t_counter(
counter_file CHAR(100) PRIMARY KEY,
counter_num INT NOT NULL
)\p\g
The applet consists of two classes: Counter and Database. The Counter class is the subclass of the Applet class that provides the user interface to the applet. It contains two instance variables. One, count, is the number this applet is supposed to display-the number of page hits. The other, database, is an instance of the Database class that provides wrappers for the JDBC access needed by the applet.
The Counter class does not define any new methods; rather, it simply overrides the java.applet.Applet.init() and java.applet.Applet.paint() methods. The init() method is used to create a Database instance and find out from it what the page hit count is for display. The paint() method displays the page hit count.
The interesting JDBC-related work is all encapsulated inside the Database class. It has a single instance variable, connection, which is an instance of a JDBC Connection implementation. The connection variable is initialized in the Database class constructor:
public Database(String url, String user, String pass)
throws java.sql.SQLException {
connection =
DriverManager.getConnection(url, user, pass);
}
By getting an instantiated Connection object, the applet is ready to do whatever database access it needs to do.
The applet uses the getCount() method to figure out how many page hits this particular access to the Web page represents. That seemingly benign query actually represents several steps:
Creating the Statement is done through this JDBC call:
java.sql.Statement statement = connection.createStatement();
For this query, you want the number of hits for this page from the t_counter table:
sql = "SELECT counter_num FROM t_counter " +
"WHERE counter_file = '" + page + "'";
result_set = statement.executeQuery(sql);
The result_set variable now holds the results of the query. For queries that return multiple rows, an application loops through the next() method in the result set until no more rows exist. This query, however, should return only one row with one column, unless the page has never been hit. If the page has never been hit, the query does not find any rows, and the count variable should be set to 0:
if( !result_set.next() ) count = 0;
Otherwise, you must retrieve that row into the count variable as an integer:
else count = result_set.getInt(1);
After incrementing the count to reflect this new hit, you close out the Statement object and get a new one to prepare for the UPDATE:
count++; statement.close(); statement = connection.createStatement();
If this is the first time the page is hit, the applet must insert a new row into the database. Otherwise, it should update the existing row:
if( count == 1 ) {
sql = "INSERT INTO t_counter " +
"(counter_file, counter_num) " +
"VALUES ('" + file + "', " + count + ")";
}
else {
sql = "UPDATE t_counter " +
"SET counter_num = " + count + " " +
"WHERE counter_file = '" + file + "'";
}
statement.executeUpdate(sql);
The method then cleans up and returns the hit count. Listing 31.1
puts the whole applet
together.
Listing 31.1. The Counter
applet.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Counter extends java.applet.Applet {
Database db;
String count;
public void init() {
String driver = getParameter("driver");
String url = getParameter("url");
String user = getParameter("user");
String pass = getParameter("password");
String page = getParameter("page");
try {
Class.forName(driver);
db = new Database(url, user, pass);
count = db.getCount(page);
}
catch( java.sql.SQLException e ) {
e.printStackTrace();
count = "Database exception";
}
catch( Exception e ) {
e.printStackTrace();
count = "Unable to load driver";
}
}
public void paint(java.awt.Graphics g) {
g.setFont(new java.awt.Font(getParameter("font"),
java.awt.Font.BOLD, 14));
g.drawString(count, 5, 15);
}
}
class Database {
private Connection connection;
public Database(String url, String user, String pass)
throws java.sql.SQLException {
connection =
DriverManager.getConnection(url, user, pass);
}
public String getCount(String page) {
int count = 0;
try {
java.sql.Statement statement =
connection.createStatement();
java.sql.ResultSet result_set;
String sql;
sql = "SELECT counter_num FROM t_counter " +
"WHERE counter_file = '" + page + "'";
result_set = statement.executeQuery(sql);
if( !result_set.next() ) count = 0;
else count = result_set.getInt(1);
count++;
statement.close();
statement = connection.createStatement();
if( count == 1 ) {
sql = "INSERT INTO t_counter " +
"(counter_file, counter_num) " +
"VALUES ('" + page + "', " +count+ ")";
}
else {
sql = "UPDATE t_counter " +
"SET counter_num = " + count + " " +
"WHERE counter_file = '" + page + "'";
}
statement.executeUpdate(sql);
statement.close();
connection.close();
}
catch( java.sql.SQLException e ) {
e.printStackTrace();
}
return ("" + count);
}
}
| Note |
How are drivers registered with the DriverManager? In the preceding example, it was done by specifically loading the driver passed into the program through the driver parameter. Using the Class.forName() construct, a reference is created to that driver. In its static constructor, the driver tells the DriverManager of its existence. A JDBC-compliant driver must tell the DriverManager about its existence when it is instantiated. The preferred method of listing multiple JDBC drivers for the DriverManager is through the jdbc.drivers property. |
In simple applications such as the Counter applet just described, there is no need to perform any tricks with the results from a query. The data is simply retrieved sequentially and processed. More commonly, however, an application will need to process the data in a more complex fashion. For example, a set of classes may want to deal with data on a more abstract level than the Database class from the Counter example. Such classes may not know exactly what data is being retrieved. They can query the meta-data interfaces to intelligently process the data that they would not otherwise know. Listing 31.2 shows a generic database View class that is populated with database objects based on a result set.
Listing 31.2. A generic database View
class.
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Hashtable;
import java.util.Vector;
public class View {
private Vector objects;
public void populate(ResultSet result_set, String cl) {
ResultSetMetaData meta_data;
int i, maxi;
try {
objects = new Vector();
meta_data = result_set.getMetaData();
maxi = meta_data.getColumnCount();
while( result_set.next() ) {
Hashtable row = new Hashtable();
DataObject obj;
for(i=1; i<=maxi; i++) {
String key;
Object value;
int t;
key = meta_data.getColumnLabel(i);
t = meta_data.getColumnType(i);
value = result_set.getObject(i, t);
row.put(key, value);
}
obj = (DataObject)Class.forName(cl);
obj.restore(row);
objects.addElement(obj);
}
}
catch( java.sql.SQLException e ) {
e.printStackTrace();
objects = new Vector();
return;
}
}
}
In the View class, reference is made to a DataObject class that implements a restore(java.util.Hashtable) method not listed.
Because this is a generic class to be reused by many applications, it knows nothing about the queries it is executing. Instead, it takes any random result set and assumes that each row corresponds to an instance of the class named by the second parameter of populate().
To get the information it needs for performing the data retrievals, the populate() method first gets the meta-data object for this result set. This method is specifically interested in knowing how many columns are in the result set as well as the names of the columns. To store the columns in a Hashtable object that the DataObject object can use for restoring itself, all data must be in the form of objects. Thus, for each column in the result set, the method finds the row's data type from the meta-data and retrieves the column as an object. The final step is to store that row in the Hashtable.
JDBC supports stored procedures and prepared statements through an inheritance hierarchy extended from the Statement class. Stored procedures and prepared statements are precompiled SQL calls sitting on the server. You call a stored procedure like a function, using its name and passing arguments. Prepared statements, on the other hand, you send to the server with place- holders for your input. As you make the actual prepared statement calls, you bind the place- holders to actual Java values. JDBC provides two classes to support this functionality:
The basic difference between these two classes is that PreparedStatement expects only input parameters; CallableStatement allows you to bind input and output parameters. Using stored procedures or prepared statements generally gives your application an added speed advantage, because such statements are stored in a precompiled format in most database engines. In addition, stored procedures allow an application to provide a call-level interface to a database without worrying the Java developer about SQL syntax. Listing 31.3 provides a simple SELECT operation using a stored procedure instead of straight SQL.
Listing 31.3. A simple SELECT
operation using stored procedures.
import java.sql.*;
import java.util.Properties;
public class ProcSelect {
public static void main(String args[]) {
Properties props = new Properties();
props.put("user", "po7");
props.put("password", "po7");
try {
Connection c;
CallableStatement statement;
ResultSet results;
c = DriverManager.getConnection("jdbc:weblogic:oracle", props);
System.out.println("Preparing call...");
statement = c.prepareCall("BEGIN sp_get_account(?, ?, ?, ?); END;");
System.out.println("Setting first int...");
statement.setInt(1, 1);
statement.registerOutParameter(2, java.sql.Types.INTEGER);
statement.registerOutParameter(3, java.sql.Types.CHAR);
statement.registerOutParameter(4, java.sql.Types.INTEGER);
System.out.println("Executing...");
statement.execute();
System.out.println("Getting results...");
System.out.println("Id: " + statement.getString(2));
System.out.println("Type: " + statement.getString(3) + "cheese");
System.out.println("Balance: " + statement.getString(4) + "\n");
}
catch( Exception e ) {
e.printStackTrace();
}
}
}
JDBC provides a lot of functionality beyond the commonly used methods already discussed:
JDBC implementations should default automatically to committing transactions unless the application otherwise requests that transactions require an explicit commit. An application can toggle the automatic commit of the JDBC implementation it is using through the Connection.setAutoCommit() method. Here is an example:
connection.setAutoCommit(false);
Of course, by not setting the AutoCommit attribute or by setting it to true, the JDBC implementation makes certain that the DBMS commits after each statement you send to thedatabase. When Connection.setAutoCommit() is set to false, however, the JDBC implementation requires specific commits from the application before a transaction is committed to the database. A series of statements executed as a single transaction looks like this:
public void add_comment(String comment) {
try {
Statement s;
ResultSet r;
int comment_id;
connection.setAutoCommit(false);
s = connection.createStatement();
r = s.executeQuery("SELECT next_id " +
"FROM t_id " +
"WHERE id_name = 'comment_id'");
if( !r.next() ) {
throw new SQLException("No comment id exists " +
"in t_id table.");
}
comment_id = r.getInt(1) + 1;
s.close();
s = connection.createStatement();
s.executeUpdate("UPDATE t_id " +
"SET comment_id = " + comment_id + " " +
"WHERE next_id = 'comment_id'");
s.close();
s = connection.createStatement();
s.executeUpdate("INSERT INTO t_comment " +
"(comment_id, comment_text) " +
"VALUES(" + comment_id + ", '" +
comment + "')");
connection.commit();
}
catch( SQLException e ) {
e.printStackTrace();
try {
connection.rollback();
}
catch( SQLException e2 ) System.exit(-1);
}
}
This approach is used to add a comment to a comment table for some applications. To insert the new comment, the application needs to generate a new comment_id and then update the table for generating IDs so that the next one is 1 greater than this ID. Once the application has an ID for this comment, it then inserts the comment into the database and commits the entire transaction. If an error occurs at any time, the entire transaction is rolled back.
JDBC currently has no support for a two-phase commit. Applications written against distributed databases require extra support in the form of third-party APIs to allow your Java applications the ability to do two-phase commits.
JDBC provides limited cursor support. It enables an application to get a cursor associated with a result set through the ResultSet.getCursorName() method. The application can then use the cursor name to perform positioned UPDATE or DELETE statements.
In some cases, especially with stored procedures, an application can have a statement that returns multiple result sets. JDBC handles this through the method Statement.getMoreResults(). Even though there are result sets left to be processed, this method returns true. The application can then get the next ResultSet object by calling Statement.getResultSet(). Processing multiple result sets simply involves looping through the database as long as Statement.getMoreResults() returns true.
Building a JDBC implementation requires a lot more in-depth knowledge of both your DBMS and the JDBC specification than does simply coding the implementation. Most people will never encounter the need to roll their own implementation because database vendors logically want to make them available for their product. Understanding the inner workings of JDBC, however, can help advance your application programming.
JDBC is a low-level interface. It provides direct SQL-level access to the database. Most business applications and class libraries will want to abstract from that SQL-level access to provide such things as object persistence and business-aware database access. A narrow example of such an abstraction is the Database class from the Counter example used earlier in this chapter.
The ideal object method of accomplishing these goals is to reuse existing JDBC implementations for the DBMS in question and add custom interfaces. If the DBMS is an oddball DBMS, or if you have concerns about the available implementations, writing one from scratch makes sense.
The first concern of any JDBC implementation is how it is going to talk to the database. Figure 31.2 shows the architecture of three possible JDBC implementations. Depending on the design goals in question, one of these methods will suit any JDBC implementation:
Figure 31.2: Possible JDBC implementation architectures.
Extending a vendor JDBC implementation, of course, is not really the same as building a JDBC implementation. Because a key to any object-oriented project is reusing code instead of building from scratch, however, it is listed here.
With all three architectures, the application is apparently isolated from the actual communication mechanism. In truth, however, the native C library method places severe restrictions on any application using a JDBC implementation built on top of it. Because it uses native calls, it is naturally not portable across operating systems. In addition, because of virtual machine restrictions on most browsers, native calls are either fully restricted or severely limited.
Using one of these mechanisms for database communication, you must construct the four basic interfaces: java.sql.Driver, java.sql.Connection, java.sql.Statement, and java.sql.ResultSet. These interfaces provide minimum functionality so that testing against simple queries and updates can be done. Once these are functional, the implementation needs the meta-data interfaces as well as the Statement subclasses to be complete and to be JDBC compliant.
Nothing requires an application to use the JDBC interface to access a database. In fact, before JDBC, developers were programming with Java classes written specifically to go against several major database engines. JDBC isolates the database access behind a single interface. This isolation can provide developers with the ability to write database access in Java without having to know which database engine their application is actually hitting. With a single prevalent database API, finding people with experience programming against it proves much simpler than finding people to program against a proprietary API. JDBC is, however, a low-level specification that requires developers to write both SQL code and Java code.
Both examples in this chapter demonstrate two different ways in which you can extend JDBC. In the Counter applet, earlier in this chapter, a database class was created as a wrapper around the JDBC implementation. The applet itself was divided into a representational portion, the Counter class, and a functional portion, the Database class. If changes are made to the visual representation, such as making the hit count appear through an odometer graphic, no changes must be made to the functional logic because it is isolated in a separate class. In fact, if the applet were more complex, requiring multiple developers, all the SQL would still be isolated in a class specifically interested in the functional behavior of the application. This reduces the amount of people needed to write SQL code.
The View class example, also presented earlier in this chapter, uses a more abstract way of extending JDBC. The View class assumes that rows in result sets translate into business objects. In an application using this class, View objects are created whose purpose is to make JDBC calls and populate the applications with meaningful objects.
Another manner in which JDBC can be extended is to take advantage of database-specific features. Although it is prudent to question the need to make use of any proprietary features of a given DBMS, it is equally important that you do not ignore the extra power a specific DBMS gives you. It is, after all, very rare that an application actually needs to switch database engines.
Although the original Java release did not address the issue of database access, the JDBC specification attempts to address this issue by defining a set of interfaces that can give applications access to data independently of the DBMS being used to store that data. Although this back-end independence can be very liberating, it is important to balance it with the advantages of the DBMS being used.
Many books cover only the subjects of database application design and programming. This chapter does not attempt to delve into those matters; instead, it focuses on using Java in database programming. Programmers interested in using Java to write database applications should become familiar with the general subject matter.
In spite of the vastness of the subject matter, this chapter should whet your appetite for database programming and prepare you for Chapter 43, "Developing Your Own Database Application," which goes into the details of building a Java database application. Much of the Java experience you already have translates into many of the issues specific to Java database programming. Your next step should therefore be to get access to a database and start coding.