Database Connection
Download
Report
Transcript Database Connection
1
25
Accessing
Databases with
JDBC
1992-2007 Pearson Education, Inc. All rights reserved.
2
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.
To use the RowSet interface from package javax.sql to
manipulate databases.
To use JDBC 4.0’s automatic JDBC driver discovery.
To use PreparedStatements to create precompiled SQL
statements with parameters.
How transaction processing makes database
applications more robust.
1992-2007 Pearson Education, Inc. All rights reserved.
3
25.1
25.2
25.3
25.4
25.5
Introduction
Relational Databases
Relational Database Overview: The books Database
SQL
25.4.1 Basic SELECT Query
25.4.2 WHERE Claus
25.4.3 ORDER BY Claus
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
Instructions for installing MySQL and MySQL
Connector/J
1992-2007 Pearson Education, Inc. All rights reserved.
4
25.6
25.7
Instructions for Setting Up a MySQL User Account
Creating Database book 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
RowSet
Interface
25.10 Java DB/Apache Derby
25.11 PreparedStatements
25.12 Stored Procedures
25.13 Transaction Processing
25.14 Wrap-Up
25.15 Web Resources and Recommended Readings
1992-2007 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
1992-2007 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
1992-2007 Pearson Education, Inc. All rights reserved.
7
Software Engineering Observation 25.1
Using the JDBC API enables developers to
change the underlying DBMS without
modifying the Java code that accesses the
database.
1992-2007 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.
1992-2007 Pearson Education, Inc. All rights reserved.
10
Fig. 25.2 | Result of selecting distinct Department and Location
data from table Employee.
1992-2007 Pearson Education, Inc. All rights reserved.
11
25.3 Relational Database Overview: The
books Database
• Sample books database
– Four tables
• authors
– authorID, firstName, lastName
• 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 the books database.
1992-2007 Pearson Education, Inc. All rights reserved.
13
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
Fig. 25.4 | Sample data from the authors table.
1992-2007 Pearson Education, Inc. All rights reserved.
14
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
2005 Pearson Education, Inc. All rights reserved.
15
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.5 | authorISBN table from the books database.
1992-2007 Pearson Education, Inc. All rights reserved.
16
authorID
isbn
authorID
isbn
1
0131869000
2
0131450913
2
0131869000
1
0131828274
1
0131483986
2
0131828274
2
0131483986
3
0131450913
1
0131450913
4
0131828274
Fig. 25.6 | Sample data from the authorISBN table of books.
1992-2007 Pearson Education, Inc. All rights reserved.
17
Column
Description
isbn
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 use to give every book a unique
identification number.
title
Title of the book (a string).
editionNumber
Edition number of the book (an integer).
copyright
Copyright year of the book (a string).
Fig. 25.7 | titles table from the books database.
1992-2007 Pearson Education, Inc. All rights reserved.
18
isbn
title
editionNumber copyright
0131869000
Visual Basic How to Program
3
2006
0131525239
Visual C# How to Program
2
2006
0132222205
Java How to Program
7
2007
0131857576
C++ How to Program
5
2005
0132404168
C How to Program
5
2007
0131450913
Internet & World Wide Web
How to Program
3
2004
Fig. 25.8 | Sample data from the titles table of the books database.
1992-2007 Pearson Education, Inc. All rights reserved.
19
25.3 Relational Database Overview: The
books Database (Cont.)
• Entity-relationship (ER) diagram
– Tables in the database
– Relationships among tables
• 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.
20
Fig. 25.9 | Table relationships in the books database.
1992-2007 Pearson Education, Inc. All rights reserved.
21
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.
1992-2007 Pearson Education, Inc. All rights reserved.
22
Common Programming Error 25.2
Providing the same value for the primary
key in multiple rows causes the DBMS to
report an error.
1992-2007 Pearson Education, Inc. All rights reserved.
23
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.
1992-2007 Pearson Education, Inc. All rights reserved.
24
25.4 SQL
• SQL keywords
– SQL queries and statements
2005 Pearson Education, Inc. All rights reserved.
25
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.10 | SQL query keywords.
1992-2007 Pearson Education, Inc. All rights reserved.
26
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.
27
authorID
lastName
1
Deitel
2
Deitel
3
Goldberg
4
Choffnes
Fig. 25.11 | Sample authorID and lastName data from the authors table.
1992-2007 Pearson Education, Inc. All rights reserved.
28
Software Engineering Observation 25.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).
1992-2007 Pearson Education, Inc. All rights reserved.
29
Common Programming Error 25.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.
1992-2007 Pearson Education, Inc. All rights reserved.
30
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.
31
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?).
1992-2007 Pearson Education, Inc. All rights reserved.
32
title
editionNumber
copyright
Visual C# How to Program
2
2006
Visual Basic 2005 How to Program
3
2006
Java How to Program
7
2007
C How to Program
5
2007
Fig. 25.12 | Sampling of titles with copyrights after 2005 from table titles.
1992-2007 Pearson Education, Inc. All rights reserved.
33
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.
34
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 25.13 | Authors whose last name starts with D from the authors table.
1992-2007 Pearson Education, Inc. All rights reserved.
35
Portability Tip 25.2
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.
1992-2007 Pearson Education, Inc. All rights reserved.
36
25.4.2 WHERE Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘_i%’
2005 Pearson Education, Inc. All rights reserved.
37
authorID
firstName
lastName
3
Andrew
Goldberg
Fig. 25.14 | The only author from the authors table whose last name
contains o as the second letter.
1992-2007 Pearson Education, Inc. All rights reserved.
38
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.
39
authorID
firstName
lastName
4
David
Choffnes
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
Fig. 25.15 | Sample data from table authors in ascending order by lastName.
1992-2007 Pearson Education, Inc. All rights reserved.
40
authorID
firstName
lastName
3
Andrew
Goldberg
1
Harvey
Deitel
2
Paul
Deitel
4
David
Choffnes
Fig. 25.16 | Sample data from table authors in descending order by lastName.
1992-2007 Pearson Education, Inc. All rights reserved.
41
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.
42
authorID
firstName
lastName
4
David
Choffnes
1
Harvey
Deitel
2
Paul
Deitel
4
Andrew
Goldberg
Fig. 25.17 | Sample data from authors in ascending order
by lastName and firstName.
1992-2007 Pearson Education, Inc. All rights reserved.
43
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.
44
isbn
title
edition
-Number
copy-right
0132404168
C How to Program
5
2007
0131857576
C++ How to Program
5
2005
0131450913
Internet and World Wide Web How to Program 3
2004
0132222205
Java How to Program
7
2007
0131869000
Visual Basic 2005 How to Program
3
2006
013152539
Visual C# How to Program
2
2006
Fig. 25.18 | Sampling of books from table titles whose titles end with
How to Program in ascending order by title.
1992-2007 Pearson Education, Inc. All rights reserved.
45
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.
46
firstName
lastName isbn
firstName lastName
isbn
David
Choffnes
0131828274
Paul
Deitel
0131525239
Harvey
Deitel
0131525239
Paul
Deitel
0132404168
Harvey
Deitel
0132404168
Paul
Deitel
0131869000
Harvey
Deitel
0131869000
Paul
Deitel
0132222205
Harvey
Deitel
0132222205
Paul
Deitel
0131450913
Harvey
Deitel
0131450913
Paul
Deitel
0131525239
Harvey
Deitel
0131525239
Paul
Deitel
0131857576
Harvey
Deitel
0131857576
Paul
Deitel
0131828274
Harvey
Deitel
0131828274
Andrew
Goldberg
0131450913
Fig. 25.19 | Sampling of authors and ISBNs for the books they have written in
ascending order by lastName and firstName.
1992-2007 Pearson Education, Inc. All rights reserved.
47
Software Engineering Observation 25.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).
1992-2007 Pearson Education, Inc. All rights reserved.
48
Common Programming Error 25.5
Failure to qualify names for columns
that have the same name in two or more
tables is an error.
1992-2007 Pearson Education, Inc. All rights reserved.
49
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.
50
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
5
Sue
Smith
Fig. 25.20 | Sample data from table Authors after an INSERT operation.
1992-2007 Pearson Education, Inc. All rights reserved.
51
Common Programming Error 25.6
It is normally an error to specify a
value for an autoincrement column.
1992-2007 Pearson Education, Inc. All rights reserved.
52
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.
1992-2007 Pearson Education, Inc. All rights reserved.
53
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.
54
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
5
Sue
Jones
Fig. 25.21 | Sample data from table authors after an UPDATE operation.
1992-2007 Pearson Education, Inc. All rights reserved.
55
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.
56
authorID
firstName
lastName
1
Harvey
Deitel
2
Paul
Deitel
3
Andrew
Goldberg
4
David
Choffnes
Fig. 25.22 | Sample data from table authors after a DELETE operation.
1992-2007 Pearson Education, Inc. All rights reserved.
57
25.5 Instructions to Install MySQL and
MySQL Connector/J
• Install MySQL
– Platform-specific installation requirements:
• dev.mysql.com/doc/refman/5.0/en/generalinstallation-issues.html
– Download your platform’s installer from:
• dev.mysql.com/downloads/mysql/5.0.html
• Need only the Windows Essentials package on Microsoft
Windows
– Follow installation instructions for your platform:
• dev.mysql.com/doc/refman/5.0/en/installing.
html
2005 Pearson Education, Inc. All rights reserved.
58
25.5 Instructions to Install MySQL and
MySQL Connector/J
• MySQL Server Instance Configuration Wizard
– Click Next > then select Standard Configuration and
click
Next > again.
– Not necessary to install MySQL as a Windows service for
our examples
• Uncheck Install as a Windows Service
• Check Include Bin Directory in Windows PATH
– Click Next > then click Execute to perform the server
configuration.
– Click Finish to close the wizard.
2005 Pearson Education, Inc. All rights reserved.
59
25.5 Instructions to Install MySQL and
MySQL Connector/J
• Install MySQL Connector/J
– Must install Connector/J JDBC driver from:
• dev.mysql.com/downloads/connector/j/
5.0.html
– Download mysql-connector-java-5.0.4.zip
– Extract mysql-connector-java-5.0.4.zip to your
hard disk into the folder mysql-connector-java-5.0.4
– Documentation for MySQL Connector/J is in
connector-j.pdf in the docs subdirectory of
mysql-connector-java-5.0.4
– Docs also online at
dev.mysql.com/doc/connector/j/en/
connector-j.html
2005 Pearson Education, Inc. All rights reserved.
60
25.6 Instructions on Setting MySQL User
Account
• Set up a user account
– Start database server
• mysqld-nt.exe on Windows
– Start the MySQL monitor
• mysql –h localhost –u root
– Select the built-in database mysql
• USE mysql;
– Add the user account jhtp7 and specify privileges
• create user 'jhtp7'@'localhost' identified by 'jhtp7';
• grant select, insert, update, delete, create, drop,
references, execute on *.* to 'jhtp7'@'localhost';
– Exit the MySQL Monitor
• exit;
2005 Pearson Education, Inc. All rights reserved.
61
25.7 Creating Database books in MySQL
• Create books database
– Open Command Prompt and change to the directory
containing the SQL script books.sql
– Start the MySQL monitor
• mysql –h localhost –u jhtp7 –p
– Execute the script
• source books.sql;
– Exit the MySQL Monitor
• exit;
2005 Pearson Education, Inc. All rights reserved.
62
25.8 Manipulating Databases with JDBC
• Connect to a database
• Query the database
• Display the results of the query
2005 Pearson Education, Inc. All rights reserved.
63
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.
64
25.8.1 Connecting to and Querying a
Database
• JDBC
– Java Database Connectivity
– JDBC driver
• Enable Java applications to connect to database
• Enable programmers to manipulate databases using JDBC
– Execute SQL statements
– Retrieve results
– Present data in a user frendly interface
– Propagete changes back to database
– JDBC API
• Classes and interfaces -
2005 Pearson Education, Inc. All rights reserved.
65
25.8.1 Connecting to and Querying a
Database
•JDBC API
– Establishing connections with databases
– Sending SQL statements to databases
– Processing the results of the SQL statements
– Obtaining database metadata
• Four key interfaces:
– Driver, Connection, Statement, ResultSet
– JDBC API define these interfaces
– JDBC driver vendors provide implementations for them
– Programmers use the interfaces
2005 Pearson Education, Inc. All rights reserved.
66
25.8.1 Connecting to and Querying a
Database
•Following steps
– Loading drivers
– Establishing connections
– Creating statements
– Executing statements
– Obtain metadata
– Processing resultSet
2005 Pearson Education, Inc. All rights reserved.
67
Loading Drivers
•Appropriate driver must be loaded
– Class.forName(String DriverClassName);
– Drivers are concrete classes that imlement
• Java.sql.Driver – interface
• For Access databases
• Driver: sun.jdbc.odbc.JdbcOdbcDriver
– in JDK
• After Jav a 6 automatically found and loaded
2005 Pearson Education, Inc. All rights reserved.
68
Establishing connections
•To connected to the database use
static method
– getConnection(String databseuRL)
– Of the DriverManager class
•Where databaseURL is the unique
identiier of the database on the
internet
2005 Pearson Education, Inc. All rights reserved.
69
Establishing connections
• For Access databases
• databaseURL:
– Jdbc:odbc:datasource
– An ODBC datasource canbe created using
ODBC Administrator of Windows
• Exaaple:
• Connection connection =
DriverManager.getConnections(databaseURL,userName,
passpard);
• Connection connection =
DriverManager.getConnections(“jdbc:odbc:Personel”,””,
””);
2005 Pearson Education, Inc. All rights reserved.
70
Creating statements
• Once a connection object is created
– in previous slide : connection
• Statement statement =
connection.createStatement();
• createStatement method of Connection class is
invoked on the connection object
• Returns a Statement object on which queries can
be executed
2005 Pearson Education, Inc. All rights reserved.
71
Creating statements
• Executing queries:
– Use executeQuery, execıte or executeUpdate
– Methods of the Statement class on the statement object
– ExecuteQuery returns a resultSet object
– Methods of Statemet on a statement object
• Examples:
– statement.executeQuery(“SELECT name from autors”);
– On the statement object executeOuery method is inovked
sending the SQL query as a String
2005 Pearson Education, Inc. All rights reserved.
72
Processing resultSet
• The resultSet maintains a table whose current
raw can be retrieved
• ExecuteQuery method returns a ResultSet object
2005 Pearson Education, Inc. All rights reserved.
73
Example
• The first example queries a simple databes
– An Access database
– ODBC source name Personel
– The database has an employee tabhle
• The employee table cansists of
– EmployeeID and Name columns
2005 Pearson Education, Inc. All rights reserved.
74
Example (cont.)
// Import for the JDBC classes and interfaces from
package java.sql
import java.sql.*;
public class DataBase {
static final String DATASOURCE = “jdbc:odbc:Personel";
public static void main(String[] args) throws Exception {
Connection connection; // Connection object
Statement statement; // connection statement
connection =
DriverManager.getConnection(DATASOURCE,"","");
statement=connection.createStatement();
2005 Pearson Education, Inc. All rights reserved.
75
Example (cont.)
// insert a new employee
String newInsert = "INSERT INTO
employee (employeeID,name)
VALUES (6,‘Ali')";
statement.execute(newInsert);
// query database
String query = "SELECT * FROM
employee";
ResultSet results =
statement..executeQuery(query);
2005 Pearson Education, Inc. All rights reserved.
76
Example (cont.)
// Position the ResultSet cursor to the first row
in the ResultSet with
method next
while(results.next()) {
// Extract the contents of one column in the
current row
System.out.print(results.getString(“EmloyeeID"));
// System.out.print(results.getString(1));
System.out.print("\t");
System.out.print(results.getString(“Name"));
// System.out.print(results.getString(2));
System.out.print("\t");
System.out.println();
} // end while loop
2005 Pearson Education, Inc. All rights reserved.
77
Example (cont.)
// Close the Statement and the
database Connection.
results.close();
statement.close();
connection.close();
} // end main
}
// end Database
2005 Pearson Education, Inc. All rights reserved.
78
Example – different version
// Imports for the JDBC classes and
interfaces from package java.sql
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class Database2
{
static final String DATASOURCE =
“jdc:odbc:Personel";
2005 Pearson Education, Inc. All rights reserved.
79
Example – different version (cont.)
public static void main(String[] args) throws
SQLException, ClassNotFoundException {
Connection connection; // Connection referance
Statement statement; // connection statement
connection = DriverManager.getConnection(,
DATASOURCE,"");
statement = connection.createStatement();
String query = "SELECT * FROM employee";
ResultSet results =
statement.executeQuery(query);
ResultSetMetaData metaData =
results.getMetaData();
2005 Pearson Education, Inc. All rights reserved.
80
Example – different version (cont.)
int numberOfColunms =
metaData.getColumnCount();
System.out.println(“Nuber of
colluncs:"+numberOfCollunms);
for(int i=1;i<=muberOfCollunms;i++)
System.out.printf("%-12s\t",
metaData.getColumnName(i));
System.out.println();
2005 Pearson Education, Inc. All rights reserved.
81
Example – different version (cont.)
// Position the ResultSet cursor to the first row in the
ResultSet with
method next
while(results.next()) {
// Extract the contents of one column in the current row
for(int i = 1;i<=nuberOfCollumns;i++)
System.out.printf("%12s\t",results.getObject(i));
System.out.println();
}
// Close the Statement and the database Connection.
results.close();
statement.close();
connection.close();
} // end main
} // end Database
2005 Pearson Education, Inc. All rights reserved.
82
Example – with exception handling
// Import for the JDBC classes and interfaces from
package java.sql
import java.sql.*;
public class Database3 {
static final String DATASOURCE =
“jdc:odbc:Personel";
public static void main(String[] args) throws
SQLException,
ClassNotFoundException {
Connection connection; // Connection
referance
Statement statement; // connection statement
2005 Pearson Education, Inc. All rights reserved.
83
Example – with exception handling
// connect to database Personel and query database
try {
// establish connection to database
connection =
DriverManager.getConnection(DATASOURCE, "",
"" );
// create Statement for querying database
statement = connection.createStatement();
// query database
resultSet = statement.executeQuery(query);
2005 Pearson Education, Inc. All rights reserved.
84
Example – with exception handling
// process query results
// Obtains the metadata for the ResultSet.
ResultSetMetaData metaData =
resultSet.getMetaData();
int numberOfColumns =
metaData.getColumnCount();
System.out.println( "Authors Table of Books
Database:\n" );
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t",
metaData.getColumnName( i ) );
System.out.println();
2005 Pearson Education, Inc. All rights reserved.
85
Example – with exception handling
// Position the ResultSet cursor to the first row in the ResultSet with
method next
while ( resultSet.next() ) {
// Extract the contents of one column in the current row
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t",
resultSet.getObject( i ) );
System.out.println();
} // end while
} // end try
// Catch SQLException, which is thrown if the query execution or ResultSet process fails
catch ( SQLException sqle ) {
sqle.printStackTrace();
} // end catch
// ClassNotFoundException is thrown if the class loader cannot locate the driver class
catch ( ClassNotFoundException cnf ) {
cnf.printStackTrace();
} // end catch
2005 Pearson Education, Inc. All rights reserved.
86
Example – with exception handling
Finally { // ensure resultSet, statement and
connection are closed
// Close the Statement and the database Connection.
try {
resultSet.close();
statement.close();
connection.close();
} // end try
catch ( Exception e ) {
e.printStackTrace();
} // end catch
} // end finally
} // end main
} // end class Database3
2005 Pearson Education, Inc. All rights reserved.
87
Example – with exception handling
// Initialize a Connection reference called connection.
// Invokes Connection method createStatement to obtain an object that
implements interface Statement.
// Use the Statement object’s executeQuery method to execute a query that
selects all the author information from table authors.
// Obtains the metadata for the ResultSet.
// Uses ResultSetMetaData method getColumnCount to retrieve the number
of columns in the ResultSet.
// Obtain column name using method getColumnName
// Position the ResultSet cursor to the first row in the ResultSet with method
next
// Extract the contents of one column in the current row
2005 Pearson Education, Inc. All rights reserved.
88
25.8.1 Connecting to and Querying a
Database
import java.sql.*;
public class dbConnection {
static final String DATASOURCE =
“jdc:odbc:Personel";
public static void main(String[] args) throws
Exception {
Connection con =
DriverManager.getConnection(DATASOURCE,"","");
Statement st = con.createStatement();
2005 Pearson Education, Inc. All rights reserved.
89
25.8.1 Connecting to and Querying a
Database
PreparedStatement prSt =
con.prepareStatement("insert into
empluyee (EmployeeID,Name) values
(?,?)");
prSt.setString(1, “7");
prSt.setString(2, “Mehmet");
prSt.executeUpdate();
st.close();
con.close()
} // end main
} // end dbConnection class
2005 Pearson Education, Inc. All rights reserved.
90
Example: An update Query
• An example getting information from the user
and by connecting to a database update the
database
• Get a new employee:
– Employee ID and Name from the user
– from a GUI
• Insert the new employee to the Employee
database
2005 Pearson Education, Inc. All rights reserved.
91
Example: An update Query
import
import
import
import
javax.swing.*;
java.sql.*;
java.awt.event.*;
java.awt.*;
public class DbConnection {
public static void main(String[] args)throws Exception {
Frame frame = new Frame();
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setSize(300,300);
frame.setVisible(true);
} // end main
} // end class DbConnection
2005 Pearson Education, Inc. All rights reserved.
92
Example: An update Query
• Frame class extends from JFrame
– Two labels and two textFields and a button
– When the user enters new emloyee ID and name
• Presses the Send button
• An action event is generated
• The actionPerformed method
– gets the values of the two textFields
– Creates a specific connection object that accept these two
strings as arguments
– Call the connectTo method of over the object to connect to
the database
2005 Pearson Education, Inc. All rights reserved.
93
Example: An update Query
class Frame extends JFrame{
private
private
private
private
private
JLabel label1;
JLabel label2;
JTextField textField1;
JTextField textField2;
JButton button1;
public Frame()
{
label1=new JLabel("New Employee ID");
label2=new JLabel("New Name");
button1 = new JButton("Send");
textField1 = new JTextField();
textField2 = new JTextField();
2005 Pearson Education, Inc. All rights reserved.
94
Example: An update Query
setLayout(new GridLayout(3,2));
add(label1);
add(textField1);
add(label2);
add(textField2);
add(button1);
ButonHandler handler = new ButonHandler();
button1.addActionListener(handler);
} // end of constructor
2005 Pearson Education, Inc. All rights reserved.
95
Example: An update Query
private class ButonHandler implements ActionListener
{
public void actionPerformed(ActionEvent e) {
String s1 = textField1.getText();
String s2 = textField2.getText();
MyConnection myCon = new
MyConnection(s1,s2);
myCon.connectTo();
} // end method actionPerformed
} // end class ButtonHandler
} // end class Frame
2005 Pearson Education, Inc. All rights reserved.
96
Example: An update Query
class MyConnection
{
private String str1;
private String str2;
public MyConnection(String s1, String s2) {
str1 = s1;
str2 = s2;
} // end constructor
2005 Pearson Education, Inc. All rights reserved.
97
Example: An update Query
public void connectTo() {
// make the odbc within java
String connectionStr =
"jdbc:odbc:Personel";
try {
Connection con =
DriverManager.getConnection(
connectionStr,"","");
PreparedStatement prSt =
con.prepareStatement("insert into employee
values (?,?)");
2005 Pearson Education, Inc. All rights reserved.
98
Example: An update Query
prSt.setString(1, str1);
prSt.setString(2, str2);
prSt.executeUpdate();
} // end try
catch (Eexception e) {
e.getStackTrace();
} // end catch
Finally {
prSt.close();
con.close();
} // end finally
} // end method connectTo
} // end class MyConnection
2005 Pearson Education, Inc. All rights reserved.
99
Another Desing Option
• Frame class
– Get data from the user – to insert a new employee
– Send the information to an object of MyConnection
• MyConnect class
– Having the values of two Strings
– Prepare a SQL query statement
– Call a generic Connection class
• Connection class
– A generic Connectin class written for geeral purpases
– Accept
• Driver Name, dataSource,query
• Returns a ResultSet reference
2005 Pearson Education, Inc. All rights reserved.
100
Another Desing Option
Frame and MyConnection classes are the same
But MyConnection’s connectTo method:
creates an object from a generic connection class
sending
drivename,url,username,pasward.and query
returns a resultset
or for update or insert queries sending
drivename,url,username,pasward.and query
and parameters
2005 Pearson Education, Inc. All rights reserved.
101
Another Desing Option
class MyConnection
{
private String str1;
private String str2;
private String Driver = …;
private String url =…;
private ResultSet myResults;
private String query = "insert into employee(
employeeID, Name) values (“+str1+”,”+str2+”)"
2005 Pearson Education, Inc. All rights reserved.
102
Another Desing Option
public MyConnection(String s1, String s2) {
str1 = s1;
str2 = s2;
prepareQuery();
sendQuery();
} // end constructor
public void prepareQuery() { // prepare query here }
public void sendQuery() {
ConnectClass conClass = new ConnectClass(Driver,url,query);
myResults = conClass.execute();
}
// process results here
// other methods
} // end class
2005 Pearson Education, Inc. All rights reserved.
103
Another Desing Option
class ConnectClass
{
private String driver;
private String url;
private String query;
private String userName;
private String passward;
public ConnectClass(String sdrv, String surl,
Srring squery) {
driver = sdrv;
url = surl;
query = squery;
}
2005 Pearson Education, Inc. All rights reserved.
104
Another Desing Option
// a generic method for exeuting a general query and
// returning its results as a Resultset reference
public ResultSet execute() throws Exception {
Class.forName(driver);
Connection con =
DriverManager.getConnection(url,userName,passward);
Statement statement = con.createStatement();
return statement..executeQuery(query);
} // end method
// other methods for get set …
} end class
2005 Pearson Education, Inc. All rights reserved.
1
2
// Fig. 25.23: DisplayAuthors.java
// Displaying the contents of the authors table.
3
4
import java.sql.Connection;
import java.sql.Statement;
5
6
import java.sql.DriverManager;
import java.sql.ResultSet;
7
8
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
105
Outline
Imports for the JDBC classes and
interfaces from package java.sql
DisplayAuthors
.java
9
10 public class DisplayAuthors
(1 of 3 )
11 {
12
13
// JDBC driver name and database URL
static final String DRIVER = "com.mysql.jdbc.Driver";
14
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
15
16
// launch the application
17
public static void main( String args[] )
18
{
19
20
21
22
Connection connection = null; // manages connection
Statement statement = null; // query statement
ResultSet resultSet = null; // manages results
23
24
// connect to database books and query database
try
25
26
{
27
28
// load the driver class
Class.forName( DRIVER );
Declare a String
constant that specifies the
JDBC driver’s class name
Declare a String
constant that specifies
the database URL
Loads the class definition
for the database driver.
1992-2007 Pearson Education, Inc. All rights reserved.
// establish connection to database
connection =
29
30
DriverManager.getConnection( DATABASE_URL, "jhtp7", "jhtp7" );
31
32
33
106
// create Statement for querying database
statement = connection.createStatement();
34
35
36
37
38
39
Invokes ConnectionDisplayAuthors
method
createStatement to .java
obtain an object
// query database
Use theinterface
Statement
object’s
that implements
Statement.
resultSet = statement.executeQuery(
executeQuery
to
(2 ofmethod
3)
"SELECT authorID, firstName, lastName FROM authors" );
execute a query that selects
all the author information
// process query results
Obtains the metadata
ResultSetMetaData metaData = resultSet.getMetaData();
from
table authors.
Uses
ResultSetMetaData
for
the
ResultSet.
int numberOfColumns = metaData.getColumnCount();
System.out.println( "Authors Table of Books Database:\n" );method getColumnCount
to retrieve the number of
columns in the ResultSet.
for ( int i = 1; i <= numberOfColumns; i++ )
Obtain column
System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
name using method
System.out.println();
getColumnName
Position the ResultSet cursor to the first
while ( resultSet.next() )
row in the ResultSet with method next
{
40
41
42
43
44
45
46
47
48
49
50
51
for ( int i = 1; i <= numberOfColumns; i++ )
52
53
System.out.printf( "%-8s\t", resultSet.getObject( i ) );
System.out.println();
} // end while
54
55
Initialize a
Outline
Connection
reference
called connection.
}
Extract the contents
of one column in the
current row
// end try
1992-2007 Pearson Education, Inc. All rights reserved.
56
57
58
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
59
60
61
62
63
} // end catch
catch ( ClassNotFoundException classNotFound )
{
classNotFound.printStackTrace();
} // end catch
64
65
66
67
68
69
finally // ensure resultSet, statement and connection
{
try
{
resultSet.close();
Close the Statement
statement.close();
70
71
72
connection.close();
} // end try
catch ( Exception exception )
73
74
75
{
Catch SQLException, which
Outline
is thrown if the query execution
or ResultSet process fails
107
DisplayAuthors
ClassNotFoundException
is
.java
thrown if the class loader
cannot
locate the driver class
are
closed
(3 of 3 )
and
the database Connection.
exception.printStackTrace();
} // end catch
76
} // end finally
77
} // end main
78 } // end class DisplayAuthors
Authors Table of Books Database:
authorID
1
2
3
4
firstName
Harvey
Paul
Andrew
David
lastName
Deitel
Deitel
Goldberg
Choffnes
1992-2007 Pearson Education, Inc. All rights reserved.
108
Software Engineering Observation 25.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 Web site,
servlet.java.sun.com/products/
jdbc/drivers.
1992-2007 Pearson Education, Inc. All rights reserved.
109
Software Engineering Observation 25.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.
1992-2007 Pearson Education, Inc. All rights reserved.
110
RDBMS
Database URL format
MySQL
jdbc:mysql://hostname:portNumber/databaseName
ORACLE
jdbc:oracle:thin:@hostname:portNumber:databaseName
DB2
jdbc:db2:hostname:portNumber/databaseName
Java DB/Apache Derby
jdbc:derby:dataBaseName (embedded)
jdbc:derby://hostname:portNumber/databaseName (network)
Microsoft SQL Server
jdbc:sqlserver://hostname:portNumber;databaseName=dataBaseName
Sybase
jdbc:sybase:Tds:hostname:portNumber/databaseName
Fig. 25.24 | Popular JDBC database URL formats.
1992-2007 Pearson Education, Inc. All rights reserved.
111
Software Engineering Observation 25.6
Metadata enables programs to process
ResultSet contents dynamically when
detailed information about the
ResultSet is not known in advance.
1992-2007 Pearson Education, Inc. All rights reserved.
112
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.
1992-2007 Pearson Education, Inc. All rights reserved.
113
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 search of the column names to locate
the appropriate column.
1992-2007 Pearson Education, Inc. All rights reserved.
114
Common Programming Error 25.9
Specifying column number 0 when
obtaining values from a ResultSet
causes a SQLException.
1992-2007 Pearson Education, Inc. All rights reserved.
115
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.
1992-2007 Pearson Education, Inc. All rights reserved.
116
Software Engineering Observation 25.7
Each Statement object can open only one
ResultSet object at a time. When a
Statement returns a new ResultSet, the
Statement closes the prior ResultSet.
To use multiple ResultSets in parallel,
separate Statement objects must return
the ResultSets.
1992-2007 Pearson Education, Inc. All rights reserved.
117
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
// Fig. 25.25: ResultSetTableModel.java
2
// A TableModel that supplies ResultSet data to a JTable.
3
import java.sql.Connection;
4
import java.sql.Statement;
5
6
import java.sql.DriverManager;
import java.sql.ResultSet;
7
8
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
118
Outline
ResultSetTable
Model.java
9 import javax.swing.table.AbstractTableModel;
10
(1 of 7 )
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).
17 public class ResultSetTableModel extends AbstractTableModel
18 {
19
private Connection connection;
20
private Statement statement;
Class ResultSetTableModel extends
21
private ResultSet resultSet;
class AbstractTableModel, which
22
private ResultSetMetaData metaData;
23
private int numberOfRows;
implements interface TableModel.
24
25
26
27
// keep track of database connection status
private boolean connectedToDatabase = false;
Instance variable keeps track
of database connection status
1992-2007 Pearson Education, Inc. All rights reserved.
28
// constructor initializes resultSet and obtains its meta data object;
29
// determines number of rows
30
public ResultSetTableModel( String driver, String url, String Constructor
username,
String password, String query
)
Establishes
a connection
throws SQLException, ClassNotFoundException
to the database.
31
32
33
34
35
{
Class.forName( driver );
// connect to database
connection = DriverManager.getConnection( url, username, password );
39
40
41
42
statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
43
44
45
// update database connection status
connectedToDatabase = true;
46
47
48
49
Outline
accepts five String
arguments—the driver class
name, the database URL, the
ResultSetTable
username, the password
and the
Model.java
default query to perform
36
37
38
// create Statement to query database
119
(2 of 7 )
Invokes Connection
method createStatement to
create a Statement object.
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.
1992-2007 Pearson Education, Inc. All rights reserved.
50
51
// get class that represents column type
public Class getColumnClass( int column ) throws IllegalStateException
52
53
{
54
Verify database
Override method getColumnClass to obtain
connection status
IllegalStateException( "Not Connected to Database" );
ResultSetTable
a Class object that represents
the superclass
Model.java
of all objects in a particular column
Java class of column
if ( !connectedToDatabase )
throw new
56
57
58
// determine
try
61
62
63
Outline
// ensure database connection is available
55
59
60
120
{
String className = metaData.getColumnClassName( column
(3 of 7 ) class
Obtains the fully qualified
+name
1 ); for the specified column.
// return Class object that represents className
Loads
return Class.forName( className );
64
} // end try
65
66
67
68
69
catch ( Exception exception )
{
exception.printStackTrace();
} // end catch
the class definition for the class and
returns the corresponding Class object.
Returns the
default type.
70
71
return Object.class; // if problems occur above, assume type Object
} // end method getColumnClass
72
73
// get number of columns in ResultSet
74
75
76
77
78
public int getColumnCount() throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
method
throw new IllegalStateException( Override
"Not Connected
togetColumnCount
Database" );
79
to obtain the number of columns in the
model’s underlying ResultSet
1992-2007 Pearson Education, Inc. All rights reserved.
80
81
// determine number of columns
try
82
83
{
84
} // end try
85
catch ( SQLException sqlException )
86
87
88
{
121
Obtains the number of
columns in the ResultSet.
return metaData.getColumnCount();
ResultSetTable
Model.java
sqlException.printStackTrace();
} // end catch
89
90
Outline
(4 of 7 )
return 0; // if problems occur above, return 0 for number of columns
91
92
93
} // end method getColumnCount
94
public String getColumnName( int column ) throws IllegalStateException
95
96
97
98
99
{
// get name of a particular column in ResultSet
// ensure database connection is available
if ( !connectedToDatabase )
method
getColumnName
throw new IllegalStateException( "NotOverride
Connected
to Database"
);
100
101
// determine column name
try
102
103
{
104
105
106
107
108
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
} // end catch
to obtain the name of the column in
the model’s underlying ResultSet
return metaData.getColumnName( column + 1 );
Obtains the column name
from the ResultSet.
109
1992-2007 Pearson Education, Inc. All rights reserved.
return ""; // if problems, return empty string for column name
110
111
112
} // end method getColumnName
113
// return number of rows in ResultSet
114
public int getRowCount() throws IllegalStateException
115
116
117
118
119
{
120
121
return numberOfRows;
} // end method getRowCount
122
123
124
125
126
// obtain value in particular row and column
public Object getValueAt( int row, int column )
throws IllegalStateException
{
Outline
// ensure database connection is available
if ( !connectedToDatabase )
Override
method
getColumnCount
throw new IllegalStateException(
"Not
Connected
to Database" );
to obtain the number of rows in the
model’s underlying ResultSet
127
128
129
130
// 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" );
131
132
133
// obtain a value at specified ResultSet row and column
try
{
134
135
136
122
ResultSetTable
Model.java
(5 of 7 )
of the model’s underlying ResultSet
Uses ResultSet method absolute to position
ResultSet cursor at a specific row.
resultSet.absolute( row + 1 );
the
return resultSet.getObject( column + 1 );
} // end try
Uses ResultSet method getObject
to obtain the Object in a specific
column of the current row.
1992-2007 Pearson Education, Inc. All rights reserved.
137
138
catch ( SQLException sqlException )
{
139
140
sqlException.printStackTrace();
} // end catch
123
Outline
141
return ""; // if problems, return empty string object
142
143
144
145
} // end method getValueAt
146
147
public void setQuery( String query )
throws SQLException, IllegalStateException
148
149
150
{
151
152
153
154
155
156
// set new database query string
(6 of 7 )
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );
// specify query and execute it
resultSet = statement.executeQuery( query );
Executes the query to
obtain a new ResultSet.
// obtain meta data for ResultSet
Uses ResultSet method last to
position the ResultSet cursor at
ResultSet the last row in the ResultSet.
157
158
metaData = resultSet.getMetaData();
159
160
// determine number of rows in
resultSet.last(); // move to last row
161
162
163
164
165
numberOfRows = resultSet.getRow(); // get row number
166
ResultSetTable
Model.java
// notify JTable that model has changed
fireTableStructureChanged();
} // end method setQuery
Uses ResultSet method getRow
Invokes
methodthe
fireTableAStructureChanged
to obtain
row number for the
to notify
any row
JTable
using
this
current
in the
ResultSet.
ResultSetTableModel object as its model that
the structure
of the model
has Education,
changed.Inc. All rights reserved.
1992-2007
Pearson
167
// close Statement and Connection
168
169
public void disconnectFromDatabase()
{
170
171
124
Verify whether the connection
is already terminated
Method disconnectFromDatabase
Statement and Connection
implement an appropriate termination method
for class ResultSetTableModel
if ( connectedToDatabase )
{
172
173
174
175
// close
try
{
resultSet.close();
176
177
178
179
180
statement.close();
connection.close();
} // end try
catch ( SQLException sqlException )
{
181
182
183
184
185
186
sqlException.printStackTrace();
} // end catch
finally // update database connection status
{
connectedToDatabase = false;
} // end finally
187
188
189 }
Outline
} // end if
} // end method disconnectFromDatabase
// end class ResultSetTableModel
ResultSetTable
Model.java
Close the Statement and Connection
if a7 )
(7 of
ResultSetTableModel object is garbage collected.
Set connectedToDatabase to false
to ensure that clients do not use an instance
of ResultSetTableModel after that
instance has already been terminated
1992-2007 Pearson Education, Inc. All rights reserved.
125
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.26 | ResultSet constants for specifying ResultSet type.
1992-2007 Pearson Education, Inc. All rights reserved.
126
Portability Tip 25.3
Some JDBC drivers do not support
scrollable ResultSets. In such cases, the
driver typically returns a ResultSet in
which the cursor can move only forward.
For more information, see your database
driver documentation.
1992-2007 Pearson Education, Inc. All rights reserved.
127
Portability Tip 25.4
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.
1992-2007 Pearson Education, Inc. All rights reserved.
128
Common Programming Error 25.11
Attempting to update a ResultSet when the
database driver does not support updatable
ResultSets causes
SQLFeatureNotSupportedExceptions.
1992-2007 Pearson Education, Inc. All rights reserved.
129
Common Programming Error 25.12
Attempting to move the cursor backward
through a ResultSet when the database
driver does not support backward
scrolling causes a SQLException.
1992-2007 Pearson Education, Inc. All rights reserved.
130
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.27 | ResultSet constants for specifying result properties.
1992-2007 Pearson Education, Inc. All rights reserved.
1
// Fig. 25.28: DisplayQueryResults.java
2
// Display the contents of the Authors table in the books database.
3
import java.awt.BorderLayout;
4
5
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
6
import java.awt.event.WindowAdapter;
7
8
9
10
11
12
import
import
import
import
import
import
131
Outline
DisplayQuery
Results.java
java.awt.event.WindowEvent;
java.sql.SQLException;
java.util.regex.PatternSyntaxException;
javax.swing.JFrame;
javax.swing.JTextArea;
javax.swing.JScrollPane;
(1 of 8 )
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
20
21
22
import
import
import
import
import
javax.swing.JLabel;
javax.swing.JTextField;
javax.swing.RowFilter;
javax.swing.table.TableRowSorter;
javax.swing.table.TableModel;
23
24 public class DisplayQueryResults extends JFrame
25 {
26
27
// JDBC database URL, username and password
static final String DRIVER = "com.mysql.jdbc.Driver";
28
29
30
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
static final String USERNAME = "jhtp7";
static final String PASSWORD = "jhtp7";
Declare the database driver
class name, database URL,
username and password for
accessing
the database
1992-2007 Pearson
Education,
Inc. All rights reserved.
31
32
132
// default query retrieves all data from authors table
33
34
static final String DEFAULT_QUERY = "SELECT * FROM authors";
35
private ResultSetTableModel tableModel;
36
private JTextArea queryArea;
37
38
39
// create ResultSetTableModel and GUI
public DisplayQueryResults()
40
41
Outline
Declare the default query
Declare tableModel to be a reference
to ResultSetTableModel
DisplayQuery
Results.java
(2 of 8 )
{
super( "Displaying Query Results" );
42
43
44
// create ResultSetTableModel and display database table
try
45
{
// create TableModel for results of query SELECT * FROM authors
tableModel = new ResultSetTableModel( DRIVER, DATABASE_URL,
USERNAME, PASSWORD, DEFAULT_QUERY );
46
47
48
49
50
// set up JTextArea in which user types queries
51
52
queryArea = new JTextArea( DEFAULT_QUERY, 3, 100 );
queryArea.setWrapStyleWord( true );
53
54
queryArea.setLineWrap( true );
55
56
57
58
59
JScrollPane scrollPane = new JScrollPane( queryArea,
ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER );
60
JButton submitButton = new JButton( "Submit Query" );
Create TableModel
for results of default
query “SELECT *
FROM authors”
// set up JButton for submitting queries
1992-2007 Pearson Education, Inc. All rights reserved.
61
62
// create Box to manage placement of queryArea and
63
64
65
// submitButton in GUI
Box boxNorth = Box.createHorizontalBox();
boxNorth.add( scrollPane );
66
67
68
69
70
71
boxNorth.add( submitButton );
72
73
74
final JTextField filterText = new JTextField();
JButton filterButton = new JButton( "Apply Filter" );
Box boxSouth = boxNorth.createHorizontalBox();
75
76
boxSouth.add( filterLabel );
133
Outline
DisplayQuery
Results.java
// create JTable delegate for tableModel
JTable resultTable = new JTable( tableModel );
Create JTable delegate
for tableModel (3 of 8 )
JLabel filterLabel = new JLabel( "Filter:" );
77
78
79
80
81
boxSouth.add( filterText );
boxSouth.add( filterButton );
82
add( new JScrollPane( resultTable ), BorderLayout.CENTER );
83
add( boxSouth, BorderLayout.SOUTH );
84
85
// create event listener for submitButton
86
submitButton.addActionListener(
// place GUI components on content pane
add( boxNorth, BorderLayout.NORTH );
87
1992-2007 Pearson Education, Inc. All rights reserved.
88
89
new ActionListener()
{
90
91
// pass query to table model
public void actionPerformed( ActionEvent event )
92
93
94
95
{
Register an event handler for the
submitButton thatOutline
the user clicks
to submit a query to the database
// perform a new query
try
{
DisplayQuery
Results.java
96
97
tableModel.setQuery( queryArea.getText() );
} // end try
(4 of 8 )
98
99
catch ( SQLException sqlException )
{
100
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database
JOptionPane.ERROR_MESSAGE );
134
Invoke ResultSetTableModel
method setQuery to execute
error",
the new query
101
102
103
104
// try to recover from invalid user query
105
// by executing default query
106
107
108
109
110
111
try
{
tableModel.setQuery( DEFAULT_QUERY );
queryArea.setText( DEFAULT_QUERY );
} // end try
catch ( SQLException sqlException2 )
112
113
114
115
{
JOptionPane.showMessageDialog( null,
sqlException2.getMessage(), "Database error",
JOptionPane.ERROR_MESSAGE );
116
1992-2007 Pearson Education, Inc. All rights reserved.
117
118
// ensure database connection is closed
tableModel.disconnectFromDatabase();
119
120
System.exit( 1 ); // terminate application
122
} // end outer catch
123
124
125
} // end actionPerformed
} // end ActionListener inner class
); // end call to addActionListener
126
127
final TableRowSorter< TableModel > sorter =
128
129
130
new TableRowSorter< TableModel >( tableModel );
resultTable.setRowSorter( sorter );
setSize( 500, 250 ); // set window size
131
setVisible( true ); // display window
132
133
134
135
136
// create listener for filterButton
filterButton.addActionListener(
new ActionListener()
{
DisplayQuery
Results.java
(5 of 8 )
137
138
// pass filter text to listener
public void actionPerformed( ActionEvent e )
139
140
{
146
135
} // end inner catch
121
141
142
143
144
145
Ensure that the database
connection is Outline
closed
Set up
TableRowSorter
String text = filterText.getText();
if ( text.length() == 0 )
sorter.setRowFilter( null );
else
{
No filter initially
try
1992-2007 Pearson Education, Inc. All rights reserved.
147
{
136
sorter.setRowFilter(
RowFilter.regexFilter( text ) );
148
149
150
151
} // end try
catch ( PatternSyntaxException pse )
152
153
154
155
{
156
157
158
159
160
JOptionPane.showMessageDialog( null,
"Bad regex pattern", "Bad regex pattern",
JOptionPane.ERROR_MESSAGE );
} // end catch
} // end else
} // end method actionPerfomed
} // end annonymous inner class
); // end call to addActionLister
161
162
163
164
165
166
} // end try
catch ( ClassNotFoundException classNotFound )
{
JOptionPane.showMessageDialog( null,
"Database Driver not found", "Driver not found",
JOptionPane.ERROR_MESSAGE );
167
168
169
System.exit( 1 ); // terminate application
} // end catch
Outline
Set filter using
regular
expression
DisplayQuery
Results.java
(6 of 8 )
1992-2007 Pearson Education, Inc. All rights reserved.
170
171
172
173
catch ( SQLException sqlException )
{
137
JOptionPane.showMessageDialog( null, sqlException.getMessage(),
"Database error", JOptionPane.ERROR_MESSAGE );
Outline
174
175
// ensure database connection is closed
176
177
178
tableModel.disconnectFromDatabase();
Ensure that the database
connection is closed
System.exit( 1 ); // terminate application
DisplayQuery
Results.java
(7 of 8 )
179
180
} // end catch
181
182
183
// dispose of window when user quits application (this overrides
// the default of HIDE_ON_CLOSE)
setDefaultCloseOperation( DISPOSE_ON_CLOSE );
184
185
186
187
188
189
// ensure database connection is closed when user quits application
addWindowListener(
new WindowAdapter()
{
190
191
// disconnect from database and exit when window has closed
public void windowClosed( WindowEvent event )
192
193
{
194
195
196
197
198
tableModel.disconnectFromDatabase();
System.exit( 0 );
} // end method windowClosed
} // end WindowAdapter inner class
); // end call to addWindowListener
} // end DisplayQueryResults constructor
Ensure that the database
connection is closed
when window is closed
199
1992-2007 Pearson Education, Inc. All rights reserved.
200
201
// execute application
public static void main( String args[] )
202
{
203
new DisplayQueryResults();
204
} // end main
205 } // end class DisplayQueryResults
138
Outline
DisplayQuery
Results.java
(8 of 8 )
1992-2007 Pearson Education, Inc. All rights reserved.
139
25.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.
140
25.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.
141
Portability Tip 25.5
A RowSet can provide scrolling
capability for drivers that do not
support scrollable ResultSets.
1992-2007 Pearson Education, Inc. All rights reserved.
1
// Fig. 25.29: JdbcRowSetTest.java
2
// Displaying the contents of the authors table using JdbcRowSet.
3
import java.sql.ResultSetMetaData;
4
5
6
7
8
import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
import com.sun.rowset.JdbcRowSetImpl; // Sun's JdbcRowSet implementation
142
public class JdbcRowSetTest
9 {
10
// JDBC driver name and database URL
11
12
static final String DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/books";
13
static final String USERNAME = "jhtp7";
14
15
16
static final String PASSWORD = "jhtp7";
// constructor connects to database, queries database, processes
17
18
// results and displays results in window
public JdbcRowSetTest()
19
20
{
Outline
JdbcRowSetTest
.java
(1 of 3 )
// connect to database books and query database
21
try
22
23
24
{
Class.forName( DRIVER );
1992-2007 Pearson Education, Inc. All rights reserved.
25
26
27
28
29
30
31
32
// specify properties of JdbcRowSet
JdbcRowSet rowSet = new JdbcRowSetImpl();
Use Sun’s reference
Invoke
JdbcRowSet
method Outline
rowSet.setUrl( DATABASE_URL ); // set database URL implementation of JdbcRowSet
Invoke JdbcRowSet
setUrl
to specify(JdbcRowSetImpl)
the method
database URL
rowSet.setUsername( USERNAME ); // set username
interface
to
Invoke
JdbcRowSet
method
setUsername
to
specify
the
username
rowSet.setPassword( PASSWORD ); // set password
JdbcRowSet
method
createInvoke
a to
JdbcRowSet
object
setUsername
specify
the password
rowSet.setCommand( "SELECT * FROM authors" ); // set query
setCommand
to JdbcRowSetTest
specify the query
Invoke JdbcRowSet
method
rowSet.execute(); // execute query
143
.java
execute to execute the query
33
34
// process query results
ResultSetMetaData metaData = rowSet.getMetaData();
35
36
int numberOfColumns = metaData.getColumnCount();
System.out.println( "Authors Table of Books Database:\n" );
(2 of 3 )
37
38
39
40
41
// display rowset header
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t", metaData.getColumnName( i ) );
System.out.println();
42
43
44
45
46
47
48
// display each row
while ( rowSet.next() )
{
for ( int i = 1; i <= numberOfColumns; i++ )
System.out.printf( "%-8s\t", rowSet.getObject( i ) );
System.out.println();
49
50
51
52
} // end while
53
// close the underlying ResultSet, Statement and Connection
rowSet.close();
} // end try
1992-2007 Pearson Education, Inc. All rights reserved.
54
catch ( SQLException sqlException )
55
{
56
57
58
sqlException.printStackTrace();
System.exit( 1 );
} // end catch
59
catch ( ClassNotFoundException classNotFound )
60
61
62
{
63
} // end catch
classNotFound.printStackTrace();
System.exit( 1 );
64
65
} // end DisplayAuthors constructor
66
67
// launch the application
public static void main( String args[] )
144
Outline
JdbcRowSetTest
.java
(3 of 3 )
68
{
69
JdbcRowSetTest application = new JdbcRowSetTest();
70
} // end main
71 } // end class JdbcRowSetTest
Authors Table of Books Database:
authorID
1
2
3
4
firstName
Harvey
Paul
Andrew
David
lastName
Deitel
Deitel
Goldberg
Choffnes
1992-2007 Pearson Education, Inc. All rights reserved.
145
25.11 Java DB/Apache Derby
• As of JDK 6, Sun Microsystems now bundles the
open-source, pure Java database Java DB (the
Sun branded version of Apache Derby) with the
JDK
• We use the embedded version of Java DB
• There is also a network version that executes
similarly to the MySQL DBMS introduced earlier
in the chapter
2005 Pearson Education, Inc. All rights reserved.
146
25.11 Java DB/Apache Derby
• Java DB comes with several batch files to configure and run it
• First set the environment variable JAVA_HOME to refer to the JDK’s
C:\Program Files\Java\jdk1.6.0 installation directory
• Open the batch file setEmbeddedCP.bat (located in C:\Program
Files\Java\jdk1.6.0\db\frameworks\embedded\bin) in a text
editor such as Notepad
• Locate the line
rem set DERBY_INSTALL=
and change it to
set DERBY_INSTALL=C:\Program Files\Java\jdk1.6.0\db
• Also, comment out the line
@FOR %%X in ("%DERBY_HOME%") DO SET DERBY_HOME=%%~sX
by preceding it with REM
• Save your changes and close this file
2005 Pearson Education, Inc. All rights reserved.
147
25.11 Java DB/Apache Derby
• Change directories to C:\Program Files\Java\
jdk1.6.0\db\frameworks\embedded\bin\. Then,
type setEmbeddedCP.bat and press Enter to set the
environment variables required by Java DB.
• Embedded Java DB database must reside in the same
location as the application that manipulates the database
– Change to the directory that contains the code for Figs. 25.30–
25.32
• Execute the command
"C:\Program Files\Java\jdk1.6.0\db\frameworks\embedded\bin\ij"
to start the command-line tool for interacting with Java
DB. The double quotes are necessary because the path
contains a space.
2005 Pearson Education, Inc. All rights reserved.
148
25.11 Java DB/Apache Derby
• At the ij> prompt type
connect 'jdbc:derby:AddressBook;create=true;
user=jhtp7;password=jhtp7';
to create the AddressBook database in the current
directory. This command also creates the user jhtp7 with
the password jhtp7 for accessing the database.
• To create the database table and insert sample data in the
database type
run 'address.sql';
• To terminate the Java DB command-line tool, type
exit;
2005 Pearson Education, Inc. All rights reserved.
149
25.12 PreparedStatements
•PreparedStatements execute more efficiently
than Statement objects
•PreparedStatements can specify parameters
2005 Pearson Education, Inc. All rights reserved.
150
25.12 PreparedStatements
• PreparedStatement to locate all book titles for an
author with a specific last name and first name, and to
execute that query for several authors:
– PreparedStatement authorBooks =
connection.prepareStatement(
"SELECT lastName, firstName, title " +
"FROM authors INNER JOIN authorISBN " +
"ON authors.authorID=authorISBN.authorID " +
"INNER JOIN titles " +
"ON authorISBN.isbn=titles.isbn " +
"WHERE lastName = ? AND firstName = ?" );
• Question marks (?) are placeholders for values that will
be passed as part of the query to the database
2005 Pearson Education, Inc. All rights reserved.
151
25.12 PreparedStatements
• 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" );
• setString automatically escapes String parameter
values as necessary (e.g., the quote in the name O’Brien)
• More info at java.sun.com/javase/6/docs/api/
java/sql/PreparedStatement.html
2005 Pearson Education, Inc. All rights reserved.
152
Performance Tip 25.2
PreparedStatements are more
efficient than Statements when
executing SQL statements multiple times
and with different parameter values.
1992-2007 Pearson Education, Inc. All rights reserved.
153
Error-Prevention Tip 25.1
Use PreparedStatements with
parameters for queries that receive
String values as arguments to ensure
that the Strings are quoted properly in
the SQL statement.
1992-2007 Pearson Education, Inc. All rights reserved.
1
2
3
// Fig. 25.30: Person.java
// Person class that represents an entry in an address book.
public class Person
4
5
{
private String firstName;
private String lastName;
private String email;
9
10
private String phoneNumber;
11
// no-argument constructor
12
13
14
15
public Person()
{
} // end no-argument Person constructor
16
17
// constructor
public Person( int id, String first, String last,
Person.java
(1 of 3 )
String emailAddress, String phone )
{
setAddressID( id );
setFirstName( first );
22
23
setLastName( last );
setEmail( emailAddress );
24
setPhoneNumber( phone );
25
26
Outline
private int addressID;
6
7
8
18
19
20
21
154
} // end five-argument Person constructor
1992-2007 Pearson Education, Inc. All rights reserved.
27
28
// sets the addressID
public void setAddressID( int id )
29
{
30
31
32
33
34
35
36
addressID = id;
} // end method setAddressID
37
38
} // end method getAddressID
39
40
41
// sets the firstName
public void setFirstName( String first )
{
42
43
44
firstName = first;
} // end method setFirstName
45
46
47
48
49
// returns the first name
public String getFirstName()
{
return firstName;
} // end method getFirstName
50
51
52
53
54
// sets the lastName
public void setLastName( String last )
{
lastName = last;
55
56
// returns the addressID
public int getAddressID()
{
return addressID;
155
Outline
Person.java
(2 of 3 )
} // end method setLastName
1992-2007 Pearson Education, Inc. All rights reserved.
57
58
// returns the first name
public String getLastName()
59
{
60
61
62
63
64
65
66
return lastName;
} // end method getLastName
67
68
} // end method setEmail
69
70
71
// returns the email address
public String getEmail()
{
72
73
74
return email;
} // end method getEmail
75
76
77
78
79
// sets the phone number
public void setPhoneNumber( String phone )
{
phoneNumber = phone;
} // end method setPhoneNumber
80
81
82
83
84
// returns the email address
public String getPhoneNumber()
{
return phoneNumber;
// sets the email address
public void setEmail( String emailAddress )
{
email = emailAddress;
156
Outline
Person.java
(3 of 3 )
85
} // end method getPhoneNumber
86 } // end class Person
1992-2007 Pearson Education, Inc. All rights reserved.
1
// Fig. 25.31: PersonQueries.java
2
3
// PreparedStatements used by the Address Book application
import java.sql.Connection;
4
5
6
7
8
9
import
import
import
import
import
import
java.sql.DriverManager;
java.sql.PreparedStatement;
java.sql.ResultSet;
java.sql.SQLException;
java.util.List;
java.util.ArrayList;
157
Outline
All program to use
PreparedStatements
PersonQueries
.java
(1 of 7 )
10
11 public class PersonQueries
12 {
13
private static final String URL = "jdbc:derby:AddressBook";
14
private static final String USERNAME = "jhtp7";
15
private static final String PASSWORD = "jhtp7";
16
17
private Connection connection = null; // manages connection
18
19
20
21
private PreparedStatement selectAllPeople = null;
private PreparedStatement selectPeopleByLastName = null;
private PreparedStatement insertNewPerson = null;
22
23
24
25
26
27
// constructor
public PersonQueries()
{
try
{
connection =
28
29
Note that we do not load the
Java DB driver first. JDBC
4’s automatic
DriverManager.getConnection(
URL,driver
USERNAME, PASSWORD
discovery is used here.
Declare
PreparedStatements
);
1992-2007 Pearson Education, Inc. All rights reserved.
// create query that selects all entries in the AddressBook
selectAllPeople =
30
31
Outline
connection.prepareStatement( "SELECT * FROM Addresses" );
32
33
34
// create query that selects entries with a specific last name
selectPeopleByLastName = connection.prepareStatement(
"SELECT * FROM Addresses WHERE LastName = ?" );
35
36
37
38
39
40
// create insert that adds a new entry into the database
insertNewPerson = connection.prepareStatement(
"INSERT INTO Addresses " +
41
42
43
"( FirstName, LastName, Email, PhoneNumber ) " +
"VALUES ( ?, ?, ?, ? )" );
} // end try
44
45
catch ( SQLException sqlException )
{
46
47
48
49
50
sqlException.printStackTrace();
System.exit( 1 );
} // end catch
} // end PersonQueries constructor
51
// select all of the addresses in the database
52
public List< Person > getAllPeople()
53
54
{
55
158
PersonQueries
.java
(2 of 7 )
Configure each
PreparedStatement.
Each ? represents a
parameter.
List< Person > results = null;
ResultSet resultSet = null;
56
1992-2007 Pearson Education, Inc. All rights reserved.
57
58
59
try
{
// executeQuery returns ResultSet containing matching entries
60
61
resultSet = selectAllPeople.executeQuery();
results = new ArrayList< Person >();
62
63
64
65
66
while ( resultSet.next() )
{
results.add( new Person(
resultSet.getInt( "addressID" ),
67
resultSet.getString( "firstName" ),
68
69
70
71
resultSet.getString( "lastName" ),
resultSet.getString( "email" ),
resultSet.getString( "phoneNumber" ) ) );
} // end while
72
73
} // end try
catch ( SQLException sqlException )
74
{
75
76
77
sqlException.printStackTrace();
} // end catch
finally
78
79
80
{
81
82
159
Outline
Executes the query in
PreparedStatement
selectAllPeople.PersonQueries
.java
Process the ResultSet.
(3 of 7 )
try
{
resultSet.close();
} // end try
1992-2007 Pearson Education, Inc. All rights reserved.
83
84
85
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
86
87
close();
} // end catch
88
89
90
91
92
160
Outline
} // end finally
PersonQueries
.java
return results;
} // end method getAllPeople
(4 of 7 )
93
// select person by last name
94
95
96
97
public List< Person > getPeopleByLastName( String name )
{
List< Person > results = null;
98
99
ResultSet resultSet = null;
100
try
101
102
103
{
104
105
106
107
Specify the parameter to
PreparedStatement
// executeQuery returns ResultSet containing matching selectPeopleByLastName.
entries
Executes the query in
resultSet = selectPeopleByLastName.executeQuery();
PreparedStatement
selectPeopleByLastName.
results = new ArrayList< Person >();
selectPeopleByLastName.setString( 1, name ); // specify last name
108
1992-2007 Pearson Education, Inc. All rights reserved.
while ( resultSet.next() )
{
109
110
results.add( new Person(
111
112
113
114
115
116
117
118
resultSet.getInt( "addressID" ),
resultSet.getString( "firstName" ),
resultSet.getString( "lastName" ),
resultSet.getString( "email" ),
resultSet.getString( "phoneNumber" ) ) );
} // end while
} // end try
119
120
catch ( SQLException sqlException )
{
121
122
123
sqlException.printStackTrace();
} // end catch
finally
124
125
126
{
127
128
129
130
131
132
133
134
135
136
137
138
Process the ResultSet.
161
Outline
PersonQueries
.java
(5 of 7 )
try
{
resultSet.close();
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
close();
} // end catch
} // end finally
return results;
} // end method getPeopleByName
1992-2007 Pearson Education, Inc. All rights reserved.
139
// add an entry
140
public int addPerson(
String fname, String lname, String email, String num )
141
142
143
144
145
146
147
148
149
162
Outline
{
int result = 0;
PersonQueries
.java
// set parameters, then execute insertNewPerson
try
{
insertNewPerson.setString( 1, fname );
insertNewPerson.setString( 2, lname );
150
151
152
153
insertNewPerson.setString( 3, email );
insertNewPerson.setString( 4, num );
154
155
156
result = insertNewPerson.executeUpdate();
} // end try
catch ( SQLException sqlException )
157
158
159
{
160
} // end catch
(6 of 7 )
Specify the parameters to
PreparedStatement
insertNewPerson.
// insert the new entry; returns # of rows updated
Executes the insert operation in
PreparedStatement
insertNewPerson.
sqlException.printStackTrace();
close();
1992-2007 Pearson Education, Inc. All rights reserved.
161
163
return result;
162
163
164
165
166
} // end method addPerson
167
168
169
{
170
171
172
173
174
// close the database connection
public void close()
try
{
connection.close();
Outline
PersonQueries
.java
(7 of 7 )
} // end try
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
175
} // end catch
176
} // end method close
177 } // end interface PersonQueries
1992-2007 Pearson Education, Inc. All rights reserved.
1
// Fig. 25.32: AddressBookDisplay.java
2
// A simple address book
3
import java.awt.event.ActionEvent;
4
5
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
6
import java.awt.event.WindowEvent;
7
8
9
10
11
12
import
import
import
import
import
import
java.awt.FlowLayout;
java.awt.GridLayout;
java.util.List;
javax.swing.JButton;
javax.swing.Box;
javax.swing.JFrame;
164
Outline
AddressBook
Display.java
(1 of 14 )
13 import javax.swing.JLabel;
14 import javax.swing.JPanel;
15 import javax.swing.JTextField;
16 import javax.swing.WindowConstants;
17 import javax.swing.BoxLayout;
18
19
20
21
22
import javax.swing.BorderFactory;
import javax.swing.JOptionPane;
public class AddressBookDisplay extends JFrame
{
23
24
25
private Person currentEntry;
private PersonQueries personQueries;
private List< Person > results;
26
27
private int numberOfEntries = 0;
private int currentEntryIndex;
28
29
30
private JButton browseButton;
private JLabel emailLabel;
1992-2007 Pearson Education, Inc. All rights reserved.
31
32
private JTextField emailTextField;
private JLabel firstNameLabel;
33
34
private JTextField firstNameTextField;
private JLabel idLabel;
35
private JTextField idTextField;
36
private JTextField indexTextField;
37
38
39
private JLabel lastNameLabel;
private JTextField lastNameTextField;
private JTextField maxTextField;
40
41
private JButton nextButton;
private JLabel ofLabel;
42
43
44
private JLabel phoneLabel;
private JTextField phoneTextField;
private JButton previousButton;
45
private JButton queryButton;
46
47
48
49
50
private
private
private
private
private
51
52
private JButton insertButton;
53
54
// no-argument constructor
public AddressBookDisplay()
55
56
57
58
59
{
165
Outline
AddressBook
Display.java
(2 of 14 )
JLabel queryLabel;
JPanel queryPanel;
JPanel navigatePanel;
JPanel displayPanel;
JTextField queryTextField;
super( "Address Book" );
// establish database connection and set up PreparedStatements
personQueries = new PersonQueries();
60
1992-2007 Pearson Education, Inc. All rights reserved.
61
62
// create GUI
navigatePanel = new JPanel();
63
previousButton = new JButton();
64
65
66
indexTextField = new JTextField( 2 );
ofLabel = new JLabel();
maxTextField = new JTextField( 2 );
67
68
69
nextButton = new JButton();
displayPanel = new JPanel();
idLabel = new JLabel();
70
71
idTextField = new JTextField( 10 );
firstNameLabel = new JLabel();
72
firstNameTextField = new JTextField( 10 );
73
lastNameLabel = new JLabel();
74
75
76
lastNameTextField = new JTextField( 10 );
emailLabel = new JLabel();
emailTextField = new JTextField( 10 );
77
78
79
80
81
phoneLabel = new
phoneTextField =
queryPanel = new
queryLabel = new
queryTextField =
82
queryButton = new JButton();
83
84
browseButton = new JButton();
insertButton = new JButton();
85
86
setLayout( new FlowLayout( FlowLayout.CENTER, 10, 10 ) );
87
setSize( 400, 300 );
88
89
setResizable( false );
166
Outline
AddressBook
Display.java
(3 of 14 )
JLabel();
new JTextField( 10 );
JPanel();
JLabel();
new JTextField( 10 );
1992-2007 Pearson Education, Inc. All rights reserved.
90
91
navigatePanel.setLayout(
new BoxLayout( navigatePanel, BoxLayout.X_AXIS ) );
92
93
94
95
96
97
98
99
previousButton.setText( "Previous" );
previousButton.setEnabled( false );
previousButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
100
101
previousButtonActionPerformed( evt );
} // end method actionPerformed
102
103
104
} // end anonymous inner class
); // end call to addActionListener
105
106
107
navigatePanel.add( previousButton );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
108
109
110
111
112
indexTextField.setHorizontalAlignment(
JTextField.CENTER );
indexTextField.addActionListener(
new ActionListener()
{
113
114
115
116
117
118
119
167
Outline
AddressBook
Display.java
(4 of 14 )
public void actionPerformed( ActionEvent evt )
{
indexTextFieldActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
1992-2007 Pearson Education, Inc. All rights reserved.
120
121
navigatePanel.add( indexTextField );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
122
123
124
125
ofLabel.setText( "of" );
navigatePanel.add( ofLabel );
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
126
127
128
maxTextField.setHorizontalAlignment(
JTextField.CENTER );
129
130
maxTextField.setEditable( false );
navigatePanel.add( maxTextField );
131
navigatePanel.add( Box.createHorizontalStrut( 10 ) );
168
Outline
AddressBook
Display.java
(5 of 14 )
132
133
134
135
136
137
138
139
140
141
nextButton.setText( "Next" );
nextButton.setEnabled( false );
nextButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
nextButtonActionPerformed( evt );
} // end method actionPerformed
142
143
} // end anonymous inner class
); // end call to addActionListener
144
145
navigatePanel.add( nextButton );
146
add( navigatePanel );
147
148
displayPanel.setLayout( new GridLayout( 5, 2, 4, 4 ) );
1992-2007 Pearson Education, Inc. All rights reserved.
149
150
169
idLabel.setText( "Address ID:" );
151
152
displayPanel.add( idLabel );
153
154
idTextField.setEditable( false );
displayPanel.add( idTextField );
155
156
firstNameLabel.setText( "First Name:" );
157
158
159
displayPanel.add( firstNameLabel );
displayPanel.add( firstNameTextField );
160
161
lastNameLabel.setText( "Last Name:" );
displayPanel.add( lastNameLabel );
162
163
164
165
displayPanel.add( lastNameTextField );
166
displayPanel.add( emailTextField );
167
168
phoneLabel.setText( "Phone Number:" );
169
170
171
displayPanel.add( phoneLabel );
displayPanel.add( phoneTextField );
add( displayPanel );
172
173
174
queryPanel.setLayout(
new BoxLayout( queryPanel, BoxLayout.X_AXIS) );
Outline
AddressBook
Display.java
(6 of 14 )
emailLabel.setText( "Email:" );
displayPanel.add( emailLabel );
175
1992-2007 Pearson Education, Inc. All rights reserved.
176
177
queryPanel.setBorder( BorderFactory.createTitledBorder(
"Find an entry by last name" ) );
Outline
178
queryLabel.setText( "Last Name:" );
179
180
181
queryPanel.add( Box.createHorizontalStrut( 5 ) );
queryPanel.add( queryLabel );
queryPanel.add( Box.createHorizontalStrut( 10 ) );
182
183
184
185
queryPanel.add( queryTextField );
queryPanel.add( Box.createHorizontalStrut( 10 ) );
186
187
188
189
queryButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
190
191
192
193
194
195
196
197
198
170
AddressBook
Display.java
(7 of 14 )
queryButton.setText( "Find" );
{
queryButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
queryPanel.add( queryButton );
queryPanel.add( Box.createHorizontalStrut( 5 ) );
add( queryPanel );
199
1992-2007 Pearson Education, Inc. All rights reserved.
200
browseButton.setText( "Browse All Entries" );
201
browseButton.addActionListener(
202
new ActionListener()
203
204
205
{
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
171
Outline
public void actionPerformed( ActionEvent evt )
{
browseButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
AddressBook
Display.java
(8 of 14 )
add( browseButton );
insertButton.setText( "Insert New Entry" );
insertButton.addActionListener(
new ActionListener()
{
public void actionPerformed( ActionEvent evt )
{
insertButtonActionPerformed( evt );
} // end method actionPerformed
} // end anonymous inner class
); // end call to addActionListener
223
1992-2007 Pearson Education, Inc. All rights reserved.
224
225
226
227
228
229
230
231
add( insertButton );
Outline
addWindowListener(
new WindowAdapter()
{
public void windowClosing( WindowEvent evt )
{
personQueries.close(); // close database connection
232
233
234
235
System.exit( 0 );
} // end method windowClosing
} // end anonymous inner class
); // end call to addWindowListener
236
237
setVisible( true );
238
239
240
241
242
243
244
245
246
172
AddressBook
Display.java
(9 of 14 )
} // end no-argument constructor
// handles call when previousButton is clicked
private void previousButtonActionPerformed( ActionEvent evt )
{
currentEntryIndex--;
if ( currentEntryIndex < 0 )
currentEntryIndex = numberOfEntries - 1;
247
248
249
250
251
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
indexTextFieldActionPerformed( evt );
} // end method previousButtonActionPerformed
1992-2007 Pearson Education, Inc. All rights reserved.
252
// handles call when nextButton is clicked
253
254
private void nextButtonActionPerformed( ActionEvent evt )
{
Outline
currentEntryIndex++;
255
256
257
258
259
260
if ( currentEntryIndex >= numberOfEntries )
currentEntryIndex = 0;
AddressBook
Display.java
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
261
262
263
264
265
indexTextFieldActionPerformed( evt );
} // end method nextButtonActionPerformed
266
{
267
268
269
270
271
272
173
(10 of 14 )
// handles call when queryButton is clicked
private void queryButtonActionPerformed( ActionEvent evt )
Executes the query in
results =
personQueries.getPeopleByLastName( queryTextField.getText()PreparedStatement
);
selectPeopleByLastName
numberOfEntries = results.size();
from class PersonQueries.
if ( numberOfEntries != 0 )
{
273
274
275
276
277
278
currentEntryIndex = 0;
currentEntry = results.get( currentEntryIndex );
idTextField.setText( "" + currentEntry.getAddressID() );
firstNameTextField.setText( currentEntry.getFirstName() );
lastNameTextField.setText( currentEntry.getLastName() );
emailTextField.setText( currentEntry.getEmail() );
279
280
phoneTextField.setText( currentEntry.getPhoneNumber() );
maxTextField.setText( "" + numberOfEntries );
1992-2007 Pearson Education, Inc. All rights reserved.
281
282
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
nextButton.setEnabled( true );
283
284
previousButton.setEnabled( true );
} // end if
285
286
else
browseButtonActionPerformed( evt );
Outline
AddressBook
Display.java
287
288
} // end method queryButtonActionPerformed
289
290
291
// handles call when a new value is entered in indextTextField
private void indexTextFieldActionPerformed( ActionEvent evt )
{
292
293
currentEntryIndex =
( Integer.parseInt( indexTextField.getText() ) - 1 );
294
295
296
297
if ( numberOfEntries != 0 && currentEntryIndex < numberOfEntries )
{
currentEntry = results.get( currentEntryIndex );
298
idTextField.setText("" + currentEntry.getAddressID() );
299
300
firstNameTextField.setText( currentEntry.getFirstName() );
lastNameTextField.setText( currentEntry.getLastName() );
301
302
303
emailTextField.setText( currentEntry.getEmail() );
phoneTextField.setText( currentEntry.getPhoneNumber() );
maxTextField.setText( "" + numberOfEntries );
304
305
306
174
(11 of 14 )
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
} // end if
} // end method indexTextFieldActionPerformed
307
1992-2007 Pearson Education, Inc. All rights reserved.
308
309
// handles call when browseButton is clicked
private void browseButtonActionPerformed( ActionEvent evt )
310
311
{
312
313
314
315
175
Outline
try
{
results = personQueries.getAllPeople();
numberOfEntries = results.size();
if ( numberOfEntries != 0 )
{
316
317
Executes the query in
PreparedStatementAddressBook
Display.java
selectAllPeople from
class
PersonQueries.
(12 of 14 )
318
319
currentEntryIndex = 0;
currentEntry = results.get( currentEntryIndex );
320
idTextField.setText( "" + currentEntry.getAddressID() );
321
322
323
324
firstNameTextField.setText( currentEntry.getFirstName() );
lastNameTextField.setText( currentEntry.getLastName() );
emailTextField.setText( currentEntry.getEmail() );
phoneTextField.setText( currentEntry.getPhoneNumber() );
325
maxTextField.setText( "" + numberOfEntries );
326
327
328
329
330
331
indexTextField.setText( "" + ( currentEntryIndex + 1 ) );
nextButton.setEnabled( true );
previousButton.setEnabled( true );
} // end if
} // end try
catch ( Exception e )
332
333
334
335
{
e.printStackTrace();
} // end catch
} // end method browseButtonActionPerformed
336
1992-2007 Pearson Education, Inc. All rights reserved.
337
// handles call when insertButton is clicked
338
339
private void insertButtonActionPerformed( ActionEvent evt )
{
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
176
Outline
int result = personQueries.addPerson( firstNameTextField.getText(),
lastNameTextField.getText(), emailTextField.getText(),
Executes
phoneTextField.getText() );
if ( result == 1 )
JOptionPane.showMessageDialog( this, "Person added!",
"Person added", JOptionPane.PLAIN_MESSAGE );
the insert operation in
PreparedStatement
AddressBook
insertNewPerson
from class
Display.java
PersonQueries.
(13 of 14 )
else
JOptionPane.showMessageDialog( this, "Person not added!",
"Error", JOptionPane.PLAIN_MESSAGE );
browseButtonActionPerformed( evt );
} // end method insertButtonActionPerformed
// main method
public static void main( String args[] )
{
357
new AddressBookDisplay();
358
} // end method main
359 } // end class AddressBookDisplay
1992-2007 Pearson Education, Inc. All rights reserved.
177
Outline
AddressBook
Display.java
(14 of 14 )
1992-2007 Pearson Education, Inc. All rights reserved.
178
25.12 Stored Procedures
• 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.
179
Portability Tip 25.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.
1992-2007 Pearson Education, Inc. All rights reserved.
180
Portability Tip 25.7
According to the Java API documentation
for interface CallableStatement, for
maximum portability between database
systems, programs should process the update
counts or ResultSets returned from a
CallableStatement before obtaining the
values of any output parameters.
1992-2007 Pearson Education, Inc. All rights reserved.
181
25.13 Transaction Processing
• Many applications require guarantees that a
series of database insertions, updates and
deletions executes properly before the
applications continue processing the next
database operation
• Enables a program that interacts with a database
to treat a database operation (or set of
operations) as a single operation
– Known as an atomic operation or a transaction
– At the end of a transaction, decide to commit or roll back
2005 Pearson Education, Inc. All rights reserved.
182
25.13 Transaction Processing
• Committing a 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 a transaction leaves the database in
its state prior to the database operation
2005 Pearson Education, Inc. All rights reserved.
183
25.13 Transaction Processing
• Methods of interface Connection
– 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 or rollback
– getAutoCommit determines the autocommit state for the
Connection.
2005 Pearson Education, Inc. All rights reserved.