Transcript JDBC notes

1
25
Accessing
Databases with
JDBC
 2005 Pearson Education, Inc. All rights reserved.
2
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
 2005 Pearson Education, Inc. All rights reserved.
3
OBJECTIVES
In this chapter you will 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.
 2005 Pearson Education, Inc. All rights reserved.
4
25.1
25.2
25.3
25.4
Introduction
Relational Databases
Relational Database Overview: The books Database
SQL
25.4.1 Basic SELECT Query
25.4.2 WHERE Clause
25.4.3 ORDER BY Clause
25.4.4 Merging Data from Multiple Tables: INNER JOIN
25.4.5 INSERT Statement
25.4.6 UPDATE Statement
25.4.7 DELETE Statement
25.5 Instructions to install MySQL and MySQL Connector/J
25.6 Instructions on Setting MySQL User Account
25.7 Creating Database books in MySQL
25.8 Manipulating Databases with JDBC
25.8.1 Connecting to and Querying a Database
25.8.2 Querying the books Database
25.9 Stored Procedures
25.10 RowSet Interface
25.11 Wrap-Up
 2005 Pearson Education, Inc. All rights reserved.
5
25.1 Introduction
• Database
– Collection of data
• DBMS
– Database management system
– Storing and organizing data
• SQL
– Relational database
– Structured Query Language
 2005 Pearson Education, Inc. All rights reserved.
6
25.1 Introduction (Cont.)
• RDBMS
– Relational database management system
– MySQL
• Open source
• Available for both Windows and Linux
• dev.mysql.com/downloads/mysql/4.0.hml
• JDBC
– Java Database Connectivity
– JDBC driver
• Enable Java applications to connect to database
• Enable programmers to manipulate databases using JDBC
 2005 Pearson Education, Inc. All rights reserved.
7
Software Engineering Observation 25.1
The separation of the JDBC API from
particular database drivers enables
developers to change the underlying database
without modifying the Java code that accesses
the database.
 2005 Pearson Education, Inc. All rights reserved.
8
25.2 Relational Databases
• Relational database
– Table
• Rows, columns
– Primary key
• Unique data
• SQL queries
– Specify which data to select from a table
 2005 Pearson Education, Inc. All rights reserved.
9
Fig. 25.1 | Employee table sample data.
 2005 Pearson Education, Inc. All rights reserved.
10
Fig. 25.2 | Result of selecting distinct Department and Location data
from table Employee.
 2005 Pearson Education, Inc. All rights reserved.
11
25.3 Relational Database Overview: The
books Database
• Sample books database
– Four tables
• authors
– authorID, firstName, lastName
• publishers
– publisherID, publisherName
• titles
– isbn, title, editionNumber, copyright,
publisherID, imageFile, price
• authorISBN
– authorID, isbn
 2005 Pearson Education, Inc. All rights reserved.
12
Column
Description
authorID
Author’s ID number in the database. In the books database, this integer
column is defined as autoincremented. For each row inserted in this table, the
authorID value is increased by 1 automatically to ensure that each row has a
unique authorID. This column represents the table’s primary key.
firstName
Author’s first name (a string).
lastName
Author’s last name (a string).
Fig. 25.3 | authors table from books.
 2005 Pearson Education, Inc. All rights reserved.
13
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
Fig. 25.4 | Sample data from the authors table.
 2005 Pearson Education, Inc. All rights reserved.
14
Column
Description
publisherID
The publisher’s ID number in the database. This autoincremented
integer is the table’s primary key.
publisherName
The name of the publisher (a string).
Fig. 25.5 | publishers table from books.
 2005 Pearson Education, Inc. All rights reserved.
15
publisherID
publisherName
1
Prentice Hall
2
Prentice Hall PTG
Fig. 25.6 | Data from the publishers table.
 2005 Pearson Education, Inc. All rights reserved.
16
Column
Description
isbn
title
ISBN of the book (a string). The table’s primary key. ISBN is an
abbreviation for “International Standard Book Number”—a
numbering scheme that publishers worldwide use to give every
book a unique identification number.
Title of the book (a string).
editionNumber
Edition number of the book (an integer).
copyright
Copyright year of the book (a string).
publisherID
Publisher’s ID number (an integer). A foreign key that relates this
table to the publishers table.
Name of the file containing the book’s cover image (a string).
imageFile
price
Suggested retail price of the book (a real number). [Note: The
prices shown in Fig. 25.8 are for example purposes only.]
Fig. 25.7 | titles table from books.
 2005 Pearson Education, Inc. All rights reserved.
17
25.3 Relational Database Overview: The
books Database (Cont.)
• Foreign key
– A column
• matches the primary key column in another table
– Helps maintain the Rule of Referential Integrity
• Every foreign key value must appear as another table’s
primary key value
• Entity-relationship (ER) diagram
– Tables in the database
– Relationships among tables
 2005 Pearson Education, Inc. All rights reserved.
18
25.3 Relational Database Overview: The
books Database (Cont.)
• Rule of Entity Integrity
– Primary key uniquely identifies each row
– Every row must have a value for every column of the
primary key
– Value of the primary key must be unique in the table
 2005 Pearson Education, Inc. All rights reserved.
19
Common Programming Error 25.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.
 2005 Pearson Education, Inc. All rights reserved.
20
Common Programming Error 25.2
Providing the same value for the primary key
in multiple rows causes the DBMS to report
an error.
 2005 Pearson Education, Inc. All rights reserved.
21
editionN copyumber
right
publisher Image
ID
File
C How to
Program
4
2004
1
chtp4.jpg 85.00
0130384747
C++ How to
Program
4
2003
1
cpphtp4.j 85.00
pg
0130461342
Java Web
Services for
Experienced
Programmers
Java How to
Program
1
2003
1
jwsfep1.j 54.99
pg
6
2005
1
jhtp6.jpg 85.00
013100252X
The Complete 4
C++ Training
Course
2003
2
cppctc4.j 109.99
pg
0130895601
Advanced Java 1
2 Platform
How to
Program
2002
1
advjhtp1. 69.95
jpg
isbn
title
0131426443
0131483986
price
Fig. 25.8 | Sample data from the titles table of books.
 2005 Pearson Education, Inc. All rights reserved.
22
Column
Description
authorID
The author’s ID number, a foreign key to the authors table.
isbn
The ISBN for a book, a foreign key to the titles table.
Fig. 25.9 | authorISBN table from books.
 2005 Pearson Education, Inc. All rights reserved.
23
authorID
isbn
authorID
isbn
1
0130895725
2
0139163050
2
0130895725
3
0130829293
2
0132261197
3
0130284173
2
0130895717
3
0130284181
2
0135289106
4
0130895601
Fig. 25.10 | Sample data from the authorISBN table of books.
 2005 Pearson Education, Inc. All rights reserved.
24
Common Programming Error 25.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.
 2005 Pearson Education, Inc. All rights reserved.
25
Fig. 25.11 | Table relationships in books.
 2005 Pearson Education, Inc. All rights reserved.
26
25.4 SQL
• SQL keywords
– SQL queries and statements
 2005 Pearson Education, Inc. All rights reserved.
27
SQL keyword Description
SELECT
Retrieves data from one or more tables.
FROM
Tables involved in the query. Required in every SELECT.
WHERE
Criteria for selection that determine the rows to be retrieved, deleted or
updated. Optional in a SQL query or a SQL statement.
GROUP BY
Criteria for grouping rows. Optional in a SELECT query.
ORDER BY
Criteria for ordering rows. Optional in a SELECT query.
INNER JOIN
Merge rows from multiple tables.
INSERT
Insert rows into a specified table.
UPDATE
Update rows in a specified table.
DELETE
Delete rows from a specified table.
Fig. 25.12 | SQL query keywords.
 2005 Pearson Education, Inc. All rights reserved.
28
25.4.1 Basic SELECT Query
• Simplest format of a SELECT query
– SELECT * FROM tableName
• SELECT * FROM authors
• Select specific fields from a table
– SELECT authorID, lastName FROM authors
 2005 Pearson Education, Inc. All rights reserved.
29
authorID
lastName
1
Deitel
2
Deitel
3
Nieto
4
Santry
Fig. 25.13 | Sample authorID and lastName data from the authors table.
 2005 Pearson Education, Inc. All rights reserved.
30
Software Engineering Observation 25.2
For most queries, the asterisk (*) should not be used
to specify column names. In general, programmers
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).
 2005 Pearson Education, Inc. All rights reserved.
31
Common Programming Error 25.4
If a programmer assumes that the columns
are always returned in the same order from a
query that uses the asterisk (*), the program
may process the result 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.
 2005 Pearson Education, Inc. All rights reserved.
32
25.4.2 WHERE Clause
• specify the selection criteria
– SELECT columnName1, columnName2, … FROM
tableName WHERE criteria
• SELECT title, editionNumber, copyright
FROM titles
WHERE copyright > 2002
 2005 Pearson Education, Inc. All rights reserved.
33
title
editionNumber
copyright
The Complete C++ Training Course
4
2003
Java How to Program
5
2003
C How to Program
4
2004
Internet and World Wide Web How to
Program
3
2004
Java How to Program
6
2005
C# How to Program
1
2003
Fig. 25.14 | Sampling of titles with copyrights after 2002 from table titles.
 2005 Pearson Education, Inc. All rights reserved.
34
25.4.2 WHERE Clause (Cont.)
•WHERE clause condition operators
– <, >, <=, >=, =, <>
– LIKE
• wildcard characters % and _
• SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘D%’
 2005 Pearson Education, Inc. All rights reserved.
35
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 25.15 | Authors whose last name starts with D from the authors
table.
 2005 Pearson Education, Inc. All rights reserved.
36
Portability Tip 25.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?).
 2005 Pearson Education, Inc. All rights reserved.
37
Portability Tip 25.2
Read your database system’s documentation
carefully to determine whether your system
supports the LIKE operator.
 2005 Pearson Education, Inc. All rights reserved.
38
Portability Tip 25.3
Some databases use the * character in place of
the % character in a pattern.
 2005 Pearson Education, Inc. All rights reserved.
39
25.4.2 WHERE Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘_i%’
 2005 Pearson Education, Inc. All rights reserved.
40
authorID
firstName
lastName
3
Tem
Nieto
Fig. 25.16 | The only author from the authors table
whose last name contains i as the second letter.
 2005 Pearson Education, Inc. All rights reserved.
41
Portability Tip 25.4
Some database systems use the ? character in
place of the _ character in a pattern.
 2005 Pearson Education, Inc. All rights reserved.
42
25.4.3 ORDER BY Clause
• Optional ORDER BY clause
– SELECT columnName1, columnName2, … FROM
tableName ORDER BY column ASC
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName ASC
– SELECT columnName1, columnName2, … FROM
tableName ORDER BY column DESC
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName DESC
 2005 Pearson Education, Inc. All rights reserved.
43
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
Fig. 25.17 | Sample data from table authors in ascending order by
lastName.
 2005 Pearson Education, Inc. All rights reserved.
44
authorID
firstName
lastName
4
Sean
Santry
3
Tem
Nieto
1
Harvey
Deitel
2
Paul
Deitel
Fig. 25.18 | Sample data from table authors in descending order by lastName.
 2005 Pearson Education, Inc. All rights reserved.
45
25.4.3 ORDER BY Clause (Cont.)
•ORDER BY multiple fields
– ORDER BY column1 sortingOrder, column2 sortingOrder, …
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName, firstName
 2005 Pearson Education, Inc. All rights reserved.
46
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
Fig. 25.19 | Sample data from authors in ascending order by lastName
and firstName.
 2005 Pearson Education, Inc. All rights reserved.
47
25.4.3 ORDER BY Clause (Cont.)
• Combine the WHERE and ORDER BY clauses
• SELECT isbn, title, editionNumber, copyright, price
FROM titles WHERE title LIKE ‘%How to Program’
ORDER BY title ASC
 2005 Pearson Education, Inc. All rights reserved.
48
Edition copy-Number right price
isbn
title
0130895601
1
2002
69.95
0131426443
Advanced Java 2 Platform How to
Program
C How to Program
4
2004
85.00
0130384747
C++ How to Program
4
2003
85.00
013028419x
2001
69.95
2004
85.00
0130284181
e-Business and e-Commerce How to 1
Program
Internet and World Wide Web How 3
to Program
Perl How to Program
1
2001
69.95
0134569555
Visual Basic 6 How to Program
1
1999
69.95
0130284173
XML How to Program
1
2001
69.95
0131450913
Fig. 25.20 | Sampling of books from table titles whose titles end with
How to Program in ascending order by title.
 2005 Pearson Education, Inc. All rights reserved.
49
25.4.4 Merging Data from Multiple Tables:
INNER JOIN
• Split related data into separate tables
• Join the tables
– Merge data from multiple tables into a single view
– INNER JOIN
• SELECT columnName1, columnName2, …
FROM table1
INNER JOIN table2
ON table1.columnName = table2.column2Name
• SELECT firstName, lastName, isbn
FROM authors, authorISBN
INNER JOIN authorISBN
ON authors.authorID = authorISBN.authorID
ORDER BY lastName, firstName
 2005 Pearson Education, Inc. All rights reserved.
50
Software Engineering Observation 25.3
If a SQL statement includes columns from
multiple tables that have the same name, the
statement must precede those column names
with their table names and a dot (e.g.,
authors.authorID).
 2005 Pearson Education, Inc. All rights reserved.
51
Common Programming Error 25.5
In a query, failure to qualify names for
columns that have the same name in two or
more tables is an error.
 2005 Pearson Education, Inc. All rights reserved.
52
firstName lastName isbn
first
Name
lastName isbn
Harvey
Deitel
0130895601
Paul
Deitel
0130895717
Harvey
Deitel
0130284181
Paul
Deitel
0132261197
Harvey
Deitel
0134569555
Paul
Deitel
0130895725
Harvey
Deitel
0139163050
Paul
Deitel
0130829293
Harvey
Deitel
0135289106
Paul
Deitel
0134569555
Harvey
Deitel
0130895717
Paul
Deitel
0130829277
Harvey
Deitel
0130284173
Tem
Nieto
0130161438
Harvey
Deitel
0130829293
Tem
Nieto
013028419x
Paul
Deitel
0130852473
Sean
Santry
0130895601
Fig. 25.21 | Sampling of authors and ISBNs for the books they have
written in ascending order by lastName and firstName.
 2005 Pearson Education, Inc. All rights reserved.
53
25.4.5 INSERT Statement
• Insert a row into a table
– INSERT INTO tableName ( columnName1, … , columnNameN )
VALUES ( value1, … , valueN )
• INSERT INTO authors ( firstName, lastName )
VALUES ( ‘Sue’, ‘Smith’ )
 2005 Pearson Education, Inc. All rights reserved.
54
authorID
firstName
lastName
1
2
3
4
5
Harvey
Paul
Tem
Sean
Sue
Deitel
Deitel
Nieto
Santry
Smith
Fig. 25.22 | Sample data from table Authors after an INSERT operation.
 2005 Pearson Education, Inc. All rights reserved.
55
Common Programming Error 25.6
It is an error to specify a value for an
autoincrement column.
 2005 Pearson Education, Inc. All rights reserved.
56
Common Programming Error 25.7
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.
 2005 Pearson Education, Inc. All rights reserved.
57
25.4.6 UPDATE Statement
• Modify data in a table
– UPDATE tableName
SET columnName1 = value1, … , columnNameN = valueN
WHERE criteria
• UPDATE authors
SET lastName = ‘Jones’
WHERE lastName = ‘Smith’ AND firstName = ‘Sue’
 2005 Pearson Education, Inc. All rights reserved.
58
authorID
firstName
lastName
1
2
3
4
5
Harvey
Paul
Tem
Sean
Sue
Deitel
Deitel
Nieto
Santry
Jones
Fig. 25.23 | Sample data from table authors after an UPDATE operation.
 2005 Pearson Education, Inc. All rights reserved.
59
25.4.7 DELETE Statement
• Remove data from a table
– DELETE FROM tableName WHERE criteria
• DELETE FROM authors
WHERE lastName = ‘Jones’ AND firstName = ‘Sue’
 2005 Pearson Education, Inc. All rights reserved.
60
authorID
firstName
lastName
1
2
3
4
Harvey
Paul
Tem
Sean
Deitel
Deitel
Nieto
Santry
Fig. 25.24 | Sample data from table authors after a DELETE operation.
 2005 Pearson Education, Inc. All rights reserved.
61
25.5 Instructions to Install MySQL and
MySQL Connector/J
• Install MySQL
– Insert CD and change directory to
D:\software\MySQL\mysql-4.0.20c-win
– Double click SETUP.EXE
– Following the instruction
• Install MySQL Connector/J
– Copy mysql-connector-java-3.0.14-production.zip
– Open mysql-connector-java-3.0.14-production.zip
• Extract its content to the C:\ driv
 2005 Pearson Education, Inc. All rights reserved.
62
25.6 Instructions on Setting MySQL User
Account
• Set up a user account
– Start database server by executing the script
C:\mysql\bin\mysqld
– Start the MySQL monitor by executing the command
C:\mysql\bin>mysql –h localhost –u root
– Create an account
mysql> USE mysql;
mysql> INSERT INTO user SET Host=‘localhost’,
User=‘jhtp6’, Password=PASSWORD(‘jhtp6’),
Select_priv=‘Y’, Insert_priv=‘Y’,
Update_priv=‘Y’, Delete_priv=‘Y’,
Create_priv=‘Y’, Drop_priv=‘Y’,
References_priv=‘Y’, Execute_priv=‘Y’;
mysql> FLUSH PRIVILEGES;
mysql> exit;
 2005 Pearson Education, Inc. All rights reserved.
63
25.7 Creating Database books in MySQL
• Create books database
– Open Command Prompt
– Change to the C:\mysql\bin directory
– Start database by executing the command
C:\mysql\bin\mysqld
– Copy SQL script books.sql to C:\mysql\bin
directory
– Open another Command Prompt
– Change to the C:\mysql\bin directory
– Create the books database by executing the command
C:\mysql\bin>mysql –h localhost –u jhtp6 –p < books.sql
 2005 Pearson Education, Inc. All rights reserved.
64
25.8 Manipulating Databases with JDBC
• Connect to a database
• Query the database
• Display the results of the query in JTable
 2005 Pearson Education, Inc. All rights reserved.
65
25.8.1 Connecting to and Querying a
Database
•DisplayAuthors
– Retrieves the entire authors table
– Displays the data in the standard output stream
– Example illustrates
• Connect to the database
• Query the database
• Process the result
 2005 Pearson Education, Inc. All rights reserved.
1
2
// Fig. 25.25: DisplayAuthors.java
// Displaying the contents of the authors table.
3
4
import java.sql.Connection;
import java.sql.Statement;
5
6
7
8
9
10
11
12
13
14
15
16
import
import
import
import
java.sql.DriverManager;
java.sql.ResultSet;
java.sql.ResultSetMetaData;
java.sql.SQLException;
Outline
Imports the JDBC classes and
interfaces from package java.sql
public class DisplayAuthors
{
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
// launch the application
17
18
19
20
public static void main( String args[] )
{
Connection connection = null; // manages connection
Statement statement = null; // query statement
21
22
// connect to database books and query database
23
24
25
26
27
28
29
30
66
DisplayAuthors
.java
(1 of 3)
Lines 3-8
Declare a String
13 the
constant that Line
specifies
Line
14name
JDBC driver’s
class
Line 25
Lines 28-29
Declare a String
constant that specifies
the database URL
try
{
Class.forName( JDBC_DRIVER ); // load database driver class
// establish connection to database
connection =
DriverManager.getConnection( DATABASE_URL, "jhtp6", "jhtp6" );
Loads the class definition
for the database driver.
Declare and initialize a
Connection reference
called connection.
 2005 Pearson Education,
Inc. All rights reserved.
// create Statement for querying database
statement = connection.createStatement();
31
32
Invokes ConnectionOutline
method
createStatement to obtain an object
// query database
that implements
interface Statement.
Use the Statement
object’s
ResultSet resultSet = statement.executeQuery(
executeQuery method to
"SELECT authorID, firstName, lastName FROM authors" );
DisplayAuthors
execute a query
that selects
.java
// process query results
all
the author
information
Obtains
the metadata
ResultSetMetaData metaData = resultSet.getMetaData();
from
authors.
Uses
forResultSetMetaData
thetable
ResultSet.
int numberOfColumns = metaData.getColumnCount();
(2 of 3)
System.out.println( "Authors Table of Books Database:" ); method getColumnCount
Line 32
to retrieve the number
of
for ( int i = 1; i <= numberOfColumns; i++ )
columns in the ResultSet.
Lines
35-36
Obtain
column
System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
39method
nameLine
using
System.out.println();
getColumnName
Line 40
Position the ResultSet cursor to the first
while ( resultSet.next() )
44
row in the ResultSet with methodLine
next
{
Line 47
for ( int i = 1; i <= numberOfColumns; i++ )
Extract the contents
System.out.printf( "%-8s\t", resultSet.getObject( i ) );
Line
50 in the
of
one
column
System.out.println();
Line
current
row54
} // end while
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
67
} // end try
catch ( SQLException sqlException )
55
56
{
57
58
System.exit( 1 );
} // end catch
sqlException.printStackTrace();
Catch SQLException, which
is thrown if the query execution
or ResultSet process fails
 2005 Pearson Education,
Inc. All rights reserved.
59
catch ( ClassNotFoundException classNotFound )
60
{
ClassNotFoundException is
61
classNotFound.printStackTrace();
62
System.exit( 1 );
thrown if the class loader
63
} // end catch
cannot locate the driver class
64
finally // ensure statement and connection are closed properly
65
{
DisplayAuthors
66
try
.java
67
{
Close
the
Statement
and
68
statement.close();
the database Connection.
69
connection.close();
(3 of 3)
70
} // end try
Line 69
71
catch ( Exception exception )
72
{
Lines 68-69
73
exception.printStackTrace();
74
System.exit( 1 );
75
} // end catch
76
} // end finally
77
} // end main
78 } // end class DisplayAuthors
68
Outline
Program output
Authors Table of Books Database:
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
 2005 Pearson Education,
Inc. All rights reserved.
69
Type
Description
1
The JDBC-to-ODBC bridge driver connects Java programs to Microsoft
ODBC (Open Database Connectivity) data sources. The Java 2 Software
Development Kit from Sun Microsystems, Inc. includes the JDBC-to-ODBC
Bridge driver (sun.jdbc.odbc.JdbcOdbcDriver). This driver typically
requires the ODBC driver on the client computer and normally requires
configuration of ODBC data sources. The Bridge driver was introduced
primarily for development purposes, before other types of drivers were
available, and should not be used for production applications.
2
Native-API, partly Java drivers enable JDBC programs to use databasespecific APIs (normally written in C or C++) that allow client programs to
access databases via the Java Native Interface (JNI). JNI is a bridge between a
JVM and code written and compiled in a platform-specific language such as C
or C++. Such code is known as native code. JNI enables Java applications to
interact with native code. A Type 2 driver translates JDBC into databasespecific calls. Type 2 drivers were introduced for reasons similar to the Type 1
ODBC bridge driver.
3
Pure Java client to server drivers take JDBC requests and translate them into
a network protocol that is not database specific. These requests are sent to a
server, which translates the database requests into a database-specific protocol.
4
Pure Java drivers implement database-specific network protocols, so that Java
programs can connect directly to a database.
Fig. 25.26 | JDBC driver types.
 2005 Pearson Education, Inc. All rights reserved.
70
Software Engineering Observation 25.4
Most major database vendors provide their
own JDBC database drivers, and many thirdparty vendors provide JDBC drivers as well.
For more information on JDBC drivers, visit
the Sun Microsystems JDBC Web site,
servlet.java.sun.com/products/jdb
c/drivers.
 2005 Pearson Education, Inc. All rights reserved.
71
Software Engineering Observation 25.5
On the Microsoft Windows platform, most
databases support access via Open Database
Connectivity (ODBC). ODBC is a technology
developed by Microsoft to allow generic access
to disparate database systems on the Windows
platform (and some UNIX platforms). The
JDBC-to-ODBC Bridge allows any Java
program to access any ODBC data source.
The driver is class JdbcOdbcDriver in
package sun.jdbc.odbc.
 2005 Pearson Education, Inc. All rights reserved.
72
RDBMS JDBC driver name
Database URL format
jdbc:mysql://hostname/databaseNa
me
MySQL
com.mysql.jdbc.Driver
ORACLE
oracle.jdbc.driver.OracleDr jdbc:oracle:thin:@hostname:port
iver
Number:databaseName
DB2
COM.ibm.db2.jdbc.net.DB2Dri jdbc:db2:hostname:portnumber/data
ver
baseName
Sybase
com.sybase.jdbc.SybDriver
jdbc:sybase:Tds:hostname:portnub
er/databaseName
Fig. 25.27 | Popular JDBC driver names and database URL.
 2005 Pearson Education, Inc. All rights reserved.
73
Software Engineering Observation 25.6
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.
 2005 Pearson Education, Inc. All rights reserved.
74
Software Engineering Observation 25.7
Metadata enables programs to process
ResultSet contents dynamically when
detailed information about the ResultSet is
not known in advance.
 2005 Pearson Education, Inc. All rights reserved.
75
Common Programming Error 25.8
Initially, a ResultSet cursor is positioned
before the first row. Attempting to access a
ResultSet’s contents before positioning the
ResultSet cursor to the first row with
method next causes a SQLException.
 2005 Pearson Education, Inc. All rights reserved.
76
Performance Tip 25.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 linear search of
the column names to locate the appropriate
column.
 2005 Pearson Education, Inc. All rights reserved.
77
Common Programming Error 25.9
Specifying column number 0 when obtaining
values from a ResultSet causes a
SQLException.
 2005 Pearson Education, Inc. All rights reserved.
78
Common Programming Error 25.10
Attempting to manipulate a ResultSet after
closing the Statement that created the
ResultSet causes a SQLException. The
program discards the ResultSet when the
corresponding Statement is closed.
 2005 Pearson Education, Inc. All rights reserved.
79
Software Engineering Observation 25.8
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.
 2005 Pearson Education, Inc. All rights reserved.
80
25.8.2 Querying the books Database
• Allow the user to enter any query into the
program
• Display the results of a query in a JTable
 2005 Pearson Education, Inc. All rights reserved.
1
2
3
4
5
6
// Fig. 25.28: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
7
8
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
81
Outline
ResultSetTableMode
l.java
9 import javax.swing.table.AbstractTableModel;
10
11 // ResultSet rows and columns are counted from 1 and JTable
12 // rows and columns are counted from 0. When processing
13 // ResultSet rows or columns for use in a JTable, it is
14 // necessary to add 1 to the row or column number to manipulate
15 // the appropriate ResultSet column (i.e., JTable column 0 is
16 // ResultSet column 1 and JTable row 0 is ResultSet row 1).
(1 of 8)
Line 17
Line 26
17 public class ResultSetTableModel extends AbstractTableModel
18 {
19
20
21
22
private
private
private
private
23
24
25
26
private int numberOfRows;
27
Connection connection;
Statement statement;
ResultSet resultSet;
ResultSetMetaData metaData;
// keep track of database connection status
private boolean connectedToDatabase = false;
Class ResultSetTableModel extends
class AbstractTableModel, which
implements interface TableModel.
Instance variable keeps track
of database connection status
 2005 Pearson Education,
Inc. All rights reserved.
28
29
30
31
32
33
// constructor initializes resultSet and obtains its meta data object;
// determines number of rows
public ResultSetTableModel( String driver, String url,
Constructor
String username, String password, String query )
throws SQLException, ClassNotFoundException
{
// load database driver class
Class.forName( driver );
36
37
38
39
// connect to database
connection = DriverManager.getConnection( url, username, password );
40
41
// create Statement to query database
statement = connection.createStatement(
44
45
46
47
48
49
50
51
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
// update database connection status
connectedToDatabase = true;
(2 of 8)
Lines 30-31
Invokes Connection
method createStatement to
create a Statement object. Line 38
Indicate that connect to
database is successful
// set query and execute it
Invokes ResultSetTableModel
setQuery( query );
method setQuery to perform
} // end constructor ResultSetTableModel
the default query.
Outline
accepts five String
arguments—the driver class
name, the database URL, the
Establishes
a connection
username,
the password
and the
ResultSetTableMode
to thequery
database.
l.java
default
to perform
34
35
42
43
82
Lines 41-43
Line 46
Line 49
 2005 Pearson Education,
Inc. All rights reserved.
52
53
// get class that represents column type
public Class getColumnClass( int column ) throws IllegalStateException
54
55
56
57
{
Outline
// ensure database connection is available
Verify database
if ( !connectedToDatabase )
Override method
connection status
throw new IllegalStateException( "Not Connected to Database" );
58
59
// determine Java class of column
60
61
try
{
62
63
64
65
83
getColumnClass to obtain
a Class object that represents
the superclass
ResultSetTableMode
of all objects in a particular l.java
column
String className = metaData.getColumnClassName( column
Obtains the fully qualified
(3 of 8) class
+ name
1 ); for the specified column.
// return Class object that represents className
Loads
return Class.forName( className );
66
67
68
69
} // end try
catch ( Exception exception )
{
exception.printStackTrace();
70
71
72
73
} // end catch
Lines
the class definition for the
class53-73
and
returns the corresponding Class object.
Line 56
return Object.class; // if problems occur above, assume type Object
} // end method getColumnClass
Line 62
Returns the
Line
65
default
type.
74
Line 72
 2005 Pearson Education,
Inc. All rights reserved.
75
76
77
78
// get number of columns in ResultSet
public int getColumnCount() throws IllegalStateException
{
// ensure database connection is available
97
98
99
100
101
Outline
if ( !connectedToDatabase )
Override method getColumnCount
throw new IllegalStateException( "Not Connected to Database" );
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
84
// determine number of columns
try
{
return metaData.getColumnCount();
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
} // end catch
to obtain the number of columns in theResultSetTableMode
model’s underlying ResultSet
l.java
Obtains the number of
columns in the ResultSet.
(4 of 8)
Lines 76-93
Line 85
return 0; // if problems occur above, return 0 for number of columns
} // end method getColumnCount
Lines 96-113
// get name of a particular column in ResultSet
public String getColumnName( int column ) throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
Override method
throw new IllegalStateException( "Not Connected to Database" );
getColumnName
to obtain the name of the column in
the model’s underlying ResultSet
 2005 Pearson Education,
Inc. All rights reserved.
102
103
// determine column name
try
104
105
106
107
108
109
110
111
112
113
114
115
116
117
{
118
119
120
121
122
123
124
85
Outline
return metaData.getColumnName( column + 1 );
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
} // end catch
Obtains the column name
from the ResultSet.
return ""; // if problems, return empty string for column name
} // end method getColumnName
ResultSetTableMode
l.java
(5 of 8)
Line 105
// return number of rows in ResultSet
public int getRowCount() throws IllegalStateException
{
Lines 116-123
// ensure database connection is available
if ( !connectedToDatabase )
Override method getColumnCount
throw new IllegalStateException( "Not Connected to Database" );
return numberOfRows;
} // end method getRowCount
to obtain the number of rows in the
model’s underlying ResultSet
 2005 Pearson Education,
Inc. All rights reserved.
125
126
127
128
129
130
131
132
// obtain value in particular row and column
public Object getValueAt( int row, int column )
86
Outline
throws IllegalStateException
{
// ensure database connection is available
Override method getValueAt to obtain
if ( !connectedToDatabase )
the Object in a particular row and column
throw new IllegalStateException( "Not Connected to Database" );
of the model’s underlying ResultSet
ResultSetTableMode
l.java
133
134
135
136
137
138
// obtain a value at specified ResultSet row and column
try
(6 of 8)
{
Uses ResultSet method absolute to position
resultSet.absolute( row + 1 );
row. 126-145
return resultSet.getObject( column + 1 ); the ResultSet cursor at a specificLines
} // end try
139
140
141
142
143
144
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
} // end catch
145
146
147
148
149
150
151
152
153
154
Uses ResultSet method getObject
Line 136
to obtain the Object in a specific
column of the current row.
Line 137
return ""; // if problems, return empty string object
} // end method getValueAt
// set new database query string
public void setQuery( String query )
throws SQLException, IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );
 2005 Pearson Education,
Inc. All rights reserved.
155
156
// specify query and execute it
resultSet = statement.executeQuery( query );
157
158
// obtain meta data for ResultSet
Uses ResultSet method last to
position the ResultSet cursor at
ResultSet
the last row in the ResultSet.
159
160
161
162
metaData = resultSet.getMetaData();
163
164
numberOfRows = resultSet.getRow();
165
166
// notify JTable that model has changed
fireTableStructureChanged();
167
168
// determine number of rows in
resultSet.last();
} // end method setQuery
Executes the query to
obtain a new ResultSet.Outline
// move to last row
87
ResultSetTableMode
l.java
// get row number
(7 of 8)
Uses ResultSet method getRow
to obtain the row number for the Line 156
current row in the ResultSet.
Line 162
Invokes method fireTableAStructureChanged
to notify any JTable using this
Line 163
ResultSetTableModel object as its model that
the structure of the model has changed.
Line 166
 2005 Pearson Education,
Inc. All rights reserved.
169
170
// close Statement and Connection
public void disconnectFromDatabase()
171
172
173
174
{
88
Verify whether the connection
is already terminated
Method disconnectFromDatabase
implement an appropriate termination method
Statement and Connection
for class ResultSetTableModel
if ( !connectedToDatabase )
return;
175
176
// close
try
177
178
179
180
181
182
{
183
184
185
186
sqlException.printStackTrace();
} // end catch
finally // update database connection status
{
187
188
connectedToDatabase = false;
} // end finally
189
190 }
Outline
statement.close();
connection.close();
} // end try
catch ( SQLException sqlException )
{
} // end method disconnectFromDatabase
// end class ResultSetTableModel
ResultSetTableMode
l.java
Close the Statement and Connection if a(8 of 8)
ResultSetTableModel object is garbage collected.
Lines 170-189
Line 172
Set connectedToDatabase
to false
Lines 178-179
to ensure that clients do not use an instance
of ResultSetTableModel after that
Line 187
instance has already been terminated
 2005 Pearson Education,
Inc. All rights reserved.
89
Portability Tip 25.5
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.
 2005 Pearson Education, Inc. All rights reserved.
90
Portability Tip 25.6
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.
 2005 Pearson Education, Inc. All rights reserved.
91
Common Programming Error 25.11
Attempting to update a ResultSet when the
database driver does not support updatable
ResultSets causes SQLExceptions.
 2005 Pearson Education, Inc. All rights reserved.
92
ResultSet static
type constant
Description
TYPE_FORWARD_ONLY
Specifies that a ResultSet’s cursor can move only in the
forward direction (i.e., from the first row to the last row in the
ResultSet).
TYPE_SCROLL_INSENSITIVE
Specifies that a ResultSet’s cursor can scroll in either direction
and that the changes made to the ResultSet during
ResultSet processing are not reflected in the ResultSet
unless the program queries the database again.
TYPE_SCROLL_SENSITIVE
Specifies that a ResultSet’s cursor can scroll in either direction
and that the changes made to the ResultSet during
ResultSet processing are reflected immediately in the
ResultSet.
Fig. 25.29 | ResultSet constants for specifying ResultSet type.
 2005 Pearson Education, Inc. All rights reserved.
93
ResultSet static
concurrency constant
Description
CONCUR_READ_ONLY
Specifies that a ResultSet cannot be updated (i.e.,
changes to the ResultSet contents cannot be
reflected in the database with ResultSet’s update
methods).
CONCUR_UPDATABLE
Specifies that a ResultSet can be updated (i.e.,
changes to the ResultSet contents can be reflected in
the database with ResultSet’s update methods).
Fig. 25.30 | ResultSet constants for specifying result properties.
 2005 Pearson Education, Inc. All rights reserved.
94
Common Programming Error 25.12
Attempting to move the cursor backwards
through a ResultSet when the database
driver does not support backwards scrolling
causes a SQLException.
 2005 Pearson Education, Inc. All rights reserved.
1
// Fig. 25.31: DisplayQueryResults.java
2
// Display the contents of the Authors table in the
3
// Books database.
4
import java.awt.BorderLayout;
5
import java.awt.event.ActionListener;
6
import java.awt.event.ActionEvent;
7
import java.awt.event.WindowAdapter;
8
import java.awt.event.WindowEvent;
9
import java.sql.SQLException;
10 import javax.swing.JFrame;
95
Outline
DisplayQueryResult
s.java
(1 of 7)
11 import javax.swing.JTextArea;
12 import javax.swing.JScrollPane;
Lines 22-25
13 import javax.swing.ScrollPaneConstants;
14 import javax.swing.JTable;
15 import javax.swing.JOptionPane;
16 import javax.swing.JButton;
17 import javax.swing.Box;
18
19 public class DisplayQueryResults extends JFrame
20 {
21
// JDBC driver and database URL
22
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
23
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
24
static final String USERNAME= "jhtp6";
25
static final String PASSWORD= "jhtp6";
26
Declare the database driver
class name, database URL,
username and password for
accessing the database
 2005 Pearson Education,
Inc. All rights reserved.
27
28
// default query selects all rows from authors table
static final String DEFAULT_QUERY = "SELECT * FROM authors";
29
30
private ResultSetTableModel tableModel;
31
32
33
34
35
36
37
38
39
40
41
42
private JTextArea queryArea;
// create ResultSetTableModel and display database table
try
{
// create TableModel for results of query SELECT * FROM authors
tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL,
USERNAME, PASSWORD, DEFAULT_QUERY );
// set up JTextArea in which user types queries
queryArea = new JTextArea( DEFAULT_QUERY, 3, 100 );
47
48
queryArea.setWrapStyleWord( true );
queryArea.setLineWrap( true );
49
50
51
52
53
54
55
56
DeclareOutline
the default query
Declare tableModel to be a reference
to ResultSetTableModel
// create ResultSetTableModel and GUI
public DisplayQueryResults()
{
super( "Displaying Query Results" );
43
44
45
46
96
DisplayQueryResult
s.java
(2 of 7)
Line 28
Create TableModel
Line 30
for results of default
query “SELECT *
Lines
42-43
FROM
authors”
JScrollPane scrollPane = new JScrollPane( queryArea,
ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER );
// set up JButton for submitting queries
JButton submitButton = new JButton( "Submit Query" );
 2005 Pearson Education,
Inc. All rights reserved.
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
// create Box to manage placement of queryArea and
// submitButton in GUI
Box box = Box.createHorizontalBox();
box.add( scrollPane );
box.add( submitButton );
// create JTable delegate for tableModel
JTable resultTable = new JTable( tableModel );
97
Outline
DisplayQueryResult
Create JTable delegate
for tableModel s.java
// place GUI components on content pane
add( box, BorderLayout.NORTH );
add( new JScrollPane( resultTable ), BorderLayout.CENTER );
// create event listener for submitButton
submitButton.addActionListener(
(3 of 7)
Register an event handler for the Line 64
submitButton that the user clicks
to submit a query to the database Lines 71-110
new ActionListener()
{
// pass query to table model
public void actionPerformed( ActionEvent event )
Line 81
{
// perform a new query
try
{
tableModel.setQuery( queryArea.getText() );
} // end try
catch ( SQLException sqlException )
Invoke ResultSetTableModel
{
method setQuery to execute
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database error",
the new query
JOptionPane.ERROR_MESSAGE );
 2005 Pearson Education,
Inc. All rights reserved.
89
90
91
92
93
94
95
96
// try to recover from invalid user query
// by executing default query
try
{
tableModel.setQuery( DEFAULT_QUERY );
queryArea.setText( DEFAULT_QUERY );
} // end try
catch ( SQLException sqlException2 )
97
98
{
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
98
Outline
DisplayQueryResult
s.java
JOptionPane.showMessageDialog( null,
sqlException2.getMessage(), "Database error",
JOptionPane.ERROR_MESSAGE );
(4 of 7)
Line 103
// ensure database connection is closed
tableModel.disconnectFromDatabase();
Ensure that the database
connection is closed
System.exit( 1 ); // terminate application
} // end inner catch
} // end outer catch
} // end actionPerformed
} // end ActionListener inner class
); // end call to addActionListener
setSize( 500, 250 ); // set window size
setVisible( true ); // display window
} // end try
catch ( ClassNotFoundException classNotFound )
 2005 Pearson Education,
Inc. All rights reserved.
116
{
99
JOptionPane.showMessageDialog( null,
117
118
"MySQL driver not found", "Driver not found",
119
JOptionPane.ERROR_MESSAGE );
Outline
120
System.exit( 1 ); // terminate application
121
122
} // end catch
123
catch ( SQLException sqlException )
124
{
125
126
DisplayQueryResult
s.java
JOptionPane.showMessageDialog( null, sqlException.getMessage(),
"Database error", JOptionPane.ERROR_MESSAGE );
Line 129
127
128
// ensure database connection is closed
129
tableModel.disconnectFromDatabase();
130
131
132
(5 of 7)
System.exit( 1 );
Ensure that the database
connection is closed
// terminate application
} // end catch
133
134
// dispose of window when user quits application (this overrides
135
// the default of HIDE_ON_CLOSE)
136
setDefaultCloseOperation( DISPOSE_ON_CLOSE );
137
138
// ensure database connection is closed when user quits application
139
addWindowListener(
140
 2005 Pearson Education,
Inc. All rights reserved.
141
new WindowAdapter()
142
{
100
143
// disconnect from database and exit when window has closed
144
public void windowClosed( WindowEvent event )
145
{
146
tableModel.disconnectFromDatabase();
147
System.exit( 0 );
148
} // end method windowClosed
149
} // end WindowAdapter inner class
150
); // end call to addWindowListener
151
} // end DisplayQueryResults constructor
153
// execute application
154
public static void main( String args[] )
155
{
157
Ensure that the database
connection is closed
when window has closed
DisplayQueryResult
s.java
(6 of 7)
Lines 144-148
152
156
Outline
new DisplayQueryResults();
} // end main
158 } // end class DisplayQueryResults
 2005 Pearson Education,
Inc. All rights reserved.
101
Outline
DisplayQueryResult
s.java
(7 of 7)
Program output
 2005 Pearson Education,
Inc. All rights reserved.
102
25.9 Stored Procedure
• Stored procedures
– Store SQL statements in a database
– Invoke SQL statements by programs accessing the
database
• Interface CallableStatement
– Receive arguments
– Output parameters
 2005 Pearson Education, Inc. All rights reserved.
103
Portability Tip 25.7
Although the syntax for creating stored
procedures differs across database
management systems, interface
CallableStatement provides a uniform
interface for specifying input and output
parameters for stored procedures and for
invoking stored procedures.
 2005 Pearson Education, Inc. All rights reserved.
104
Portability Tip 25.8
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.
 2005 Pearson Education, Inc. All rights reserved.
105
15.10 RowSet Interface
• Interface RowSet
– Configures the database connection automatically
– Prepares query statements automatically
– Provides set methods to specify the properties needed to establish
a connection
– Part of the javax.sql package
• Two types of RowSet
– Connected RowSet
• Connects to database once and remain connected
– Disconnected RowSet
• Connects to database, executes a query and then closes connection
 2005 Pearson Education, Inc. All rights reserved.
106
15.10 RowSet Interface (Cont.)
• Package javax.sql.rowset
– JdbcRowSet
• Connected RowSet
• Wrapper around a ResultSet
• Scrollable and updatable by default
– CachedRowSet
•
•
•
•
Disconnected RowSet
Cache the data of ResultSet in memory
Scrollable and updatable by default
Serializable
– Can be passed between Java application
• Limitation
– Amount of data that can be stored in memory is limited
 2005 Pearson Education, Inc. All rights reserved.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// Fig. 25.32: JdbcRowSetTest.java
// Displaying the contents of the authors table using JdbcRowSet.
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl; // Sun’s JdbcRowSet implementation
public class JdbcRowSetTest
{
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
static final String USERNAME = "jhtp6";
static final String PASSWORD = "jhtp6";
107
Outline
JdbcRowSetTest.jav
a
(1 of 3)
Line 27
// constructor connects to database, queries database, processes
// results and displays results in window
public JdbcRowSetTest()
{
// connect to database books and query database
try
{
Class.forName( JDBC_DRIVER ); // load database driver class
Line 17
Line 28
Line 29
// specify properties of JdbcRowSet
Use Sun’s reference
JdbcRowSet rowSet = new JdbcRowSetImpl();
InvokeURL
JdbcRowSet
method
implementation
of JdbcRowSet
Line 30
rowSet.setUrl( DATABASE_URL ); // set database
Invoke
JdbcRowSet
method
setUrl interface
to specify(JdbcRowSetImpl)
the database URL
to
rowSet.setUsername( USERNAME ); // set username
Invoke
JdbcRowSet
method
setUsername
to
specify
the
username
rowSet.setPassword( PASSWORD ); // set password
createInvoke
a JdbcRowSet
object
JdbcRowSet
method
Line 31
rowSet.setCommand( "SELECT * FROM authors"setUsername
); // set queryto specify the password
Invoke JdbcRowSet
method
setCommand
to specify the query
rowSet.execute(); // execute query
execute to execute the query
 2005 Pearson Education,
Inc. All rights reserved.
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
// process query results
ResultSetMetaData metaData = rowSet.getMetaData();
int numberOfColumns = metaData.getColumnCount();
System.out.println( "Authors Table of Books Database:" );
// display rowset header
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
System.out.println();
// display each row
while ( rowSet.next() )
{
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t", rowSet.getObject( i ) );
System.out.println();
} // end while
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
System.exit( 1 );
} // end catch
catch ( ClassNotFoundException classNotFound )
{
classNotFound.printStackTrace();
System.exit( 1 );
} // end catch
} // end DisplayAuthors constructor
108
Outline
JdbcRowSetTest.jav
a
(2 of 3)
 2005 Pearson Education,
Inc. All rights reserved.
63
// launch the application
64
public static void main( String args[] )
65
{
66
67
109
Outline
JdbcRowSetTest window = new JdbcRowSetTest();
} // end main
68 } // end class JdbcRowSetTest
Authors Table of Books Database:
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Tem
Nieto
4
Sean
Santry
JdbcRowSetTest.jav
a
(3 of 3)
Program output
 2005 Pearson Education,
Inc. All rights reserved.