Relational Databases

1
Chapter 23

• Relational Databases

2
CHAPTER GOALS

• To learn how to query a database with the
  Structured Query Language (SQL)
• To connect to databases with Java Database
  Connectivity (JDBC)
• To write database programs that insert, update,
  and query data in a relational database

3
Relational Database

• Stores information in tables
• Each table column has a name and data type

4
Product Table in a Relational Database
5
Product Table

• Each row corresponds to a product
• Column headers correspond to attributes of the
  product
• All items in the same column are the same data
  type

6
SQL

• SQL stands for Structured Query Language
• SQL is a command language for interacting with
  databases
• Most relational databases follow the SQL
  standard
• Some SQL commands
• o QUERY
• o CREATE TABLE
• o INSERT
• o DROP TABLE

7
Some SQL Types and Their Corresponding Java Types
previous start next
File Purse.java

previous start next
8
SQL to Create a Table

• CREATE TABLE Products
• Product_Code CHAR(11) Description
  CHAR(40) Unit_Price DECIMAL(10,2)

9
SQL to Add Data to a Database

• Use the INSERT command to insert rows into the
  table
• Issue one command for each row of the table
• INSERT INTO Products VALUES ('3554-0632-1', 'Hair
  dryer', 29.95)

10
Linking Tables

• Replicating the same data in multiple rows has
  two problems
• It is wasteful to store the same information
  multiple times
• If the data needs to change it has to be changed
  in multiple places
• Instead you should distribute the data over
  multiple tables

11
Poor Design for an Invoice Table with Replicated
Customer Data
12
Two Tables for Invoice and Customer Data
13
Linking Tables

• In the table above, customer data are not
  replicated
• Customer table contains a single record for each
  customer
• Invoice table has no customer data
• The two tables are linked by the Customer_Number
  field
• The customer number is a unique identifier

14
Linking Tables

• Primary key is a column (or combination of
  columns) whose value uniquely specifies a table
  record
• Foreign key is a reference to a primary key in a
  linked table
• Foreign keys need not be unique

15
Implementing One-to-Many Relationships
previous start next

• Each invoice may have many items
• Do not replicate columns, one for each item
• Do distribute the information in two tables,
  invoice and item
• Link each item to its invoice with an
  Invoice_Number foreign key in the item table

previous start next
16

• Poor Design for Invoice Table with Replicated
  Columns

17
Linked Invoice and Item Tables
18
Sample Database
19
Queries

• Once the database is constructed, we can query
  it for information
• o What are the names and addresses of all
  customers?
• o What are the names and addresses of all
  customers who live in California?
• o What are the names and addresses of all
  customers who buy toasters?
• o What are the names and addresses of all
  customers with unpaid invoices?

20
Simple Query

• Use the SQL SELECT statement to query a database
  
• The query to select all data from the Customer
  table
• SELECT FROM customer
• The outcome of a query is a view
• A view is a "window" through which you can see
  some of the database

21
Selecting Columns

• You may want to view only some of the columns in
  the table
• The query to select the city and state of all
  customers from the Customer table
• SELECT City, State FROM Customer

22
Selecting Subsets

• You can select rows that fit a particular
  criteria
• When you want to select a subset , use the WHERE
  clause
• The query to find all customers in California
• SELECT FROM Customer WHERE State 'CA'
• The query to select all customers NOT in
  California
• SELECT FROM Customer WHERE State ltgt 'CA'

23
Result of SELECT FROM Customer WHERE State
'CA'
24
Selecting Subsets

• You can match patterns with LIKE
• The right-hand side is a string that can contain
  special characters
• o Special symbol _ matches exactly one
  character
• o Special symbol matches any character
  sequence
• The expression to match all Name strings whose
  second character is an "o"
• Name LIKE '_o'

25
Selecting Subsets

• You can combine expressions with logical
  connectives AND, OR, NOT SELECT
• FROM Product
• WHERE Price lt 100
• AND Description ltgt 'Toaster'
• You can select both row and column subsets
• SELECT Name, City FROM Customer WHERE State
  'CA'

26
Calculations

• Use the count function to find out how many
  customers there are in California
• SELECT COUNT() FROM Customer WHERE State
  'CA'
• The means you want to calculate whole records
• Other functions are SUM, AVG, MAX, MIN
• These functions must access a specific column.
  Put the column name in parentheses SELECT
  AVG(Price) FROM Product

27

• Joins

• The information you want may be spread over
  multiple tables
• Use Product.Product_Code to specify the
  Product_Code column in the Product table
• Use Item.Product_Code to specify the
  Product_Code column in the Item table
• To select all invoices that include a car
  vacuum SELECT Item.Invoice_Number
• FROM Product, Item WHERE
  Product.Description 'Car vacuum' AND
  Product.Product_Code Item.Product_Code

28
Joins

• A query that involves multiple tables is a join
  
• When using a join, do these things
• List all tables that are involved in the query
  in the FROM clause
• Use the TableName.ColumnName syntax to refer to
  column names
• List all join conditions in the WHERE clause
  (TableName1.ColumnName1 TableName2.ColumnName2)
  

29

• Updating and Deleting Data

• The DELETE and UPDATE commands modify the
  database
• The DELETE command deletes rows
• To delete all customers in California
• DELETE FROM Customer WHERE State 'CA'

30

• Updating and Deleting Data

• The UPDATE query lets you update columns of all
  records that fulfill a certain condition
• To add one to the quantity of every item in
  invoice number 11731
• UPDATE Item
• SET Quantity Quantity 1
• WHERE Invoice_Number '11731'
• Both UPDATE and DELETE return the number of
  rows updated or deleted

31

• File TestDB.java

01 import java.sql.Connection 02 import
java.sql.ResultSet 03 import java.sql.Statement
04 05 / 06 Tests a database
installation by creating and querying 07 a
sample table. Call this program as 08 java
-classpath driver_class_path. TestDB
database.properties 09 / 10 public class
TestDB 11 12 public static void
main(String args) throws Exception 13
14 if (args.length 0) 15
16 System.out.println( 17
"Usage TestDB propertiesFile") 18
System.exit(0) 19
32

• 20 else
• 21 SimpleDataSource.init(args0)
• 22
• 23 Connection conn SimpleDataSource.getCo
  nnection()
• 24
• 25 Statement stat conn.createStatement()
  
• 26
• 27 stat.execute("CREATE TABLE Test (Name
  CHAR(20))")
• 28 stat.execute("INSERT INTO Test VALUES
  ('Romeo')")
• 29
• 30 ResultSet result stat.executeQuery("SE
  LECT FROM Test")
• 31 result.next()
• 32 System.out.println(result.getString("Nam
  e"))
• 33 result.close()
• 34
• 35 stat.execute("DROP TABLE Test")
• 36
• 37 stat.close()
• 38 conn.close()

33

• File SimpleDataSource.java

01 import java.sql.Connection 02 import
java.sql.DriverManager 03 import
java.sql.SQLException 04 import
java.io.FileInputStream 05 import
java.io.IOException 06 import
java.util.Properties 07 08 / 09 A
simple data source for getting database
connections. 10 / 11 public class
SimpleDataSource 12 13 / 14
Initializes the data source. 15 _at_param
fileName the name of the property file that 16
contains the database driver, url, username
and password 17 / 18 public static void
init(String fileName)
34

• 19 throws IOException, ClassNotFoundExcepti
  on
• 20
• 21 Properties props new Properties()
• 22 FileInputStream in new
  FileInputStream(fileName)
• 23 props.load(in)
• 24
• 25 String driver props.getProperty("jdbc.
  driver")
• 26 url props.getProperty("jdbc.url")
• 27 String username props.getProperty("jdb
  c.username")
• 28 String password props.getProperty("jdb
  c.password")
• 29
• 30 Class.forName(driver)
• 31
• 32
• 33 /
• 34 Gets a connection to the database.
• 35 _at_return the database connection
• 36 /

35

• 37 public static Connection getConnection()
  throws SQLException
• 38
• 39 return DriverManager.getConnection(url,
  
• 40 username, password)
• 41
• 42
• 43 private static String url
• 44 private static String username
• 45 private static String password
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55

36

• File database.properties

1 jdbc.driverCOM.cloudscape.core.JDBCDriver 2
jdbc.urljdbccloudscapebigjavacreatetrue 3
jdbc.username 4 jdbc.password
37

Connecting to the DataBase

• Use a Connection object to access a database
  from a Java program
• Load the database driver
• Ask the DriverManager for a connection
• When you are done, close the database connection
  
• String driver ...
• String url ...
• String username ...
• String password ...
• Class.forName(driver) //load driver
• Connection conn DeviceManager.getConnection
  (url, username, password)
• //database commands
• conn.close()

38
Connecting to the Database

• Decouple connection management from the other
  database code
• You can use the SimpleDataSource class to do
  this
• Call its static init method with the name of the
  database configuration file
• Configuration file is a text file containing
  four lines
• jdbc.driver ...
• jdbc.url ...
• jdbc.username ...
• jdbc.password ...

39
Connecting to the Database

• The init method uses the Properties class to read
  the file
• Properties class has a load method to read a file
  of key/value pairs from a stream
• Properties props new Properties()
  FileInputStream in new FileInputStream(fileName)
  props.load(in)
• The getProperty method returns the value of a
  given key
• String driver props.getProperty("jdbc.driver"
  )
• Now when you need a connection call
• Connection conn SimpleDataSource.getConnectio
  n()

40
Executing SQL Statements

• A Connection object can create Statement objects
  Statement stat conn.createStatement()
• The execute method of the Statement class
  executes a SQL statement
• stat.execute("CREATE TABLE Test (Name
  CHAR(20))")
• stat.execute("INSERT INTO Test VALUES
  ('Romeo')")
• Use executeQuery method of the Statement class to
  issue a query
• String queryStatement "SELECT FROM Test"
• ResultSet result stat.executeQuery(queryStateme
  nt)
• The result of a SQL query is returned in a
  ResultSet object

41
Executing SQL Statements

• Use the executeUpdate method of the Statement
  class to execute a UPDATE statement
• The method returns the number of rows effected
• String updateStatement "UPDATE Item
• "SET Quantity Quantity 1"
• "WHERE Invoice_Number '11731'"
• int count stat.executeUpdate(updateStatement)

42
Executing SQL Statements

• You can also use a generic execute statement to
  execute queries
• It returns a boolean value to indicate whether
  the SQL command yields a result set
• If there is a result set, you can get it with
  the getResultSet method
• If there is no result set, you can get the
  update count with the getUpdateCount method
• String command . . .
• boolean hasResultSet stat.execute(command)
• if (hasResultSet)
• ResultSet result stat.getResultSet()
• . . .
• else
• int count stat.getUpdateCount()
• . . .

43
Executing SQL Statements

• You can reuse a Statement object to execute new
  SQL commands
• Each Statement object should only have one active
  ResultSet
• If you need to look at multiple result sets at
  the same time, create multiple Statement objects
• Close the current ResultSet before issuing a new
  query on the same statement
• result.close()
• When you are done with a Statement object, close
  it
• That will also close the ResultSet
• stat.close()

44
Analyzing Query Results

• Use the next method of the ResultSet to iterate
  through the query results a row at a time
• When a result set is first returned from an
  executeQuery, no row data are available
• Use the next method to move to the first row
• The next method returns a boolean value
  indicating whether more rows of data are
  available
• while (result.next())
• //inspect column data from the current row

45
Analyzing Query Results

• To get the column values from a row, use one of
  the various get methods
• There are two get methods for each type of data
  (string, integer, double ...)
• One uses an integer parameter that indicates
  the column position
• Column positions start at 1
• String productCode result.getString(1)

46
Analyzing Query Results
import java.io.IOException import
java.net.ServerSocket import java.net.Socket
/ A server that executes the Simple Bank
Access Protocol. / public class BankServer
public static void main(String args )
throws IOException final int
ACCOUNTS_LENGTH 10 Bank bank new
Bank(ACCOUNTS_LENGTH) final int SBAP_PORT
8888 ServerSocket server new
ServerSocket(SBAP_PORT)
System.out.println("Waiting for clients to
connect...")
import java.io.IOException import
java.net.ServerSocket import java.net.Socket
/ A server that executes the Simple Bank
Access Protocol. / public class BankServer
public static void main(String args )
throws IOException final int
ACCOUNTS_LENGTH 10 Bank bank new
Bank(ACCOUNTS_LENGTH) final int SBAP_PORT
8888 ServerSocket server new
ServerSocket(SBAP_PORT)
System.out.println("Waiting for clients to
connect...")

• The other type of get method uses a string
  parameter for the column name
• String productCode result.getString("Product_
  Code")
• Use getInt to fetch an integer column value
• int quantity result.getInt("Quantity")
• Use getDouble to fetch an double column value
• double unitPrice result.getDouble("Price")

47
Result Set Meta Data

• Result set meta data describes the properties of
  the result set
• Use the ResultSetMetaData class to find out the
  column names in an unknown table
• You can get the meta data object form the result
  set
• ResultSetMetaData metaData
  result.getMetaData()

48
Result Set Meta Data

• Use getColumnCount method to get the number of
  columns
• Use getColumnLabel method to get the name of each
  column
• Use getColumnDisplaySize method to get the column
  width
• for (int i 1) i lt metaData.getColumnCount()
  i)
• String columnName metaData.getColumnLabel
  (i)
• int columnSize metaData.getColumnDisplayS
  ize(i)
• . . .

49
Result Set Meta Data

• The following program reads a file containing SQL
  statements and executes them
• If there is a result set, the result set is
  printed
• Meta data from the result set is used to
  determine the column count and column label
• Run the program as
• java ExecSQL database.properties product.sql
• Or interactively as
• java ExecSQL database.properties

50
File cityzone.sql
1 CREATE TABLE CityZone (City CHAR(30), Zone
CHAR(45)) 2 INSERT INTO CityZone VALUES ('San
Francisco', 'America/Los_Angeles') 3 INSERT
INTO CityZone VALUES ('Kaoshiung',
'Asia/Taipei') 4 SELECT FROM CityZone
51
File ExecSQL.java

• 001 import java.net.
• 002 import java.sql.
• 003 import java.io.
• 004 import java.util.
• 005
• 006 class ExecSQL
• 007
• 008 /
• 009 Executes all SQL statements in a file
• 010 _at_param args
• 011 ltulgt
• 012 ltligtargs0 the property file for the
  database connectionlt/ligt
• 013 ltligtargs1 the file with SQL
  statementslt/ligt
• 014 lt/ulgt
• 015 /
• 016 public static void main (String args)
• 017

52

• 018 try
• 019
• 020 if (args.length 0)
• 021
• 022 System.out.println
• 023 ("Usage ExecSQL propertyFile
  statementFile")
• 024 System.exit(0)
• 025
• 026 Connection con getConnection(args
  0)
• 027 Statement stmt con.createStatement
  ()
• 028
• 029
• 030 String tableName ""
• 031
• 032 Reader reader
• 033 if (args.length gt 1)
• 034 reader new FileReader(args1)
  
• 035 else
• 036 reader new InputStreamReader(Sy
  stem.in)

53

• 038
• 039
• 040 String line
• 041 while ((line in.readLine()) !
  null)
• 042
• 043 boolean hasResultSet
  stmt.execute(line)
• 044 if (hasResultSet)
• 045 showResultSet(stmt)
• 046
• 047
• 048 in.close()
• 049 stmt.close()
• 050 con.close()
• 051
• 052 catch (SQLException ex)
• 053
• 054 System.out.println
  ("SQLException")
• 055 while (ex ! null)
• 056

54

• 058 ex.getSQLState())
• 059 System.out.println ("Message "
• 060 ex.getMessage())
• 061 System.out.println ("Vendor "
• 062 ex.getErrorCode())
• 063 ex ex.getNextException()
• 064 System.out.println ("")
• 065
• 066
• 067 catch (IOException ex)
• 068
• 069 System.out.println("IOException "
  ex)
• 070 ex.printStackTrace ()
• 071
• 072
• 073
• 074 /
• 075 Opens a database connection
• 076 _at_param fileName the name of the
  property file that contains the

55

• 078 _at_return the connection to the database
• 079 /
• 080 public static Connection
  getConnection(String fileName)
• 081 throws SQLException, IOException
• 082
• 083 Properties props new Properties()
• 084 FileInputStream in new
  FileInputStream(fileName)
• 085 props.load(in)
• 086
• 087 String drivers props.getProperty("jdb
  c.drivers")
• 088 if (drivers ! null)
• 089 System.setProperty("jdbc.drivers",
  drivers)
• 090 String url props.getProperty("jdbc.ur
  l")
• 091 String username props.getProperty("jd
  bc.username")
• 092 String password props.getProperty("jd
  bc.password")
• 093
• 094 return
• 095 DriverManager.getConnection(url,
  username, password)
• 096

56

• 098 public static void showResultSet(Statement
  stmt)
• 099 throws SQLException
• 100
• 101 ResultSet rs stmt.getResultSet()
• 102 ResultSetMetaData rsmd
  rs.getMetaData()
• 103 int columnCount rsmd.getColumnCount()
  
• 104 while (rs.next())
• 105
• 106 for (int i 1 i lt columnCount
  i)
• 107
• 108 if (i gt 1) System.out.print(",
  ")
• 109 System.out.print(rs.getString(i))
  
• 110
• 111 System.out.println()
• 112
• 113 rs.close()
• 114
• 115

57
Case Study A Bank Database

• This is a reimplementation of the ATMSimulation
• In the simulation each customer has a customer
  number, a PIN, a checking account and a savings
  account
• The data will be stored in two tables in a
  database

58
Tables for ATMSimulation
59
Case Study A Bank Database

• The Bank class needs to connect to the database
  whenever it is asked to find a customer
• Its find method
• Connects to the database
• Selects the customer with the given account
  number
• Verifies the PIN
• Creates an customer object from the database
  information

60
Case Study A Bank Database
public Customer find(int customerNumber, int
pin) throws SQLException Customer c
null Connection conn SimpleDataSource.getCon
nection() Statement stat conn.createStatemen
t() ResultSet result stat.executeQuery("SELE
CT " " FROM Customer WHERE
Customer_Number " customerNumber)
61

• if (result.next() pin result.getInt("PIN"
  ))
• c new Customer(customerNumber,
• result.getInt("Checking_Account_Number"),
  
• result.getInt("Savings_Account_Number"))
  
• result.close()
• stat.close()
• conn.close()
• return c

62
Case Study A Bank Database

• The BankAccount methods are different now
• The getBalance method gets the balance from the
  data base
• The withdraw and deposit methods update the
  database immediately

63
File ATMSimulation.java
01 import javax.swing.JFrame 02 03 / 04
A simulation of an automatic teller machine 05
/ 06 public class ATMSimulation 07 08
public static void main(String args) 09
10 if (args.length 0) 11
12 System.out.println( 13
"Usage ATMSimulation propertiesFile") 14
System.exit(0) 15 16 else
64
17 18 try 19 20
SimpleDataSource.init(args0) 21
22 catch (Exception ex) 23
24 ex.printStackTrace() 25
System.exit(0) 26 27
28 29 JFrame frame new ATM() 30
frame.setTitle("First National Bank of
Java") 31 frame.setDefaultCloseOpera
tion(JFrame.EXIT_ON_CLOSE) 32
frame.pack() 33 frame.show() 34
35
65
File ATM.java

• 001 import java.awt.Container
• 002 import java.awt.FlowLayout
• 003 import java.awt.GridLayout
• 004 import java.awt.event.ActionEvent
• 005 import java.awt.event.ActionListener
• 006 import java.io.FileInputStream
• 007 import java.io.IOException
• 008 import java.sql.SQLException
• 009 import java.util.Properties
• 010 import javax.swing.JButton
• 011 import javax.swing.JFrame
• 012 import javax.swing.JOptionPane
• 013 import javax.swing.JPanel
• 014 import javax.swing.JTextArea
• 015
• 016 /
• 017 A frame displaying the components of an
  ATM

66

• 018 /
• 019 class ATM extends JFrame
• 020
• 021 /
• 022 Constructs the user interface of the
  ATM application.
• 023 /
• 024 public ATM()
• 025
• 026 theBank new Bank()
• 027
• 028 // construct components
• 029
• 030 pad new KeyPad()
• 031
• 032 display new JTextArea(4, 20)
• 033
• 034 aButton new JButton(" A ")
• 035 aButton.addActionListener(new
  AButtonListener())
• 036

67

• 038 bButton.addActionListener(new
  BButtonListener())
• 039
• 040 cButton new JButton(" C ")
• 041 cButton.addActionListener(new
  CButtonListener())
• 042
• 043 // add components to content pane
• 044
• 045 JPanel buttonPanel new JPanel()
• 046 buttonPanel.setLayout(new
  GridLayout(3, 1))
• 047 buttonPanel.add(aButton)
• 048 buttonPanel.add(bButton)
• 049 buttonPanel.add(cButton)
• 050
• 051 Container contentPane
  getContentPane()
• 052 contentPane.setLayout(new
  FlowLayout())
• 053 contentPane.add(pad)
• 054 contentPane.add(display)
• 055 contentPane.add(buttonPanel)
• 056 try

68

• 058 setNextState(START_STATE)
• 059
• 060 catch (SQLException exception)
• 061
• 062 JOptionPane.showMessageDialog(null,
  
• 063 exception.getMessage())
• 064
• 065
• 066
• 067 /
• 068 Sets the current customer number to
  the keypad value
• 069 and sets state to PIN.
• 070 /
• 071 public void setCustomerNumber() throws
  SQLException
• 072
• 073 customerNumber (int)pad.getValue()
• 074 setNextState(PIN_STATE)
• 075
• 076

69

• 078 Gets PIN from keypad, finds customer
  in bank.
• 079 If found sets state to ACCOUNT, else
  to START.
• 080 /
• 081 public void selectCustomer() throws
  SQLException
• 082
• 083 int pin (int)pad.getValue()
• 084 currentCustomer
• 085 theBank.find(customerNumber,
  pin)
• 086 if (currentCustomer null)
• 087 setNextState(START_STATE)
• 088 else
• 089 setNextState(ACCOUNT_STATE)
• 090
• 091
• 092 /
• 093 Sets current account to checking or
  savings. Sets
• 094 state to TRANSACT
• 095 _at_param account one of CHECKING_ACCOUNT
  or SAVINGS_ACCOUNT
• 096 /

70

• 098
• 099 if (account CHECKING_ACCOUNT)
• 100 currentAccount
• 101 currentCustomer.getCheckingAcco
  unt()
• 102 else
• 103 currentAccount
• 104 currentCustomer.getSavingsAccou
  nt()
• 105 setNextState(TRANSACT_STATE)
• 106
• 107
• 108 /
• 109 Withdraws amount typed in keypad from
  current account.
• 110 Sets state to ACCOUNT.
• 111 /
• 112 public void withdraw() throws
  SQLException
• 113
• 114 currentAccount.withdraw(pad.getValue())
  
• 115 setNextState(ACCOUNT_STATE)
• 116

71

• 118 /
• 119 Deposits amount typed in keypad to
  current account.
• 120 Sets state to ACCOUNT.
• 121 /
• 122 public void deposit() throws SQLException
• 123
• 124 currentAccount.deposit(pad.getValue())
  
• 125 setNextState(ACCOUNT_STATE)
• 126
• 127
• 128 /
• 129 Sets state and updates display
  message.
• 130 _at_param state the next state
• 131 /
• 132 public void setNextState(int newState)
• 133 throws SQLException
• 134
• 135 state newState
• 136 pad.clear()

72

• 138 display.setText("Enter customer
  number\nA OK")
• 139 else if (state PIN_STATE)
• 140 display.setText("Enter PIN\nA
  OK")
• 141 else if (state ACCOUNT_STATE)
• 142 display.setText("Select Account\n"
• 143 "A Checking\nB Savings\nC
  Exit")
• 144 else if (state TRANSACT_STATE)
• 145 display.setText("Balance "
• 146 currentAccount.getBalance()
• 147 "\nEnter amount and select
  transaction\n"
• 148 "A Withdraw\nB Deposit\nC
  Cancel")
• 149
• 150
• 151 private class AButtonListener implements
  ActionListener
• 152
• 153 public void actionPerformed(ActionEvent
  event)
• 154
• 155 try
• 156

73

• 158 setCustomerNumber()
• 159 else if (state PIN_STATE)
• 160 selectCustomer()
• 161 else if (state ACCOUNT_STATE)
• 162 selectAccount(CHECKING_ACCOUNT
  )
• 163 else if (state
  TRANSACT_STATE)
• 164 withdraw()
• 165
• 166 catch (SQLException exception)
• 167
• 168 JOptionPane.showMessageDialog(nul
  l,
• 169 exception.getMessage())
• 170
• 171
• 172
• 173
• 174 private class BButtonListener implements
  ActionListener
• 175
• 176 public void actionPerformed(ActionEvent
  event)

74

• 178 try
• 179
• 180 if (state ACCOUNT_STATE)
• 181 selectAccount(SAVINGS_ACCOUNT)
  
• 182 else if (state
  TRANSACT_STATE)
• 183 deposit()
• 184
• 185 catch (SQLException exception)
• 186
• 187 JOptionPane.showMessageDialog(nul
  l,
• 188 exception.getMessage())
• 189
• 190
• 191
• 192
• 193 private class CButtonListener implements
  ActionListener
• 194
• 195 public void actionPerformed(ActionEvent
  event)
• 196

75

• 198
• 199 if (state ACCOUNT_STATE)
• 200 setNextState(START_STATE)
• 201 else if (state
  TRANSACT_STATE)
• 202 setNextState(ACCOUNT_STATE)
• 203
• 204 catch (SQLException exception)
• 205
• 206 JOptionPane.showMessageDialog(nul
  l,
• 207 exception.getMessage())
• 208
• 209
• 210
• 211
• 212 private int state
• 213 private int customerNumber
• 214 private Bank theBank
• 215 private Customer currentCustomer
• 216 private BankAccount currentAccount

76

• 218 private JButton aButton
• 219 private JButton bButton
• 220 private JButton cButton
• 221
• 222 private KeyPad pad
• 223 private JTextArea display
• 224
• 225 private static final int START_STATE 1
• 226 private static final int PIN_STATE 2
• 227 private static final int ACCOUNT_STATE
  3
• 228 private static final int TRANSACT_STATE
  4
• 229
• 230 private static final int CHECKING_ACCOUNT
  1
• 231 private static final int SAVINGS_ACCOUNT
  2
• 232

77
File Bank.java
01 import java.sql.Connection 02 import
java.sql.ResultSet 03 import java.sql.Statement
04 import java.sql.SQLException 05 06
/ 07 A bank consisting of multiple bank
accounts. 08 / 09 public class Bank 10 11
/ 12 Finds a customer with a given
number and PIN. 13 _at_param conn the
database connection 14 _at_param
customerNumber the customer number 15
_at_param pin the personal identification number 16
_at_return the matching customer, or null if
none found 17 /
78
18 public Customer find(int customerNumber,
int pin) 19 throws SQLException 20
21 Customer c null 22
Connection conn SimpleDataSource.getConnection()
23 Statement stat conn.createStatement(
) 24 ResultSet result
stat.executeQuery("SELECT " 25 "
FROM Customer WHERE Customer_Number " 26
customerNumber) 27 28 if
(result.next() pin result.getInt("PIN"))
29 c new Customer(customerNumber, 30
result.getInt("Checking_Account_Numbe
r"), 31 result.getInt("Savings_Accoun
t_Number")) 32 result.close() 33
stat.close() 34 conn.close() 35
return c 36 37
79
File BankAccount.java

• 01 import java.sql.Connection
• 02 import java.sql.ResultSet
• 03 import java.sql.Statement
• 04 import java.sql.SQLException
• 05
• 06 /
• 07 A bank account has a balance that can be
  changed by
• 08 deposits and withdrawals.
• 09 /
• 10 public class BankAccount
• 11
• 12 /
• 13 Constructs a bank account with a given
  balance.
• 14 _at_param anAccountNumber the account
  number
• 15 /
• 16 public BankAccount(int anAccountNumber)
• 17

80
18 accountNumber anAccountNumber 19
20 21 / 22 Deposits money into a
bank account. 23 _at_param accountNumber the
account number 24 _at_param amount the amount
to deposit 25 / 26 public void
deposit(double amount) 27 throws
SQLException 28 29 Connection conn
SimpleDataSource.getConnection() 30
Statement stat conn.createStatement() 31
stat.execute("UPDATE Account" 32 "
SET Balance Balance " amount 33
" WHERE Account_Number " accountNumber)
34 stat.close() 35
conn.close() 36 37
81
38 / 39 Withdraws money from a bank
account. 40 _at_param accountNumber the
account number 41 _at_param amount the amount
to withdraw 42 / 43 public void
withdraw(double amount) 44 throws
SQLException 45 46 Connection conn
SimpleDataSource.getConnection() 47
Statement stat conn.createStatement() 48
stat.execute("UPDATE Account" 49 "
SET Balance Balance - " amount 50
" WHERE Account_Number " accountNumber)
51 stat.close() 52
conn.close() 53 54 55 / 56
Gets the balance of a bank account. 57
_at_param accountNumber the account number
82
58 _at_return the account balance 59
/ 60 public double getBalance() 61
throws SQLException 62 63 double
balance 0 64 Connection conn
SimpleDataSource.getConnection() 65
Statement stat conn.createStatement() 66
ResultSet result stat.executeQuery("SELECT
Balance" 67 " FROM Account WHERE
Account_Number " 68
accountNumber) 69 if (result.next()) 70
balance result.getDouble(1) 71
result.close() 72 stat.close() 73
conn.close() 74 return balance 75
76 77 private int accountNumber 78
83
File Customer.java
01 / 02 A bank customer with a checking
and savings account. 03 / 04 public class
Customer 05 06 / 07 Constructs
a customer with a given number and PIN. 08
_at_param aCustomerNumber the customer number 09
_at_param checkingAccountNumber the checking
account number 10 _at_param
savingsAccountNumber the savings account
number 11 / 12 public Customer(int
aCustomerNumber, 13 int
checkingAccountNumber, int savingsAccountNumber) 1
4 15 customerNumber
aCustomerNumber 16 checkingAccount new
BankAccount(checkingAccountNumber) 17
savingsAccount new BankAccount(savingsAccountNum
ber)
84
18 19 20 / 21 Gets the
checking account of this customer. 22
_at_return the checking account 23 / 24
public BankAccount getCheckingAccount() 25
26 return checkingAccount 27 28
29 / 30 Gets the savings account
of this customer. 31 _at_return the checking
account 32 / 33 public BankAccount
getSavingsAccount() 34 35 return
savingsAccount 36 37
85
38 private int customerNumber 39 private
BankAccount checkingAccount 40 private
BankAccount savingsAccount 41
86
Transactions

• A transaction is a set of database updates that
  should either succeed in their entirety or not
  happen at all
• If you transfer money from one account to
  another, you want both the withdraw and deposit
  to happen or neither
• Use COMMIT and ROLLBACK commands to manage
  transactions
• To transfer money from one account to another
• UPDATE Account SET Balance Balance - 1000
• WHERE Account_Numb