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 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.
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!
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.
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.
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();
}
}
}
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 get
SomeTypeOrOther
methods and feeding the original column names to these methods. After you have the data, you display the data on the computer screen.