第13章创建更加复杂的数据库ppt课件

1、本章主要内容本章主要内容:了解关系数据库中多个表的构造和用法了解关系数据库中多个表的构造和用法;创建从关系数据库的多个表中检索信息的创建从关系数据库的多个表中检索信息的Java运用程序运用程序;实现类图上一对一关系的数据库运用程序实现类图上一对一关系的数据库运用程序;实现类图上一对多关系的数据库运用程序实现类图上一对多关系的数据库运用程序;实现类图上关联类的数据库运用程序实现类图上关联类的数据库运用程序;第第1313章章 创建更加复杂的数据库运用程序创建更加复杂的数据库运用程序CustomernameaddressphoneNoDockdockIDlocationelectricitywate

2、raddSliptoDockLeaseamountstartDateendDatecalculateFee()SlipslipIDwidthslipLengthBoatstateRegistrationNoboatLengthmanufacturerYear11111.*10.10.1Bradshaw Marina类图第第1313章章 创建更加复杂的数据库运用程序创建更加复杂的数据库运用程序CustomernameaddressphoneNoBoatstateRegistrationNoboatLengthmanufacturerYear1113.1 13.1 实现一对一的关系实现一对一的关系

3、(1) CustomerTable表 PhoneNo是主键。13.1.1 13.1.1 创建创建BoatTableBoatTable表表(2) BoatTable表StateRegistrationNo是主键；BoatTable表中的CustomerPhoneNo和CustomerTable表中的PhoneNo是一样的。13.1.1 13.1.1 创建创建BoatTableBoatTable表表BoatTable表中的CustomerPhoneNo字段称为外键。13.1.1 13.1.1 创建创建BoatTableBoatTable表表在Access中，可以在Relationship窗口以图形

4、的方式来定义和描画公用属性。点击“工具菜单下的“关系子菜单：13.1.1 13.1.1 创建创建BoatTableBoatTable表表13.1.1 13.1.1 创建创建BoatTableBoatTable表表v从两个表中检索信息时，运用外键和主键将一从两个表中检索信息时，运用外键和主键将一个表的信息与另一个表的信息相连。个表的信息与另一个表的信息相连。v例如，查找并显示例如，查找并显示BoatTableBoatTable中一切船只的形中一切船只的形状注册号、制造商，以及每艘船的拥有者的姓状注册号、制造商，以及每艘船的拥有者的姓名和号码的名和号码的SQLSQL语句为：语句为：SELECT S

5、tateRegistrationNo, Manufacturer, CustomerName, PhoneNo FROM BoatTable, CustomerTable WHERE CustomerPhoneNo = PhoneNo ;13.1.2 13.1.2 运用运用SQLSQL衔接数据库中的表衔接数据库中的表建立名为建立名为CustomerAndBoatDatabaseConnectCustomerAndBoatDatabaseConnect的新的新类，此类的独一职责是管理到数据库的衔接。类，此类的独一职责是管理到数据库的衔接。/ Connect to CustomerAndBoatD

6、atabaseimport java.sql.*;public class CustomerAndBoatDatabaseConnect/ The Data Source name is CustomerAndBoatDatabasestatic String url = jdbc:odbc:CustomerAndBoatDatabase;static Connection aConnection;13.1.3 13.1.3 建立建立CustomerCustomer到到BoatBoat的公用衔接的公用衔接/ establish the database connectionpublic sta

7、tic Connection initialize()try / load the jdbc - odbc bridge driver for Windows Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); / create connection instance aConnection = DriverManager.getConnection(url, , ); catch (ClassNotFoundException e)System.out.println(e); catch (SQLException e) System.out.print

8、ln(e); return aConnection;13.1.3 13.1.3 建立建立CustomerCustomer到到BoatBoat的公用衔接的公用衔接/ close the database connectionpublic static void terminate()try aConnection.close();catch (SQLException e) System.out.println(e);13.1.3 13.1.3 建立建立CustomerCustomer到到BoatBoat的公用衔接的公用衔接对第12章的Customer类进展修正：添加船只援用属性；添加相应的se

9、tter和getter方法；在构造函数中将船只援用初始化为空；运用由类CustomerAndBoatDatabaseConnect建立的共用数据库衔接。13.1.4 13.1.4 修正客户问题域类修正客户问题域类public static void initialize(Connection c) CustomerDA.initialize(c);import java.util.Vector;import java.sql.*;public class BoatDAstatic Vector boats = new Vector();static Boat aBoat;/ declare v

10、ariables for the database connectionstatic Connection aConnection;static Statement aStatement;/ declare variables for Boat attribute valuesstatic String stateRegistrationNo;static double length; static String manufacturer; static int year; static String phoneNo;13.1.5 13.1.5 定义定义BoatDABoatDA类类/ esta

11、blish the database connectionpublic static void initialize(Connection c)try aConnection=c;aStatement=aConnection.createStatement();catch (SQLException e) System.out.println(e);13.1.5 13.1.5 定义定义BoatDABoatDA类类/ close the database connectionpublic static void terminate()try aStatement.close(); catch (

12、SQLException e) System.out.println(e);13.1.5 13.1.5 定义定义BoatDABoatDA类类public static Boat find(String key) throws NotFoundException aBoat = null; / define the SQL query statement using the state reg key String sqlQuery = SELECT StateRegistrationNo, BoatLength, +Manufacturer, Year +FROM BoatTable + WH

13、ERE StateRegistrationNo = + key + ;/ execute the SQL query statementtry ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor & returns true if there is data boolean gotIt = rs.next();13.1.5 13.1.5 定义定义BoatDABoatDA类类if (gotIt)/ extract the datastateRegistrationNo = rs.getString

14、(1);length = rs.getDouble(2);manufacturer = rs.getString(3);year = rs.getInt(4);/ create Boat instanceaBoat = new Boat(stateRegistrationNo, length, manufacturer, year);else/ nothing was retrievedthrow (new NotFoundException(not found );rs.close(); catch (SQLException e) System.out.println(e);return

15、aBoat; 13.1.5 13.1.5 定义定义BoatDABoatDA类类public static Vector getAll()Vector boats = new Vector();/ define the SQL query statement for get allString sqlQuery = SELECT StateRegistrationNo, BoatLength, + Manufacturer, Year FROM BoatTable;try / execute the SQL query statementResultSet rs = aStatement.exe

16、cuteQuery(sqlQuery);boolean moreData = rs.next();/ next method sets cursor &/ returns true if there is data13.1.5 13.1.5 定义定义BoatDABoatDA类类 while (moreData)/ extract the boat datastateRegistrationNo = rs.getString(1);length = rs.getDouble(2);manufacturer = rs.getString(3);year = rs.getInt(4); / crea

17、te Boat instanceaBoat = new Boat(stateRegistrationNo, length, manufacturer, year);boats.addElement(aBoat);moreData = rs.next(); rs.close();catch (SQLException e) System.out.println(e);return boats;13.1.5 13.1.5 定义定义BoatDABoatDA类类public static void addNew(Boat aBoat) throws DuplicateException/ retrie

18、ve the boat attribute valuesstateRegistrationNo = aBoat.getStateRegistrationNo();length = aBoat.getLength();manufacturer = aBoat.getManufacturer();year = aBoat.getYear();phoneNo = aBoat.getCustomer().getPhoneNo();/ create the SQL insert statement using attribute valuesString sqlInsert = INSERT INTO

19、BoatTable (StateRegistrationNo, + BoatLength, Manufacturer, Year, + CustomerPhoneNo) + VALUES ( + stateRegistrationNo + , + length + , + manufacturer + , + year + , + phoneNo + );13.1.5 13.1.5 定义定义BoatDABoatDA类类tryBoat b = find(stateRegistrationNo);throw (new DuplicateException(Boat Exists );/ if No

20、tFoundException, add boat to databasecatch(NotFoundException e)try / execute the SQL update statement int result = aStatement.executeUpdate(sqlInsert); catch (SQLException ee) System.out.println(ee);13.1.5 13.1.5 定义定义BoatDABoatDA类类public static void update(Boat aBoat) throws NotFoundException/ retri

21、eve the Boat attribute valuesstateRegistrationNo = aBoat.getStateRegistrationNo();length = aBoat.getLength();manufacturer = aBoat.getManufacturer();year = aBoat.getYear();/ define the SQL query statement using the boat reg number keyString sqlUpdate = UPDATE BoatTable + SET BoatLength = + length + ,

22、 Manufacturer = + manufacturer + + , Year = + year + WHERE StateRegistrationNo = + stateRegistrationNo + ;tryBoat b = find(stateRegistrationNo); int result = aStatement.executeUpdate(sqlUpdate); catch (SQLException e) System.out.println(e);13.1.5 13.1.5 定义定义BoatDABoatDA类类public static void delete(Bo

23、at aBoat) throws NotFoundException/ retrieve the state registration no (key)stateRegistrationNo = aBoat.getStateRegistrationNo();/ create the SQL delete statementString sqlDelete = DELETE FROM BoatTable + WHERE StateRegistrationNo = + stateRegistrationNo +;tryBoat b = find(stateRegistrationNo);/ if

24、found, execute the SQL update statement int result = aStatement.executeUpdate(sqlDelete); catch (SQLException e) System.out.println(e); 13.1.5 13.1.5 定义定义BoatDABoatDA类类在Boat类中添加4个静态方法： initialize, find, getAll, terminate。3个实例方法：addNew, update, delete。13.1.6 13.1.6 修正修正BoatBoat类以协同运用类以协同运用BoatDABoatD

25、A类类/ DA static methods public static void initialize(Connection c)BoatDA.initialize(c);public static Boat find(String key) throws NotFoundExceptionreturn BoatDA.find(key);public static Vector getAll()return BoatDA.getAll();public static void terminate()BoatDA.terminate();/ DA instance methodspublic

26、void addNew() throws DuplicateExceptionBoatDA.addNew(this);public void delete() throws NotFoundExceptionBoatDA.delete(this);public void update() throws NotFoundExceptionBoatDA.update(this);13.1.6 13.1.6 修正修正BoatBoat类以协同运用类以协同运用BoatDABoatDA类类支持衔接BoatTable和CustomerTable中的信息；包括船只援用变量和要代表船只属性的变量；13.1.7

27、13.1.7 修正修正CustomerDACustomerDA类类import java.util.Vector;import java.sql.*;public class CustomerDA static Vector customers = new Vector();static Customer aCustomer;static Boat aBoat;/ declare variables for the database connectionstatic Connection aConnection;static Statement aStatement;/ declare var

28、iables for Customer attribute valuesstatic String name, address, phoneNumber;/ declare variables for Boat attribute valuesstatic String stateRegistrationNo, manufacturer;static double length;static int year;13.1.7 13.1.7 修正修正CustomerDACustomerDA类类/ establish the database connectionpublic static void

29、 initialize(Connection c) try aConnection=c;aStatement=aConnection.createStatement();catch (SQLException e) System.out.println(e);/ close the database connectionpublic static void terminate() try / close the statement aStatement.close();catch (SQLException e) System.out.println(e);13.1.7 13.1.7 修正修正

30、CustomerDACustomerDA类类public static Customer find(String key) throws NotFoundException / retrieve Customer and Boat dataaCustomer = null;/ define the SQL query statement using the phone number keyString sqlQuery = SELECT CustomerName, Address, PhoneNo, + StateRegistrationNo, BoatLength, + Manufactur

31、er, Year + FROM CustomerTable, BoatTable + WHERE PhoneNo = CustomerPhoneNo + AND PhoneNo = + key + ;/ execute the SQL query statementtry ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor & returns true if there is data boolean gotIt = rs.next();13.1.7 13.1.7 修正修正CustomerDAC

32、ustomerDA类类if (gotIt)name = rs.getString(1);address = rs.getString(2);phoneNumber = rs.getString(3);stateRegistrationNo = rs.getString(4); length = rs.getDouble(5);manufacturer = rs.getString(6);year = rs.getInt(7); / create Customer & Boat instanceaCustomer = new Customer(name, address, phoneNumber

33、);aBoat = new Boat(stateRegistrationNo, length, manufacturer, year);aBoat.assignBoatToCustomer(aCustomer); else/ nothing was retrieved throw (new NotFoundException(not found );rs.close(); catch (SQLException e) System.out.println(e);return aCustomer;13.1.7 13.1.7 修正修正CustomerDACustomerDA类类v同以前一样，fin

34、d方法仍前往一个单一的客户实例；不同的是，此实例包含对客户的船只实例的一个援用。v因此，不用执行单独的SQL语句在BoatTable中查找船只。13.1.7 13.1.7 修正修正CustomerDACustomerDA类类getAll 方法与find方法类似，但是它会前往客户援用的一个Vector。13.1.7 13.1.7 修正修正CustomerDACustomerDA类类CustomerDA类的addNew方法：每一条客户记录插入到CustomerTable中之后，CustomerDA类的addNew方法调用BoatDA类的addNew方法将相关的船只记录添加到BoatTable中。1

35、3.1.7 13.1.7 修正修正CustomerDACustomerDA类类public static void addNew(Customer aCustomer) throws DuplicationException name = aCustomer.getName();address = aCustomer.getAddress();phoneNumber = aCustomer.getPhoneNo();aBoat = aCustomer.getBoat();/ create the SQL insert statement using attribute valuesString

36、 sqlInsert = INSERT INTO CustomerTable + (Name, Address, PhoneNo) + VALUES ( + name + , + address + , + phoneNumber + );/ see if this customer already exists in the databasetry Customer c = find(phoneNumber);throw (new DuplicationException(Customer Exists );13.1.7 13.1.7 修正修正CustomerDACustomerDA类类/

37、if NotFoundException, add customer to databasecatch (NotFoundException e) try / execute the SQL update statement, a 1 return goodint result = aStatement.executeUpdate(sqlInsert);aBoat.addNew(); catch (SQLException ee) System.out.println(ee);13.1.7 13.1.7 修正修正CustomerDACustomerDA类类CustomerDA类的delete方

38、法：从数据库中删除某条客户记录时也必需将对应的船只记录删除。public static void delete(Customer aCustomer) throws NotFoundException phoneNumber = aCustomer.getPhoneNo();/ create the SQL delete statementString sqlDelete = DELETE FROM CustomerTable + WHERE PhoneNo = + phoneNumber + ;try Customer c = Customer.find(phoneNumber);/ if

39、found, execute the SQL update statement, a 1 return is good/ deleteint result = aStatement.executeUpdate(sqlDelete);aCustomer.getBoat().delete(); catch (SQLException e) System.out.println(e);13.1.7 13.1.7 修正修正CustomerDACustomerDA类类实现Dock和Slip之间存在的一对多关系。DockdockIDlocationelectricitywateraddSliptoDock

40、SlipslipIDwidthslipLength1.*113.2 13.2 实现一对多的关系实现一对多的关系(1) DockTable表1表示有电或有水；0表示无电或无水13.2.1 13.2.1 创建创建DoctTable,SlipTableDoctTable,SlipTable(2) SlipTable表SlipNo和DockId组合在一同构成一个衔接的主键。DockId, BoatId是外键。13.2.1 13.2.1 创建创建DoctTable,SlipTableDoctTable,SlipTable(3) DockTable与SlipTable之间的关系13.2.1 13.2.1

41、创建创建DoctTable,SlipTableDoctTable,SlipTable/ Connect to DockAndSlipDatabaseimport java.sql.*;public class DockAndSlipDatabaseConnect/ The Data Source name is DockAndSlipDatabasestatic String url = jdbc:odbc:DockAndSlipDatabase;static Connection aConnection;13.2.2 13.2.2 建立到建立到DockAndSlipDatabaseDockA

42、ndSlipDatabase的通用衔接的通用衔接/ establish the database connectionpublic static Connection initialize()try / load the jdbc - odbc bridge driver for Windows Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); / create connection instance - table name is Customers aConnection = DriverManager.getConnection(url, , );

43、 catch (ClassNotFoundException e)System.out.println(e);catch (SQLException e) System.out.println(e);return aConnection;13.2.2 13.2.2 建立到建立到DockAndSlipDatabaseDockAndSlipDatabase的通用衔接的通用衔接/ close the database connectionpublic static void terminate()try aConnection.close();catch (SQLException e) Syste

44、m.out.println(e);13.2.2 13.2.2 建立到建立到DockAndSlipDatabaseDockAndSlipDatabase的通用衔接的通用衔接l在在DockDock类中导入类中导入java.sqljava.sql程序包的程序包的ConnectionConnection类；类；l定义规范的定义规范的initializeinitialize、terminateterminate、findfind和和getAllgetAll方法。方法。lDockDock类不需求用于插入、更新或删除码头记录的方类不需求用于插入、更新或删除码头记录的方法。法。13.2.3 13.2.3 修正

45、修正DockDockimport java.util.*;import java.sql.Connection;public class Dock/ attributes private int id; private String location; private boolean electricity; private boolean water; / implement slip association with Vector class private Vector slips;13.2.3 13.2.3 修正修正DockDock / constructor public Dock(

46、int anId, String aLocation, boolean anElectricity, boolean aWater) setId(anId);setLocation(aLocation);setElectricity(anElectricity);setWater(aWater);slips = new Vector(10); / start with 10 slips13.2.3 13.2.3 修正修正DockDock/ DA static methods public static void initialize(Connection c)DockDA.initialize

47、(c);public static Dock find(int key) throws NotFoundExceptionreturn DockDA.find(key);public static Vector getAll()return DockDA.getAll();public static void terminate()DockDA.terminate();13.2.3 13.2.3 修正修正DockDock/ set accessor methodspublic void setId(int anId) id = anId;public void setLocation(Stri

48、ng aLocation) location = aLocation;public void setElectricity(boolean anElectricity) electricity = anElectricity;public void setWater(boolean aWater) water = aWater;13.2.3 13.2.3 修正修正DockDock/ get accessor methodspublic int getId() return id;public String getLocation() return location;public boolean

49、 getElectricity() return electricity;public boolean getWater() return water;public Vector getSlips() return slips;13.2.3 13.2.3 修正修正DockDockpublic String tellAboutSelf()String hasElectricity = getElectricity()?Has Electricity:Has No Electricity;String hasWater = getWater()?Has Water:Has No Water;ret

50、urn (Dock + getId() + Location is + getLocation() + , +hasElectricity + , + hasWater);/ custom method addSlipToDockpublic void addSlipToDock(Slip aSlip) slips.addElement(aSlip); / connect dock to slipaSlip.setDock(this); / connect slip to dock13.2.3 13.2.3 修正修正DockDockimport java.util.Vector;import

51、java.sql.*;public class DockDAstatic Vector docks = new Vector();static Slip aSlip;static Dock aDock;static Connection aConnection;static Statement aStatement;/ declare variables for Slip attribute valuesstatic int slipNo;static int width;static double slipLength;static int dockId; 13.2.4 13.2.4 新建新

52、建DockDADockDA类类/ declare variables for Dock attribute valuesstatic int id;static String location;static boolean electricity;static boolean water;/ Yes/No variables needed to convert boolean to text for dbmsstatic int electricityYN;static int waterYN;13.2.4 13.2.4 新建新建DockDADockDA类类/ establish the da

53、tabase connectionpublic static void initialize(Connection c)try aConnection = c; / create statement object instance for this connection aStatement = aConnection.createStatement(); catch (SQLException e) System.out.println(e);13.2.4 13.2.4 新建新建DockDADockDA类类/ close the database connectionpublic stati

54、c void terminate()try aStatement.close();catch (SQLException e) System.out.println(e);13.2.4 13.2.4 新建新建DockDADockDA类类public static Dock find(int key) throws NotFoundExceptionaDock = null;/ define the SQL query statementString sqlQuery = SELECT DockTable.DockId, Location, Electricity, Water, “ + Sli

55、pNo, Width, SlipLength FROM DockTable, SlipTable “ + WHERE DockTable.DockId = + key + AND SlipTable.DockId = DockTable.DockId + ORDER BY SlipNo;/ execute the SQL query statementtry / get the dock ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor & returns true if there is d

56、ata boolean dockCreated = false; boolean more = rs.next();13.2.4 13.2.4 新建新建DockDADockDA类类while(more) / loop for each row of result set if (dockCreated = false)/ extract the Dock datadockId = rs.getInt(1);location = rs.getString(2);electricityYN = rs.getInt(3);waterYN = rs.getInt(4);/ convert ints t

57、o booleanselectricity = integerToBoolean(electricityYN);water = integerToBoolean(waterYN);/ create Dock instanceaDock = new Dock(dockId, location, electricity, water);dockCreated = true; 13.2.4 13.2.4 新建新建DockDADockDA类类 / extract the Slip dataslipNo= rs.getInt(5);width = rs.getInt(6);slipLength = rs

58、.getDouble(7);aSlip = new Slip(slipNo, width, slipLength, aDock);more = rs.next(); / set cursor to next row elseslipNo = rs.getInt(5);width = rs.getInt(6);slipLength = rs.getDouble(7);aSlip = new Slip(slipNo, width, slipLength, aDock);more = rs.next(); if(dockCreated = false)throw (new NotFoundExcep

59、tion(Dock not found ); catch (SQLException e) System.out.println(e);return aDock;13.2.4 13.2.4 新建新建DockDADockDA类类public static Vector getAll()Vector docks = new Vector();/ define the SQL query statement for get allString sqlQuery = SELECT DockTable.DockId, Location, Electricity, Water, + SlipNo, Wid

60、th, SlipLength FROM DockTable, SlipTable + WHERE SlipTable.DockId = DockTable.DockId“ + ORDER BY DockTable.DockId, SlipNo;try / execute the SQL query statementResultSet rs = aStatement.executeQuery(sqlQuery);/ next method sets cursor & returns true if there is databoolean moreData = rs.next(); /init