Chapter 16

Using Java Database Connectivity

In This Chapter

Connecting to a database

Inserting values into a database

Making queries to a database

Whenever I teach Java to professional programmers, I always hear the same old thing. “We don’t need to make any cute little characters fly across the screen. No blinking buttons for us. We need to access databases. Yup, just show us how to write Java programs that talk to databases.”

So here it is, folks — Java Database Connectivity.

JDBC and Java DB

When I first started working with databases, my toughest problem was connecting to a database. I had written all the Java code. (Well, I had copied all the Java code from some book.) The Java part was easy. The hard part was getting my code to find the database on the system.

Part of the problem was that the way you get your code to talk to the database depends on the kind of system you have and the kind of database that you’re running on your system. The books that I was using couldn’t be too specific on all the details because the details (having nothing to do with Java) varied from one reader’s computer to another. And now I’m writing my own chapter about database connectivity. What’s an author to do?

Fortunately, the Java Development Kit (JDK) comes with its own built-in database — Java DB. Based on the Apache Derby database, Java DB is secure, lightweight, and standards-based. Java DB runs seamlessly along with the rest of the Java JDK. The Java gurus introduced Java DB with the release of Java 6.

Java DB makes life easier for me by providing a common database that all my readers can use. The database is freely available, and it requires no setup.

And what if you don’t use Java DB? What if all your data is stored in other kinds of databases; namely, MySQL, PostgreSQL, SQLite, Oracle, Microsoft Access, DB2, or almost any other database? Then Java has a solution for you! The Java Database Connectivity (JDBC) classes provide common access to most database management systems. Just get a driver for your favorite vendor’s system, customize two lines of code in each of this chapter’s examples, and you’re ready to run the code.

Creating Data

The crux of JDBC is contained in two packages: java.sql and javax.sql, which are both in the Java API. This chapter’s examples use the classes in java.sql. The first example is shown in Listing 16-1.

Listing 16-1: Creating a Database and a Table, and Inserting Data

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.SQLException;

class CreateTable {

public static void main(String args[]) {

final String DRIVER =

“org.apache.derby.jdbc.EmbeddedDriver”;

final String CONNECTION =

“jdbc:derby:AccountDatabase;create=true”;

try {

Class.forName(DRIVER).newInstance();

} catch (InstantiationException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try (Connection connection =

DriverManager.getConnection(CONNECTION);

Statement statement =

connection.createStatement()) {

statement.executeUpdate(

“create table ACCOUNTS “

+ “ (NAME VARCHAR(32) NOT NULL PRIMARY KEY, “

+ “ ADDRESS VARCHAR(32), “

+ “ BALANCE FLOAT) “);

statement.executeUpdate(

“insert into ACCOUNTS values “

+ “ (‘Barry Burd’, ‘222 Cyber Lane’, 24.02)”);

statement.executeUpdate(

“insert into ACCOUNTS values “

+ “ (‘Joe Dow’, ‘111 Luddite Street’, 55.63)”);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

To use MySQL instead of Java DB, make the following changes in Listing 16-1: Change the value of DRIVER to “com.mysql.jdbc.Driver”. Change the value of CONNECTION to “jdbc:mysql://localhost/AccountDatabase;create=true”. Make similar changes for DB2, Oracle, and other databases.

To run database code, you must have a file containing a suitable database driver, and that file must be in a place where Java can find it. In this chapter’s examples, I connect to a Java DB database, also known as an Apache Derby database. The driver is in a file named derby.jar, which normally lives in the JDK’s db/lib directory. To make db/lib/derby.jar available to my Java programs, I add this .jar file to my Java classpath.

The way you add a .jar file to your classpath depends on the kind of IDE you use and possibly on the kind operating system you use. In Eclipse, I select Project⇒Properties⇒Java Build Path. Then I click the Add External JARs button and navigate to the db/lib directory. For other IDEs, the steps are slightly different.

When you run the code in Listing 16-1, nothing seems to happen. The program starts running and then stops running. That’s about it. The code has no visible output because all the output goes to a database. So, to see the result of running the code in Listing 16-1, you have to look for changes in the database itself. So read on!

In the previous paragraph, I wrote that running Listing 16-1 is terribly uneventful. I wrote that “nothing seems to happen” and that “The code has no visible output.” But, if you look closely, you can find some evidence of a run of the Listing 16-1 code. In particular, your hard drive has a few additional files after the first run of this code. One of these files, named derby.log, contains text describing the starting and stopping of the Derby database software. You can also find a new folder named derbyDB, which contains more log files, a service.properties file, and a folder full of .dat files. (These .dat files contain all the stuff stored in the database.) If you use Eclipse, you can make these new files and folders visible by selecting your project branch in the Package Explorer, and then selecting File⇒Refresh.

Using SQL commands

In Listing 16-1, the heart of the code lies in three calls to executeUpdate. Each executeUpdate call contains a string — a normal, Java, double-quoted string of characters. To keep the code readable, I’ve chopped each string into parts. I separate the parts with plus signs (Java’s string concatenation operator).

Java’s plus sign does double duty. For numbers, the plus sign performs addition. For strings, the plus sign squishes two strings together, creating one big, combined string.

You can make a double-quoted string as long as you like. When you get to the right edge of your screen, just keep typing. If you want to see the whole string without scrolling, however, you can break the string into pieces, as I did in Listing 16-1. Just separate the pieces with plus signs.

You cannot break a Java string into pieces by just pressing Enter and moving to the next line. When you start a string with the double-quote (“), the ending double-quote must be on the same line of code.

If you’re familiar with SQL (Structured Query Language), the command strings in the calls to executeUpdate make sense to you. If not, pick up a copy of SQL For Dummies, 7th Edition, by Allen G. Taylor. One way or another, don’t go fishing around this chapter for explanations of create table and insert into. You won’t find the explanations because these command strings aren’t part of Java. These commands are just strings of characters that you feed to the executeUpdate method. These strings, which are written in SQL, create a new database table and add rows of data to the table. When you write a Java database program, that’s what you do. You write ordinary SQL commands and surround those commands with calls to Java methods.

The code in this chapter adheres strictly to the techniques defined in JDBC version 1.0. Later versions of the JDBC classes support something called scrollable result sets. With a scrollable result set, you have methods like insertRow — methods that save you the effort of writing complete SQL command strings.

Connecting and disconnecting

Aside from the calls to the executeUpdate method, the code in Listing 16-1 is cut-and-paste stuff. Here’s a rundown on what each part of the code means:

Class.forName: Find a database driver.

To talk to a database, you need an intermediary piece of software, or a database driver. Drivers come in all shapes and sizes, and many of them are quite expensive. But Listing 16-1 uses a small, freebie driver — the Derby JDBC Embedded driver. The code for the Derby JDBC Embedded driver is kept in the EmbeddedDriver class (which is a Java class). This class lives inside the org.apache.derby.jdbc package.

To use this EmbeddedDriver class, you call the Class.forName method. Believe it or not, the Java API has a class named Class. The Class class contains information about classes that are available to the Java Virtual Machine (JVM). In Listing 16-1, the call to Class.forName looks for the org.apache.derby.jdbc.EmbeddedDriver class. After an EmbeddedDriver instance is loaded, you can proceed to connect with a database.

DriverManager.getConnection: Establish a session with a particular database.

The getConnection method lives in a Java class named DriverManager. In Listing 16-1, the call to getConnection creates an AccountDatabase and opens a connection to that database. Of course, you may already have an AccountDatabase before you start running the code in Listing 16-1. If you do, the text ;create=true string in the getConnection call has no effect.

In the parameter for getConnection (refer to Listing 16-1), notice the colons. The code doesn’t simply name the AccountDatabase, it tells the DriverManager class what protocols to use to connect with the database. The code jdbc:derby: — which is the same as the http: in a web address — tells the computer to use the jdbc protocol to talk to the derby protocol, which in turn talks directly to your AccountDatabase.

connection.createStatement: Make a statement.

It seems strange, but in Java Database Connectivity, you create a single statement object. After you’ve created a statement object, you can use that object many times, with many different SQL strings, to issue many different commands to the database. So, before you start calling the statement.executeUpdate method, you have to create an actual statement object. The call to connection.createStatement creates that statement object for you.

try . . . catch . . . : Acknowledge exceptions that can be thrown in the code.

If you read Chapter 12, you know that some method calls throw checked exceptions. A checked exception is one that has to be acknowledged somewhere in the calling code. Well, a call to Class.forName can throw three kinds of exceptions, and just about everything else in Listing 16-1 can throw an SQLException. To acknowledge these exceptions, I add try-catch statements to my code.

try-with-resources: Release resources, come what may!

As Ritter always says, you’re not being considerate of others if you don’t clean up your own messes. Every connection and every database statement lock up some system resources. When you’re finished using these resources, you release them. You can do this by making explicit calls to close methods, but you must enclose the method calls inside try-catch statements.

But there’s a catch (pun intended)! When things go wrong, they don’t simply go wrong. They often go haywire! If you can’t close a statement, Java jumps to a catch clause. But what if the catch clause throws its own exception? And what happens later when your code tries to close the whole connection?

To address these issues in one fell swoop, Java 7 has a new try-with-resources statement. A try-with-resources is like the old try statement of Chapter 12. But in a try-with-resources statement, you add parentheses after the word try. Inside the parentheses, you put some statements that create resources. (In Listing 16-1, the statements between parentheses are the calls to getConnection and to createStatement.) You separate the statements with semi-colons.

Java’s try-with-resources statement automatically closes and releases your resources at the end of the statement’s execution. In addition, try-with-resources takes care of all the messy details associated with failed attempts to catch exceptions gracefully. It’s a win-win.

Retrieving Data

What good is a database if you can’t get data from it? In this section, you query the database that you created in Listing 16-1. The code to issue the query is shown in Listing 16-2.

Listing 16-2: Making a Query

import static java.lang.System.out;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.ResultSet;

import java.text.NumberFormat;

class GetData {

public static void main(String args[]) {

NumberFormat currency =

NumberFormat.getCurrencyInstance();

final String DRIVER =

“org.apache.derby.jdbc.EmbeddedDriver”;

final String CONNECTION =

“jdbc:derby:AccountDatabase”;

try {

Class.forName(DRIVER).newInstance();

} catch (InstantiationException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try (Connection connection =

DriverManager.getConnection(CONNECTION);

Statement statement =

connection.createStatement();

ResultSet resultset =

statement.executeQuery(

“select * from ACCOUNTS”)) {

while(resultset.next()) {

out.print(resultset.getString(“NAME”));

out.print(“, “);

out.print(resultset.getString(“ADDRESS”));

out.print(“ “);

out.println(currency.format(

resultset.getFloat(“BALANCE”)));

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

To use MySQL instead of Java DB, make the following changes in Listing 16-2: Change the value of DRIVER to “com.mysql.jdbc.Driver”. Change the value of CONNECTION to “jdbc:mysql://localhost/AccountDatabase;create=true”. Make similar changes for DB2, for Oracle, and for other databases.

A run of the code from Listing 16-2 is shown in Figure 16-1. The code queries the database and then steps through the rows of the database, printing the data from each of the rows.

Listing 16-2 starts with the usual calls to forName, getConnection, and createStatement. Then the code calls executeQuery and supplies the call with an SQL command. For those who know SQL commands, this particular command gets all data from the ACCOUNTS table (the table that you create in Listing 16-1).

The thing returned from calling executeQuery is of type java.sql.ResultSet. (That’s the difference between executeUpdate and executeQuery — executeQuery returns a result set, and executeUpdate doesn’t.) This result set is very much like a database table. Like the original table, the result set is divided into rows and columns. Each row contains the data for one account. Each row has a name, an address, and a balance amount.

After you call executeQuery and get your result set, you can step through the result set one row at a time. To do this, you go into a little loop and test the condition resultset.next() at the top of each loop iteration. Each time around, the call to resultset.next() does two things:

It moves you to the next row of the result set (the next account) if another row exists.

It tells you whether another row exists by returning a boolean value — true or false.

If the condition resultset.next() is true, the result set has another row. The computer moves to that other row, so you can march into the body of the loop and scoop data from that row. On the other hand, if resultset.next() is false, the result set doesn’t have any more rows. You jump out of the loop and start closing everything.

Now, imagine that the computer is pointing to a row of the result set, and you’re inside the loop in Listing 16-2. Then you’re retrieving data from the result set’s row by calling the result set’s getString and getFloat methods. Back in Listing 16-1, you set up the ACCOUNTS table with the columns NAME, ADDRESS, and BALANCE. So, here in Listing 16-2, you’re getting data from these columns by calling your getSomeTypeOrOther methods and feeding the original column names to these methods. After you have the data, you display the data on the computer screen.

Each Java ResultSet instance has several nice getSomeTypeOrOther methods. Depending on the type of data you put into a column, you can call methods getArray, getBigDecimal, getBlob, getInt, getObject, getTimestamp, and several others.