HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 
After The Connection, by Robert J. Brunner

In this series we've explored the process behind selecting a database and a JDBC driver as well as establishing a connection between your Java application and your database using JDBC. To actually do something useful, however, you need to be able to actively interact with a database using JDBC.

Early on in their history, relational database vendors agreed on a common interpretive language called SQL (Structured Query Language) that could interact with any database that supported this standard. This tactic helped build a large user base and a large number of third-party tools. Relational databases came to supplant the previously popular hierarchical databases as the dominant type of database system.

JDBC technology is built on this standard query language. As a result, to understand the fundamentals of JDBC, you must first understand the basics of SQL. Before delving into SQL, however, we need to cover some of the basic theory of relational database systems.

The two main concepts that underlie relational databases are the idea of a database transaction and the representation of data in a table format. Fundamentally, a transaction represents a logical unit of work with a database. In fact, the basic test for classifying something as a database, the famous ACID test that stands for Atomicity, Consistency, Isolation, and Durability, is defined in terms of transactions. If you wish to save your work, you need to commit your transaction; if you wish to undo your work, you need to roll back your transaction. Data locking, for both read and write operations, is used to prevent users from interfering with each other's transactions.

The data in a relational database is conceptualized to occupy a table (think of a spreadsheet) consisting of rows (records of data) of multiple columns (different types of data that make up the records). An entire theoretical framework has been developed, including such concepts as independent and dependent tables and different normal forms, to prove theorems concerning relational database tables. For our purposes, however, we need to review only four concepts:

  • Primary keys: Used to provide a distinct identity to each row in a table; essentially, they're why you always feel like a number when dealing with a particular bureaucracy (e.g., a call-number or an employee or Social Security number)
  • Joins: Operations used to combine data from two distinct tables
  • Indices: Used to facilitate rapid data retrieval; however, indices can occupy a significant amount of space and are therefore created only for a select number of columns in a table (generally including the primary key)
  • Views: Results of given queries, often considered and treated as new tables

SQL Primer
At its most basic, SQL is used to interact with a relational database system in two different fashions, each with its own name: SQL data definition language (DDL) and SQL data manipulation language (DML). The former is used to create and drop tables from a particular database. The latter is used to select, insert, update, or delete data from a particular table. SQL is, by its very nature, an interpretive language that provides a great deal of flexibility but can also limit performance.

A few caveats are in order when writing or reading SQL. First, SQL commands are case insensitive and can be spread over multiple lines in an interactive SQL editor. An important footnote to this caveat, however, is that table and column names are generally case sensitive (as a point, I'll capitalize all SQL commands and use mixed-case notation for all table and column names). This last issue is one of the biggest stumbling blocks for SQL novices as it's easy to forget. Second, SQL introduces standard data types that include representations for variable-length character arrays, integers, and floating-point numbers (see your database manual for more information). Finally, many interactive SQL tools use a special delimiter (such as the semicolon) to separate multiple SQL commands.

Before doing anything else, we need to create a table to hold our data. If we want to make a table to hold a simple list of contacts, we might use the following SQL statement:

CREATE TABLE Contacts (
First VARCHAR(20) NOT NULL,
Middle VARCHAR(20) NOT NULL,
Last VARCHAR(20) NOT NULL,
Phone INT,
Age INT
) ;

This command creates a table named Contacts in the currently connected database. Its five columns are First, Middle, Last, Phone, and Age. The three name columns are variable- length character arrays with an initial size specification of 20 characters. These columns must contain actual data, indicated by the NOT NULL column qualifier. The last two columns are integer data types that are optional since they don't have the NOT NULL qualifier.

The complementary operation to remove a table from the database is considerably simpler:

DROP TABLE Contacts ;

After the necessary tables are created, the next step is to populate them with the relevant data. Many database systems have useful utilities that can automate the bulk inserts of large amounts of data; however, in SQL this is done using the INSERT command:

INSERT INTO Contacts
VALUES(
'Robert',
'J.',
'Brunner',
1234567890,
21
) ;

Alternatively, we can explicitly specify the order of the columns that the value fields will occupy:

INSERT INTO Contacts (
First,
Middle,
Last,
Phone,
Age
)

VALUES(
'Robert',
'J.',
'Brunner',
1234567890,
21
) ;

Once we have a table filled with the data of interest, we can begin to flex the full power of relational databases. They can use the relations within the data to selectively query, update, and delete data according to specific constraints. Formally, we wish to perform a procedure (including SELECT, UPDATE, DELETE) on a table with a particular group of rows indicated by the appropriate relational operators (see Figure 1). For example, to display the full contents of our Contacts table (assuming we have inserted several entries), we can issue the following SQL command:

SELECT * FROM Contacts ;

Figure 1
Figure  1:

While instructive, this example doesn't demonstrate the full power of the SELECT statement. Sometimes you may want to selectively extract certain columns for a subset of all the rows in a table (see Figure 2). For example, to pull out the First, Last, Age, and Phone columns for all rows that have an Age column value greater than 30, we'd use the following SQL command:

SELECT First, Last, Age, Phone
FROM Contacts
WHERE Age > 30 ;

Figure 2
Figure  2:

A similar syntax is used for the DELETE and UPDATE SQL commands, which either delete or update specific rows, respectively.

Morphing SQL into JDBC
Anyone serious about learning and using JDBC needs a good reference. I recommend the JDBC API Tutorial and Reference, Second Edition: Universal Data Access for the Java 2 Platform by Seth White et al. (Addison-Wesley), part of the Java Series. To understand why a good reference can be invaluable, recall that SQL has predefined data types, while the Java language has its own predefined data types (one of Java's most important features). To pull data from a database into a Java application using SQL, you have to convert from SQL data types to Java data types and vice versa. The book referenced above devotes an entire chapter to this process, with many informative tables that demonstrate the allowed and recommended conversions.

Before delving into the more commonly used interfaces in the JDBC API, an introduction to the specific error-handling features is appropriate. Any time a database is involved, the whole concept of error handling can quickly become a quagmire. Fortunately, the JDBC API has provided an elegant solution - the SQLException object - which allows for chained exceptions, a novel concept in the Java arena. As a result, the following convention is standard when using the JDBC API:

try {
// JDBC Code
}catch(SQLException e) {
while (e != null) {
System.out.println("\nERROR: \n");
System.out.println("Message: "
+ e.getMessage ());
System.out.println("SQLState: "
+ e.getSQLState ()); System.out.println("ErrorCode: " + e.getErrorCode ());
e = e.getNextException();
}
}

Sometimes a database operation can produce a warning condition (encapsulated by the SQLWarning object), which is less severe than an exception but can also be chained. Since these conditions aren't exceptions, they're not handled in the typical try...catch block fashion. Instead, you need to check explicitly to see if a database operation generated a warning and act appropriately (see the previously mentioned JDBC book or the online Java tutorial, http://java.sun.com/docs/books/tutorial, for more information).

Now let's discuss using Java to interact with a database. Once a connection has been established with a database (see JDJ, Vol. 5, issue 10), the next step is to create a Statement object. This object encapsulates the process of passing SQL commands to the database and processing the results. As the code snippet below demonstrates, a Statement object is created from a Connection object, which in effect owns the newly created Statement object.

First we need the SQL command we want to send to the database. In this case it's our earlier table creation command, with the table renamed so we don't try to create a new table with the same name as an existing one (which would throw a SQLException).

String createString =
"CREATE TABLE NewContacts " +
"First VARCHAR(20) NOT NULL, " +
"Middle VARCHAR(20) NOT NULL, " +
"Last VARCHAR(20) NOT NULL, " +
"Phone INT, " +
"Age INT)" ;

Now we can create our SQL Statement object, which is obtained from an already established connection.

try{
Statement stmt = con.createStatement() ;

Since this particular SQL string effectively updates the database and doesn't return any data, we call the executeUpdate method, which will execute the SQL command and return the number of rows affected (i.e., the update count).

int count = stmt.executeUpdate(createString);

// Process the Update Count

To properly release database resources (which are generally controlled by expensive license limitations), we need to close our objects properly:

stmt.close();
con.close();

} catch(SQLException ex) {
// Handle Exception
}

This same syntax is used for SQL CREATE, DROP, INSERT, UPDATE, and DELETE operations.

On the other hand, SQL SELECT operations generate a new view of the data. To handle this data, the JDBC API uses the ResultSet object, which provides an interface to access the rows and columns of the resultant data. First we create the appropriate SQL string - in this case the previous SELECT statement - which returned certain columns for all rows with Age greater than 30:

String selectStatement =
"SELECT First, Last, Age, Phone " +
"FROM NewContacts " +
"WHERE Age > 30" ;

The SQL Statement object is created as before, but now we'll call the executeQuery method, which will return the data resulting from our query. Notice that the ResultSet object is created from the Statement object; therefore, the Statement object effectively owns the ResultSet object. As a result, when the Statement object is closed or the object reference is reused, the ResultSet object is also closed.

try{

Statement stmt =
con.createStatement() ;

ResultSet rs =
stmt.executeQuery(selectStatement) ;

To access the resulting rows of our query, we use the built-in iterator of the ResultSet object to access all the rows produced by the query. This iterator is always initialized to point to the (fictitious) record that comes before the first record; thus, when it's first accessed (by the next method), it points to the first row:

while(rs.next()) {

To access the columns for each row in the ResultSet object, we need to convert them to their Java data types, which is done using the appropriate getXXX method, where we replace the XXX in the method declaration by the actual data type. For example, for the particular query we're using, we have two String objects followed by two integers. Assuming the appropriate variables have already been declared, we can easily extract the data:

firstName = rs.getString(1) ;
lastName = rs.getString(2) ;
age = rs.getInt(3) ;
phone = rs.getInt(4) ;

// Utilize the new data for some processing
// For example, we could create a new object,
// or print out all of the rows.
}

As before, you should close everything down properly so as to free up valuable database resources.

Of course, there's considerably more in the JDBC API than I've covered here. Some notable additions are the DatabaseMetadata and ResultSetMetadata interfaces, which can be used to obtain a great deal of information about the database or ResultSet, respectively, at runtime. I also didn't cover the PreparedStatement, which allows a database and JDBC driver to precompile particular SQL statements for improved performance or the CallableStatement interfaces, which allow a Java program to call a database stored procedure.

Conclusion
Hopefully this article has provided a gentle introduction to both SQL and the process of encapsulating SQL statements using JDBC. While I've intentionally avoided many of the details, you certainly have enough knowledge to get started, both with SQL and JDBC. With this basic understanding, you can begin to explore building components on top of the JDBC layer, as well as using applets, servlets, and JavaServer Pages to interact with a database.

Author Bio
Robert Brunner is a member of the research staff at the California Institute of Technology, where he focuses on very large (multiterabyte) databases, particularly on KDD (Knowledge Discovery in Databases) and advanced indexing techniques. He has used Java and databases for more than three years, and has been the Java database instructor for the Java Programming Certificate at California State University Pomona for the past two years. [email protected]

 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.