Chapter 8: Java Database Connectivity (JDBC)
Outline
8.1 Introduction
8.2 Relational-Database Model
8.3 Relational Database Overview: The books Database 8.4 Structured Query Language (SQL)
8.4.1 Basic SELECT Query 8.4.2 WHERE Clause
8.4.3 ORDER BY Clause
8.4.4 Merging Data from Multiple Tables: Joining 8.4.5 INSERT INTO Statement
8.4.6 UPDATE Statement
8.4.7 DELETE FROM Statement
8.5 Creating Database books in Cloudscape 8.6 Manipulating Databases with JDBC
8.6.1 Connecting to and Querying a JDBC Data Source
8.6.2 Querying the books Database
Chapter 8: Java Database Connectivity (JDBC)
8.7 Case Study: Address-Book Application 8.7.1 PreparedStatements
8.7.2 Transaction Processing 8.7.3 Address-Book Application 8.8 Stored Procedures
8.9 Batch Processing
8.10 Processing Multiple ResultSets or Update Counts 8.11 Updatable ResultSets
8.12 JDBC 2.0 Optional Package javax.sql 8.12.1 DataSource
8.12.2 Connection Pooling 8.12.3 RowSets
8.13 Internet and World Wide Web Resources
8.1 Introduction
• Database
– Collection of data
• DBMS
– Database management system – Storing and organizing data
• SQL
– Relational database
– Structured Query Language
• JDBC
– Java Database Connectivity
– JDBC driver
8.2 Relational-Database Model
• Relational database
– Table – Record
– Field, column – Primary key
• Unique data
• SQL statement
– Query
– Record sets
8.2 Relational-Database Model (Cont.)
Number Name Department Salary Location
23603 J ones 413 1100 New J ersey
24568 Kerwin 413 2000 New J ersey
34589 Larson 642 1800 Los Ang ele s
35761 M yers 611 1400 Orla ndo
47132 Neumann 413 9000 New J ersey
78321 Stephe ns 611 8500 Orla ndo
Row/Rec ord
Column/ Field Prima ry key
Fig. 8.1 Relational-database structure of an Employee table.
8.2 Relational-Database Model (Cont.)
Department Location 413
61 642 1
New Jersey Orlando Los Angeles
Fig. 8.2 Result set formed by selecting Department and
Location data from the Employee table.
8.3 Relational Database Overview:
The books Database
• Sample books database
– Four tables
• Authors, publishers, authorISBN and titles
– Relationships among the tables
8.3 Relational Database Overview:
The books Database (Cont.)
Field Description
authorID Author’s ID number in the database. In the books database, this integer field is defined as an autoincremented field. For each new record inserted in this table, the database automatically increments the authorID value to ensure that each record has a unique authorID. This field represents the table’s primary key.
firstName Author’s first name (a string).
lastName Author’s last name (a string).
Fig. 8.3 authors table from books.
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry
Fig. 8.4 Data from the authors table of books.
8.3 Relational Database Overview:
The books Database (Cont.)
Field Description
publisherID The publisher’s ID number in the database. This autoincremented integer is the table’s primary-key field.
publisherName The name of the publisher (a string).
Fig. 8.5 publishers table from books.
publisherID publisherName
1 Prentice Hall
2 Prentice Hall PTG
Fig. 8.6 Data from the publishers table of books.
8.3 Relational Database Overview:
The books Database (Cont.)
Field Description
authorID The author’s ID number, which allows the database to associate each book with a specific author. The integer ID number in this field must also appear in the authors table.
isbn The ISBN number for a book (a string).
Fig. 8.7 authorISBN table from books.
8.3 Relational Database Overview:
The books Database (Cont.)
authorID isbn authorID isbn
1 0130895725 2 0139163050
1 0132261197 2 013028419x
1 0130895717 2 0130161438
1 0135289106 2 0130856118
1 0139163050 2 0130125075
1 013028419x 2 0138993947
1 0130161438 2 0130852473
1 0130856118 2 0130829277
1 0130125075 2 0134569555
1 0138993947 2 0130829293
1 0130852473 2 0130284173
1 0130829277 2 0130284181
1 0134569555 2 0130895601
1 0130829293 3 013028419x
1 0130284173 3 0130161438
1 0130284181 3 0130856118
1 0130895601 3 0134569555
2 0130895725 3 0130829293
2 0132261197 3 0130284173
2 0130895717 3 0130284181
2 0135289106 4 0130895601
Fig. 8.8 Data from the authorISBN table of books.
8.3 Relational Database Overview:
The books Database (Cont.)
Field Description
isbn ISBN number of the book (a string).
title Title of the book (a string).
editionNumber Edition number of the book (an integer).
copyright Copyright year of the book (a string).
publisherID Publisher’s ID number (an integer). This value must correspond to an ID number in the publishers table.
imageFile Name of the file containing the book’s cover image (a string).
price Suggested retail price of the book (a real number). [Note: The prices shown in this book are for example purposes only.]
Fig. 8.9 titles table from books.
8.3 Relational Database Overview:
The books Database (Cont.)
isbn title edition- Number
copy- right
publish-
erID image-File price 0130895725 C How to
Program 3 2001 1 chtp3.jpg 69.95
0132261197 C How to
Program 2 1994 1 chtp2.jpg 49.95
0130895717 C++ How to
Program 3 2001 1 cpphtp3.jpg 69.95
0135289106 C++ How to Program
2 1998 1 cpphtp2.jpg 49.95
0139163050 The Complete C++ Training Course
3 2001 2 cppctc3.jpg 109.95
013028419x e-Business and e- Commerce How to Program
1 2001 1 ebechtp1.jpg 69.95
0130161438 Internet and
World Wide Web How to Program
1 2000 1 iw3htp1.jpg 69.95
0130856118 The Complete Internet and
World Wide Web Programming Training Course
1 2000 2 iw3ctc1.jpg 109.95
0130125075 Java How to Program (Java 2)
3 2000 1 jhtp3.jpg 69.95
0138993947 Java How to Program (Java 1.1)
2 1998 1 jhtp2.jpg 49.95
0130852473 The Complete Java 2 Training Course
3 2000 2 javactc3.jpg 109.95
0130829277 The Complete Java Training Course (Java 1.1)
2 1998 2 javactc2.jpg 99.95
0134569555 Visual Basic 6
How to Program 1 1999 1 vbhtp1.jpg 69.95 0130829293 The Complete
Visual Basic 6 Training Course
1 1999 2 vbctc1.jpg 109.95
0130284173 XML How to
Program 1 2001 1 xmlhtp1.jpg 69.95
8.3 Relational Database Overview:
The books Database (Cont.)
authorISBN authorID
isbn authors
authorID firstName
lastName
publishers publisherID publisherName
titles isbn title editionNumber
copyright publisherID
imageFile price
1 1
1
Fig. 8.11 Table relationships in books.
8.4 Structured Query Language (SQL)
• SQL overview
• SQL keywords
SQL keyword Description
SELECT Select (retrieve) fields from one or more tables.
FROM Tables from which to get fields. Required in every SELECT.
WHERE Criteria for selection that determine the rows to be retrieved.
GROUP BY Criteria for grouping records.
ORDER BY Criteria for ordering records.
INSERT INTO Insert data into a specified table.
UPDATE Update data in a specified table.
DELETE FROM Delete data from a specified table.
Fig. 8.12 SQL query keywords.
8.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
authorID lastName
1 Deitel
2 Deitel
3 Nieto
4 Santry
Fig. 8.13 authorID and lastName from the authors table.
8.4.2 WHERE Clause
• specify the selection criteria
– SELECT fieldName1, fieldName2, … FROM tableName WHERE criteria
• SELECT title, editionNumber, copyright
FROM titles
WHERE copyright > 1999
• WHERE clause condition operators
– <, >, <=, >=, =, <>
– LIKE
• wildcard characters % and _
8.4.2 WHERE Clause (Cont.)
title editionNumber copyright
C How to Program 3 2001
C++ How to Program 3 2001
The Complete C++ Training Course 3 2001
e-Business and e-Commerce How to Program
1 2001
Internet and World Wide Web How to Program
1 2000
The Complete Internet and World Wide Web Programming Training Course
1 2000
Java How to Program (Java 2) 3 2000
The Complete Java 2 Training Course 3 2000
XML How to Program 1 2001
Perl How to Program 1 2001
Advanced Java 2 Platform How to Program 1 2002
Fig. 8.14 Titles with copyrights after 1999 from table titles.
8.4.2 WHERE Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘D%’
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
Fig. 8.15 Authors whose last name starts with D from the authors table.
8.4.2 WHERE Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
WHERE lastName LIKE ‘_i%’
authorID firstName lastName
3 Tem Nieto
Fig. 8.16 The only author from the authors table whose last name
contains i as the second letter.
8.4.3 ORDER BY Clause
• Optional ORDER BY clause
– SELECT fieldName1, fieldName2, … FROM tableName ORDER BY field ASC
– SELECT fieldName1, fieldName2, … FROM tableName ORDER BY field DESC
• ORDER BY multiple fields
– ORDER BY field1 sortingOrder, field2 sortingOrder, …
• Combine the WHERE and ORDER BY clauses
8.4.3 ORDER BY Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName ASC
authorID firstName lastName
2 Paul Deitel
1 Harvey Deitel
3 Tem Nieto
4 Sean Santry
Fig. 8.17 Authors from table authors in ascending order by lastName.
8.4.3 ORDER BY Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName DESC
authorID firstName lastName
4 Sean Santry
3 Tem Nieto
2 Paul Deitel
1 Harvey Deitel
Fig. 8.18 Authors from table authors in descending order by lastName.
8.4.3 ORDER BY Clause (Cont.)
• SELECT authorID, firstName, lastName
FROM authors
ORDER BY lastName, firstName
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry
Fig. 8.19 Authors from table authors in ascending order by lastName
and by firstName.
8.4.3 ORDER BY Clause (Cont.)
• SELECT isbn, title, editionNumber, copyright, price
FROM titles WHERE title LIKE ‘%How to Program’
ORDER BY title ASC
isbn title edition-
Number copy-
right price 0130895601 Advanced Java 2 Platform How to Program 1 2002 69.95
0132261197 C How to Program 2 1994 49.95
0130895725 C How to Program 3 2001 69.95
0135289106 C++ How to Program 2 1998 49.95
0130895717 C++ How to Program 3 2001 69.95
0130161438 Internet and World Wide Web How to Program
1 2000 69.95
0130284181 Perl How to Program 1 2001 69.95
0134569555 Visual Basic 6 How to Program 1 1999 69.95
0130284173 XML How to Program 1 2001 69.95
013028419x e-Business and e-Commerce How to Program
1 2001 69.95
Fig. 8.20 Books from table titles whose title ends with How to Program
in ascending order by title.
8.4.4 Merging Data from Multiple Tables: Joining
• Join the tables
– Merge data from multiple tables into a single view – SELECT fieldName1, fieldName2, …
FROM table1, table2
WHERE table1.fieldName = table2.fieldName – SELECT firstName, lastName, isbn
FROM authors, authorISBN
WHERE authors.authorID = authorISBN.authorID
ORDER BY lastName, firstName
8.4.4 Merging Data from Multiple Tables: Joining (Cont.)
firstName lastName isbn firstName lastName isbn
Harvey Deitel 0130895601 Harvey Deitel 0130284173 Harvey Deitel 0130284181 Harvey Deitel 0130829293 Harvey Deitel 0134569555 Paul Deitel 0130852473 Harvey Deitel 0130829277 Paul Deitel 0138993947 Harvey Deitel 0130852473 Paul Deitel 0130125075 Harvey Deitel 0138993947 Paul Deitel 0130856118 Harvey Deitel 0130125075 Paul Deitel 0130161438 Harvey Deitel 0130856118 Paul Deitel 013028419x Harvey Deitel 0130161438 Paul Deitel 0139163050 Harvey Deitel 013028419x Paul Deitel 0135289106 Harvey Deitel 0139163050 Paul Deitel 0130895717 Harvey Deitel 0135289106 Paul Deitel 0132261197 Harvey Deitel 0130895717 Paul Deitel 0130895725 Harvey Deitel 0132261197 Tem Nieto 0130284181 Harvey Deitel 0130895725 Tem Nieto 0130284173
Paul Deitel 0130895601 Tem Nieto 0130829293
Paul Deitel 0130284181 Tem Nieto 0134569555
Paul Deitel 0130284173 Tem Nieto 0130856118
Paul Deitel 0130829293 Tem Nieto 0130161438
Paul Deitel 0134569555 Tem Nieto 013028419x
Paul Deitel 0130829277 Sean Santry 0130895601
Fig. 8.21 Authors and the ISBN numbers for the books they have written in asc ending order by lastName and firstName.
8.4.5 INSERT INTO Statement
• Insert a new record into a table
– INSERT INTO tableName ( fieldName1, … , fieldNameN ) VALUES ( value1, … , valueN )
• INSERT INTO authors ( firstName, lastName ) VALUES ( ‘Sue’, ‘Smith’ )
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry
5 Sue Smith
Fig. 8.22 Table Authors after an INSERT INTO operation to add a record.
8.4.6 UPDATE Statement
• Modify data in a table
– UPDATE tableName
SET fieldName1 = value1, … , fieldNameN = valueN WHERE criteria
• UPDATE authors
SET lastName = ‘Jones’
WHERE lastName = ‘Smith’ AND firstName = ‘Sue’
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry
5 Sue Jones
Fig. 8.23 Table authors after an UPDATE operation to change a record.
8.4.7 DELETE FROM Statement
• Remove data from a table
– DELETE FROM tableName WHERE criteria
• DELETE FROM authors
WHERE lastName = ‘Jones’ AND firstName = ‘Sue’
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry
Fig. 8.24 Table authors after a DELETE operation to remove a record.
8.5 Creating Database books in Cloudscape
• Cloudscape – pure-Java database management system
• Start Cloudscape server
• Create database books
Fig. 8.25 Executing Cloudscape from a command prompt in Windows 2000.
8.6 Manipulating Databases with JDBC
• Connect to a database
• Query the database
• Display the results of the query
8.6.1 Connecting to and Querying a JDBC Data Source
• DisplayAuthors
– Retrieves the entire authors table
– Displays the data in a JTextArea
Outline
Fig. 8.26
Displaying the authors table from the books database.
Line 7 Line 26
Lines 29-30 Line 33
1 // Fig. 8.26: DisplayAuthors.java
2 // Displaying the contents of table authors in database books.
3 package com.deitel.advjhtp1.jdbc;
4
5 // Java core packages 6 import java.awt.*;
7 import java.sql.*;
8 import java.util.*;
9
10 // Java extension packages 11 import javax.swing.*;
12
13 public class DisplayAuthors extends JFrame { 14
15 // constructor connects to database, queries database, 16 // processes results and displays results in window 17 public DisplayAuthors()
18 {
19 super( "Authors Table of Books Database" );
20 21
22 // connect to database books and query database 23 try {
24
25 // load database driver class
26 Class.forName( "COM.cloudscape.core.RmiJdbcDriver" );
Imports package java.sql, which contains classes and interfaces for the JDBC API.
Loads the class
definition for the
Outline
Fig. 8.26
Displaying the authors table from the books database.
Lines 36-37 Line 41
Line 42
Lines 44-47 Lines 51-59 Lines 62-63
35 // query database
36 ResultSet resultSet =
37 statement.executeQuery( "SELECT * FROM authors" );
38
39 // process query results
40 StringBuffer results = new StringBuffer();
41 ResultSetMetaData metaData = resultSet.getMetaData();
42 int numberOfColumns = metaData.getColumnCount();
43
44 for ( int i = 1; i <= numberOfColumns; i++ ) { 45 results.append( metaData.getColumnName( i ) 46 + "\t" );
47 } 48
49 results.append( "\n" );
50
51 while ( resultSet.next() ) { 52
53 for ( int i = 1; i <= numberOfColumns; i++ ) { 54 results.append( resultSet.getObject( i ) 55 + "\t" );
56 } 57
58 results += "\n";
59 } 60
61 // close statement and connection 62 statement.close();
63 connection.close();
64
65 // set up GUI and display window 66 JTextArea textArea = new JTextArea(
67 results.toString() );
68 Container container = getContentPane();
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.
Append the column names to the StringBuffer results.
Append the data in each ResultSet row to the StringBuffer results.
Close the Statement and
the database Connection.
Outline
Fig. 8.26
Displaying the authors table from the books database.
Lines 66-73
70 container.add( new JScrollPane( textArea ) );
71
72 setSize( 300, 100 ); // set window size 73 setVisible( true ); // display window 74 } // end try
75
76 // detect problems interacting with the database 77 catch ( SQLException sqlException ) {
78 JOptionPane.showMessageDialog( null,
79 sqlException.getMessage(), "Database Error", 80 JOptionPane.ERROR_MESSAGE );
81
82 System.exit( 1 );
83 } 84
85 // detect problems loading database driver
86 catch ( ClassNotFoundException classNotFound ) { 87 JOptionPane.showMessageDialog( null,
88 classNotFound.getMessage(), "Driver Not Found", 89 JOptionPane.ERROR_MESSAGE );
90
91 System.exit( 1 );
92 }
93 } // end DisplayAuthors constructor definition 94
95 // launch the application
Create the GUI that displays the
StringBuffer results, set the size
of the application window and
show the application window.
Outline
Fig. 8.26
Displaying the authors table from the books database.
Program output
8.6.1 Connecting to and Querying a JDBC Data Source (Cont.)
Type Description
1 The JDBC-to-ODBC bridge driver connects Java to a Microsoft ODBC (Open Database Connectivity) data source. The Java 2 Software Development Kit from Sun Microsystems, Inc. includes the JDBC-to-ODBC bridge driver
(sun.jdbc.odbc.JdbcOdbcDriver). This driver typically requires the ODBC driver to be installed on the client computer and normally requires configuration of the ODBC data source. The bridge driver was introduced primarily to allow Java programmers to build data-driven Java applications before the database vendors had Type 3 and Type 4 drivers.
2 Native-API, partly Java drivers enable JDBC programs to use database-specific APIs (normally written in C or C++) that allow client programs to access
databases via the Java Native Interface. This driver type translates JDBC into database-specific code. Type 2 drivers were introduced for reasons similar to the Type 1 ODBC bridge driver.
3 JDBC-Net pure Java drivers take JDBC requests and translate them into a network protocol that is not database specific. These requests are sent to a
server, which translates the database requests into a database-specific protocol.
8.6.2 Querying the books Database
• Allow the user to enter any query into the program
• Display the results of a query in a JTable
Outline
Fig. 8.28
ResultSetTableMo del enables a Jtable to
display the contents of a ResultSet.
Line 34
1 // Fig. 8.28: ResultSetTableModel.java
2 // A TableModel that supplies ResultSet data to a JTable.
3 package com.deitel.advjhtp1.jdbc;
4
5 // Java core packages 6 import java.sql.*;
7 import java.util.*;
8
9 // Java extension packages 10 import javax.swing.table.*;
11
12 // ResultSet rows and columns are counted from 1 and JTable 13 // rows and columns are counted from 0. When processing 14 // ResultSet rows or columns for use in a JTable, it is
15 // necessary to add 1 to the row or column number to manipulate 16 // the appropriate ResultSet column (i.e., JTable column 0 is 17 // ResultSet column 1 and JTable row 0 is ResultSet row 1).
18 public class ResultSetTableModel extends AbstractTableModel { 19 private Connection connection;
20 private Statement statement;
21 private ResultSet resultSet;
22 private ResultSetMetaData metaData;
23 private int numberOfRows;
24
25 // initialize resultSet and obtain its meta data object;
26 // determine number of rows
Outline
Fig. 8.28
ResultSetTableMo del enables a Jtable to
display the contents of a ResultSet.
Line 37 Line 42
Lines 50-51 Line 54
Line 63
36 // create Statement to query database 37 statement = connection.createStatement(
38 ResultSet.TYPE_SCROLL_INSENSITIVE, 39 ResultSet.CONCUR_READ_ONLY );
40
41 // set query and execute it 42 setQuery( query );
43 } 44
45 // get class that represents column type 46 public Class getColumnClass( int column ) 47 {
48 // determine Java class of column 49 try {
50 String className =
51 metaData.getColumnClassName( column + 1 );
52
53 // return Class object that represents className 54 return Class.forName( className );
55 } 56
57 // catch SQLExceptions and ClassNotFoundExceptions 58 catch ( Exception exception ) {
59 exception.printStackTrace();
60 } 61
62 // if problems occur above, assume type Object 63 return Object.class;
64 } 65
66 // get number of columns in ResultSet 67 public int getColumnCount()
68 {
Invokes Connection
method createStatement to create a Statement object.
Invokes ResultSetTableModel method setQuery to perform the default query.
Obtains the fully qualified class name for the specified column.
Loads the class definition for the class and returns the corresponding Class object.
Returns the
default type.
Outline
Fig. 8.28
ResultSetTableMo del enables a Jtable to
display the contents of a ResultSet.
Line 71 Line 88
69 // determine number of columns 70 try {
71 return metaData.getColumnCount();
72 } 73
74 // catch SQLExceptions and print error message 75 catch ( SQLException sqlException ) {
76 sqlException.printStackTrace();
77 } 78
79 // if problems occur above, return 0 for number of columns 80 return 0;
81 } 82
83 // get name of a particular column in ResultSet 84 public String getColumnName( int column )
85 {
86 // determine column name 87 try {
88 return metaData.getColumnName( column + 1 );
89 } 90
91 // catch SQLExceptions and print error message 92 catch ( SQLException sqlException ) {
93 sqlException.printStackTrace();
94 }
Obtains the number of columns in the ResultSet.
Obtains the column name
from the ResultSet.
Outline
Fig. 8.28
ResultSetTableMo del enables a Jtable to
display the contents of a ResultSet.
Line 111 Line 113
Lines 130-131
104 } 105
106 // obtain value in particular row and column 107 public Object getValueAt( int row, int column ) 108 {
109 // obtain a value at specified ResultSet row and column 110 try {
111 resultSet.absolute( row + 1 );
112
113 return resultSet.getObject( column + 1 );
114 } 115
116 // catch SQLExceptions and print error message 117 catch ( SQLException sqlException ) {
118 sqlException.printStackTrace();
119 } 120
121 // if problems, return empty string object 122 return "";
123 } 124
125 // close Statement and Connection 126 protected void finalize()
127 {
128 // close Statement and Connection 129 try {
130 statement.close();
131 connection.close();
132 } 133
134 // catch SQLExceptions and print error message 135 catch ( SQLException sqlException ) {
136 sqlException.printStackTrace();
137 }
Uses ResultSet method absolute to position the ResultSet cursor at a specific row.
Uses ResultSet method getObject to obtain the Object in a specific column of the current row.
Close the Statement and Connection if a
ResultSetTableModel object is garbage collected.
Outline
Fig. 8.28
ResultSetTableMo del enables a Jtable to
display the contents of a ResultSet.
Line 144 Line 150 Line 151 Line 154
139
140 // set new database query string
141 public void setQuery( String query ) throws SQLException 142 {
143 // specify query and execute it
144 resultSet = statement.executeQuery( query );
145
146 // obtain meta data for ResultSet 147 metaData = resultSet.getMetaData();
148
149 // determine number of rows in ResultSet
150 resultSet.last(); // move to last row 151 numberOfRows = resultSet.getRow(); // get row number 152
153 // notify JTable that model has changed 154 fireTableStructureChanged();
155 }
156 } // end class ResultSetTableModel
Executes the query to obtain a new ResultSet.
Uses ResultSet method getRow to obtain the row number for the current row in the ResultSet.
Uses ResultSet method last to position the ResultSet cursor at the last row in the ResultSet.
Invokes method
fireTableAStructureChanged to notify any
JTable using this ResultSetTableModel
object as its model that the structure of the
model has changed.
8.6.2 Querying the books Database (Cont.)
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 record to the last record 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. 8.29 ResultSet c onstants for spec ifying ResultSet type.
8.6.2 Querying the books Database (Cont.)
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. 8.30 ResultSet c onstants for spec ifying result set properties.
Outline
Fig. 8.31
DisplayQueryResu lts for querying database books.
Lines 26, 29, 32
1 // Fig. 8.31: DisplayQueryResults.java
2 // Display the contents of the Authors table in the 3 // Books database.
4 package com.deitel.advjhtp1.jdbc;
5
6 // Java core packages 7 import java.awt.*;
8 import java.awt.event.*;
9 import java.sql.*;
10 import java.util.*;
11
12 // Java extension packages 13 import javax.swing.*;
14 import javax.swing.table.*;
15
16 public class DisplayQueryResults extends JFrame { 17 private ResultSetTableModel tableModel;
18 private JTextArea queryArea;
19
20 // create ResultSetTableModel and GUI 21 public DisplayQueryResults()
22 {
23 super( "Displaying Query Results" );
24
25 // Cloudscape database driver class name
26 String driver = "COM.cloudscape.core.RmiJdbcDriver";
27
28 // URL to connect to books database
29 String url = "jdbc:cloudscape:rmi:books";
30
31 // query to select entire authors table 32 String query = "SELECT * FROM authors";
33
Define the database
driver class name,
database URL and
default query.
Outline
Fig. 8.31
DisplayQueryResu lts for querying database books.
Line 61
34 // create ResultSetTableModel and display database table 35 try {
36
37 // create TableModel for results of query 38 // SELECT * FROM authors
39 tableModel =
40 new ResultSetTableModel( driver, url, query );
41
42 // set up JTextArea in which user types queries 43 queryArea = new JTextArea( query, 3, 100 );
44 queryArea.setWrapStyleWord( true );
45 queryArea.setLineWrap( true );
46
47 JScrollPane scrollPane = new JScrollPane( queryArea, 48 ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, 49 ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER );
50
51 // set up JButton for submitting queries
52 JButton submitButton = new JButton( "Submit Query" );
53
54 // create Box to manage placement of queryArea and 55 // submitButton in GUI
56 Box box = Box.createHorizontalBox();
57 box.add( scrollPane );
58 box.add( submitButton );
59
Outline
Fig. 8.31
DisplayQueryResu lts for querying database books.
Lines 70-94 Line 79
69 // create event listener for submitButton 70 submitButton.addActionListener(
71
72 new ActionListener() { 73
74 // pass query to table model
75 public void actionPerformed( ActionEvent e ) 76 {
77 // perform a new query 78 try {
79 tableModel.setQuery( queryArea.getText() );
80 } 81
82 // catch SQLExceptions that occur when 83 // performing a new query
84 catch ( SQLException sqlException ) { 85 JOptionPane.showMessageDialog( null, 86 sqlException.toString(),
87 "Database error",
88 JOptionPane.ERROR_MESSAGE );
89 }
90 } // end actionPerformed 91
92 } // end ActionListener inner class 93
94 ); // end call to addActionListener 95
96 // set window size and display window 97 setSize( 500, 250 );
98 setVisible( true );
99 } // end try 100
Register an event handler for the submitButton that the user clicks to submit a query to the database.
Invokes ResultSetTableModel
method setQuery to execute the
new query.
Outline
Fig. 8.31
DisplayQueryResu lts for querying database books.
101 // catch ClassNotFoundException thrown by
102 // ResultSetTableModel if database driver not found 103 catch ( ClassNotFoundException classNotFound ) { 104 JOptionPane.showMessageDialog( null,
105 "Cloudscape driver not found", "Driver not found", 106 JOptionPane.ERROR_MESSAGE );
107
108 System.exit( 1 ); // terminate application 109 }
110
111 // catch SQLException thrown by ResultSetTableModel 112 // if problems occur while setting up database
113 // connection and querying database 114 catch ( SQLException sqlException ) { 115 JOptionPane.showMessageDialog( null, 116 sqlException.toString(),
117 "Database error", JOptionPane.ERROR_MESSAGE );
118
119 System.exit( 1 ); // terminate application 120 }
121 } // end DisplayQueryResults constructor 122
123 // execute application
124 public static void main( String args[] ) 125 {
126 DisplayQueryResults app = new DisplayQueryResults();
Outline
Fig. 8.31
DisplayQueryResu lts for querying database books.
Program output
8.7 Case Study: Address-Book Application
• Address-book application
– Insert address-book entries – Locate address-book entries – Update address-book entries – Delete address-book entries
• Database addressbook
– Table names
– Table addresses
– Table phoneNumbers
8.7 Case Study: Address-Book Application (Cont.)
names personID firstName
lastName addresses
addressID personID address1 address2
city state zipcode
1
phoneNumbers phoneID personID phoneNumber
emailAddresses emailID personID emailAddress 1
1
Fig. 8.32 Table relationships in database addressbook.
8.7.1 PreparedStatements
• Interface PreparedStatements
– More flexible – More efficient
• Define a query with a PreparedStatement
– PreparedStatement authorBooks = connection.prepareStatement(
“SELECT lastName, firstName, title ” + “FROM authors, titles, authorISBN ” +
“WHERE authors.authorID = authorISBN.authorID ” +
8.7.1 PreparedStatements (Cont.)
• Set parameters in PreparedStatement
– authorBooks.setString( 1, “Deitel” );
– authorBooks.setString( 2, “Paul” );
• PreparedStatement with parameter values
– SELECT lastName, firstName, title FROM authors, titles, authorISBN
WHERE authors.authorID = authorISBN.authorID AND titles.ISBN = authorISBN.isbn AND
lastName = ‘Deitel’ AND firstName = ‘Paul’
8.7.2 Transaction Processing
• Transaction processing
– Also known as an atomic operation – Operation successful
• Commit the transaction
– Operation failed
• Roll back the transaction
• Java transaction processing
– Method setAutoCommit – Method commit
– Method roolback
8.7.3 Address-Book Application
• Address-Book Application
– Class AddressBookEntry
– Interface AddressBookDataAccess – Class DataAccessException
– Class CouldscapeDataAccess
– Class AddressBookEntryFrame
– Class AddressBook
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
Lines 6-18
1 // Fig. 8.33: AddressBookEntry.java
2 // JavaBean to represent one address book entry.
3 package com.deitel.advjhtp1.jdbc.addressbook;
4
5 public class AddressBookEntry { 6 private String firstName = "";
7 private String lastName = "";
8 private String address1 = "";
9 private String address2 = "";
10 private String city = "";
11 private String state = "";
12 private String zipcode = "";
13 private String phoneNumber = "";
14 private String emailAddress = "";
15 private int personID;
16 private int addressID;
17 private int phoneID;
18 private int emailID;
19
20 // empty constructor
21 public AddressBookEntry() 22 {
23 } 24
25 // set person's id
26 public AddressBookEntry( int id )
Properties for all the
fields in the four tables of
database addressbook.
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
36
37 // get person's first name 38 public String getFirstName() 39 {
40 return firstName;
41 } 42
43 // set person's last name
44 public void setLastName( String last ) 45 {
46 lastName = last;
47 } 48
49 // get person's last name 50 public String getLastName() 51 {
52 return lastName;
53 } 54
55 // set first line of person's address
56 public void setAddress1( String firstLine ) 57 {
58 address1 = firstLine;
59 } 60
61 // get first line of person's address 62 public String getAddress1()
63 {
64 return address1;
65 } 66
67 // set second line of person's address
68 public void setAddress2( String secondLine ) 69 {
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
71 } 72
73 // get second line of person's address 74 public String getAddress2()
75 {
76 return address2;
77 } 78
79 // set city in which person lives
80 public void setCity( String personCity ) 81 {
82 city = personCity;
83 } 84
85 // get city in which person lives 86 public String getCity()
87 {
88 return city;
89 } 90
91 // set state in which person lives
92 public void setState( String personState ) 93 {
94 state = personState;
95 } 96
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
103 // set person's zip code
104 public void setZipcode( String zip ) 105 {
106 zipcode = zip;
107 } 108
109 // get person's zip code 110 public String getZipcode() 111 {
112 return zipcode;
113 } 114
115 // set person's phone number
116 public void setPhoneNumber( String number ) 117 {
118 phoneNumber = number;
119 } 120
121 // get person's phone number 122 public String getPhoneNumber() 123 {
124 return phoneNumber;
125 } 126
127 // set person's email address
128 public void setEmailAddress( String email ) 129 {
130 emailAddress = email;
131 } 132
133 // get person's email address 134 public String getEmailAddress() 135 {
136 return emailAddress;
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
138
139 // get person's ID
140 public int getPersonID() 141 {
142 return personID;
143 } 144
145 // set person's addressID
146 public void setAddressID( int id ) 147 {
148 addressID = id;
149 } 150
151 // get person's addressID 152 public int getAddressID() 153 {
154 return addressID;
155 } 156
157 // set person's phoneID
158 public void setPhoneID( int id ) 159 {
160 phoneID = id;
161 } 162
163 // get person's phoneID
Outline
Fig. 8.33
AddressBookEntry bean represents an address book entry.
173 } 174
175 // get person's emailID 176 public int getEmailID() 177 {
178 return emailID;
179 }
180 } // end class AddressBookEntry
Outline
Fig. 8.34
AddressBookDataA ccess interface describes the methods for accessing the addressbook database.
Lines 13-31
1 // Fig. 8.34: AddressBookDataAccess.java
2 // Interface that specifies the methods for inserting, 3 // updating, deleting and finding records.
4 package com.deitel.advjhtp1.jdbc.addressbook;
5
6 // Java core packages 7 import java.sql.*;
8
9 public interface AddressBookDataAccess { 10
11 // Locate specified person by last name. Return 12 // AddressBookEntry containing information.
13 public AddressBookEntry findPerson( String lastName );
14
15 // Update information for specified person.
16 // Return boolean indicating success or failure.
17 public boolean savePerson(
18 AddressBookEntry person ) throws DataAccessException;
19
20 // Insert a new person. Return boolean indicating 21 // success or failure.
22 public boolean newPerson( AddressBookEntry person ) 23 throws DataAccessException;
24
25 // Delete specified person. Return boolean indicating if 26 // success or failure.
Describe methods required by the address-book
application to perform insertions, updates,
deletions and searches with
the addressbook database.
Outline
Fig. 8.35
DataAccessExcept ion is thrown when there is a problem
accessing the data source.
1 // Fig. 8.35 DataAccessException.java
2 // Class AddressBookDataAccess throws DataAccessExceptions 3 // when there is a problem accessing the data source.
4 package com.deitel.advjhtp1.jdbc.addressbook;
5
6 public class DataAccessException extends Exception { 7
8 private Exception exception;
9
10 // constructor with String argument
11 public DataAccessException( String message ) 12 {
13 super( message );
14 } 15
16 // constructor with Exception argument
17 public DataAccessException( Exception exception ) 18 {
19 exception = this.exception;
20 } 21
22 // printStackTrace of exception from constructor 23 public void printStackTrace()
24 {
25 exception.printStackTrace();
26 } 27 }
Outline
Fig. 8.36
CloudscapeDataAc cess implements interface
AddressBookDataA ccess to perform the connection to the database and the database interactions.
1 // Fig. 8.36: CloudscapeDataAccess.java
2 // An implementation of interface AddressBookDataAccess that 3 // performs database operations with PreparedStatements.
4 package com.deitel.advjhtp1.jdbc.addressbook;
5
6 // Java core packages 7 import java.sql.*;
8
9 public class CloudscapeDataAccess 10 implements AddressBookDataAccess { 11
12 // reference to database connection 13 private Connection connection;
14
15 // reference to prepared statement for locating entry 16 private PreparedStatement sqlFind;
17
18 // reference to prepared statement for determining personID 19 private PreparedStatement sqlPersonID;
20
21 // references to prepared statements for inserting entry 22 private PreparedStatement sqlInsertName;
23 private PreparedStatement sqlInsertAddress;
24 private PreparedStatement sqlInsertPhone;
25 private PreparedStatement sqlInsertEmail;
26
Outline
Fig. 8.36
CloudscapeDataAc cess implements interface
AddressBookDataA ccess to perform the connection to the database and the database interactions.
Line 43
Lines 46-126
33 // references to prepared statements for updating entry 34 private PreparedStatement sqlDeleteName;
35 private PreparedStatement sqlDeleteAddress;
36 private PreparedStatement sqlDeletePhone;
37 private PreparedStatement sqlDeleteEmail;
38
39 // set up PreparedStatements to access database 40 public CloudscapeDataAccess() throws Exception 41 {
42 // connect to addressbook database 43 connect();
44
45 // locate person
46 sqlFind = connection.prepareStatement(
47 "SELECT names.personID, firstName, lastName, " + 48 "addressID, address1, address2, city, state, " + 49 "zipcode, phoneID, phoneNumber, emailID, " + 50 "emailAddress " +
51 "FROM names, addresses, phoneNumbers, emailAddresses " + 52 "WHERE lastName = ? AND " +
53 "names.personID = addresses.personID AND " + 54 "names.personID = phoneNumbers.personID AND " + 55 "names.personID = emailAddresses.personID" );
56
57 // Obtain personID for last person inserted in database.
58 // [This is a Cloudscape-specific database operation.]
59 sqlPersonID = connection.prepareStatement(
60 "VALUES ConnectionInfo.lastAutoincrementValue( " + 61 "'APP', 'NAMES', 'PERSONID')" );
62
63 // Insert first and last names in table names.
64 // For referential integrity, this must be performed 65 // before sqlInsertAddress, sqlInsertPhone and
Invokes utility method connect to perform the connection to the database.
Invoke Connection method
prepareStatement to create each of the SQL statements that
manipulate database
addressbook.
Outline
Fig. 8.36
CloudscapeDataAc cess implements interface
AddressBookDataA ccess to perform the connection to the database and the database interactions.
Lines 46-126
66 // sqlInsertEmail.
67 sqlInsertName = connection.prepareStatement(
68 "INSERT INTO names ( firstName, lastName ) " + 69 "VALUES ( ? , ? )" );
70
71 // insert address in table addresses
72 sqlInsertAddress = connection.prepareStatement(
73 "INSERT INTO addresses ( personID, address1, " + 74 "address2, city, state, zipcode ) " +
75 "VALUES ( ? , ? , ? , ? , ? , ? )" );
76
77 // insert phone number in table phoneNumbers 78 sqlInsertPhone = connection.prepareStatement(
79 "INSERT INTO phoneNumbers " + 80 "( personID, phoneNumber) " + 81 "VALUES ( ? , ? )" );
82
83 // insert email in table emailAddresses
84 sqlInsertEmail = connection.prepareStatement(
85 "INSERT INTO emailAddresses " + 86 "( personID, emailAddress ) " + 87 "VALUES ( ? , ? )" );
88
89 // update first and last names in table names 90 sqlUpdateName = connection.prepareStatement(
91 "UPDATE names SET firstName = ?, lastName = ? " +