20. Accessing Databases with JDBC
Objectives
In this chapter you’ll learn:
• Relational database concepts.
• To use Structured Query Language (SQL) to retrieve data from and manipulate data in a database.
• To use the JDBC™ API of package java.sql to access databases.
• To use the RowSet interface from package javax.sql to manipulate databases.
• To use JDBC 4.0’s automatic JDBC driver discovery.
• To use PreparedStatements to create precompiled SQL statements with parameters.
• How transaction processing makes database applications more robust.
It is a capital mistake to theorize before one has data.
—Arthur Conan Doyle
Now go, write it before them in a table, and note it in a book, that it may be for the time to come for ever and ever.
—The Holy Bible, Isaiah 30:8
Get your facts first, and then you can distort them as much as you please.
—Mark Twain
I like two kinds of men: domestic and foreign.
—Mae West
Outline
20.1 Introduction
20.2 Relational Databases
20.3 Relational Database Overview: The books Database
20.4 SQL
20.4.1 Basic SELECT Query
20.4.2 WHERE Clause
20.4.3 ORDER BY Clause
20.4.4 Merging Data from Multiple Tables: INNER JOIN
20.4.5 INSERT Statement
20.4.6 UPDATE Statement
20.4.7 DELETE Statement
20.5 Instructions for Installing MySQL and MySQL Connector/J
20.6 Instructions for Setting Up a MySQL User Account
20.7 Creating Database books in MySQL
20.8 Manipulating Databases with JDBC
20.8.1 Connecting to and Querying a Database
20.8.2 Querying the books Database
20.9 RowSet Interface
20.10 Java DB/Apache Derby
20.11 PreparedStatements
20.12 Stored Procedures
20.13 Transaction Processing
20.14 Wrap-Up
20.15 Web Resources
20.1 Introduction
A database is an organized collection of data. There are many different strategies for organizing data to facilitate easy access and manipulation. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data for many users. Database management systems allow for the access and storage of data without concern for the internal representation of data.
Today’s most popular database systems are relational databases, where the data is stored without consideration of its physical structure (Section 20.2). A language called SQL—pronounced “sequel,” or as its individual letters—is the international standard language used almost universally with relational databases to perform queries (i.e., to request information that satisfies given criteria) and to manipulate data. [Note: As you learn about SQL, you’ll see some authors writing “a SQL statement” (which assumes the pronunciation “sequel”) and others writing “an SQL statement” (which assumes that the individual letters are pronounced). In this book we pronounce SQL as “sequel.”
Some popular relational database management systems (RDBMSs) are Microsoft SQL Server, Oracle, Sybase, IBM DB2, Informix, PostgreSQL and MySQL. The JDK now comes with a pure-Java RDBMS called Java DB—Sun’s version of Apache Derby. In this chapter, we present examples using MySQL and Java DB.
Java programs communicate with databases and manipulate their data using the JDBC™ API. A JDBC driver enables Java applications to connect to a database in a particular DBMS and allows you to manipulate that database using the JDBC API.
Software Engineering Observation 20.1
Using the JDBC API enables developers to change the underlying DBMS without modifying the Java code that accesses the database.
Most popular database management systems now provide JDBC drivers. There are also many third-party JDBC drivers available. In this chapter, we introduce JDBC and use it to manipulate MySQL and Java DB databases. The techniques demonstrated here can also be used to manipulate other databases that have JDBC drivers. Check your DBMS’s documentation to determine whether your DBMS comes with a JDBC driver. If not, third-party vendors provide JDBC drivers for many DBMSs.
For more information on JDBC, visit
java.sun.com/javase/technologies/database/index.jsp
This site contains JDBC information including the JDBC specification, FAQs, a learning resource center and software downloads to search for JDBC drivers for your DBMS,
developers.sun.com/product/jdbc/drivers/
This site provides a search engine to help you locate drivers appropriate for your DBMS.
20.2 Relational Databases
A relational database is a logical representation of data that allows the data to be accessed without consideration of its physical structure. A relational database stores data in tables. Figure 20.1 illustrates a sample table that might be used in a personnel system. The table name is Employee, and its primary purpose is to store the attributes of an employee. Tables are composed of rows, and rows are composed of columns in which values are stored. This table consists of six rows. The Number column of each row in this table is the table’s primary key—a column (or group of columns) in a table with a unique value that cannot be duplicated in other rows. This guarantees that each row can be identified by its primary key. Good examples of primary key columns are a social security number, an employee ID number and a part number in an inventory system, as values in each of these columns are guaranteed to be unique. The rows in Fig. 20.1 are displayed in order by primary key. In this case, the rows are listed in increasing order, but we could also use decreasing order.
Fig. 20.1. Employee table sample data.
Rows in tables are not guaranteed to be stored in any particular order. As we’ll demonstrate in an upcoming example, programs can specify ordering criteria when requesting data from a database.
Each column represents a different data attribute. Rows are normally unique (by primary key) within a table, but particular column values may be duplicated between rows. For example, three different rows in the Employee table’s Department column contain number 413.
Different users of a database are often interested in different data and different relationships among the data. Most users require only subsets of the rows and columns. To obtain these subsets, we use queries to specify which data to select from a table. You use SQL to define complex queries that select data from a table. For example, you might select data from the Employee table to create a result that shows where each department is located, and present the data sorted in increasing order by department number. This result is shown in Fig. 20.2. SQL queries are discussed in Section 20.4.
Fig. 20.2. Result of selecting distinct Department and Location data from table Employee.
20.3 Relational Database Overview: The books Database
We now overview relational databases in the context of a sample books database we created for this chapter. Before we discuss SQL, we overview the tables of the books database. We use this database to introduce various database concepts, including how to use SQL to obtain information from the database and to manipulate the data. We provide a script to create the database. You can find the script in the examples directory for this chapter. Section 20.5 explains how to use this script.
The database consists of three tables: authors, authorISBN and titles. The authors table (described in Fig. 20.3) consists of three columns that maintain each author’s unique ID number, first name and last name. Figure 20.4 contains sample data from the authors table of the books database.
Fig. 20.3. authors table from the books database.
Fig. 20.4. Sample data from the authors table.
The authorISBN table (described in Fig. 20.5) consists of two columns that maintain each ISBN and the corresponding author’s ID number. This table associates authors with their books. Both columns are foreign keys that represent the relationship between the tables authors and titles—one row in table authors may be associated with many rows in table titles, and vice versa. Figure 20.6 contains sample data from the authorISBN table of the books database. [Note: To save space, we have split the contents of this table into two columns, each containing the authorID and isbn columns.] The authorID column is a foreign key—a column in this table that matches the primary key column in another table (i.e., authorID in the authors table). Foreign keys are specified when creating a table. The foreign key helps maintain the Rule of Referential Integrity: Every foreign-key value must appear as another table’s primary-key value. This enables the DBMS to determine whether the authorID value for a particular book is valid. Foreign keys also allow related data in multiple tables to be selected from those tables for analytic purposes—this is known as joining the data.
Fig. 20.5. authorISBN table from the books database.
Fig. 20.6. Sample data from the authorISBN table of books.
The titles table described in Fig. 20.7 consists of four columns that stand for the ISBN, the title, the edition number and the copyright year. The table is in Fig. 20.8.
Fig. 20.7. titles table from the books database.
Fig. 20.8. Sample data from the titles table of the books database.
There is a one-to-many relationship between a primary key and a corresponding foreign key (e.g., one publisher can publish many books). A foreign key can appear many times in its own table, but can appear only once (as the primary key) in another table. Figure 20.9 is an entity-relationship (ER) diagram for the books database. This diagram shows the database tables and the relationships among them. The first compartment in each box contains the table’s name. The names in italic are primary keys. A table’s primary key uniquely identifies each row in the table. Every row must have a primary-key value, and that value must be unique in the table. This is known as the Rule of Entity Integrity.
Fig. 20.9. Table relationships in the books database.
Common Programming Error 20.1
Not providing a value for every column in a primary key breaks the Rule of Entity Integrity and causes the DBMS to report an error.
Common Programming Error 20.2
Providing the same value for the primary key in multiple rows causes the DBMS to report an error.
The lines connecting the tables (Fig. 20.9) represent the relationships between the tables. Consider the line between the authorISBN and authors tables. On the authors end of the line, there is a 1, and on the authorISBN end, there is an infinity symbol (∞), indicating a one-to-many relationship in which every author in the authors table can have an arbitrary number of books in the authorISBN table. Note that the relationship line links the authorID column in the table authors (i.e., its primary key) to the authorID column in table authorISBN (i.e., its foreign key). The authorID column in the authorISBN table is a foreign key.
Common Programming Error 20.3
Providing a foreign-key value that does not appear as a primary-key value in another table breaks the Rule of Referential Integrity and causes the DBMS to report an error.
The line between the titles and authorISBN tables illustrates another one-to-many relationship; a title can be written by any number of authors. In fact, the sole purpose of the authorISBN table is to provide a many-to-many relationship between the authors and titles tables—an author can write any number of books and a book can have any number of authors.
20.4 SQL
We now provide an overview of SQL in the context of our books database. You’ll be able to use the SQL discussed here in the examples later in the chapter and in examples in Chapters 21–23.
The next several subsections discuss the SQL keywords listed in Fig. 20.10 in the context of SQL queries and statements. Other SQL keywords are beyond this text’s scope. To learn other keywords, refer to the SQL reference guide supplied by the vendor of the RDBMS you are using. [Note: For more information on SQL, refer to the web resources in Section 20.15.]
Fig. 20.10. SQL query keywords.
20.4.1 Basic SELECT Query
Let us consider several SQL queries that extract information from database books. A SQL query “selects” rows and columns from one or more tables in a database. Such selections are performed by queries with the SELECT keyword. The basic form of a SELECT query is
SELECT * FROM tableName
in which the asterisk (*) indicates that all columns from the tableName table should be retrieved. For example, to retrieve all the data in the authors table, use
SELECT * FROM authors
Most programs do not require all the data in a table. To retrieve only specific columns from a table, replace the asterisk (*) with a comma-separated list of the column names. For example, to retrieve only the columns authorID and lastName for all rows in the authors table, use the query
SELECT authorID, lastName FROM authors
This query returns the data listed in Fig. 20.11.
Fig. 20.11. Sample authorID and lastName data from the authors table.
Software Engineering Observation 20.2
For most queries, the asterisk (*) should not be used to specify column names. In general, you process results by knowing in advance the order of the columns in the result—for example, selecting authorID and lastName from table authors ensures that the columns will appear in the result with authorID as the first column and lastName as the second column. Programs typically process result columns by specifying the column number in the result (starting from number 1 for the first column). Selecting columns by name also avoids returning unneeded columns and protects against changes in the actual order of the columns in the table(s).
Common Programming Error 20.4
If you assume that the columns are always returned in the same order from a query that uses the asterisk (*), the program may process the results incorrectly. If the column order in the table(s) changes or if additional columns are added at a later time, the order of the columns in the result would change accordingly.
20.4.2 WHERE Clause
In most cases, it is necessary to locate rows in a database that satisfy certain selection criteria. Only rows that satisfy the selection criteria (formally called predicates) are selected. SQL uses the optional WHERE clause in a query to specify the selection criteria for the query. The basic form of a query with selection criteria is
SELECT columnName1, columnName2, ... FROM tableName WHERE criteria
For example, to select the title, editionNumber and copyright columns from table titles for which the copyright date is greater than 2005, use the query
SELECT title, editionNumber, copyright
FROM titles
WHERE copyright > '2005'
Figure 20.12 shows the result of the preceding query. The WHERE clause criteria can contain the operators <, >, <=, >=, =, <> and LIKE. Operator LIKE is used for pattern matching with wildcard characters percent (%) and underscore (_). Pattern matching allows SQL to search for strings that match a given pattern.
Fig. 20.12. Sampling of titles with copyrights after 2005 from table titles.
A pattern that contains a percent character (%) searches for strings that have zero or more characters at the percent character’s position in the pattern. For example, the next query locates the rows of all the authors whose last name starts with the letter D:
SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE 'D%'
This query selects the two rows shown in Fig. 20.13—two of the four authors have a last name starting with the letter D (followed by zero or more characters). The % in the WHERE clause’s LIKE pattern indicates that any number of characters can appear after the letter D in the lastName. Note that the pattern string is surrounded by single-quote characters.
Fig. 20.13. Authors whose last name starts with D from the authors table.
Portability Tip 20.1
See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i.e., should they be all uppercase letters, all lowercase letters or some combination of the two?).
Read your database system’s documentation carefully to determine whether your system supports the LIKE operator. The SQL we discuss is supported by most RDBMSs, but it is always a good idea to check the features of SQL that are supported by your RDBMS.
An underscore (_) in the pattern string indicates a single wildcard character at that position in the pattern. For example, the following query locates the rows of all the authors whose last names start with any character (specified by _), followed by the letter o, followed by any number of additional characters (specified by %):
SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE '_o%'
The preceding query produces the row shown in Fig. 20.14, because only one author in our database has a last name that contains the letter o as its second letter.
Fig. 20.14. The only author from the authors table whose last name contains o as the second letter.
20.4.3 ORDER BY Clause
The rows in the result of a query can be sorted into ascending or descending order by using the optional ORDER BY clause. The basic form of a query with an ORDER BY clause is
SELECT columnName1, columnName2, ... FROM tableName ORDER BY column ASC
SELECT columnName1, columnName2, ... FROM tableName ORDER BY column DESC
where ASC specifies ascending order (lowest to highest), DESC specifies descending order (highest to lowest) and column specifies the column on which the sort is based. For example, to obtain the list of authors in ascending order by last name (Fig. 20.15), use the query
SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName ASC
Fig. 20.15. Sample data from table authors in ascending order by lastName.
Note that the default sorting order is ascending, so ASC is optional. To obtain the same list of authors in descending order by last name (Fig. 20.16), use the query
SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName DESC
Fig. 20.16. Sample data from table authors in descending order by lastName.
Multiple columns can be used for sorting with an ORDER BY clause of the form
ORDER BY column1 sortingOrder, column2 sortingOrder, ...
where sortingOrder is either ASC or DESC. Note that the sortingOrder does not have to be identical for each column. The query
SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName, firstName
sorts all the rows in ascending order by last name, then by first name. If any rows have the same last name value, they are returned sorted by first name (Fig. 20.17).
Fig. 20.17. Sample data from authors in ascending order by lastName and firstName.
The WHERE and ORDER BY clauses can be combined in one query, as in
SELECT isbn, title, editionNumber, copyright
FROM titles
WHERE title LIKE '%How to Program'
ORDER BY title ASC
which returns the isbn, title, editionNumber and copyright of each book in the titles table that has a title ending with "How to Program" and sorts them in ascending order by title. A portion of the query results are shown in Fig. 20.18.
Fig. 20.18. Sampling of books from table titles whose titles end with How to Program in ascending order by title.
20.4.4 Merging Data from Multiple Tables: INNER JOIN
Database designers often split related data into separate tables to ensure that a database does not store data redundantly. For example, the books database has tables authors and titles. We use an authorISBN table to store the relationship data between authors and their corresponding titles. If we did not separate this information into individual tables, we would need to include author information with each entry in the titles table. This would result in the database storing duplicate author information for authors who wrote multiple books. Often, it is necessary to merge data from multiple tables into a single result. Referred to as joining the tables, this is specified by an INNER JOIN operator in the query. An INNER JOIN merges rows from two tables by matching values in columns that are common to the tables. The basic form of an INNER JOIN is:
SELECT columnName1, columnName2, ...
FROM table1
INNER JOIN table2
ON table1.columnName = table2.columnName
The ON clause of the INNER JOIN specifies the columns from each table that are compared to determine which rows are merged. For example, the following query produces a list of authors accompanied by the ISBNs for books written by each author:
SELECT firstName, lastName, isbn
FROM authors
INNER JOIN authorISBN
ON authors.authorID = authorISBN.authorID
ORDER BY lastName, firstName
The query merges the firstName and lastName columns from table authors with the isbn column from table authorISBN, sorting the result in ascending order by lastName and firstName. Note the use of the syntax tableName.columnName in the ON clause. This syntax, called a qualified name, specifies the columns from each table that should be compared to join the tables. The “tableName.” syntax is required if the columns have the same name in both tables. The same syntax can be used in any query to distinguish columns in different tables that have the same name. In some systems, table names qualified with the database name can be used to perform cross-database queries. As always, the query can contain an ORDER BY clause. Figure 20.19 depicts a portion of the results of the preceding query, ordered by lastName and firstName. [Note: To save space, we split the result of the query into two columns, each containing the firstName, lastName and isbn columns.]
Fig. 20.19. Sampling of authors and ISBNs for the books they have written in ascending order by lastName and firstName.
Software Engineering Observation 20.3
If a SQL statement includes columns with the same name from multiple tables, the statement must precede those column names with their table names and a dot (e.g., authors.authorID).
Failure to qualify names for columns that have the same name in two or more tables is an error.
20.4.5 INSERT Statement
The INSERT statement inserts a row into a table. The basic form of this statement is
INSERT INTO tableName ( columnName1, columnName2, ..., columnNameN )
VALUES ( value1, value2, ..., valueN )
where tableName is the table in which to insert the row. The tableName is followed by a comma-separated list of column names in parentheses (this list is not required if the INSERT operation specifies a value for every column of the table in the correct order). The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. The values specified here must match the columns specified after the table name in both order and type (e.g., if columnName1 is supposed to be the firstName column, then value1 should be a string in single quotes representing the first name). Always explicitly list the columns when inserting rows. If the table’s column order changes or a new column is added, using only VALUES may cause an error. The INSERT statement
INSERT INTO authors ( firstName, lastName )
VALUES ( 'Sue', 'Smith' )
inserts a row into the authors table. The statement indicates that values are provided for the firstName and lastName columns. The corresponding values are 'Sue' and 'Smith'. We do not specify an authorID in this example because authorID is an autoincremented column in the authors table. For every row added to this table, MySQL assigns a unique authorID value that is the next value in the autoincremented sequence (i.e., 1, 2, 3 and so on). In this case, Sue Smith would be assigned authorID number 5. Figure 20.20 shows the authors table after the INSERT operation. [Note: Not every database management system supports autoincremented columns. Check the documentation for your DBMS for alternatives to autoincremented columns.]
Fig. 20.20. Sample data from table Authors after an INSERT operation.
Common Programming Error 20.6
It is normally an error to specify a value for an autoincrement column.
SQL uses the single-quote (') character as a delimiter for strings. To specify a string containing a single quote (e.g., O’Malley) in a SQL statement, the string must have two single quotes in the position where the single-quote character appears in the string (e.g., 'O''Malley'). The first of the two single-quote characters acts as an escape character for the second. Not escaping single-quote characters in a string that is part of a SQL statement is a SQL syntax error.
20.4.6 UPDATE Statement
An UPDATE statement modifies data in a table. The basic form of the UPDATE statement is
UPDATE tableName
SET columnName1 = value1, columnName2 = value2, ..., columnNameN = valueN
WHERE criteria
where tableName is the table to update. The tableName is followed by keyword SET and a comma-separated list of column name/value pairs in the format columnName = value. The optional WHERE clause provides criteria that determine which rows to update. Though not required, the WHERE clause is typically used, unless a change is to be made to every row. The UPDATE statement
UPDATE authors
SET lastName = 'Jones'
WHERE lastName = 'Smith' AND firstName = 'Sue'
updates a row in the authors table. The statement indicates that lastName will be assigned the value Jones for the row in which lastName is equal to Smith and firstName is equal to Sue. [Note: If there are multiple rows with the first name “Sue” and the last name “Smith,” this statement will modify all such rows to have the last name “Jones.”] If we know the authorID in advance of the UPDATE operation (possibly because we searched for it previously), the WHERE clause can be simplified as follows:
WHERE AuthorID = 5
Figure 20.21 shows the authors table after the UPDATE operation has taken place.
Fig. 20.21. Sample data from table authors after an UPDATE operation.
20.4.7 DELETE Statement
A SQL DELETE statement removes rows from a table. The basic form of a DELETE is
DELETE FROM tableName WHERE criteria
where tableName is the table from which to delete. The optional WHERE clause specifies the criteria used to determine which rows to delete. If this clause is omitted, all the table’s rows are deleted. The DELETE statement
DELETE FROM authors
WHERE lastName = 'Jones' AND firstName = 'Sue'
deletes the row for Sue Jones in the authors table. If we know the authorID in advance of the DELETE operation, the WHERE clause can be simplified as follows:
WHERE authorID = 5
Figure 20.22 shows the authors table after the DELETE operation has taken place.
Fig. 20.22. Sample data from table authors after a DELETE operation.
20.5 Instructions for Installing MySQL and MySQL Connector/J
MySQL 5.0 Community Edition is an open-source database management system that executes on many platforms, including Windows, Solaris, Linux, and Macintosh. Complete information about MySQL is available from www.mysql.com. The examples in Section 20.8 and Section 20.9 manipulate MySQL databases.
Installing MySQL
To install MySQL Community Edition:
1. To learn about the installation requirements for your platform, visit the site dev.mysql.com/doc/refman/5.0/en/installing-cs.html.
2. Visit dev.mysql.com/downloads/mysql/5.0.html and download the installer for your platform. For the MySQL examples in this chapter, you need only the Windows Essentials package on Microsoft Windows, or the Standard package on most other platforms. [Note: For these instructions, we assume you are running Microsoft Windows. Complete installation instructions for other platforms are available at dev.mysql.com/doc/refman/5.0/en/installing.html.]
3. Double click mysql-essential-5.0.67-win32.msi to start the installer. [Note: This name may differ based on the current version of MySQL 5.0.] Click Next >.
4. Choose Typical for the Setup Type and click Next >. Then click Install.
When the installation completes, click Next > twice, then Finish to begin configuring the serrver. To configure the server:
1. Click Next > then select Standard Configuration and click Next > again.
2. You have the option of installing MySQL as a Windows service, which enables the MySQL server to begin executing automatically each time your system starts. For our examples, this is unnecessary, so uncheck Install as a Windows Service, then check Include Bin Directory in Windows PATH. This will enable you to use the MySQL commands in the Windows Command Prompt.
3. Click Next > then click Execute to perform the server configuration.
4. Click Finish to close the wizard.
Installing MySQL Connector/J
To use MySQL with JDBC, you also need to install MySQL Connector/J (the J stands for Java)—a JDBC driver that allows programs to use JDBC to interact with MySQL. MySQL Connector/J can be downloaded from
dev.mysql.com/downloads/connector/j/5.1.html
The documentation for Connector/J is located at dev.mysql.com/doc/connector/j/en/connector-j.html. At the time of this writing, the current generally available release of MySQL Connector/J is 5.1.7. To install MySQL Connector/J:
1. Download mysql-connector-java-5.1.7.tar.gz.
2. Open mysql-connector-java-5.1.7.tar.gz with a file extractor, such as WinZip (www.winzip.com). Extract its contents to the C: drive. This will create a directory named mysql-connector-java-5.1.7. This folder’s docs subdirectory contains the documentation for MySQL Connector/J (connector-j.pdf). You can also view it online at dev.mysql.com/doc/connector/j/en/connector-j.html.
20.6 Instructions for Setting Up a MySQL User Account
For the MySQL examples to execute correctly, you need to set up a user account that allows users to create, delete and modify a database. After MySQL is installed, follow the steps below to set up a user account (these steps assume MySQL is installed in its default installation directory):
1. Open a Command Prompt and start the database server by executing the command mysqld-nt.exe. Note that this command has no output—it simply starts the MySQL server. Do not close this window—doing so terminates the server.
2. Next, you’ll start the MySQL monitor so you can set up a user account, open another Command Prompt and execute the command
mysql -h localhost -u root
The -h option indicates the host (i.e., computer) on which the MySQL server is running—in this case your local computer (localhost). The -u option indicates the user account that will be used to log in to the server—root is the default user account that is created during installation to allow you to configure the server. Once you’ve logged in, you’ll see a mysql> prompt at which you can type commands to interact with the MySQL server.
USE mysql;
to select the built-in database named mysql, which stores server information, such as user accounts and their privileges for interacting with the server. Note that each command must end with a semicolon. To confirm the command, MySQL issues the message “Database changed.”
4. Next, you’ll add the javafp user account to the mysql built-in database. The mysql database contains a table called user with columns that represent the user’s name, password and various privileges. To create the javafp user account with the password javafp, execute the following commands from the mysql> prompt:
create user 'javafp'@'localhost' identified by 'javafp';
grant select, insert, update, delete, create, drop, references,
execute on *.* to 'javafp'@'localhost';
This creates the javafp user with the privileges needed to create the databases used in this chapter and manipulate those databases.
5. Type the command
exit;
to terminate the MySQL monitor.
20.7 Creating Database books in MySQL
For each MySQL database we discuss in this book, we provide a SQL script in a file with the .sql extension that sets up the database and its tables. You can execute these scripts in the MySQL monitor. In the examples directory for this chapter, you’ll find the SQL script books.sql to create the books database. For the following steps, we assume that the MySQL server (mysqld-nt.exe) is still running. To execute the books.sql script:
1. Open a Command Prompt and use the cd command to change directories to the location that contains the books.sql script.
2. Start the MySQL monitor by typing
mysql -h localhost -u javafp -p
The -p option prompts you for the password for the javafp user account. When prompted, enter the password javafp.
3. Execute the script by typing
source books.sql;
This creates a new directory named books in the server’s data directory—located on Windows at C:ProgramFilesMySQLMySQLServer5.0data by default. This new directory contains the books database.
4. Type the command
exit;
to terminate the MySQL monitor. You are now ready to proceed to the first JDBC example.
20.8 Manipulating Databases with JDBC
In this section, we present two examples. The first example introduces how to connect to a database and query the database. The second example demonstrates how to display the result of the query in a JTable.
20.8.1 Connecting to and Querying a Database
The example of Fig. 20.23 performs a simple query on the books database that retrieves the entire authors table and displays the data. The program illustrates connecting to the database, querying the database and processing the result. The following discussion presents the key JDBC aspects of the program. [Note: Sections 20.5––20.7 demonstrate how to start the MySQL server, configure a user account and create the books database. These steps must be performed before executing the program of Fig. 20.23.]
Fig. 20.23. Displaying the contents of the authors table.
Lines 3–8 import the JDBC interfaces and classes from package java.sql used in this program. Line 13 declares a string constant for the database URL. This identifies the name of the database to connect to, as well as information about the protocol used by the JDBC driver (discussed shortly). Method main (lines 16–69) connects to the books database, queries the database, displays the result of the query and closes the database connection.
In past versions of Java, programs were required to load an appropriate database driver before connecting to a database. JDBC 4.0, part of Java SE 6, supports automatic driver discovery—you are no longer required to load the database driver in advance. To ensure that the program can locate the database driver class, you must include the class’s location in the program’s classpath when you execute the program. For MySQL, you include the file mysql-connector-java-5.1.7-bin.jar (in the C:mysql-connector-java-5.1.7 directory) in your program’s classpath, as in:
java -classpath
.;c:mysql-connector-java-5.1.7mysql-connector-java-5.1.7-bin.jar
DisplayAuthors
In the -classpath option of the preceding command, notice the period (.) at the beginning of the classpath information. If this period is missing, the JVM will not look for classes in the current directory and thus will not find the DisplayAuthors class file. You may also copy the mysql-connector-java-5.1.7-bin.jar file to your JDK’s jrelibext folder. After doing so, you can run the application simply using the command
java DisplayAuthors
Software Engineering Observation 20.4
Most major database vendors provide their own JDBC database drivers, and many third-party vendors provide JDBC drivers as well. For more information on JDBC drivers, visit the Sun Microsystems JDBC website, servlet.java.sun.com/products/jdbc/drivers.
Lines 26–27 of Fig. 20.23 create a Connection object (package java.sql) referenced by connection. An object that implements interface Connection manages the connection between the Java program and the database. Connection objects enable programs to create SQL statements that manipulate databases. The program initializes connection with the result of a call to static method getConnection of class DriverManager (package java.sql), which attempts to connect to the database specified by its URL. Method getConnection takes three arguments—a String that specifies the database URL, a String that specifies the username and a String that specifies the password. The username and password are set in Section 20.6. If you used a different username and password, you need to replace the username (second argument) and password (third argument) passed to method getConnection in line 27. The URL locates the database (possibly on a network or in the local file system of the computer). The URL jdbc:mysql://localhost/books specifies the protocol for communication (jdbc), the subprotocol for communication (mysql) and the location of the database (//localhost/books, where localhost is the host running the MySQL server and books is the database name). The subprotocol mysql indicates that the program uses a MySQL-specific subprotocol to connect to the MySQL database. If the DriverManager cannot connect to the database, method getConnection throws a SQLException (package java.sql). Figure 20.24 lists the JDBC driver names and database URL formats of several popular RDBMSs.
Fig. 20.24. Popular JDBC database URL formats.
Software Engineering Observation 20.5
Most database management systems require the user to log in before accessing the database contents. DriverManager method getConnection is overloaded with versions that enable the program to supply the user name and password to gain access.
Line 30 invokes Connection method createStatement to obtain an object that implements interface Statement (package java.sql). The program uses the Statement object to submit SQL to the database.
Lines 33–34 use the Statement object’s executeQuery method to submit a query that selects all the author information from table authors. This method returns an object that implements interface ResultSet and contains the query results. The ResultSet methods enable the program to manipulate the query result.
Lines 37–50 process the ResultSet. Line 37 obtains the metadata for the ResultSet as a ResultSetMetaData (package java.sql) object. The metadata describes the ResultSet’s contents. Programs can use metadata programmatically to obtain information about the ResultSet’s column names and types. Line 38 uses ResultSetMetaData method getColumnCount to retrieve the number of columns in the ResultSet. Lines 41–42 display the column names.
Software Engineering Observation 20.6
Metadata enables programs to process ResultSet contents dynamically when detailed information about the ResultSet is not known in advance.
Lines 45–50 display the data in each ResultSet row. First, the program positions the ResultSet cursor (which points to the row being processed) to the first row in the ResultSet with method next (line 45). Method next returns boolean value true if it is able to position to the next row; otherwise, the method returns false.
Common Programming Error 20.8
Initially, a ResultSet cursor is positioned before the first row. A SQLException occurs if you attempt to access a ResultSet’s contents before positioning the ResultSet cursor to the first row with method next.
If there are rows in the ResultSet, line 48 extracts the contents of one column in the current row. When processing a ResultSet, it is possible to extract each column of the ResultSet as a specific Java type. In fact, ResultSetMetaData method getColumnType returns a constant integer from class Types (package java.sql) indicating the type of a specified column. Programs can use these values in a switch statement to invoke ResultSet methods that return the column values as appropriate Java types. If the type of a column is Types.INTEGER, ResultSet method getInt returns the column value as an int. ResultSet get methods typically receive as an argument either a column number (as an int) or a column name (as a String) indicating which column’s value to obtain. Visit
java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/GettingStartedTOC.fm.html
for detailed mappings of SQL data types to Java types and to determine the appropriate ResultSet method to call for each SQL data type.
Performance Tip 20.1
If a query specifies the exact columns to select from the database, the ResultSet contains the columns in the specified order. In this case, using the column number to obtain the column’s value is more efficient than using the column name. The column number provides direct access to the specified column. Using the column name requires a search of the column names to locate the appropriate column.
For simplicity, this example treats each value as an Object. The program retrieves each column value with ResultSet method getObject (line 48) and prints the String representation of the Object. Note that, unlike array indices, which start at 0, ResultSet column numbers start at 1. The finally block (lines 56–68) closes the ResultSet (line 60), the Statement (line 61) and the database Connection (line 62). [Note: Lines 60–62 will throw NullPointerExceptions if the ResultSet, Statement or Connection objects were not created properly. In production code, you should check the variables that refer to these objects to see if they are null before you call close.]
Common Programming Error 20.9
Specifying column number 0 when obtaining values from a ResultSet causes a SQLException.
Common Programming Error 20.10
A SQLException occurs if you attempt to manipulate a ResultSet after closing the Statement that created it. The ResultSet is discarded when the corresponding Statement is closed.
Software Engineering Observation 20.7
Each Statement object can open only one ResultSet object at a time. When a Statement returns a new ResultSet, the Statement closes the prior ResultSet. To use multiple ResultSets in parallel, separate Statement objects must return the ResultSets.
20.8.2 Querying the books Database
The next example (Fig. 20.25 and Fig. 20.28) allows the user to enter any query into the program. The example displays the result of a query in a JTable, using a TableModel object to provide the ResultSet data to the JTable. A JTable is a swing GUI component that can be bound to a database to display the results of a query. Class ResultSetTableModel (Fig. 20.25) performs the connection to the database via a TableModel and maintains the ResultSet. Class DisplayQueryResults (Fig. 20.28) creates the GUI and specifies an instance of class ResultSetTableModel to provide data for the JTable.
Fig. 20.25. A TableModel that supplies ResultSet data to a JTable.
ResultSetTableModel Class
Class ResultSetTableModel (Fig. 20.25) extends class AbstractTableModel (package javax.swing.table), which implements interface TableModel. Class ResultSetTableModel overrides TableModel methods getColumnClass, getColumnCount, getColumnName, getRowCount and getValueAt. The default implementations of TableModel methods isCellEditable and setValueAt (provided by AbstractTableModel) are not overridden, because this example does not support editing the JTable cells. The default implementations of TableModel methods addTableModelListener and removeTableModelListener (provided by AbstractTableModel) are not overridden, because the implementations of these methods in AbstractTableModel properly add and remove event listeners.
The ResultSetTableModel constructor (lines 30–46) accepts four String arguments—the URL of the database, the username, the password and the default query to perform. The constructor throws any exceptions that occur in its body back to the application that created the ResultSetTableModel object, so that the application can determine how to handle the exception (e.g., report an error and terminate the application). Line 34 establishes a connection to the database. Lines 37–39 invoke Connection method createStatement to create a Statement object. This example uses a version of method createStatement that takes two arguments—the result set type and the result set concurrency. The result set type (Fig. 20.26) specifies whether the ResultSet’s cursor is able to scroll in both directions or forward only and whether the ResultSet is sensitive to changes. ResultSets that are sensitive to changes reflect those changes immediately after they are made with methods of interface ResultSet. If a ResultSet is insensitive to changes, the query that produced the ResultSet must be executed again to reflect any changes made. The result set concurrency (Fig. 20.27) specifies whether the ResultSet can be updated with ResultSet’s update methods. This example uses a ResultSet that is scrollable, insensitive to changes and read only. Line 45 invokes our method setQuery (lines 144–163) to perform the default query.
Fig. 20.26. ResultSet constants for specifying ResultSet type.
Fig. 20.27. ResultSet constants for specifying result properties.
Portability Tip 20.3
Some JDBC drivers do not support scrollable ResultSets. In such cases, the driver typically returns a ResultSet in which the cursor can move only forward. For more information, see your database driver documentation.
Some JDBC drivers do not support updatable ResultSets. In such cases, the driver typically returns a read-only ResultSet. For more information, see your database driver documentation.
Common Programming Error 20.11
Attempting to update a ResultSet when the database driver does not support updatable ResultSets causes SQLFeatureNotSupportedExceptions.
Common Programming Error 20.12
Attempting to move the cursor backward through a ResultSet when the database driver does not support backward scrolling causes a SQLException.
Method getColumnClass (lines 49–69) returns a Class object that represents the superclass of all objects in a particular column. The JTable uses this information to configure the default cell renderer and cell editor for that column in the JTable. Line 58 uses ResultSetMetaData method getColumnClassName to obtain the fully qualified class name for the specified column. Line 51 loads the class and returns the corresponding Class object. If an exception occurs, the catch in lines 63–66 prints a stack trace and line 68 returns Object.class—the Class instance that represents class Object—as the default type. [Note: Line 58 uses the argument column + 1. Like arrays, JTable row and column numbers are counted from 0. However, ResultSet row and column numbers are counted from 1. Thus, when processing ResultSet rows or columns for use in a JTable, it is necessary to add 1 to the row or column number to manipulate the appropriate ResultSet row or column.]
Method getColumnCount (lines 72–89) returns the number of columns in the model’s underlying ResultSet. Line 81 uses ResultSetMetaData method getColumnCount to obtain the number of columns in the ResultSet. If an exception occurs, the catch in lines 83–86 prints a stack trace and line 88 returns 0 as the default number of columns.
Method getColumnName (lines 92–109) returns the name of the column in the model’s underlying ResultSet. Line 101 uses ResultSetMetaData method getColumnName to obtain the column name from the ResultSet. If an exception occurs, the catch in lines 103–106 prints a stack trace and line 108 returns the empty string as the default column name.
Method getRowCount (lines 112–119) returns the number of rows in the model’s underlying ResultSet. When method setQuery (lines 144–163) performs a query, it stores the number of rows in variable numberOfRows.
Method getValueAt (lines 122–141) returns the Object in a particular row and column of the model’s underlying ResultSet. Line 132 uses ResultSet method absolute to position the ResultSet cursor at a specific row. Line 133 uses ResultSet method getObject to obtain the Object in a specific column of the current row. If an exception occurs, the catch in lines 135–138 prints a stack trace and line 140 returns an empty string as the default value.
Method setQuery (lines 144–163) executes the query it receives as an argument to obtain a new ResultSet (line 152). Line 155 gets the ResultSetMetaData for the new ResultSet. Line 158 uses ResultSet method last to position the ResultSet cursor at the last row in the ResultSet. [Note: This can be slow if the table contains many rows.] Line 159 uses ResultSet method getRow to obtain the row number for the current row in the ResultSet. Line 162 invokes method fireTableStructureChanged (inherited from class AbstractTableModel) to notify any JTable using this ResultSetTableModel object as its model that the structure of the model has changed. This causes the JTable to repopulate its rows and columns with the new ResultSet data. Method setQuery throws any exceptions that occur in its body back to the application that invoked setQuery.
Method disconnectFromDatabase (lines 166–186) implements an appropriate termination method for class ResultSetTableModel. A class designer should provide a public method that clients of the class must invoke explicitly to free resources that an object has used. In this case, method disconnectFromDatabase closes the ResultSet, Statement and Connection (lines 173–175), which are considered limited resources. Clients of the ResultSetTableModel class should always invoke this method when the instance of this class is no longer needed. Before releasing resources, line 168 verifies whether the connection is already terminated. If not, the method proceeds. Note that the other methods in class ResultSetTableModel each throw an IllegalStateException if connectedToDatabase is false. Method disconnectFromDatabase sets connectedToDatabase to false (line 183) to ensure that clients do not use an instance of ResultSetTableModel after that instance has already been terminated. IllegalStateException is an exception from the Java libraries that is appropriate for indicating this error condition.
DisplayQueryResults Class
Class DisplayQueryResults (Fig. 20.28) implements the application’s GUI and interacts with the ResultSetTableModel via a JTable object. This application also demonstrates the JTable sorting and filtering capabilities introduced in Java SE 6.
Fig. 20.28. Displays contents of the database books.
Lines 27–29 and 32 declare the URL, username, password and default query that are passed to the ResultSetTableModel constructor to make the initial connection to the database and perform the default query. The DisplayQueryResults constructor (lines 38–189) creates a ResultSetTableModel object and the GUI for the application. Line 68 creates the JTable object and passes a ResultSetTableModel object to the JTable constructor, which then registers the JTable as a listener for TableModelEvents generated by the ResultSetTableModel.
Lines 85–124 register an event handler for the submitButton that the user clicks to submit a query to the database. When the user clicks the button, method actionPerformed (lines 90–122) invokes method setQuery from the class ResultSetTableModel to execute the new query. If the user’s query fails (e.g., because of a syntax error in the user’s input), lines 107–108 execute the default query. If the default query also fails, there could be a more serious error, so line 117 ensures that the database connection is closed and line 119 exits the program. The screen captures in Fig. 20.28 show the results of two queries. The first screen capture shows the default query that retrieves all the data from table authors of database books. The second screen capture shows a query that selects each author’s first name and last name from the authors table and combines that information with the title and edition number from the titles table. Try entering your own queries in the text area and clicking the Submit Query button to execute the query.
As of Java SE 6, JTables now allow users to sort rows by the data in a specific column. Lines 126–127 use the TableRowSorter class (from package javax.swing.table) to create an object that uses our ResultSetTableModel to sort rows in the JTable that displays query results. When the user clicks the title of a particular JTable column, the TableRowSorter interacts with the underlying TableModel to reorder the rows based on the data in that column. Line 128 uses JTable method setRowSorter to specify the TableRowSorter for resultTable.
JTables can now show subsets of the data from the underlying TableModel. This is known as filtering the data. Lines 133–159 register an event handler for the filterButton that the user clicks to filter the data. In method actionPerformed (lines 137–157), line 139 obtains the filter text. If the user did not specify filter text, line 142 uses JTable method setRowFilter to remove any prior filter by setting the filter to null. Otherwise, lines 147–148, use setRowFilter to specify a RowFilter (from package javax.swing) based on the user’s input. Class RowFilter provides several methods for creating filters. The static method regexFilter receives a String containing a regular expression pattern as its argument and an optional set of indices that specify which columns to filter. If no indices are specified, then all the columns are searched. In this example, the regular expression pattern is the text the user typed. Once the filter is set, the data displayed in the JTable is updated based on the filtered TableModel.
20.9 RowSet Interface
In the previous examples, you learned how to query a database by explicitly establishing a Connection to the database, preparing a Statement for querying the database and executing the query. In this section, we demonstrate the RowSet interface, which configures the database connection and prepares query statements automatically. The interface RowSet provides several set methods that allow you to specify the properties needed to establish a connection (such as the database URL, user name and password of the database) and create a Statement (such as a query). RowSet also provides several get methods that return these properties.
There are two types of RowSet objects—connected and disconnected. A connected RowSet object connects to the database once and remains connected until the application terminates. A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection. A program may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.
Package javax.sql.rowset contains two subinterfaces of RowSet—JdbcRowSet and CachedRowSet. JdbcRowSet, a connected RowSet, acts as a wrapper around a ResultSet object, and allows you to scroll through and update the rows in the ResultSet. Recall that by default, a ResultSet object is non-scrollable and read only—you must explicitly set the result set type constant to TYPE_SCROLL_INSENSITIVE and set the result set concurrency constant to CONCUR_UPDATABLE to make a ResultSet object scrollable and updatable. A JdbcRowSet object is scrollable and updatable by default. CachedRowSet, a disconnected RowSet, caches the data of a ResultSet in memory and disconnects from the database. Like JdbcRowSet, a CachedRowSet object is scrollable and updatable by default. A CachedRowSet object is also serializable, so it can be passed between Java applications through a network, such as the Internet. However, CachedRowSet has a limitation—the amount of data that can be stored in memory is limited. Package javax.sql.rowset contains three other subinterfaces of RowSet. For details of these interfaces, visit java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/rowsetImpl.html.
Portability Tip 20.5
A RowSet can provide scrolling capability for drivers that do not support scrollable ResultSets.
Figure 20.29 reimplements the example of Fig. 20.23 using a RowSet. Rather than establish the connection and create a Statement explicitly, Fig. 20.29 uses a JdbcRowSet object to create a Connection and a Statement automatically.
Fig. 20.29. Displaying the authors table using JdbcRowSet.
The package com.sun.rowset provides Sun’s reference implementations of the interfaces in package javax.sql.rowset. Line 23 uses Sun’s reference implementation of the JdbcRowSet interface—JdbcRowSetImpl—to create a JdbcRowSet object. We used class JdbcRowSetImpl here to demonstrate the capability of the JdbcRowSet interface. Other databases may provide their own RowSet implementations.
Lines 24–26 set the RowSet properties that are used by the DriverManager to establish a connection to the database. Line 24 invokes JdbcRowSet method setUrl to specify the database URL. Line 25 invokes JdbcRowSet method setUsername to specify the user-name. Line 26 invokes JdbcRowSet method setPassword to specify the password. Line 27 invokes JdbcRowSet method setCommand to specify the SQL query that will be used to populate the RowSet. Line 28 invokes JdbcRowSet method execute to execute the SQL query. Method execute performs four actions—it establishes a Connection to the database, prepares the query Statement, executes the query and stores the ResultSet returned by query. The Connection, Statement and ResultSet are encapsulated in the JdbcRowSet object.
The remaining code is almost identical to Fig. 20.23, except that line 31 obtains a ResultSetMetaData object from the JdbcRowSet, line 41 uses the JdbcRowSet’s next method to get the next row of the result and line 44 uses the JdbcRowSet’s getObject method to obtain a column’s value. Line 49 invokes JdbcRowSet method close, which closes the RowSet’s encapsulated ResultSet, Statement and Connection. In a CachedRowSet, invoking close also releases the memory held by that RowSet. Note that the output of this application is the same as that of Fig. 20.23.
20.10 Java DB/Apache Derby
As of JDK 6, Sun Microsystems now bundles the open-source, pure Java database Java DB (the Sun branded version of Apache Derby) with the JDK. In Section 20.11, we use Java DB to demonstrate so-called PreparedStatements. Before you can execute the application in the next section, you must set up the AddressBook database in Java DB. Section 20.11 uses the embedded version of Java DB. There is also a network version that executes similarly to the MySQL DBMS introduced earlier in the chapter. For the purpose of the following steps, we assume you are running Microsoft Windows with Java installed in its default location.
1. Java DB comes with several batch files to configure and run it. Before executing these batch files from a command prompt, you must set the environment variable JAVA_HOME to refer to the JDK’s C:ProgramFilesJavajdk1.6.0 installation directory.
2. Open the batch file setEmbeddedCP.bat (located in C:Program FilesSunJavaDBin) in a text editor such as Notepad. Locate the line
@rem set DERBY_INSTALL=
and change it to
@set DERBY_INSTALL=C:Program FilesSunJavaDB
Save your changes and close this file.
3. Open a Command Prompt and change directories to C:ProgramFilesSunJavaDBin. Then, type setEmbeddedCP.bat and press Enter to set the environment variables required by Java DB.
4. An embedded Java DB database must reside in the same location as the application that manipulates the database. For this reason, change to the directory that contains the code for Figs. 20.30–20.32. This directory contains a SQL script address.sql that builds the AddressBook database.
Fig. 20.30. Person class that represents an entry in an AddressBook.
Fig. 20.31. An interface that stores all the queries to be used by AddressBook.
Fig. 20.32. A simple address book.
5. Execute the command
"C:Program FilesSunJavaDBinij"
to start the command-line tool for interacting with Java DB. The double quotes are necessary because the path contains a space. This will display the ij> prompt.
6. At the ij> prompt type
connect 'jdbc:derby:AddressBook;create=true;user=javafp;
password=javafp';
to create the AddressBook database in the current directory. This command also creates the user javafp with the password javafp for accessing the database.
7. To create the database table and insert sample data in the database type
run 'address.sql';
8. To terminate the Java DB command-line tool, type
exit;
You are now ready to execute the AddressBook application in Section 20.12.
20.11 PreparedStatements
Interface PreparedStatement enables you to create compiled SQL statements that execute more efficiently than Statement objects. PreparedStatements also can specify parameters, making them more flexible than Statements. Programs can execute the same query repeatedly with different parameter values. For example, in the books database, you might want to locate all book titles for an author with a specific last name and first name, and you might want to execute that query for several authors. With a PreparedStatement, that query is defined as follows:
The two question marks (?) in the the preceding SQL statement’s last line are placeholders for values that will be passed as part of the query to the database. Before executing a PreparedStatement, the program must specify the parameter values by using the PreparedStatement interface’s set methods.
For the preceding query, both parameters are strings that can be set with PreparedStatement method setString as follows:
authorBooks.setString( 1, "Deitel" );
authorBooks.setString( 2, "Paul" );
Method setString’s first argument represents the number of the parameter being set and the second argument is that parameter’s value. Parameter numbers are counted from 1, starting with the first question mark (?). When the program executes the preceding PreparedStatement with the parameter values shown here, the SQL statement passed to the database is
Method setString automatically escapes String parameter values as necessary. For example, if the last name is O’Brien, the statement
authorBooks.setString( 1, "O'Brien" );
escapes the ' character in O’Brien by replacing it with two single-quote characters.
Performance Tip 20.2
PreparedStatements are more efficient than Statements when executing SQL statements multiple times and with different parameter values.
Error-Prevention Tip 20.1
Use PreparedStatements with parameters for queries that receive String values as arguments to ensure that the Strings are quoted properly in the SQL statement.
Interface PreparedStatement provides set methods for each supported SQL type. It is important to use the set method that is appropriate for the parameter’s SQL type in the database—SQLExceptions occur when a program attempts to convert a parameter value to an incorrect type. For a complete list of interface PreparedStatement’s set methods, see java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html.
Address Book Application that Uses PreparedStatements
We now present an address book application that enables you to browse existing entries, add new entries and search for entries with a specific last name. Our AddressBook Java DB database contains an Addresses table with the columns addressID, firstName, lastName, email and phoneNumber. The column addressID is a so-called identity column. This is the SQL standard way to represent an autoincremented column. The SQL script we provide for this database uses the SQL IDENTITY keyword to mark the addressID column as an identity column. For more information on using the IDENTITY keyword and creating databases, see the Java DB Developer’s Guide at developers.sun.com/docs/javadb/10.4.2.0/devguide/derbydev.pdf.
Our address book application consists of three classes—Person (Fig. 20.30), PersonQueries (Fig. 20.31) and AddressBookDisplay (Fig. 20.32). Class Person is a simple class that represents one person in the address book. The class contains fields for the address ID, first name, last name, email address and phone number, as well as set and get methods for manipulating these fields.
Class PersonQueries
Class PersonQueries (Fig. 20.31) manages the address book application’s database connection and creates the PreparedStatements that the application uses to interact with the database. Lines 18–20 declare three PreparedStatement variables. The constructor (lines 23–49) connects to the database at lines 27–28.
Lines 31–32 invoke Connection method prepareStatement to create the PreparedStatement named selectAllPeople that selects all the rows in the Addresses table. Lines 35–36 create the PreparedStatement named selectPeopleByLastName with a parameter. This statement selects all the rows in the Addresses table that match a particular last name. Notice the ? character that is used to specify the last name parameter. Lines 39–42 create the PreparedStatement named insertNewPerson with four parameters that represent the first name, last name, email address and phone number for a new entry. Again, notice the ? characters used to represent these parameters.
Method getAllPeople (lines 52–91) executes PreparedStatementselectAllPeople (line 60) by calling method executeQuery, which returns a ResultSet containing the rows that match the query (in this case, all the rows in the Addresses table). Lines 61–71 place the query results in an ArrayList of Person objects, which is returned to the caller at line 90. Method getPeopleByLastName (lines 94–136) uses PreparedStatement method setString to set the parameter to selectPeopleByLastName. Then, line 105 executes the query and lines 106–116 place the query results in an ArrayList of Person objects. Line 135 returns the ArrayList to the caller.
Method addPerson (lines 139–162) uses PreparedStatement method setString (lines 147–150) to set the parameters for the insertNewPersonPreparedStatement. Line 153 uses PreparedStatement method executeUpdate to insert the new record. This method returns an integer indicating the number of rows that were updated (or inserted) in the database. Method close (lines 165–175) simply closes the database connection.
Class AddressBookDisplay
The AddressBookDisplay (Fig. 20.32) application uses an object of class PersonQueries to interact with the database. Line 59 creates the PersonQueries object used throughout class AddressBookDisplay. When the user presses the Browse All Entries JButton, the browseButtonActionPerformed handler (lines 309–335) is called. Line 313 calls the method getAllPeople on the PersonQueries object to obtain all the entries in the database. The user can then scroll through the entries using the Previous and Next JButtons. When the user presses the Find JButton, the queryButtonActionPerformed handler (lines 265–287) is called. Lines 267–268 call method getPeopleByLastName on the PersonQueries object to obtain the entries in the database that match the specified last name. If there are several such entries, the user can then scroll through them using the Previous and Next JButtons.
To add a new entry into the AddressBook database, the user can enter the first name, last name, email and phone number (the AddressID will autoincrement) in the JTextFields and press the Insert New Entry JButton. When the user presses Insert New Entry, the insertButtonActionPerformed handler (lines 338–352) is called. Lines 340–342 call the method addPerson on the PersonQueries object to add a new entry to the database.
The user can then view different entries by pressing the Previous JButton or Next JButton, which results in calls to methods previousButtonActionPerformed (lines 241–250) or nextButtonActionPerformed (lines 253–262), respectively. Alternatively, the user can enter a number in the indexTextField and press Enter to view a particular entry.
20.12 Stored Procedures
Many database management systems can store individual SQL statements or sets of SQL statements in a database, so that programs accessing that database can invoke them. Such named collections of SQL statements are called stored procedures. JDBC enables programs to invoke stored procedures using objects that implement the interface CallableStatement. CallableStatements can receive arguments specified with the methods inherited from interface PreparedStatement. In addition, CallableStatements can specify output parameters in which a stored procedure can place return values. Interface CallableStatement includes methods to specify which parameters in a stored procedure are output parameters. The interface also includes methods to obtain the values of output parameters returned from a stored procedure.
Portability Tip 20.6
Although the syntax for creating stored procedures differs across database management systems, the interface CallableStatement provides a uniform interface for specifying input and output parameters for stored procedures and for invoking stored procedures.
Portability Tip 20.7
According to the Java API documentation for interface CallableStatement, for maximum portability between database systems, programs should process the update counts or ResultSets returned from a CallableStatement before obtaining the values of any output parameters.
20.13 Transaction Processing
Many database applications require guarantees that a series of database insertions, updates and deletions executes properly before the applications continue processing the next database operation. For example, when you transfer money electronically between bank accounts, several factors determine if the transaction is successful. You begin by specifying the source account and the amount you wish to transfer from that account to a destination account. Next, you specify the destination account. The bank checks the source account to determine if there are sufficient funds in the account to complete the transfer. If so, the bank withdraws the specified amount from the source account and, if all goes well, deposits the money into the destination account to complete the transfer. What happens if the transfer fails after the bank withdraws the money from the source account? In a proper banking system, the bank redeposits the money in the source account. How would you feel if the money was subtracted from your source account and the bank did not deposit the money in the destination account?
Transaction processing enables a program that interacts with a database to treat a database operation (or set of operations) as a single operation. Such an operation also is known as an atomic operation or a transaction. At the end of a transaction, a decision can be made either to commit the transaction or roll back the transaction. Committing the transaction finalizes the database operation(s); all insertions, updates and deletions performed as part of the transaction cannot be reversed without performing a new database operation. Rolling back the transaction leaves the database in its state prior to the database operation. This is useful when a portion of a transaction fails to complete properly. In our bank-account-transfer discussion, the transaction would be rolled back if the deposit could not be made into the destination account.
Java provides transaction processing via methods of interface Connection. Method setAutoCommit specifies whether each SQL statement commits after it completes (a true argument) or if several SQL statements should be grouped as a transaction (a false argument). If the argument to setAutoCommit is false, the program must follow the last SQL statement in the transaction with a call to Connection method commit (to commit the changes to the database) or Connection method rollback (to return the database to its state prior to the transaction). Interface Connection also provides method getAutoCommit to determine the autocommit state for the Connection.
20.14 Wrap-Up
In this chapter, you learned basic database concepts, how to interact with data in a database using SQL and how to use JDBC to allow Java applications to manipulate MySQL and Java DB databases. You learned about the SQL commands SELECT, INSERT, UPDATE and DELETE, as well as clauses such as WHERE, ORDER BY and INNER JOIN. You learned the explicit steps for obtaining a Connection to the database, creating a Statement to interact with the database’s data, executing the statement and processing the results. Then you used a RowSet to simplify the process of connecting to a database and creating statements. You used PreparedStatements to create precompiled SQL statements. You also learned how to create and configure databases in both MySQL and Java DB. We also provided overviews of CallableStatements and transaction processing. In the next chapter, you’ll learn about web application development with JavaServer Faces.
20.15 Web Resources
java.sun.com/javase/technologies/database/index.jsp
Sun Microsystems, Inc.’s Java SE database technologies home page.
java.sun.com/docs/books/tutorial/jdbc/index.html
The Java Tutorial’s JDBC track.
developers.sun.com/product/jdbc/drivers
Sun Microsystems search engine for locating JDBC drivers.
This SQL portal provides links to many resources, including SQL syntax, tips, tutorials, books, magazines, discussion groups, companies with SQL services, SQL consultants and free software.
www.datadirect.com/developer/jdbc/topics/perfoptjdbc/index.ssp
White paper that discusses designing a good JDBC application.
java.sun.com/javase/6/docs/technotes/guides/jdbc/index.html
Sun Microsystems JDBC API documentation.
The JGuru JDBC FAQs.
This site is the MySQL database home page. You can download the latest versions of MySQL and MySQL Connector/J and access their online documentation.
dev.mysql.com/doc/refman/5.0/en/index.html
MySQL reference manual.
java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/rowsetImpl.html
Overviews the RowSet interface and its subinterfaces. This site also discusses the reference implementations of these interfaces from Sun and their usage.
java.sun.com/developer/Books/JDBCTutorial/chapter5.html
Chapter 5 (RowSet Tutorial) of the book The JDBC 2.0 API Tutorial and Reference, Second Edition.