Chapter 15 Creating More Complex Database Applications

1
Chapter 15Creating More Complex Database
Applications
2
One to One Relationships
15

• There is a one-to-one relationship between
  Customer and Boat
• A customer always owns exactly one boat and a
  boat always belongs to just one customer
• Use a database to implement the one-to-one
  association using a relational database

3
Understanding Tables in CustomerAndBoatDatabase
15
4
Understanding Tables in CustomerAndBoatDatabase
15
5
Understanding Tables in CustomerAndBoatDatabase
15
6
Understanding Tables in CustomerAndBoatDatabase
7
Understanding Tables in CustomerAndBoatDatabase
15
8
Using SQL to Join Table in Database
15

• SELECT StateRegistration, Manufacturer,
  CustomerName, PhoneNoFROM BoatTable,
  CustomerTableWHERE CustomerPhoneNo PhoneNo

9
Using SQL to Join Table in Database
15

• SELECT StateRegistration, Manufacturer,
  CustomerName, PhoneNoFROM BoatTable,
  CustomerTableWHERE CustomerPhoneNo PhoneNoAND
  StateRegistrationNo MO98765

10
Establishing a Common Connection

• CustomerAndBoatDatabaseConnect
• To manage the connection to the database
• - initialize method
• - terminate method

11
Initialize method of the CustomerAndBoatDatabaseCo
nnect class
12
Terminate method

• public static void terminate( )
• try
• aConnection.close( )
• catch (SQLException e)
• System.out.println(e)

13
Modifying the Customer PD

• Include a boat reference attribute
• Initialize boat reference to null in constructor
• Include setter and getter methods to set and
  retrieve boat reference
• Use the common database connection
  CustomerAndBoatDatabaseConnect

14
Customer with Boat reference attribute and DA
methods (i)
15
Customer with Boat reference attribute and DA
methods (ii)
16
The BoatDA Class
15

• find, addNew, delete, getAll and update methods
• terminate and initial methods
• Defines a boat reference variable
• a Vector of boat reference variables

17
Initialize Terminate methods of the BoatDA
18
The BoatDA Class find and getAll Methods
15

• The find method defines a SELECT statement that
  retrieves a particular record from BoatTable
• throws NotFoundException if record is not found
• The getAll method returns a Vector of boat
  references

19

• public static Boat find(String key) throws
  NotFoundException
• aBoat null
• String sqlQuery SELECT StateRegistrationNo,
  BoatLength,
• Manufacturer,
  Year
• FROM BoatTable
• WHERE
  StateRegistrationNo key
• try
• ResultSet rs aStatement.executeQuery(sqlQ
  uery)
• boolean gotIt rs.next()
• if (gotIt)
• stateRegistrationNo rs.getString(1)
• length rs.getString(2)
• manufacturer rs.getString(3)
• year rs.getInt(4)
• aBoat new Boat(stateRegistrationNo,
  length, manufacturer, year)
• else throw (new NotFoundException(not
  found))
• rs.close()
• return aBoat

20
Boat getAll method
21

• Public static void addNew(Boat aBoat) throws
  DuplicateException
• stateRegistrationNo aBoat.getStateRegistration
  No() length aBoat.getLength()
• manufacturer aBoat.getManufacturer() year
  aBoat.getYear()
• phoneNo aBoat.getCustomer().getPhoneNo()
• String sqlInsert INSERT INTO BoatTable
  (StrateRegistration, BoatLength, Manufacturer,
  Year, CustomerPhoneNo) VALUES (
• stateRegistrationNo , length ,
  manufacturer , year
• , phoneNo )
• try Boat b find(stateRegistrationNo)
• throw (new DuplicateException(Boat
  Exists))
• catch (NotFoundException e)
• try int result aStatement.executeUpd
  ate(sqlInsert)
• catch (SQLException ee)
• System.out.println(ee)

22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
Modifying the Boat Class to Work with BoatDA
15

• The Boat class needs four static methods
• initialize
• find
• getAll
• terminate
• Three instance methods
• addNew
• update
• delete

26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
Modifying the CustomerDA Class
15

• Change the CustomerDA class to support joining
  information from the BoatTable and CustomerTable
• Now includes a reference to a Boat object

30
CustomerDA Class find and getAll Methods

• Change the SQL statement WHERE clause to specify
  both the join condition and the primary key for
  the customer of interest

31
(No Transcript)
32
CustomerDA Class find and getAll Methods
33
CustomerDA Class addNew Method

• The is a mandatory one-to-one relationship
  between Customer and Boat
• Must be a customer record for every boat record
  the addNew method must enforce this requirement
• After inserting a customer record into
  CustomerTable, a record must be inserted into the
  BoatTable

34
addNew method for CustomerDA class
35
CustomerDA Class delete and update Methods
15

• When a customer record is deleted, the
  corresponding boat record must be deleted as well
• The update method is unchanged

36
Delete method of CustomerDA class
37
Testing the CustomerAndBoatDatabase Application
15

• public class TesterOne
• static Customer aCustomer
• public static void main(String args )
• Vector customers Boat aBoat
• Connection c CustomerAndBoatDatabaseConnec
  t.initialize()
• Customer.initialize( c )
• Boat.initialize( c )
• try aCustomer Customer.find(123-4567)
  
• printDetails( )
• try customers Customer.getAll()
• for(int I 0 I lt customers.size()
  i)
• aCustomer (Customer)
  customers.get(i)
• printDetails()
• aCustomer new Customer(Ed, KC,
  339-4990)
• aBoat new Boat(MO1122233, 25, S-2,
  1984)
• aBoat.assignBoatToCustomer(aCustomer)
• try aCustomer.addNew()
• try aCustomer Customer.find(339-4990)
  
• aCustomer.delete()

38
One-To-Many Relationship
15

• The relationship between Dock and Slip
• A dock contains many slips
• A slip belongs to one dock

39
Understanding the Tables in DockAndSlipDatabase
40
Understanding the Tables in DockAndSlipDatabase
41
Understanding the Tables in DockAndSlipDatabase
42
Common Connection to DockAndSlipDatabase
15

• Same as CustomerAndBoatDatabaseConnect class
  except
• Different data source is used

43
Modifying the Dock and Slip Classes
15

• Dock class must be modified to
• Implement the initialize, terminate, find, and
  getAll methods
• Dont need to modify the Slip class nor create a
  SlipDA class

44
Dock class has Slips (i)
45
Dock class has Slips (ii)
46
Introducing the DockDA Class
15

• Methods used to terminate and initialize are
  similar to other DA classes

47
DockDA Class find Method
15
48
DockDA Class find Method

• public static Dock find(int key) throws
  NotFoundException
• aDock null
• String sqlQuery SELECT DockTable.DockId,
  Location, Electricity, Water, SlipNo, Width,
  SlipLength FROM DockTable, SlipTable WHERE
  DockTable.DockId key
• AND SlipTable.DockId DockTable.DockId
  ORDER BY SlipNo
• try ResultSet rs aStatement.executeQuery(sql
  Query)
• boolean dockCreated false
• boolean more rs.next()
• while (more)
• if (dockCreated false)
• dockId rs.getInt(1) location
  rs.getString(2) electricityYN rs.getInt(3)
• waterYN rs.getInt(4)
• electricity
  integerToBoolean(electricityYN) // converts
  integers into booleans
• water integerToBoolean(waterY
  N)
• aDock new Dock(dockId,
  location, electricity, water)
• dockCreated true
• slipNo rs.getInt(5) width
  rs.getInt(6) slipLength rs.getDouble(7)
• aSlip new Slip(slipNo, width,
  slipLength, aDock) //set up the Slips
• more rs.next()
• return aDock

49
DockDA Class getAll Method
15
50
getAll method for DockDA

• Public static Vector getAll( )
• Vector docks new Vector( )
• String sqlQuery SELECT DockTable.DockId,
  Location, Electricity, Water, SlipNo, Width,
  SlipLength, FROM DockTable, SlipTable Where
  SlipTable.DockId DockTable.DockId ORDER BY
  DockTable.DockId, SlipNo

51
Control-break logic in the getAll method of the
DockDA class
52
TesterTwo illustrate Dock has slips association
(i)
53
TesterTwo illustrate Dock has slips association
(ii)
54
Testing the DockAndSlipDatabase Application
55
Implementing an Association Class

• The Lease class has associated AnnualLease and
  DailyLease subclasses

56
Understanding Tables in CustomerLeaseSlipDatabase
57
Understanding Tables in CustomerLeaseSlipDatabase
15
58
Modifying the Customer Class
15

• Need to add a lease reference to the CustomerPD
  class
• Include getter and setter methods for the lease
  reference

59
Customer with Lease attribute added (i)
60
Customer with Lease attribute added (ii)
61
Modifying the Lease and AnnualLease Class
15

• Must associate the lease with a customers slip
• Need to add a slip reference and customer
  reference
• Include getter and setter references to manage
  these references

62
Lease with Slip Customer references (i)
63
Lease with Slip Customer references (ii)
64
Modifying the Slip Class
15

• Must associate a slip with its corresponding
  lease and customer
• Need to add an annual lease reference to the
  attribute list and set its value to null
• Three DA methods are needed
• Initialize
• Terminate
• Find requires two parameters slip number and
  dock ID

65
The SlipDA Class
15

• Need a SlipDA class
• Initialize and Terminate methods are identical to
  other DA classes
• Find method defines a SELECT statement that
  returns information from the SlipTable for a
  particular slip

66
The AnnualLeaseDA Class

• Must find and insert information about annual
  leases in the database
• Uses standard initialize and terminate methods

67
AnnualLeaseDA Class - find

• Query retrieves information from three tables
  CustomerTable, LeaseTable, and SlipTable
• The setCustomer method establishes a link with
  the annual lease instance

68
AnnualLease with DA method (i)
69
AnnualLease with DA method (ii)
70
AnnualLease with DA method (iii)
71
AnnualLeaseDA Class - addNew

• Receives a reference to the annual lease instance
  that will be added
• Lease amount, balance, payment, and start date
  are extracted
• Before executing SQL statement that inserts into
  LeaseTable checks to see if primary key already
  exists in the database