Java Advance DB 3
wilden
ch28solutions.zipch28solutions/ex28_02/DisplayQueryResults$1.class
synchronized class DisplayQueryResults$1 implements java.awt.event.ActionListener { void DisplayQueryResults$1(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_02/DisplayQueryResults$2.class
synchronized class DisplayQueryResults$2 implements java.awt.event.ActionListener { void DisplayQueryResults$2(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_02/DisplayQueryResults$3.class
synchronized class DisplayQueryResults$3 extends java.awt.event.WindowAdapter { void DisplayQueryResults$3(DisplayQueryResults); public void windowClosed(java.awt.event.WindowEvent); }
ch28solutions/ex28_02/DisplayQueryResults.class
public synchronized class DisplayQueryResults extends javax.swing.JFrame { static final String DATABASE_URL = jdbc:mysql://localhost/books; static final String USERNAME = deitel; static final String PASSWORD = deitel; static final String DEFAULT_QUERY = SELECT * FROM authors; private ResultSetTableModel tableModel; private javax.swing.JTable table; private javax.swing.JComboBox inputQuery; private javax.swing.JButton submitQuery; private javax.swing.JTextField input; public void DisplayQueryResults(); private void getTable(); public static void main(String[]); }
ch28solutions/ex28_02/DisplayQueryResults.java
ch28solutions/ex28_02/DisplayQueryResults.java
// Fig. 28.28: DisplayQueryResults.java
// Display the contents of the Authors table in the books database.
import
java
.
awt
.
*
;
import
java
.
awt
.
event
.
ActionListener
;
import
java
.
awt
.
event
.
ActionEvent
;
import
java
.
awt
.
event
.
WindowAdapter
;
import
java
.
awt
.
event
.
WindowEvent
;
import
java
.
sql
.
SQLException
;
import
java
.
util
.
regex
.
PatternSyntaxException
;
import
javax
.
swing
.
*
;
import
javax
.
swing
.
table
.
TableRowSorter
;
import
javax
.
swing
.
table
.
TableModel
;
public
class
DisplayQueryResults
extends
JFrame
{
// database URL, username and password
static
final
String
DATABASE_URL
=
"jdbc:mysql://localhost/books"
;
static
final
String
USERNAME
=
"deitel"
;
static
final
String
PASSWORD
=
"deitel"
;
// default query retrieves all data from authors table
static
final
String
DEFAULT_QUERY
=
"SELECT * FROM authors"
;
private
ResultSetTableModel
tableModel
;
private
JTable
table
;
private
JComboBox
inputQuery
;
private
JButton
submitQuery
;
private
JTextField
input
;
// create ResultSetTableModel and GUI
public
DisplayQueryResults
()
{
super
(
"Displaying Query Results"
);
// create ResultSetTableModel and display database table
try
{
// create TableModel for results of query SELECT * FROM authors
tableModel
=
new
ResultSetTableModel
(
DATABASE_URL
,
USERNAME
,
PASSWORD
,
DEFAULT_QUERY
);
}
// end try
catch
(
SQLException
sqlex
)
{
System
.
err
.
println
(
"Unable to connect"
);
sqlex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
String
names
[]
=
{
"All authors"
,
"All titles"
,
"A specific author"
,
"A specific title"
};
// if connected to database, set up GUI
inputQuery
=
new
JComboBox
(
names
);
submitQuery
=
new
JButton
(
"Submit query"
);
submitQuery
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
getTable
();
}
// end method actionPerformed
}
// end anonymous inner class
);
// end addActionListener
JPanel
topPanel
=
new
JPanel
();
input
=
new
JTextField
(
20
);
// for user input query
input
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
try
{
String
query
=
input
.
getText
();
tableModel
.
setQuery
(
query
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end method actionPerformed
}
// end anonymous inner class
);
// end addActionListener
JPanel
centerPanel
=
new
JPanel
();
centerPanel
.
setLayout
(
new
FlowLayout
()
);
centerPanel
.
add
(
new
JLabel
(
"Enter query, author or title:"
)
);
centerPanel
.
add
(
input
);
topPanel
.
setLayout
(
new
BorderLayout
()
);
topPanel
.
add
(
inputQuery
,
BorderLayout
.
NORTH
);
topPanel
.
add
(
centerPanel
,
BorderLayout
.
CENTER
);
topPanel
.
add
(
submitQuery
,
BorderLayout
.
SOUTH
);
table
=
new
JTable
(
tableModel
);
// set up GUI
setLayout
(
new
BorderLayout
()
);
add
(
topPanel
,
BorderLayout
.
NORTH
);
add
(
new
JScrollPane
(
table
),
BorderLayout
.
CENTER
);
getTable
();
setSize
(
500
,
500
);
setVisible
(
true
);
// dispose of window when user quits application (this overrides
// the default of HIDE_ON_CLOSE)
setDefaultCloseOperation
(
DISPOSE_ON_CLOSE
);
// ensure database connection is closed when user quits application
addWindowListener
(
new
WindowAdapter
()
{
// disconnect from database and exit when window has closed
public
void
windowClosed
(
WindowEvent
event
)
{
tableModel
.
disconnectFromDatabase
();
System
.
exit
(
0
);
}
// end method windowClosed
}
// end WindowAdapter inner class
);
// end call to addWindowListener
}
// end DisplayQueryResults constructor
// execute query and display result in table
private
void
getTable
()
{
try
{
int
selection
=
inputQuery
.
getSelectedIndex
();
String
query
=
null
;
// execute predefined query
switch
(
selection
)
{
case
0
:
query
=
"SELECT * FROM authors"
;
break
;
case
1
:
query
=
"SELECT * FROM titles"
;
break
;
case
2
:
query
=
"SELECT authors.lastName, authors.firstName, "
+
"titles.title, titles.isbn FROM "
+
"titles INNER JOIN (authorISBN INNER JOIN authors ON"
+
" authorISBN.authorID = authors.authorID) ON "
+
"titles.isbn = authorISBN.isbn WHERE authors.lastName"
+
" = '"
+
input
.
getText
()
+
"' ORDER BY "
+
"authors.lastName, authors.firstName ASC"
;
break
;
case
3
:
query
=
"SELECT titles.isbn, titles.title, "
+
"authors.lastName, authors.firstName FROM titles "
+
"INNER JOIN (authorISBN INNER JOIN authors ON "
+
"authorISBN.authorID = authors.authorID) ON "
+
"titles.ISBN = authorISBN.ISBN WHERE titles.title = '"
+
input
.
getText
()
+
"' ORDER BY authors.lastName, "
+
"authors.firstName ASC"
;
break
;
}
// end switch
tableModel
.
setQuery
(
query
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
// recover from invalid query by executing default query
try
{
tableModel
.
setQuery
(
DEFAULT_QUERY
);
inputQuery
.
setSelectedIndex
(
0
);
}
// end try
catch
(
SQLException
sqlException2
)
{
sqlException2
.
printStackTrace
();
tableModel
.
disconnectFromDatabase
();
// close connection
System
.
exit
(
1
);
// terminate application
}
// end inner catch
}
// end catch
}
// end method getTable
// execute application
public
static
void
main
(
String
args
[]
)
{
new
DisplayQueryResults
();
}
// end main
}
// end class DisplayQueryResults
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_02/java
ch28solutions/ex28_02/ResultSetTableModel.class
public synchronized class ResultSetTableModel extends javax.swing.table.AbstractTableModel { private java.sql.Connection connection; private java.sql.Statement statement; private java.sql.ResultSet resultSet; private java.sql.ResultSetMetaData metaData; private int numberOfRows; private boolean connectedToDatabase; public void ResultSetTableModel(String, String, String, String) throws java.sql.SQLException; public Class getColumnClass(int) throws IllegalStateException; public int getColumnCount() throws IllegalStateException; public String getColumnName(int) throws IllegalStateException; public int getRowCount() throws IllegalStateException; public Object getValueAt(int, int) throws IllegalStateException; public void setQuery(String) throws java.sql.SQLException, IllegalStateException; public void disconnectFromDatabase(); }
ch28solutions/ex28_02/ResultSetTableModel.java
ch28solutions/ex28_02/ResultSetTableModel.java
// Fig. 28.25: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
ResultSetMetaData
;
import
java
.
sql
.
SQLException
;
import
javax
.
swing
.
table
.
AbstractTableModel
;
// ResultSet rows and columns are counted from 1 and JTable
// rows and columns are counted from 0. When processing
// ResultSet rows or columns for use in a JTable, it is
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public
class
ResultSetTableModel
extends
AbstractTableModel
{
private
Connection
connection
;
private
Statement
statement
;
private
ResultSet
resultSet
;
private
ResultSetMetaData
metaData
;
private
int
numberOfRows
;
// keep track of database connection status
private
boolean
connectedToDatabase
=
false
;
// constructor initializes resultSet and obtains its meta data object;
// determines number of rows
public
ResultSetTableModel
(
String
url
,
String
username
,
String
password
,
String
query
)
throws
SQLException
{
// connect to database
connection
=
DriverManager
.
getConnection
(
url
,
username
,
password
);
// create Statement to query database
statement
=
connection
.
createStatement
(
ResultSet
.
TYPE_SCROLL_INSENSITIVE
,
ResultSet
.
CONCUR_READ_ONLY
);
// update database connection status
connectedToDatabase
=
true
;
// set query and execute it
setQuery
(
query
);
}
// end constructor ResultSetTableModel
// get class that represents column type
public
Class
getColumnClass
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine Java class of column
try
{
String
className
=
metaData
.
getColumnClassName
(
column
+
1
);
// return Class object that represents className
return
Class
.
forName
(
className
);
}
// end try
catch
(
Exception
exception
)
{
exception
.
printStackTrace
();
}
// end catch
return
Object
.
class
;
// if problems occur above, assume type Object
}
// end method getColumnClass
// get number of columns in ResultSet
public
int
getColumnCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine number of columns
try
{
return
metaData
.
getColumnCount
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
0
;
// if problems occur above, return 0 for number of columns
}
// end method getColumnCount
// get name of a particular column in ResultSet
public
String
getColumnName
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine column name
try
{
return
metaData
.
getColumnName
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string for column name
}
// end method getColumnName
// return number of rows in ResultSet
public
int
getRowCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
return
numberOfRows
;
}
// end method getRowCount
// obtain value in particular row and column
public
Object
getValueAt
(
int
row
,
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// obtain a value at specified ResultSet row and column
try
{
resultSet
.
absolute
(
row
+
1
);
return
resultSet
.
getObject
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string object
}
// end method getValueAt
// set new database query string
public
void
setQuery
(
String
query
)
throws
SQLException
,
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// specify query and execute it
resultSet
=
statement
.
executeQuery
(
query
);
// obtain meta data for ResultSet
metaData
=
resultSet
.
getMetaData
();
// determine number of rows in ResultSet
resultSet
.
last
();
// move to last row
numberOfRows
=
resultSet
.
getRow
();
// get row number
// notify JTable that model has changed
fireTableStructureChanged
();
}
// end method setQuery
// close Statement and Connection
public
void
disconnectFromDatabase
()
{
if
(
connectedToDatabase
)
{
// close Statement and Connection
try
{
resultSet
.
close
();
statement
.
close
();
connection
.
close
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
finally
// update database connection status
{
connectedToDatabase
=
false
;
}
// end finally
}
// end if
}
// end method disconnectFromDatabase
}
// end class ResultSetTableModel
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_03/DatabaseManipulation.class
public synchronized class DatabaseManipulation { static final String JDBC_DRIVER = com.mysql.jdbc.Driver; static final String DATABASE_URL = jdbc:mysql://localhost/books; static final String USERNAME = deitel; static final String PASSWORD = deitel; private java.sql.Connection connection; private java.sql.Statement statement; private java.util.Scanner scanner; public void DatabaseManipulation(); private int getChoice(); private void addAuthor(); private void editAuthor(); private void addTitle(); private void addTitleAuthor(); private void executeSQL(String); private void displayAuthors(); private void displayISBNs(); private void executeQuery(String); public static void main(String[]); }
ch28solutions/ex28_03/DatabaseManipulation.java
ch28solutions/ex28_03/DatabaseManipulation.java
// Exercise 28.3 Solution: DatabaseManipulation.java
import
java
.
sql
.
Connection
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
ResultSetMetaData
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
SQLException
;
import
java
.
util
.
Scanner
;
public
class
DatabaseManipulation
{
// JDBC driver and database URL
static
final
String
JDBC_DRIVER
=
"com.mysql.jdbc.Driver"
;
static
final
String
DATABASE_URL
=
"jdbc:mysql://localhost/books"
;
static
final
String
USERNAME
=
"deitel"
;
static
final
String
PASSWORD
=
"deitel"
;
private
Connection
connection
;
// manages connection
private
Statement
statement
;
// performs queries
private
Scanner
scanner
;
// reads user input
public
DatabaseManipulation
()
{
// Load the driver to allow connection to the database
try
{
Class
.
forName
(
JDBC_DRIVER
);
// load database driver class
// establish connection to database
connection
=
DriverManager
.
getConnection
(
DATABASE_URL
,
USERNAME
,
PASSWORD
);
// create Statement for querying database
statement
=
connection
.
createStatement
();
}
// end try
catch
(
ClassNotFoundException
cnfex
)
{
System
.
err
.
println
(
"Failed to load JDBC driver."
);
cnfex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
catch
(
SQLException
sqlex
)
{
System
.
err
.
println
(
"Unable to connect"
);
sqlex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
// create Scanner for user input
scanner
=
new
Scanner
(
System
.
in
);
int
choice
=
getChoice
();
// process user request
while
(
choice
!=
5
)
{
switch
(
choice
)
{
case
1
:
// add new author
addAuthor
();
break
;
case
2
:
// edit existing author
editAuthor
();
break
;
case
3
:
// add new title
addTitle
();
break
;
case
4
:
// associate title with author
addTitleAuthor
();
break
;
default
:
System
.
out
.
println
(
"invalid input"
);
}
// end switch
choice
=
getChoice
();
}
// end while
}
// end DisplayQueryResult constructor
// get user choice
private
int
getChoice
()
{
System
.
out
.
println
(
"Please choose one action:"
);
System
.
out
.
println
(
"1 -- Add a new author"
);
System
.
out
.
println
(
"2 -- Edit the existing information for an author"
);
System
.
out
.
println
(
"3 -- Add a new title for an author"
);
System
.
out
.
println
(
"4 -- Associate new title with an author"
);
System
.
out
.
println
(
"5 -- Exit"
);
return
scanner
.
nextInt
();
// get user choice
}
// end method getChoice
// add new author to database
private
void
addAuthor
()
{
// get first name and last name of the author
System
.
out
.
println
(
"Please enter author's first name"
);
String
firstName
=
scanner
.
next
();
System
.
out
.
println
(
"Please enter author's last name"
);
String
lastName
=
scanner
.
next
();
// insert author into authors table
String
insertSQL
=
"INSERT INTO authors ( firstName, lastName ) "
+
"VALUES ( '"
+
firstName
+
"', '"
+
lastName
+
"' )"
;
executeSQL
(
insertSQL
);
// execute SQL
}
// end method addAuthor
// edit existing author
private
void
editAuthor
()
{
// get author id, first name and last name
System
.
out
.
println
(
"Please enter author id"
);
int
id
=
scanner
.
nextInt
();
System
.
out
.
println
(
"Please enter new first name"
);
String
firstName
=
scanner
.
next
();
System
.
out
.
println
(
"Please enter new last name"
);
String
lastName
=
scanner
.
next
();
// update author
String
updateSQL
=
"UPDATE authors SET firstName = '"
+
firstName
+
"', lastName = '"
+
lastName
+
"' WHERE authorID = "
+
id
;
executeSQL
(
updateSQL
);
// execute SQL
}
// end method editAuthor
// add new title to database
private
void
addTitle
()
{
// get ISBN, title, editionNumber, copyright, publisherID,
// imageFile and price
System
.
out
.
println
(
"Please enter ISBN"
);
String
isbn
=
scanner
.
next
();
System
.
out
.
println
(
"Please enter title"
);
scanner
.
nextLine
();
// read remaining characters from last input
String
title
=
scanner
.
nextLine
();
System
.
out
.
println
(
"Please enter edition number"
);
int
editionNumber
=
scanner
.
nextInt
();
System
.
out
.
println
(
"Please enter copyright year"
);
String
copyright
=
scanner
.
next
();
// insert title into titles table
String
insertSQL
=
"INSERT INTO titles ( isbn, title, "
+
"editionNumber, copyright ) "
+
"VALUES ( '"
+
isbn
+
"', '"
+
title
+
"', '"
+
editionNumber
+
"', "
+
copyright
+
" )"
;
executeSQL
(
insertSQL
);
// execute SQL
}
// end method addTitle
// associate title with author
private
void
addTitleAuthor
()
{
// get author ID
System
.
out
.
println
(
"Please choose an author ID from the list below:"
);
displayAuthors
();
int
authorID
=
scanner
.
nextInt
();
// get title isbn
System
.
out
.
println
(
"Please choose a book isbn from the list below:"
);
displayISBNs
();
String
isbn
=
scanner
.
next
();
// update authorISBN table
String
insertSQL
=
"INSERT INTO authorISBN VALUES ( "
+
authorID
+
", '"
+
isbn
+
"' )"
;
executeSQL
(
insertSQL
);
// execute SQL
}
// end method addTitleAuthor
// execute SQL
private
void
executeSQL
(
String
sqlStatement
)
{
// execute specified SQL statement
try
{
System
.
out
.
printf
(
"Sending SQL: %s\n"
,
sqlStatement
);
int
result
=
statement
.
executeUpdate
(
sqlStatement
);
// display result of SQL execution
if
(
result
==
1
)
System
.
out
.
println
(
"Update successful\n"
);
else
System
.
out
.
println
(
"Update failed\n"
);
}
// end try
catch
(
SQLException
exception
)
{
exception
.
printStackTrace
();
}
// end catch
}
// end method executeSQL
// display authors table
private
void
displayAuthors
()
{
String
query
=
"SELECT authorID, firstName, lastName FROM authors"
;
executeQuery
(
query
);
}
// end method displayAuthors
// display book title and isbn
private
void
displayISBNs
()
{
String
query
=
"SELECT isbn, editionNumber, title FROM titles"
;
executeQuery
(
query
);
}
// end method displayISBNs
// execute query
private
void
executeQuery
(
String
query
)
{
// execute query and display result
try
{
// query database
ResultSet
resultSet
=
statement
.
executeQuery
(
query
);
// process query results
ResultSetMetaData
metaData
=
resultSet
.
getMetaData
();
int
numberOfColumns
=
metaData
.
getColumnCount
();
for
(
int
i
=
1
;
i
<=
numberOfColumns
;
i
++
)
System
.
out
.
printf
(
"%-8s\t"
,
metaData
.
getColumnName
(
i
)
);
System
.
out
.
println
();
while
(
resultSet
.
next
()
)
{
for
(
int
i
=
1
;
i
<=
numberOfColumns
;
i
++
)
System
.
out
.
printf
(
"%-8s\t"
,
resultSet
.
getObject
(
i
)
);
System
.
out
.
println
();
}
// end while
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
}
// end method executeQuery
public
static
void
main
(
String
args
[]
)
{
new
DatabaseManipulation
();
}
// end main
}
// end class DatabaseManipulation
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_04/AddEmployees$1.class
synchronized class AddEmployees$1 extends java.awt.event.WindowAdapter { void AddEmployees$1(AddEmployees); public void windowClosed(java.awt.event.WindowEvent); }
ch28solutions/ex28_04/AddEmployees$ButtonHandler.class
synchronized class AddEmployees$ButtonHandler implements java.awt.event.ActionListener { private void AddEmployees$ButtonHandler(AddEmployees); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_04/AddEmployees.class
public synchronized class AddEmployees extends javax.swing.JFrame { static final String JDBC_DRIVER = com.mysql.jdbc.Driver; static final String DATABASE_URL = jdbc:mysql://localhost/employees; static final String USERNAME = deitel; static final String PASSWORD = deitel; static final String DEFAULT_QUERY = SELECT * FROM employees; private java.sql.Connection connection; private java.sql.Statement statement; private ResultSetTableModel tableModel; private javax.swing.JTable table; private javax.swing.JButton addEmployee; private javax.swing.JButton addSalariedEmployee; private javax.swing.JButton addCommissionEmployee; private javax.swing.JButton addBasePlusCommissionEmployee; private javax.swing.JButton addHourlyEmployee; public void AddEmployees(); private void addEmployee(String); public static void main(String[]); }
ch28solutions/ex28_04/AddEmployees.java
ch28solutions/ex28_04/AddEmployees.java
// Exercise 25.4 solution: AddEmployees.java
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
SQLException
;
import
java
.
awt
.
FlowLayout
;
import
java
.
awt
.
BorderLayout
;
import
java
.
awt
.
event
.
ActionListener
;
import
java
.
awt
.
event
.
ActionEvent
;
import
java
.
awt
.
event
.
WindowAdapter
;
import
java
.
awt
.
event
.
WindowEvent
;
import
javax
.
swing
.
JFrame
;
import
javax
.
swing
.
JTable
;
import
javax
.
swing
.
JButton
;
import
javax
.
swing
.
JPanel
;
import
javax
.
swing
.
JLabel
;
import
javax
.
swing
.
JScrollPane
;
import
javax
.
swing
.
JOptionPane
;
public
class
AddEmployees
extends
JFrame
{
// JDBC driver and database URL
static
final
String
JDBC_DRIVER
=
"com.mysql.jdbc.Driver"
;
static
final
String
DATABASE_URL
=
"jdbc:mysql://localhost/employees"
;
static
final
String
USERNAME
=
"deitel"
;
static
final
String
PASSWORD
=
"deitel"
;
// default query retrieves all data from employees table
static
final
String
DEFAULT_QUERY
=
"SELECT * FROM employees"
;
private
Connection
connection
;
private
Statement
statement
;
private
ResultSetTableModel
tableModel
;
private
JTable
table
;
private
JButton
addEmployee
;
private
JButton
addSalariedEmployee
;
private
JButton
addCommissionEmployee
;
private
JButton
addBasePlusCommissionEmployee
;
private
JButton
addHourlyEmployee
;
public
AddEmployees
()
{
super
(
"Add Employees"
);
// create ResultSetTableModel with default JDBC driver,
// database URL and query
try
{
// create TableModel for results of query SELECT * FROM employees
tableModel
=
new
ResultSetTableModel
(
JDBC_DRIVER
,
DATABASE_URL
,
USERNAME
,
PASSWORD
,
DEFAULT_QUERY
);
connection
=
tableModel
.
getConnection
();
}
// end try
catch
(
ClassNotFoundException
cnfex
)
{
System
.
err
.
println
(
"Failed to load JDBC driver."
);
cnfex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
catch
(
SQLException
sqlex
)
{
System
.
err
.
println
(
"Unable to connect"
);
sqlex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
// if connected to database, set up GUI
JPanel
topPanel
=
new
JPanel
();
addEmployee
=
new
JButton
(
"Add Generic Employee"
);
addEmployee
.
addActionListener
(
new
ButtonHandler
()
);
topPanel
.
add
(
addEmployee
);
// create four buttons that allow user to add specific employee
addSalariedEmployee
=
new
JButton
(
"Add Salaried Employee"
);
addSalariedEmployee
.
addActionListener
(
new
ButtonHandler
()
);
addCommissionEmployee
=
new
JButton
(
"Add Commission Employee"
);
addCommissionEmployee
.
addActionListener
(
new
ButtonHandler
()
);
addBasePlusCommissionEmployee
=
new
JButton
(
"Add Base Plus Commission Employee"
);
addBasePlusCommissionEmployee
.
addActionListener
(
new
ButtonHandler
()
);
addHourlyEmployee
=
new
JButton
(
"Add Hourly Employee"
);
addHourlyEmployee
.
addActionListener
(
new
ButtonHandler
()
);
// add four buttons to centerPanel
JPanel
centerPanel
=
new
JPanel
();
centerPanel
.
add
(
addSalariedEmployee
);
centerPanel
.
add
(
addCommissionEmployee
);
centerPanel
.
add
(
addBasePlusCommissionEmployee
);
centerPanel
.
add
(
addHourlyEmployee
);
JPanel
inputPanel
=
new
JPanel
();
inputPanel
.
setLayout
(
new
BorderLayout
()
);
inputPanel
.
add
(
topPanel
,
BorderLayout
.
NORTH
);
inputPanel
.
add
(
centerPanel
,
BorderLayout
.
CENTER
);
table
=
new
JTable
(
tableModel
);
setLayout
(
new
BorderLayout
()
);
add
(
inputPanel
,
BorderLayout
.
NORTH
);
add
(
new
JScrollPane
(
table
),
BorderLayout
.
CENTER
);
setSize
(
800
,
300
);
setVisible
(
true
);
// dispose of window when user quits application (this overrides
// the default of HIDE_ON_CLOSE)
setDefaultCloseOperation
(
DISPOSE_ON_CLOSE
);
// ensure database connection is closed when user quits application
addWindowListener
(
new
WindowAdapter
()
{
// disconnect from database and exit when window has closed
public
void
windowClosed
(
WindowEvent
event
)
{
tableModel
.
disconnectFromDatabase
();
System
.
exit
(
0
);
}
// end method windowClosed
}
// end WindowAdapter inner class
);
// end call to addWindowListener
}
// end AddEmployees constructor
// add employee to database
private
void
addEmployee
(
String
query
)
{
try
{
statement
=
connection
.
createStatement
();
statement
.
executeUpdate
(
query
);
tableModel
.
setQuery
(
DEFAULT_QUERY
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end addEmployee
public
static
void
main
(
String
[]
args
)
{
new
AddEmployees
();
}
// end main
// inner class ButtonHandler handles button event
private
class
ButtonHandler
implements
ActionListener
{
public
void
actionPerformed
(
ActionEvent
event
)
{
String
socialSecurityNumber
=
JOptionPane
.
showInputDialog
(
"Employee Social Security Number"
);
String
insertQuery
=
""
,
displayQuery
=
""
;
// add generic employee to table employees
if
(
event
.
getSource
()
==
addEmployee
)
{
String
firstName
=
JOptionPane
.
showInputDialog
(
"First Name"
);
String
lastName
=
JOptionPane
.
showInputDialog
(
"Last Name"
);
String
birthday
=
JOptionPane
.
showInputDialog
(
"Birthday"
);
String
employeeType
=
JOptionPane
.
showInputDialog
(
"Employee Type"
);
String
department
=
JOptionPane
.
showInputDialog
(
"Department Name"
);
insertQuery
=
"INSERT INTO employees VALUES ( '"
+
socialSecurityNumber
+
"', '"
+
firstName
+
"', '"
+
lastName
+
"', '"
+
birthday
+
"', '"
+
employeeType
+
"', '"
+
department
+
"' )"
;
displayQuery
=
"SELECT socialSecurityNumber, firstName, "
+
"lastName, birthday, employeeType, departmentName FROM "
+
"employees"
;
}
// end if
// add salaried employee to table salariedEmployees
else
if
(
event
.
getSource
()
==
addSalariedEmployee
)
{
double
weeklySalary
=
Double
.
parseDouble
(
JOptionPane
.
showInputDialog
(
"Weekly Salary:"
)
);
insertQuery
=
"INSERT INTO salariedEmployees VALUES ( '"
+
socialSecurityNumber
+
"', '"
+
weeklySalary
+
"', '0' )"
;
displayQuery
=
"SELECT employees.socialSecurityNumber, "
+
"employees.firstName, employees.lastName, "
+
"employees.employeeType, salariedEmployees.weeklySalary"
+
" FROM employees, salariedEmployees WHERE "
+
"employees.socialSecurityNumber = "
+
"salariedEmployees.socialSecurityNumber"
;
}
// end if
// add commission employee to table commissionEmployees
else
if
(
event
.
getSource
()
==
addCommissionEmployee
)
{
int
grossSales
=
Integer
.
parseInt
(
JOptionPane
.
showInputDialog
(
"Gross Sales:"
)
);
double
commissionRate
=
Double
.
parseDouble
(
JOptionPane
.
showInputDialog
(
"Commission Rate:"
)
);
insertQuery
=
"INSERT INTO commissionEmployees VALUES ( '"
+
socialSecurityNumber
+
"', '"
+
grossSales
+
"', '"
+
commissionRate
+
"', '0' )"
;
displayQuery
=
"SELECT employees.socialSecurityNumber, "
+
"employees.firstName, employees.lastName, "
+
"employees.employeeType, commissionEmployees.grossSales,"
+
" commissionEmployees.commissionRate FROM employees, "
+
"commissionEmployees WHERE employees.socialSecurityNumber="
+
"commissionEmployees.socialSecurityNumber"
;
}
// end else if
// add base plus commission employee to table
// basePlusCommissionEmployees
else
if
(
event
.
getSource
()
==
addBasePlusCommissionEmployee
)
{
int
grossSales
=
Integer
.
parseInt
(
JOptionPane
.
showInputDialog
(
"Gross Sales:"
)
);
double
commissionRate
=
Double
.
parseDouble
(
JOptionPane
.
showInputDialog
(
"Commission Rate:"
)
);
double
baseSalary
=
Double
.
parseDouble
(
JOptionPane
.
showInputDialog
(
"Base Salary:"
)
);
insertQuery
=
"INSERT INTO basePlusCommissionEmployees "
+
"VALUES ( '"
+
socialSecurityNumber
+
"', '"
+
grossSales
+
"', '"
+
commissionRate
+
"', '"
+
baseSalary
+
"', '0' )"
;
displayQuery
=
"SELECT employees.socialSecurityNumber, "
+
"employees.firstName, employees.lastName, employees."
+
"employeeType, basePlusCommissionEmployees.baseSalary, "
+
"basePlusCommissionEmployees.grossSales, basePlus"
+
"CommissionEmployees.commissionRate FROM employees, "
+
"basePlusCommissionEmployees WHERE "
+
"employees.socialSecurityNumber = "
+
"basePlusCommissionEmployees.socialSecurityNumber"
;
}
// end else if
// add hourly employee to table hourlyEmployees
else
{
int
hours
=
Integer
.
parseInt
(
JOptionPane
.
showInputDialog
(
"Hours:"
)
);
double
wage
=
Double
.
parseDouble
(
JOptionPane
.
showInputDialog
(
"Wage:"
)
);
insertQuery
=
"INSERT INTO hourlyEmployees VALUES ( '"
+
socialSecurityNumber
+
"', '"
+
hours
+
"', '"
+
wage
+
"', '0' )"
;
displayQuery
=
"SELECT employees.socialSecurityNumber, "
+
"employees.firstName, employees.lastName, "
+
"employees.employeeType, hourlyEmployees.hours, "
+
"hourlyEmployees.wage FROM employees, hourlyEmployees "
+
"WHERE employees.socialSecurityNumber = "
+
"hourlyEmployees.socialSecurityNumber"
;
}
// end else
// execute insert query and display employee info
try
{
statement
=
connection
.
createStatement
();
statement
.
executeUpdate
(
insertQuery
);
// display the employee info
tableModel
.
setQuery
(
displayQuery
);
}
// end try
catch
(
SQLException
exception
)
{
JOptionPane
.
showMessageDialog
(
null
,
exception
.
toString
()
);
}
// end catch
}
// end method actionPerformed
}
// end inner class ButtonHandler
}
// end class AddEmployees
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_04/employees.sql
CREATE DATABASE IF NOT EXISTS employees; USE employees; DROP TABLE IF EXISTS salariedEmployees; DROP TABLE IF EXISTS commissionEmployees; DROP TABLE IF EXISTS basePlusCommissionEmployees; DROP TABLE IF EXISTS hourlyEmployees; DROP TABLE IF EXISTS employees; CREATE TABLE employees ( socialSecurityNumber varchar (30) NOT NULL, firstName varchar (30) NOT NULL, lastName varchar (30) NOT NULL, birthday date NOT NULL, employeeType varchar (30) NOT NULL, departmentName varchar (30) NOT NULL, PRIMARY KEY (socialSecurityNumber) ) TYPE=INNODB; CREATE TABLE salariedEmployees ( socialsecurityNumber varchar (30) NOT NULL, weeklySalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) TYPE=INNODB; CREATE TABLE commissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) TYPE=INNODB; CREATE TABLE basePlusCommissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, baseSalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) TYPE=INNODB; CREATE TABLE hourlyEmployees ( socialSecurityNumber varchar (30) NOT NULL, hours int NOT NULL, wage real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) TYPE=INNODB; INSERT INTO employees VALUES ('111-11-1111', 'John', 'Smith', '1945-1-2', 'salariedEmployee', 'R&D'); INSERT INTO employees VALUES ('222-22-2222', 'Sue', 'Jones', '1961-2-3', 'commissionEmployee', 'SALES'); INSERT INTO employees VALUES ('333-33-3333', 'Bob', 'Lowis', '1958-10-5', 'basePlusCommissionEmployee', 'SALES'); INSERT INTO employees VALUES ('444-44-4444', 'Karen', 'Price', '1972-5-25', 'hourlyEmployee', 'HR'); INSERT INTO salariedEmployees VALUES ('111-11-1111', 2013.67, 0); INSERT INTO commissionEmployees VALUES ('222-22-2222', 10100, 0.05, 0); INSERT INTO basePlusCommissionEmployees VALUES ('333-33-3333', 5000, 0.04, 300, 0); INSERT INTO hourlyEmployees VALUES ('444-44-4444', 30, 35.5, 0);
ch28solutions/ex28_04/ResultSetTableModel.class
public synchronized class ResultSetTableModel extends javax.swing.table.AbstractTableModel { private java.sql.Connection connection; private java.sql.Statement statement; private java.sql.ResultSet resultSet; private java.sql.ResultSetMetaData metaData; private int numberOfRows; private boolean connectedToDatabase; public void ResultSetTableModel(String, String, String, String, String) throws java.sql.SQLException, ClassNotFoundException; public Class getColumnClass(int) throws IllegalStateException; public int getColumnCount() throws IllegalStateException; public String getColumnName(int) throws IllegalStateException; public int getRowCount() throws IllegalStateException; public Object getValueAt(int, int) throws IllegalStateException; public void setQuery(String) throws java.sql.SQLException, IllegalStateException; public void disconnectFromDatabase(); public java.sql.Connection getConnection(); }
ch28solutions/ex28_04/ResultSetTableModel.java
ch28solutions/ex28_04/ResultSetTableModel.java
// Exercise 25.4 Solution: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
ResultSetMetaData
;
import
java
.
sql
.
SQLException
;
import
javax
.
swing
.
table
.
AbstractTableModel
;
// ResultSet rows and columns are counted from 1 and JTable
// rows and columns are counted from 0. When processing
// ResultSet rows or columns for use in a JTable, it is
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public
class
ResultSetTableModel
extends
AbstractTableModel
{
private
Connection
connection
;
private
Statement
statement
;
private
ResultSet
resultSet
;
private
ResultSetMetaData
metaData
;
private
int
numberOfRows
;
// keep track of database connection status
private
boolean
connectedToDatabase
=
false
;
// constructor initializes resultSet and obtains its meta data object;
// determines number of rows
public
ResultSetTableModel
(
String
driver
,
String
url
,
String
username
,
String
password
,
String
query
)
throws
SQLException
,
ClassNotFoundException
{
Class
.
forName
(
driver
);
// load database driver class
connection
=
DriverManager
.
getConnection
(
url
,
username
,
password
);
// create Statement to query database
statement
=
connection
.
createStatement
(
ResultSet
.
TYPE_SCROLL_INSENSITIVE
,
ResultSet
.
CONCUR_READ_ONLY
);
connectedToDatabase
=
true
;
// update database connection status
setQuery
(
query
);
// set query and execute it
}
// end constructor ResultSetTableModel
// get class that represents column type
public
Class
getColumnClass
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine Java class of column
try
{
String
className
=
metaData
.
getColumnClassName
(
column
+
1
);
// return Class object that represents className
return
Class
.
forName
(
className
);
}
// end try
catch
(
Exception
exception
)
{
exception
.
printStackTrace
();
}
// end catch
return
Object
.
class
;
// if problems occur above, assume type Object
}
// end method getColumnClass
// get number of columns in ResultSet
public
int
getColumnCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine number of columns
try
{
return
metaData
.
getColumnCount
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
0
;
// if problems occur above, return 0 for number of columns
}
// end method getColumnCount
// get name of a particular column in ResultSet
public
String
getColumnName
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine column name
try
{
return
metaData
.
getColumnName
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string for column name
}
// end method getColumnName
// return number of rows in ResultSet
public
int
getRowCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
return
numberOfRows
;
}
// end method getRowCount
// obtain value in particular row and column
public
Object
getValueAt
(
int
row
,
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// obtain a value at specified ResultSet row and column
try
{
resultSet
.
absolute
(
row
+
1
);
return
resultSet
.
getObject
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string object
}
// end method getValueAt
// set new database query string
public
void
setQuery
(
String
query
)
throws
SQLException
,
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
resultSet
=
statement
.
executeQuery
(
query
);
// execute query
metaData
=
resultSet
.
getMetaData
();
// get metadata
// determine number of rows in ResultSet
resultSet
.
last
();
// move to last row
numberOfRows
=
resultSet
.
getRow
();
// get row number
fireTableStructureChanged
();
// table model changed
}
// end method setQuery
// close Statement and Connection
public
void
disconnectFromDatabase
()
{
// close Statement and Connection
try
{
statement
.
close
();
connection
.
close
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
finally
// update database connection status
{
connectedToDatabase
=
false
;
}
// end finally
}
// end method disconnectFromDatabase
// return Connection
public
Connection
getConnection
()
{
return
connection
;
}
// end method getConnection
}
// end class ResultSetTableModel
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_05/DisplayQueryResults$1.class
synchronized class DisplayQueryResults$1 implements java.awt.event.ActionListener { void DisplayQueryResults$1(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_05/DisplayQueryResults$2.class
synchronized class DisplayQueryResults$2 implements java.awt.event.ActionListener { void DisplayQueryResults$2(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_05/DisplayQueryResults$3.class
synchronized class DisplayQueryResults$3 extends java.awt.event.WindowAdapter { void DisplayQueryResults$3(DisplayQueryResults); public void windowClosed(java.awt.event.WindowEvent); }
ch28solutions/ex28_05/DisplayQueryResults.class
public synchronized class DisplayQueryResults extends javax.swing.JFrame { static final String JDBC_DRIVER = com.mysql.jdbc.Driver; static final String DATABASE_URL = jdbc:mysql://localhost/employees; static final String USERNAME = deitel; static final String PASSWORD = deitel; static final String DEFAULT_QUERY = SELECT * FROM employees; private java.sql.Connection connection; private java.sql.Statement statement; private ResultSetTableModel tableModel; private javax.swing.JTable table; private javax.swing.JComboBox inputQuery; private javax.swing.JButton submitQuery; private javax.swing.JTextField input; public void DisplayQueryResults(); private void getTable(); public static void main(String[]); }
ch28solutions/ex28_05/DisplayQueryResults.java
ch28solutions/ex28_05/DisplayQueryResults.java
// Exercise 25.5 Solution: DisplayQueryResults.java
import
java
.
sql
.
SQLException
;
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
awt
.
FlowLayout
;
import
java
.
awt
.
BorderLayout
;
import
java
.
awt
.
event
.
ActionListener
;
import
java
.
awt
.
event
.
ActionEvent
;
import
java
.
awt
.
event
.
WindowAdapter
;
import
java
.
awt
.
event
.
WindowEvent
;
import
javax
.
swing
.
JFrame
;
import
javax
.
swing
.
JTable
;
import
javax
.
swing
.
JComboBox
;
import
javax
.
swing
.
JButton
;
import
javax
.
swing
.
JTextField
;
import
javax
.
swing
.
JScrollPane
;
import
javax
.
swing
.
JPanel
;
import
javax
.
swing
.
JLabel
;
public
class
DisplayQueryResults
extends
JFrame
{
// JDBC driver and database URL
static
final
String
JDBC_DRIVER
=
"com.mysql.jdbc.Driver"
;
static
final
String
DATABASE_URL
=
"jdbc:mysql://localhost/employees"
;
static
final
String
USERNAME
=
"deitel"
;
static
final
String
PASSWORD
=
"deitel"
;
// default query retrieves all data from employees table
static
final
String
DEFAULT_QUERY
=
"SELECT * FROM employees"
;
private
Connection
connection
;
private
Statement
statement
;
private
ResultSetTableModel
tableModel
;
private
JTable
table
;
private
JComboBox
inputQuery
;
private
JButton
submitQuery
;
private
JTextField
input
;
public
DisplayQueryResults
()
{
super
(
"Select Query. Click Submit to See Results."
);
// create ResultSetTableModel with default JDBC driver,
// database URL and query
try
{
// create TableModel for results of query SELECT * FROM employees
tableModel
=
new
ResultSetTableModel
(
JDBC_DRIVER
,
DATABASE_URL
,
USERNAME
,
PASSWORD
,
DEFAULT_QUERY
);
connection
=
tableModel
.
getConnection
();
}
// end try
catch
(
ClassNotFoundException
cnfex
)
{
System
.
err
.
println
(
"Failed to load JDBC driver."
);
cnfex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
catch
(
SQLException
sqlex
)
{
System
.
err
.
println
(
"Unable to connect"
);
sqlex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
String
queries
[]
=
{
"Select all employees working in Department "
+
"SALES."
,
"Select hourly employees working over 30 hours."
,
"Select all comission employees in descending order of the "
+
"comission rate."
,
"Specify particular query"
};
// set up GUI
inputQuery
=
new
JComboBox
(
queries
);
submitQuery
=
new
JButton
(
"Submit query"
);
submitQuery
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
getTable
();
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
JPanel
topPanel
=
new
JPanel
();
input
=
new
JTextField
(
40
);
input
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
// execute query in JTextField
try
{
String
query
=
input
.
getText
();
if
(
query
.
substring
(
0
,
6
).
equalsIgnoreCase
(
"SELECT"
)
)
tableModel
.
setQuery
(
query
);
else
statement
.
executeUpdate
(
query
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
JPanel
centerPanel
=
new
JPanel
();
centerPanel
.
setLayout
(
new
FlowLayout
()
);
centerPanel
.
add
(
new
JLabel
(
"Enter query:"
)
);
centerPanel
.
add
(
input
);
topPanel
.
setLayout
(
new
BorderLayout
()
);
topPanel
.
add
(
inputQuery
,
BorderLayout
.
NORTH
);
topPanel
.
add
(
centerPanel
,
BorderLayout
.
CENTER
);
topPanel
.
add
(
submitQuery
,
BorderLayout
.
SOUTH
);
table
=
new
JTable
(
tableModel
);
setLayout
(
new
BorderLayout
()
);
add
(
topPanel
,
BorderLayout
.
NORTH
);
add
(
new
JScrollPane
(
table
),
BorderLayout
.
CENTER
);
getTable
();
setSize
(
650
,
200
);
setVisible
(
true
);
// dispose of window when user quits application (this overrides
// the default of HIDE_ON_CLOSE)
setDefaultCloseOperation
(
DISPOSE_ON_CLOSE
);
// ensure database connection is closed when user quits application
addWindowListener
(
new
WindowAdapter
()
{
// disconnect from database and exit when window has closed
public
void
windowClosed
(
WindowEvent
event
)
{
tableModel
.
disconnectFromDatabase
();
System
.
exit
(
0
);
}
// end method windowClosed
}
// end WindowAdapter inner class
);
// end call to addWindowListener
}
// end constructor DisplayQueryResult
// execute query selected from JComboBox
private
void
getTable
()
{
// define each query
try
{
int
selection
=
inputQuery
.
getSelectedIndex
();
String
query
=
null
;
switch
(
selection
)
{
case
0
:
// select employees from the SALES department
query
=
"SELECT * FROM employees WHERE "
+
"departmentName = 'SALES'"
;
break
;
case
1
:
// select hourly employees working over 30 hours
query
=
"SELECT * FROM hourlyEmployees WHERE hours >= 30"
;
break
;
case
2
:
// select commission employees in desc order of rate
query
=
"SELECT * FROM commissionEmployees ORDER BY "
+
"commissionRate DESC"
;
break
;
case
3
:
// user defined query
query
=
input
.
getText
();
break
;
}
// end switch
statement
=
connection
.
createStatement
();
if
(
query
.
substring
(
0
,
6
).
equals
(
"SELECT"
)
)
tableModel
.
setQuery
(
query
);
else
statement
.
executeUpdate
(
query
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end method getTable
public
static
void
main
(
String
args
[]
)
{
new
DisplayQueryResults
();
}
// end main
}
// end class DisplayQueryResults
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_05/ResultSetTableModel.class
public synchronized class ResultSetTableModel extends javax.swing.table.AbstractTableModel { private java.sql.Connection connection; private java.sql.Statement statement; private java.sql.ResultSet resultSet; private java.sql.ResultSetMetaData metaData; private int numberOfRows; private boolean connectedToDatabase; public void ResultSetTableModel(String, String, String, String, String) throws java.sql.SQLException, ClassNotFoundException; public Class getColumnClass(int) throws IllegalStateException; public int getColumnCount() throws IllegalStateException; public String getColumnName(int) throws IllegalStateException; public int getRowCount() throws IllegalStateException; public Object getValueAt(int, int) throws IllegalStateException; public void setQuery(String) throws java.sql.SQLException, IllegalStateException; public void disconnectFromDatabase(); public java.sql.Connection getConnection(); }
ch28solutions/ex28_05/ResultSetTableModel.java
ch28solutions/ex28_05/ResultSetTableModel.java
// Exercise 25.4 Solution: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
ResultSetMetaData
;
import
java
.
sql
.
SQLException
;
import
javax
.
swing
.
table
.
AbstractTableModel
;
// ResultSet rows and columns are counted from 1 and JTable
// rows and columns are counted from 0. When processing
// ResultSet rows or columns for use in a JTable, it is
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public
class
ResultSetTableModel
extends
AbstractTableModel
{
private
Connection
connection
;
private
Statement
statement
;
private
ResultSet
resultSet
;
private
ResultSetMetaData
metaData
;
private
int
numberOfRows
;
// keep track of database connection status
private
boolean
connectedToDatabase
=
false
;
// constructor initializes resultSet and obtains its meta data object;
// determines number of rows
public
ResultSetTableModel
(
String
driver
,
String
url
,
String
username
,
String
password
,
String
query
)
throws
SQLException
,
ClassNotFoundException
{
Class
.
forName
(
driver
);
// load database driver class
connection
=
DriverManager
.
getConnection
(
url
,
username
,
password
);
// create Statement to query database
statement
=
connection
.
createStatement
(
ResultSet
.
TYPE_SCROLL_INSENSITIVE
,
ResultSet
.
CONCUR_READ_ONLY
);
connectedToDatabase
=
true
;
// update database connection status
setQuery
(
query
);
// set query and execute it
}
// end constructor ResultSetTableModel
// get class that represents column type
public
Class
getColumnClass
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine Java class of column
try
{
String
className
=
metaData
.
getColumnClassName
(
column
+
1
);
// return Class object that represents className
return
Class
.
forName
(
className
);
}
// end try
catch
(
Exception
exception
)
{
exception
.
printStackTrace
();
}
// end catch
return
Object
.
class
;
// if problems occur above, assume type Object
}
// end method getColumnClass
// get number of columns in ResultSet
public
int
getColumnCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine number of columns
try
{
return
metaData
.
getColumnCount
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
0
;
// if problems occur above, return 0 for number of columns
}
// end method getColumnCount
// get name of a particular column in ResultSet
public
String
getColumnName
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine column name
try
{
return
metaData
.
getColumnName
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string for column name
}
// end method getColumnName
// return number of rows in ResultSet
public
int
getRowCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
return
numberOfRows
;
}
// end method getRowCount
// obtain value in particular row and column
public
Object
getValueAt
(
int
row
,
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// obtain a value at specified ResultSet row and column
try
{
resultSet
.
absolute
(
row
+
1
);
return
resultSet
.
getObject
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string object
}
// end method getValueAt
// set new database query string
public
void
setQuery
(
String
query
)
throws
SQLException
,
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
resultSet
=
statement
.
executeQuery
(
query
);
// execute query
metaData
=
resultSet
.
getMetaData
();
// get metadata
// determine number of rows in ResultSet
resultSet
.
last
();
// move to last row
numberOfRows
=
resultSet
.
getRow
();
// get row number
fireTableStructureChanged
();
// table model changed
}
// end method setQuery
// close Statement and Connection
public
void
disconnectFromDatabase
()
{
// close Statement and Connection
try
{
statement
.
close
();
connection
.
close
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
finally
// update database connection status
{
connectedToDatabase
=
false
;
}
// end finally
}
// end method disconnectFromDatabase
// return Connection
public
Connection
getConnection
()
{
return
connection
;
}
// end method getConnection
}
// end class ResultSetTableModel
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_06/DisplayQueryResults$1.class
synchronized class DisplayQueryResults$1 implements java.awt.event.ActionListener { void DisplayQueryResults$1(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_06/DisplayQueryResults$2.class
synchronized class DisplayQueryResults$2 implements java.awt.event.ActionListener { void DisplayQueryResults$2(DisplayQueryResults); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_06/DisplayQueryResults$3.class
synchronized class DisplayQueryResults$3 extends java.awt.event.WindowAdapter { void DisplayQueryResults$3(DisplayQueryResults); public void windowClosed(java.awt.event.WindowEvent); }
ch28solutions/ex28_06/DisplayQueryResults.class
public synchronized class DisplayQueryResults extends javax.swing.JFrame { static final String JDBC_DRIVER = com.mysql.jdbc.Driver; static final String DATABASE_URL = jdbc:mysql://localhost/employees; static final String USERNAME = deitel; static final String PASSWORD = deitel; static final String DEFAULT_QUERY = SELECT * FROM employees; private java.sql.Connection connection; private java.sql.Statement statement; private java.sql.ResultSet resultSet; private ResultSetTableModel tableModel; private javax.swing.JTable table; private javax.swing.JComboBox inputQuery; private javax.swing.JButton submitQuery; private javax.swing.JTextField input; public void DisplayQueryResults(); private void getTable(); private void addBirthdayBonus(); private void addBonus(java.util.Vector); public static void main(String[]); }
ch28solutions/ex28_06/DisplayQueryResults.java
ch28solutions/ex28_06/DisplayQueryResults.java
// Exercise 25.6 Solution: DisplayQueryResults.java
import
java
.
sql
.
SQLException
;
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
ResultSet
;
import
java
.
awt
.
FlowLayout
;
import
java
.
awt
.
BorderLayout
;
import
java
.
awt
.
event
.
ActionListener
;
import
java
.
awt
.
event
.
ActionEvent
;
import
java
.
awt
.
event
.
WindowAdapter
;
import
java
.
awt
.
event
.
WindowEvent
;
import
java
.
util
.
Vector
;
import
javax
.
swing
.
JFrame
;
import
javax
.
swing
.
JTable
;
import
javax
.
swing
.
JComboBox
;
import
javax
.
swing
.
JButton
;
import
javax
.
swing
.
JTextField
;
import
javax
.
swing
.
JScrollPane
;
import
javax
.
swing
.
JPanel
;
import
javax
.
swing
.
JLabel
;
import
javax
.
swing
.
JOptionPane
;
public
class
DisplayQueryResults
extends
JFrame
{
// JDBC driver and database URL
static
final
String
JDBC_DRIVER
=
"com.mysql.jdbc.Driver"
;
static
final
String
DATABASE_URL
=
"jdbc:mysql://localhost/employees"
;
static
final
String
USERNAME
=
"deitel"
;
static
final
String
PASSWORD
=
"deitel"
;
// default query retrieves all data from employees table
static
final
String
DEFAULT_QUERY
=
"SELECT * FROM employees"
;
private
Connection
connection
;
private
Statement
statement
;
private
ResultSet
resultSet
;
private
ResultSetTableModel
tableModel
;
private
JTable
table
;
private
JComboBox
inputQuery
;
private
JButton
submitQuery
;
private
JTextField
input
;
public
DisplayQueryResults
()
{
super
(
"Select Query. Click Submit to See Results."
);
// create ResultSetTableModel with default JDBC driver,
// database URL and query
try
{
// create TableModel for results of query SELECT * FROM employees
tableModel
=
new
ResultSetTableModel
(
JDBC_DRIVER
,
DATABASE_URL
,
USERNAME
,
PASSWORD
,
DEFAULT_QUERY
);
connection
=
tableModel
.
getConnection
();
}
// end try
catch
(
ClassNotFoundException
cnfex
)
{
System
.
err
.
println
(
"Failed to load JDBC driver."
);
cnfex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
catch
(
SQLException
sqlex
)
{
System
.
err
.
println
(
"Unable to connect"
);
sqlex
.
printStackTrace
();
System
.
exit
(
1
);
// terminate program
}
// end catch
String
queries
[]
=
{
"Select all employees working in Department "
+
"SALES."
,
"Select hourly employees working over 30 hours."
,
"Select all comission employees in descending order of the "
+
"comission rate."
,
"Increase base salary by 10% for all base "
+
"plus comission employees."
,
"If the employee's birthday is "
+
"in the current month, add a $100 bonus."
,
"For all comission "
+
"employee whose gross sales over 10000, add $100 bonus."
,
"Specify particular query"
};
// set up GUI
inputQuery
=
new
JComboBox
(
queries
);
submitQuery
=
new
JButton
(
"Submit query"
);
submitQuery
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
getTable
();
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
JPanel
topPanel
=
new
JPanel
();
input
=
new
JTextField
(
40
);
input
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
e
)
{
// execute query in JTextField
try
{
String
query
=
input
.
getText
();
if
(
query
.
substring
(
0
,
6
).
equalsIgnoreCase
(
"SELECT"
)
)
tableModel
.
setQuery
(
query
);
else
statement
.
executeUpdate
(
query
);
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
JPanel
centerPanel
=
new
JPanel
();
centerPanel
.
setLayout
(
new
FlowLayout
()
);
centerPanel
.
add
(
new
JLabel
(
"Enter query:"
)
);
centerPanel
.
add
(
input
);
topPanel
.
setLayout
(
new
BorderLayout
()
);
topPanel
.
add
(
inputQuery
,
BorderLayout
.
NORTH
);
topPanel
.
add
(
centerPanel
,
BorderLayout
.
CENTER
);
topPanel
.
add
(
submitQuery
,
BorderLayout
.
SOUTH
);
table
=
new
JTable
(
tableModel
);
setLayout
(
new
BorderLayout
()
);
add
(
topPanel
,
BorderLayout
.
NORTH
);
add
(
new
JScrollPane
(
table
),
BorderLayout
.
CENTER
);
getTable
();
setSize
(
650
,
200
);
setVisible
(
true
);
// dispose of window when user quits application (this overrides
// the default of HIDE_ON_CLOSE)
setDefaultCloseOperation
(
DISPOSE_ON_CLOSE
);
// ensure database connection is closed when user quits application
addWindowListener
(
new
WindowAdapter
()
{
// disconnect from database and exit when window has closed
public
void
windowClosed
(
WindowEvent
event
)
{
tableModel
.
disconnectFromDatabase
();
System
.
exit
(
0
);
}
// end method windowClosed
}
// end WindowAdapter inner class
);
// end call to addWindowListener
}
// end constructor DisplayQueryResult
// execute query selected from JComboBox
private
void
getTable
()
{
// define each query
try
{
int
selection
=
inputQuery
.
getSelectedIndex
();
String
query
=
null
;
switch
(
selection
)
{
case
0
:
// select employees from the SALES department
query
=
"SELECT * FROM employees WHERE "
+
"departmentName = 'SALES'"
;
break
;
case
1
:
// select hourly employees working over 30 hours
query
=
"SELECT * FROM hourlyEmployees WHERE hours >= 30"
;
break
;
case
2
:
// select commission employees in desc order of rate
query
=
"SELECT * FROM commissionEmployees ORDER BY "
+
"commissionRate DESC"
;
break
;
case
3
:
// increase base salary of basePlusCommissionEmployees
query
=
"UPDATE basePlusCommissionEmployees SET "
+
"baseSalary = baseSalary * 1.1"
;
break
;
case
4
:
// employee birthday is current month, add $100 bonus
addBirthdayBonus
();
break
;
case
5
:
// add 100 to comissionEmployee gross sales over 10000
query
=
"UPDATE commissionEmployees SET "
+
"bonus = bonus + 100.00 WHERE grossSales >= 10000"
;
break
;
case
6
:
// user specified query
query
=
input
.
getText
();
break
;
}
// end switch
statement
=
connection
.
createStatement
();
if
(
query
!=
null
)
{
if
(
query
.
substring
(
0
,
6
).
equalsIgnoreCase
(
"SELECT"
)
)
tableModel
.
setQuery
(
query
);
else
statement
.
executeUpdate
(
query
);
}
// end if
}
// end try
catch
(
SQLException
sqlex
)
{
sqlex
.
printStackTrace
();
}
// end catch
}
// end method getTable
private
void
addBirthdayBonus
()
{
// get current month
int
currentMonth
=
Integer
.
parseInt
(
JOptionPane
.
showInputDialog
(
"Current month: "
)
);
// validate current month
while
(
!
(
currentMonth
>=
1
&&
currentMonth
<=
12
)
)
currentMonth
=
Integer
.
parseInt
(
JOptionPane
.
showInputDialog
(
"Current month: "
)
);
// add $100 bonus to employee whose birthday matches current month
try
{
String
getEmployees
=
"SELECT * FROM employees"
;
statement
=
connection
.
createStatement
();
resultSet
=
statement
.
executeQuery
(
getEmployees
);
String
birthday
;
Vector
<
String
>
birthdayList
=
new
Vector
<
String
>
();
// find employee whose birthday match current month
while
(
resultSet
.
next
()
)
{
birthday
=
resultSet
.
getDate
(
"birthday"
).
toString
();
int
month
=
Integer
.
parseInt
(
birthday
.
substring
(
5
,
7
)
);
if
(
month
==
currentMonth
)
{
birthdayList
.
add
(
resultSet
.
getString
(
"socialSecurityNumber"
)
);
birthdayList
.
add
(
resultSet
.
getString
(
"employeeType"
)
+
"s"
);
}
// end if
}
// end while
addBonus
(
birthdayList
);
}
// end try
catch
(
SQLException
exception
)
{
exception
.
printStackTrace
();
}
// end catch
}
// end addBirthdayBonus
private
void
addBonus
(
Vector
<
String
>
vector
)
{
String
socialSecurityNumber
,
employeeType
;
// add bonus to all employees in the vector
try
{
// add $100 to each employee listed in the vector
for
(
int
i
=
0
;
i
<
vector
.
size
()
/
2
;
i
++
)
{
socialSecurityNumber
=
vector
.
elementAt
(
i
*
2
);
employeeType
=
vector
.
elementAt
(
i
*
2
+
1
);
// add $100 bonus
statement
=
connection
.
createStatement
();
statement
.
executeUpdate
(
"UPDATE "
+
employeeType
+
" SET bonus = bonus + 100.00 WHERE socialSecurityNumber "
+
"= '"
+
socialSecurityNumber
+
"'"
);
// display after update
tableModel
.
setQuery
(
"SELECT * FROM "
+
employeeType
);
}
// end for
}
// end try
catch
(
SQLException
exception
)
{
exception
.
printStackTrace
();
}
// end catch
}
// end method addBonus
public
static
void
main
(
String
args
[]
)
{
new
DisplayQueryResults
();
}
// end main
}
// end class DisplayQueryResults
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_06/ResultSetTableModel.class
public synchronized class ResultSetTableModel extends javax.swing.table.AbstractTableModel { private java.sql.Connection connection; private java.sql.Statement statement; private java.sql.ResultSet resultSet; private java.sql.ResultSetMetaData metaData; private int numberOfRows; private boolean connectedToDatabase; public void ResultSetTableModel(String, String, String, String, String) throws java.sql.SQLException, ClassNotFoundException; public Class getColumnClass(int) throws IllegalStateException; public int getColumnCount() throws IllegalStateException; public String getColumnName(int) throws IllegalStateException; public int getRowCount() throws IllegalStateException; public Object getValueAt(int, int) throws IllegalStateException; public void setQuery(String) throws java.sql.SQLException, IllegalStateException; public void disconnectFromDatabase(); public java.sql.Connection getConnection(); }
ch28solutions/ex28_06/ResultSetTableModel.java
ch28solutions/ex28_06/ResultSetTableModel.java
// Exercise 25.4 Solution: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import
java
.
sql
.
Connection
;
import
java
.
sql
.
Statement
;
import
java
.
sql
.
DriverManager
;
import
java
.
sql
.
ResultSet
;
import
java
.
sql
.
ResultSetMetaData
;
import
java
.
sql
.
SQLException
;
import
javax
.
swing
.
table
.
AbstractTableModel
;
// ResultSet rows and columns are counted from 1 and JTable
// rows and columns are counted from 0. When processing
// ResultSet rows or columns for use in a JTable, it is
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public
class
ResultSetTableModel
extends
AbstractTableModel
{
private
Connection
connection
;
private
Statement
statement
;
private
ResultSet
resultSet
;
private
ResultSetMetaData
metaData
;
private
int
numberOfRows
;
// keep track of database connection status
private
boolean
connectedToDatabase
=
false
;
// constructor initializes resultSet and obtains its meta data object;
// determines number of rows
public
ResultSetTableModel
(
String
driver
,
String
url
,
String
username
,
String
password
,
String
query
)
throws
SQLException
,
ClassNotFoundException
{
Class
.
forName
(
driver
);
// load database driver class
connection
=
DriverManager
.
getConnection
(
url
,
username
,
password
);
// create Statement to query database
statement
=
connection
.
createStatement
(
ResultSet
.
TYPE_SCROLL_INSENSITIVE
,
ResultSet
.
CONCUR_READ_ONLY
);
connectedToDatabase
=
true
;
// update database connection status
setQuery
(
query
);
// set query and execute it
}
// end constructor ResultSetTableModel
// get class that represents column type
public
Class
getColumnClass
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine Java class of column
try
{
String
className
=
metaData
.
getColumnClassName
(
column
+
1
);
// return Class object that represents className
return
Class
.
forName
(
className
);
}
// end try
catch
(
Exception
exception
)
{
exception
.
printStackTrace
();
}
// end catch
return
Object
.
class
;
// if problems occur above, assume type Object
}
// end method getColumnClass
// get number of columns in ResultSet
public
int
getColumnCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine number of columns
try
{
return
metaData
.
getColumnCount
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
0
;
// if problems occur above, return 0 for number of columns
}
// end method getColumnCount
// get name of a particular column in ResultSet
public
String
getColumnName
(
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// determine column name
try
{
return
metaData
.
getColumnName
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string for column name
}
// end method getColumnName
// return number of rows in ResultSet
public
int
getRowCount
()
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
return
numberOfRows
;
}
// end method getRowCount
// obtain value in particular row and column
public
Object
getValueAt
(
int
row
,
int
column
)
throws
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
// obtain a value at specified ResultSet row and column
try
{
resultSet
.
absolute
(
row
+
1
);
return
resultSet
.
getObject
(
column
+
1
);
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
return
""
;
// if problems, return empty string object
}
// end method getValueAt
// set new database query string
public
void
setQuery
(
String
query
)
throws
SQLException
,
IllegalStateException
{
// ensure database connection is available
if
(
!
connectedToDatabase
)
throw
new
IllegalStateException
(
"Not Connected to Database"
);
resultSet
=
statement
.
executeQuery
(
query
);
// execute query
metaData
=
resultSet
.
getMetaData
();
// get metadata
// determine number of rows in ResultSet
resultSet
.
last
();
// move to last row
numberOfRows
=
resultSet
.
getRow
();
// get row number
fireTableStructureChanged
();
// table model changed
}
// end method setQuery
// close Statement and Connection
public
void
disconnectFromDatabase
()
{
// close Statement and Connection
try
{
statement
.
close
();
connection
.
close
();
}
// end try
catch
(
SQLException
sqlException
)
{
sqlException
.
printStackTrace
();
}
// end catch
finally
// update database connection status
{
connectedToDatabase
=
false
;
}
// end finally
}
// end method disconnectFromDatabase
// return Connection
public
Connection
getConnection
()
{
return
connection
;
}
// end method getConnection
}
// end class ResultSetTableModel
/**************************************************************************
* (C) Copyright 1992-2012 by Deitel & Associates, Inc. and *
* Pearson Education, Inc. All Rights Reserved. *
* *
* DISCLAIMER: The authors and publisher of this book have used their *
* best efforts in preparing the book. These efforts include the *
* development, research, and testing of the theories and programs *
* to determine their effectiveness. The authors and publisher make *
* no warranty of any kind, expressed or implied, with regard to these *
* programs or to the documentation contained in these books. The authors *
* and publisher shall not be liable in any event for incidental or *
* consequential damages in connection with, or arising out of, the *
* furnishing, performance, or use of these programs. *
*************************************************************************/
ch28solutions/ex28_07/address.sql
DROP TABLE Addresses; CREATE TABLE Addresses ( AddressID INT NOT NULL GENERATED ALWAYS AS IDENTITY, FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (30) NOT NULL, Email VARCHAR (30) NOT NULL, PhoneNumber VARCHAR (15) NOT NULL ); INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber) VALUES ('Mike','Green','[email protected]','555-5555'), ('Mary','Brown','[email protected]','555-1234');
ch28solutions/ex28_07/AddressBook/db.lck
ch28solutions/ex28_07/AddressBook/log/log.ctrl
ch28solutions/ex28_07/AddressBook/log/log1.dat
ch28solutions/ex28_07/AddressBook/log/logmirror.ctrl
ch28solutions/ex28_07/AddressBook/seg0/c10.dat
ch28solutions/ex28_07/AddressBook/seg0/c101.dat
ch28solutions/ex28_07/AddressBook/seg0/c111.dat
ch28solutions/ex28_07/AddressBook/seg0/c121.dat
ch28solutions/ex28_07/AddressBook/seg0/c130.dat
ch28solutions/ex28_07/AddressBook/seg0/c141.dat
ch28solutions/ex28_07/AddressBook/seg0/c150.dat
ch28solutions/ex28_07/AddressBook/seg0/c161.dat
ch28solutions/ex28_07/AddressBook/seg0/c171.dat
ch28solutions/ex28_07/AddressBook/seg0/c180.dat
ch28solutions/ex28_07/AddressBook/seg0/c191.dat
ch28solutions/ex28_07/AddressBook/seg0/c1a1.dat
ch28solutions/ex28_07/AddressBook/seg0/c1b1.dat
ch28solutions/ex28_07/AddressBook/seg0/c1c0.dat
ch28solutions/ex28_07/AddressBook/seg0/c1d1.dat
ch28solutions/ex28_07/AddressBook/seg0/c1e0.dat
ch28solutions/ex28_07/AddressBook/seg0/c1f1.dat
ch28solutions/ex28_07/AddressBook/seg0/c20.dat
ch28solutions/ex28_07/AddressBook/seg0/c200.dat
ch28solutions/ex28_07/AddressBook/seg0/c211.dat
ch28solutions/ex28_07/AddressBook/seg0/c221.dat
ch28solutions/ex28_07/AddressBook/seg0/c230.dat
ch28solutions/ex28_07/AddressBook/seg0/c241.dat
ch28solutions/ex28_07/AddressBook/seg0/c251.dat
ch28solutions/ex28_07/AddressBook/seg0/c260.dat
ch28solutions/ex28_07/AddressBook/seg0/c271.dat
ch28solutions/ex28_07/AddressBook/seg0/c281.dat
ch28solutions/ex28_07/AddressBook/seg0/c290.dat
ch28solutions/ex28_07/AddressBook/seg0/c2a1.dat
ch28solutions/ex28_07/AddressBook/seg0/c2b1.dat
ch28solutions/ex28_07/AddressBook/seg0/c2c1.dat
ch28solutions/ex28_07/AddressBook/seg0/c2d0.dat
ch28solutions/ex28_07/AddressBook/seg0/c2e1.dat
ch28solutions/ex28_07/AddressBook/seg0/c2f0.dat
ch28solutions/ex28_07/AddressBook/seg0/c300.dat
ch28solutions/ex28_07/AddressBook/seg0/c31.dat
ch28solutions/ex28_07/AddressBook/seg0/c311.dat
ch28solutions/ex28_07/AddressBook/seg0/c321.dat
ch28solutions/ex28_07/AddressBook/seg0/c331.dat
ch28solutions/ex28_07/AddressBook/seg0/c340.dat
ch28solutions/ex28_07/AddressBook/seg0/c351.dat
ch28solutions/ex28_07/AddressBook/seg0/c361.dat
ch28solutions/ex28_07/AddressBook/seg0/c371.dat
ch28solutions/ex28_07/AddressBook/seg0/c380.dat
ch28solutions/ex28_07/AddressBook/seg0/c391.dat
ch28solutions/ex28_07/AddressBook/seg0/c3a1.dat
ch28solutions/ex28_07/AddressBook/seg0/c3b1.dat
ch28solutions/ex28_07/AddressBook/seg0/c3c0.dat
ch28solutions/ex28_07/AddressBook/seg0/c41.dat
ch28solutions/ex28_07/AddressBook/seg0/c51.dat
ch28solutions/ex28_07/AddressBook/seg0/c60.dat
ch28solutions/ex28_07/AddressBook/seg0/c71.dat
ch28solutions/ex28_07/AddressBook/seg0/c81.dat
ch28solutions/ex28_07/AddressBook/seg0/c90.dat
ch28solutions/ex28_07/AddressBook/seg0/ca1.dat
ch28solutions/ex28_07/AddressBook/seg0/cb1.dat
ch28solutions/ex28_07/AddressBook/seg0/cc0.dat
ch28solutions/ex28_07/AddressBook/seg0/cd1.dat
ch28solutions/ex28_07/AddressBook/seg0/ce1.dat
ch28solutions/ex28_07/AddressBook/seg0/cf0.dat
ch28solutions/ex28_07/AddressBook/service.properties
#C:\books\2006\jhtp7\solutions\ch25solutions_JDBC\ex25_07\AddressBook # ******************************************************************** # *** Please do NOT edit this file. *** # *** CHANGING THE CONTENT OF THIS FILE MAY CAUSE DATA CORRUPTION. *** # ******************************************************************** #Thu Apr 26 17:47:58 EDT 2007 SysschemasIndex2Identifier=225 SyscolumnsIdentifier=144 SysconglomeratesIndex1Identifier=49 SysconglomeratesIdentifier=32 SyscolumnsIndex2Identifier=177 SysschemasIndex1Identifier=209 SysconglomeratesIndex3Identifier=81 SystablesIndex2Identifier=129 SyscolumnsIndex1Identifier=161 derby.serviceProtocol=org.apache.derby.database.Database SysschemasIdentifier=192 derby.storage.propertiesId=16 SysconglomeratesIndex2Identifier=65 derby.serviceLocale=en_US SystablesIdentifier=96 SystablesIndex1Identifier=113
ch28solutions/ex28_07/AddressBookDisplay$1.class
synchronized class AddressBookDisplay$1 implements java.awt.event.ActionListener { void AddressBookDisplay$1(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$2.class
synchronized class AddressBookDisplay$2 implements java.awt.event.ActionListener { void AddressBookDisplay$2(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$3.class
synchronized class AddressBookDisplay$3 implements java.awt.event.ActionListener { void AddressBookDisplay$3(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$4.class
synchronized class AddressBookDisplay$4 implements java.awt.event.ActionListener { void AddressBookDisplay$4(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$5.class
synchronized class AddressBookDisplay$5 implements java.awt.event.ActionListener { void AddressBookDisplay$5(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$6.class
synchronized class AddressBookDisplay$6 implements java.awt.event.ActionListener { void AddressBookDisplay$6(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$7.class
synchronized class AddressBookDisplay$7 implements java.awt.event.ActionListener { void AddressBookDisplay$7(AddressBookDisplay); public void actionPerformed(java.awt.event.ActionEvent); }
ch28solutions/ex28_07/AddressBookDisplay$8.class
synchronized class AddressBookDisplay$8 extends java.awt.event.WindowAdapter { void AddressBookDisplay$8(AddressBookDisplay); public void windowClosing(java.awt.event.WindowEvent); }
ch28solutions/ex28_07/AddressBookDisplay.class
public synchronized class AddressBookDisplay extends javax.swing.JFrame { private Person currentEntry; private PersonQueries personQueries; private java.util.List results; private int numberOfEntries; private int currentEntryIndex; private javax.swing.JButton browseButton; private javax.swing.JLabel emailLabel; private javax.swing.JTextField emailTextField; private javax.swing.JLabel firstNameLabel; private javax.swing.JTextField firstNameTextField; private javax.swing.JLabel idLabel; private javax.swing.JTextField idTextField; private javax.swing.JTextField indexTextField; private javax.swing.JLabel lastNameLabel; private javax.swing.JTextField lastNameTextField; private javax.swing.JTextField maxTextField; private javax.swing.JButton nextButton; private javax.swing.JLabel ofLabel; private javax.swing.JLabel phoneLabel; private javax.swing.JTextField phoneTextField; private javax.swing.JButton previousButton; private javax.swing.JButton queryButton; private javax.swing.JLabel queryLabel; private javax.swing.JPanel queryPanel; private javax.swing.JPanel navigatePanel; private javax.swing.JPanel displayPanel; private javax.swing.JTextField queryTextField; private javax.swing.JButton insertButton; private javax.swing.JButton updateButton; public void AddressBookDisplay(); private void previousButtonActionPerformed(java.awt.event.ActionEvent); private void nextButtonActionPerformed(java.awt.event.ActionEvent); private void queryButtonActionPerformed(java.awt.event.ActionEvent); private void indexTextFieldActionPerformed(java.awt.event.ActionEvent); private void browseButtonActionPerformed(java.awt.event.ActionEvent); private void insertButtonActionPerformed(java.awt.event.ActionEvent); private void updateButtonActionPerformed(java.awt.event.ActionEvent); public static void main(String[]); }
ch28solutions/ex28_07/AddressBookDisplay.java
ch28solutions/ex28_07/AddressBookDisplay.java
// Fig. 25.33: AddressBookDisplay.java
// A simple address book
import
java
.
awt
.
event
.
ActionEvent
;
import
java
.
awt
.
event
.
ActionListener
;
import
java
.
awt
.
event
.
WindowAdapter
;
import
java
.
awt
.
event
.
WindowEvent
;
import
java
.
awt
.
FlowLayout
;
import
java
.
awt
.
GridLayout
;
import
java
.
util
.
List
;
import
javax
.
swing
.
JButton
;
import
javax
.
swing
.
Box
;
import
javax
.
swing
.
JFrame
;
import
javax
.
swing
.
JLabel
;
import
javax
.
swing
.
JPanel
;
import
javax
.
swing
.
JTextField
;
import
javax
.
swing
.
WindowConstants
;
import
javax
.
swing
.
BoxLayout
;
import
javax
.
swing
.
BorderFactory
;
import
javax
.
swing
.
JOptionPane
;
public
class
AddressBookDisplay
extends
JFrame
{
private
Person
currentEntry
;
private
PersonQueries
personQueries
;
private
List
<
Person
>
results
;
private
int
numberOfEntries
=
0
;
private
int
currentEntryIndex
;
private
JButton
browseButton
;
private
JLabel
emailLabel
;
private
JTextField
emailTextField
;
private
JLabel
firstNameLabel
;
private
JTextField
firstNameTextField
;
private
JLabel
idLabel
;
private
JTextField
idTextField
;
private
JTextField
indexTextField
;
private
JLabel
lastNameLabel
;
private
JTextField
lastNameTextField
;
private
JTextField
maxTextField
;
private
JButton
nextButton
;
private
JLabel
ofLabel
;
private
JLabel
phoneLabel
;
private
JTextField
phoneTextField
;
private
JButton
previousButton
;
private
JButton
queryButton
;
private
JLabel
queryLabel
;
private
JPanel
queryPanel
;
private
JPanel
navigatePanel
;
private
JPanel
displayPanel
;
private
JTextField
queryTextField
;
private
JButton
insertButton
;
private
JButton
updateButton
;
// no-argument constructor
public
AddressBookDisplay
()
{
super
(
"Address Book"
);
// establish database connection and set up PreparedStatements
personQueries
=
new
PersonQueries
();
// create GUI
navigatePanel
=
new
JPanel
();
previousButton
=
new
JButton
();
indexTextField
=
new
JTextField
(
2
);
ofLabel
=
new
JLabel
();
maxTextField
=
new
JTextField
(
2
);
nextButton
=
new
JButton
();
displayPanel
=
new
JPanel
();
idLabel
=
new
JLabel
();
idTextField
=
new
JTextField
(
10
);
firstNameLabel
=
new
JLabel
();
firstNameTextField
=
new
JTextField
(
10
);
lastNameLabel
=
new
JLabel
();
lastNameTextField
=
new
JTextField
(
10
);
emailLabel
=
new
JLabel
();
emailTextField
=
new
JTextField
(
10
);
phoneLabel
=
new
JLabel
();
phoneTextField
=
new
JTextField
(
10
);
queryPanel
=
new
JPanel
();
queryLabel
=
new
JLabel
();
queryTextField
=
new
JTextField
(
10
);
queryButton
=
new
JButton
();
browseButton
=
new
JButton
();
insertButton
=
new
JButton
();
updateButton
=
new
JButton
();
setLayout
(
new
FlowLayout
(
FlowLayout
.
CENTER
,
10
,
10
)
);
setSize
(
400
,
325
);
setResizable
(
false
);
navigatePanel
.
setLayout
(
new
BoxLayout
(
navigatePanel
,
BoxLayout
.
X_AXIS
)
);
previousButton
.
setText
(
"Previous"
);
previousButton
.
setEnabled
(
false
);
previousButton
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
evt
)
{
previousButtonActionPerformed
(
evt
);
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
navigatePanel
.
add
(
previousButton
);
navigatePanel
.
add
(
Box
.
createHorizontalStrut
(
10
)
);
indexTextField
.
setHorizontalAlignment
(
JTextField
.
CENTER
);
indexTextField
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
evt
)
{
indexTextFieldActionPerformed
(
evt
);
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
navigatePanel
.
add
(
indexTextField
);
navigatePanel
.
add
(
Box
.
createHorizontalStrut
(
10
)
);
ofLabel
.
setText
(
"of"
);
navigatePanel
.
add
(
ofLabel
);
navigatePanel
.
add
(
Box
.
createHorizontalStrut
(
10
)
);
maxTextField
.
setHorizontalAlignment
(
JTextField
.
CENTER
);
maxTextField
.
setEditable
(
false
);
navigatePanel
.
add
(
maxTextField
);
navigatePanel
.
add
(
Box
.
createHorizontalStrut
(
10
)
);
nextButton
.
setText
(
"Next"
);
nextButton
.
setEnabled
(
false
);
nextButton
.
addActionListener
(
new
ActionListener
()
{
public
void
actionPerformed
(
ActionEvent
evt
)
{
nextButtonActionPerformed
(
evt
);
}
// end method actionPerformed
}
// end anonymous inner class
);
// end call to addActionListener
navigatePanel
.
add
(
nextButton
);
add
(
navigatePanel
);
displayPanel
.
setLayout
(
new
GridLayout
(
5
,
2
,
4
,
4
)
);
idLabel
.
setText
(
"Address ID:"
);
displayPanel
.
add
(
idLabel
);
idTextField
.
setEditable
(
false
);
displayPanel
.
add
(
idTextField
);
firstNameLabel
.
setText
(
"First Name:"
);
displayPanel
.
add
(
firstNameLabel
);