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 PreparedStatement
s 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 PreparedStatement
s
20.12 Stored Procedures
20.13 Transaction Processing
20.14 Wrap-Up
20.15 Web Resources
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.
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
.
books
DatabaseWe 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.
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.
SELECT
QueryLet 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.
WHERE
ClauseIn 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.
ORDER BY
ClauseThe 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
.
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.
INSERT
StatementThe 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.
UPDATE
StatementAn 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.
DELETE
StatementA 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.
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.
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.
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.
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.
books
in MySQLFor 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.
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
.
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 NullPointerException
s 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 ResultSet
s in parallel, separate Statement
objects must return the ResultSet
s.
books
DatabaseThe 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
ClassClass 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. ResultSet
s 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 ResultSet
s. 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 ResultSet
s. 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 ResultSet
s causes SQLFeatureNotSupportedException
s.
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
ClassClass 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 TableModelEvent
s 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, JTable
s 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
.
JTable
s 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
.
RowSet
InterfaceIn 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 ResultSet
s.
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.
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 PreparedStatement
s. 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.
PreparedStatement
sInterface PreparedStatement
enables you to create compiled SQL statements that execute more efficiently than Statement
objects. PreparedStatement
s also can specify parameters, making them more flexible than Statement
s. 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
PreparedStatement
s are more efficient than Statement
s when executing SQL statements multiple times and with different parameter values.
Error-Prevention Tip 20.1
Use PreparedStatement
s with parameters for queries that receive String
values as arguments to ensure that the String
s 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—SQLException
s 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.
PreparedStatement
sWe 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.
PersonQueries
Class PersonQueries
(Fig. 20.31) manages the address book application’s database connection and creates the PreparedStatement
s 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.
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 JButton
s. 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 JButton
s.
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 JTextField
s 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.
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
. CallableStatement
s can receive arguments specified with the methods inherited from interface PreparedStatement
. In addition, CallableStatement
s 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 ResultSet
s returned from a CallableStatement
before obtaining the values of any output parameters.
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
.
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 PreparedStatement
s 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 CallableStatement
s and transaction processing. In the next chapter, you’ll learn about web application development with JavaServer Faces.
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.