Transcript Introduction to ColdFusion

Chapter 15
Creating More Complex
Database Applications
Chapter 15 - Creating More Complex Database
Applications
1
15
One to One Relationships
 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-toone association using a relational database
Chapter 15 - Creating More Complex Database
Applications
2
15
Understanding Tables in
CustomerAndBoatDatabase
1. Create Chapter15Example1 folder
2. Locate CustomerAndBoatDatabase.mdb on the CD
3. Open CustomerAndBoatDatabase using Microsoft
Access
4. View the contents of the BoatTable

The primary key of BoatTable is StateRegistrationNo
5. View the contents of the CustomerTable

The primary key of CustomerTable is PhoneNo
Chapter 15 - Creating More Complex Database
Applications
3
Understanding Tables in
CustomerAndBoatDatabase
Chapter 15 - Creating More Complex Database
Applications
15
4
Understanding Tables in
CustomerAndBoatDatabase
Chapter 15 - Creating More Complex Database
Applications
15
5
15
Understanding Tables in
CustomerAndBoatDatabase
Chapter 15 - Creating More Complex Database
Applications
6
Understanding Tables in
CustomerAndBoatDatabase
Chapter 15 - Creating More Complex Database
Applications
7
Understanding Tables in
CustomerAndBoatDatabase
Chapter 15 - Creating More Complex Database
Applications
15
8
15
Using SQL to Join Table in Database
 The SQL statement to find and display the
state registration number and manufacturer
of all boats in BoatTable together with the
name and phone number of each boats
owner:
SELECT StateRegistration, Manufacturer, CustomerName, PhoneNo
FROM BoatTable, CustomerTable
WHERE CustomerPhoneNo = PhoneNo;
Chapter 15 - Creating More Complex Database
Applications
9
15
Using SQL to Join Table in Database
 The SQL statement displays the state
registration number, name, and address of
the owner of the boat with state registration
number MO98765:
SELECT StateRegistration, Manufacturer, CustomerName, PhoneNo
FROM BoatTable, CustomerTable
WHERE CustomerPhoneNo = PhoneNo
AND StateRegistrationNo = ‘MO98765’;
Chapter 15 - Creating More Complex Database
Applications
10
Establishing a Common Connection
 Need a new class named
CustomerAndBoatDatabaseConnect
 It is responsibility of this class is to manage
the connection to the database
 The initialize method establishes a
connection of the database
Chapter 15 - Creating More Complex Database
Applications
11
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
established by
CustomerAndBoatDatabaseConnect
Chapter 15 - Creating More Complex Database
Applications
12
15
The BoatDA Class
 Adds find, add, delete, and update methods
 Defines a boat reference variable and a
Vector of boat reference variables
Chapter 15 - Creating More Complex Database
Applications
13
15
The BoatDA Class – Initialize and Terminate
 Initialize uses the database connection
established by
CustomerAndBoatDatabaseConnect to
create a Statement
 Terminate closes the Statement instance
Chapter 15 - Creating More Complex Database
Applications
14
15
The BoatDA Class – find and getAll Methods
 The find method defines a SELECT
statement that retrieves a particular record
from BoatTable
 Find throws a NotFoundException if the
record is not found
 The getAll method returns a Vector of boat
references
Chapter 15 - Creating More Complex Database
Applications
15
The BoatDA Class – addNew Method
 Similar to the addNew method of the Customer
class
 Extracts boat attributes from the boat instance
received
 Create a SQL INSERT statement
 Before executing INSERT need to confirm that the
database does not already contain a duplicate –
using the find method
 If a duplicate is found a DuplicateException is
thrown
Chapter 15 - Creating More Complex Database
Applications
16
The BoatDA Class – update and delete
Methods
 The update and delete methods extract attributes
from the boat instance received in the argument
list, then define the appropriate SQL statements
 Before executing UPDATE and DELETE need to
confirm that the database already contains the
record – using the find method
 If a record is not found a NotFoundException is
thrown
Chapter 15 - Creating More Complex Database
Applications
17
15
Modifying the Boat Class to Work with BoatDA
 Can extend the functionality of the Boat PD to use
the BoatDA class
 The Boat class needs four static methods:




Initialize
Find
getAll
Terminate
 Three instance methods:
 addNew
 Update
 delete
Chapter 15 - Creating More Complex Database
Applications
18
15
Modifying the CustomerDA Class
 Change the CustomerDA class to support
joining information from the BoatTable and
CustomerTable
 Now includes a reference to a Boat object
Chapter 15 - Creating More Complex Database
Applications
19
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
Chapter 15 - Creating More Complex Database
Applications
20
CustomerDA Class – find and getAll Methods
Chapter 15 - Creating More Complex Database
Applications
21
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
 Preserves the integrity of the database
Chapter 15 - Creating More Complex Database
Applications
22
15
CustomerDA Class – delete and update
Methods
 The delete method must also preserve the
integrity of the database
 When a customer record is deleted, the
corresponding boat record must be deleted
as well
 The update method is unchanged
Chapter 15 - Creating More Complex Database
Applications
23
Testing the CustomerAndBoatDatabase
Application
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
15
Declare necessary reference variables
Invoke the initialization method
Attempt to retrieve customer and boat information for customer
whose phone number is 123-4567
Use the printDetails method to display the results
Try to retrieve a record that doesn’t exist
Call the getAll method
Create a new Customer and Boat instance
Call the addNew method
Verify records have been added
Test the delete method
Test the update method
Call the terminate method
Chapter 15 - Creating More Complex Database
Applications
24
Implementing a One-To-Many
Relationship
15
 The relationship between Dock and Slip is a
one-to-many relationship
 A dock contains many slips
 A slip belongs to one dock
Chapter 15 - Creating More Complex Database
Applications
25
Understanding the Tables in
DockAndSlipDatabase
1.
2.
3.
4.
Create a Chapter15Example2 folder
Copy the DockAndSlipDatabase from the CD
Open the database in Microsoft Access
View the DockTable

DockID is the primary key
5. View the SlipTable


SlipNo and DockID form the concatenated primary key
DockID is a foreign key for DockTable
Chapter 15 - Creating More Complex Database
Applications
26
Understanding the Tables in
DockAndSlipDatabase
Chapter 15 - Creating More Complex Database
Applications
27
Understanding the Tables in
DockAndSlipDatabase
Chapter 15 - Creating More Complex Database
Applications
28
Common Connection to
DockAndSlipDatabase
15
 Need a separate DA class to establish a
connection
 Same as CustomerAndBoatDatabaseConnect
class except:
 Different data source is used
Chapter 15 - Creating More Complex Database
Applications
29
15
Modifying the Dock and Slip Classes
 Dock class must be modified to support
object persistence in a relational database
 Implement the initialize, terminate, find, and
getAll methods
 Implement a tellAboutSelf method
 Don’t need to modify the Slip class nor
create a SlipDA class -- WHY?
 See Figure 15-25 on pp. 557-558
Chapter 15 - Creating More Complex Database
Applications
30
15
Introducing the DockDA Class
 Similar to other DA classes, except it does
not require insert, update, or delete methods
 Methods used to terminate and initialize are
similar to other DA classes
 See pp. 559-563
Chapter 15 - Creating More Complex Database
Applications
31
DockDA Class – find Method
 Needs SQL statement to extract dock and slip
information from the database
 WHERE clause specifies both the join condition
and the primary key for the dock of interest
 ORDER BY clause specifies the information
returned by the query is sorted by slip number
Chapter 15 - Creating More Complex Database
Applications
32
DockDA Class – find Method
Chapter 15 - Creating More Complex Database
Applications
15
33
DockDA Class – getAll Method
 Similar to find method
 Returns dock and slip information for all slips in
the marina, sorted by dock and then slip
 Dock information is repeated for each slip
 Creates only one dock instance however
 Control-break logic is used to manage this
 See Figure 15-26 on pp. 563
Chapter 15 - Creating More Complex Database
Applications
34
DockDA Class – getAll Method
Chapter 15 - Creating More Complex Database
Applications
15
35
Testing the DockAndSlipDatabase Application
1. Define necessary variable and establish the
connection to the database
2. Attempt to find Dock 1
3. printDetails method displays information about
the dock and slips
4. Attempt to find Dock 2 and slips
5. Invoke the getAll method
Chapter 15 - Creating More Complex Database
Applications
36
Testing the DockAndSlipDatabase Application
Chapter 15 - Creating More Complex Database
Applications
37
Implementing an Association Class
 The Lease class has associated
AnnualLease and DailyLease subclasses
 Implement the Lease association class in a
relational database application
Chapter 15 - Creating More Complex Database
Applications
38
Understanding Tables in
CustomerLeaseSlipDatabase
15
 Create a Chapter15Example3 folder
 Open the CustomerLeaseSlipDatabase
 View the CustomerTable, LeaseTable, and
SlipTable
 Customer phone number is primary key for
LeaseTable
 Slip number and dock ID are foreign keys in
LeaseTable
Chapter 15 - Creating More Complex Database
Applications
39
Understanding Tables in
CustomerLeaseSlipDatabase
Chapter 15 - Creating More Complex Database
Applications
40
Understanding Tables in
CustomerLeaseSlipDatabase
Chapter 15 - Creating More Complex Database
Applications
15
41
15
Modifying the Customer Class
 Need to associate a customer instance with
a lease instance
 Need to add a lease reference to the
CustomerPD class
 Include getter and setter methods for the
lease reference
 See Figure 15-31 on pp. 569-570
Chapter 15 - Creating More Complex Database
Applications
42
15
Modifying the Lease and AnnualLease
Class
 Must associate the lease with a customer’s
slip
 Need to add a slip reference and customer
reference
 Include getter and setter references to
manage these references
 See Figures 15-32 and 15-33
Chapter 15 - Creating More Complex Database
Applications
43
15
Modifying the Slip Class
 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
Chapter 15 - Creating More Complex Database
Applications
44
15
The SlipDA Class
 You must find a particular slip so it can be leased
to a customer
 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
Chapter 15 - Creating More Complex Database
Applications
45
The AnnualLeaseDA Class
 Must find and insert information about
annual leases in the database
 Define variables for annual lease attributes
 Uses standard initialize and terminate
methods
Chapter 15 - Creating More Complex Database
Applications
46
AnnualLeaseDA Class - find
 Query retrieves information from three tables:
CustomerTable, LeaseTable, and SlipTable
 The WHERE clauses uses the relationships
between these tables to create the join conditions
 The setCustomer method establishes a link with
the annual lease instance
Chapter 15 - Creating More Complex Database
Applications
47
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
Chapter 15 - Creating More Complex Database
Applications
48
Testing CustomerLeaseSlipDatabase
Application
1.
2.
3.
4.
5.
6.
7.
Declare instances of AnnualLease, Customer, and Slip
Add a new record to LeaseTable
Locate customer 123-4567
Find Slip 1 on Dock 1
Create a new annual lease instance
Retrieve newly added information
Close the database connection
Chapter 15 - Creating More Complex Database
Applications
49
Testing CustomerLeaseSlipDatabase Application
Chapter 15 - Creating More Complex Database
Applications
15
50