创建更加复杂的数据库.ppt

本章主要内容: 了解关系数据库中多个表的结构和用法; 创建从关系数据库的多个表中检索信息的Java应用程序; 实现类图上一对一关系的数据库应用程序; 实现类图上一对多关系的数据库应用程序; 实现类图上关联类的数据库应用程序;,第13章 创建更加复杂的数据库应用程序,Customer name address phoneNo,Dock dockID location electricity water addSliptoDock,Lease amount startDate endDate calculateFee(),Slip slipID width slipLength,Boat stateRegistrationNo boatLength manufacturer Year,1,1,1,1,1*,1,01,01,Bradshaw Marina类图,第13章 创建更加复杂的数据库应用程序,Customer name address phoneNo,Boat stateRegistrationNo boatLength manufacturer Year,1,1,13.1 实现一对一的关系,(1) CustomerTable表,PhoneNo是主键。,13.1.1 创建BoatTable表,(2) BoatTable表,StateRegistrationNo是主键；BoatTable表中的CustomerPhoneNo和CustomerTable表中的PhoneNo是相同的。,13.1.1 创建BoatTable表,BoatTable表中的CustomerPhoneNo字段称为外键。,13.1.1 创建BoatTable表,在Access中，可以在Relationship窗口以图形的形式来定义和描述公用属性。 点击“工具”菜单下的“关系”子菜单：,13.1.1 创建BoatTable表,13.1.1 创建BoatTable表,从两个表中检索信息时，使用外键和主键将一个表的信息与另一个表的信息相连。 例如，查找并显示BoatTable中所有船只的状态注册号、制造商，以及每艘船的拥有者的姓名和电话号码的SQL语句为：,SELECT StateRegistrationNo, Manufacturer, CustomerName, PhoneNo FROM BoatTable, CustomerTable WHERE CustomerPhoneNo = PhoneNo ;,13.1.2 使用SQL连接数据库中的表,建立名为CustomerAndBoatDatabaseConnect的新类，此类的唯一职责是管理到数据库的连接。,/ Connect to CustomerAndBoatDatabase import java.sql.*; public class CustomerAndBoatDatabaseConnect / The Data Source name is “CustomerAndBoatDatabase“ static String url = “jdbc:odbc:CustomerAndBoatDatabase“; static Connection aConnection;,13.1.3 建立Customer到Boat的公用连接,/ establish the database connection public static 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.println(e); return aConnection; ,13.1.3 建立Customer到Boat的公用连接,/ close the database connection public static void terminate() try aConnection.close(); catch (SQLException e) System.out.println(e); ,13.1.3 建立Customer到Boat的公用连接,对第12章的Customer类进行修改： 增加船只引用属性； 增加相应的setter和getter方法； 在构造函数中将船只引用初始化为空； 使用由类CustomerAndBoatDatabaseConnect建立的共用数据库连接。,13.1.4 修改客户问题域类,public static void initialize(Connection c) CustomerDA.initialize(c); ,import java.util.Vector; import java.sql.*; public class BoatDA static Vector boats = new Vector(); static Boat aBoat; / declare variables for the database connection static Connection aConnection; static Statement aStatement; / declare variables for Boat attribute values static String stateRegistrationNo; static double length; static String manufacturer; static int year; static String phoneNo;,13.1.5 定义BoatDA类,/ establish the database connection public static void initialize(Connection c) try aConnection=c; aStatement=aConnection.createStatement(); catch (SQLException e) System.out.println(e); ,13.1.5 定义BoatDA类,/ close the database connection public static void terminate() try aStatement.close(); catch (SQLException e) System.out.println(e); ,13.1.5 定义BoatDA类,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 “ + WHERE StateRegistrationNo = “ + key + “; / execute the SQL query statement try ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor ,13.1.5 定义BoatDA类,if (gotIt) / extract the data stateRegistrationNo = rs.getString(1); length = rs.getDouble(2); manufacturer = rs.getString(3); year = rs.getInt(4); / create Boat instance aBoat = new Boat(stateRegistrationNo, length, manufacturer, year); else / nothing was retrieved throw (new NotFoundException(“not found “); rs.close(); catch (SQLException e) System.out.println(e); return aBoat; ,13.1.5 定义BoatDA类,public static Vector getAll() Vector boats = new Vector(); / define the SQL query statement for get all String sqlQuery = “SELECT StateRegistrationNo, BoatLength, “ + “Manufacturer, Year FROM BoatTable“; try / execute the SQL query statement ResultSet rs = aStatement.executeQuery(sqlQuery); boolean moreData = rs.next(); / next method sets cursor & / returns true if there is data,13.1.5 定义BoatDA类,while (moreData) / extract the boat data stateRegistrationNo = rs.getString(1); length = rs.getDouble(2); manufacturer = rs.getString(3); year = rs.getInt(4); / create Boat instance aBoat = 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 定义BoatDA类,public static void addNew(Boat aBoat) throws DuplicateException / retrieve the boat attribute values stateRegistrationNo = aBoat.getStateRegistrationNo(); length = aBoat.getLength(); manufacturer = aBoat.getManufacturer(); year = aBoat.getYear(); phoneNo = aBoat.getCustomer().getPhoneNo(); / create the SQL insert statement using attribute values String sqlInsert = “INSERT INTO BoatTable (StateRegistrationNo, “ + “BoatLength, Manufacturer, Year, “ + “CustomerPhoneNo)“ + “ VALUES (“ + stateRegistrationNo + “, “ + length + “, “ + manufacturer + “, “ + year + “, “ + phoneNo + “)“;,13.1.5 定义BoatDA类,try Boat b = find(stateRegistrationNo); throw (new DuplicateException(“Boat Exists “); / if NotFoundException, add boat to database catch(NotFoundException e) try / execute the SQL update statement int result = aStatement.executeUpdate(sqlInsert); catch (SQLException ee) System.out.println(ee); ,13.1.5 定义BoatDA类,public static void update(Boat aBoat) throws NotFoundException / retrieve the Boat attribute values stateRegistrationNo = aBoat.getStateRegistrationNo(); length = aBoat.getLength(); manufacturer = aBoat.getManufacturer(); year = aBoat.getYear(); / define the SQL query statement using the boat reg number key String sqlUpdate = “UPDATE BoatTable “ + “ SET BoatLength = “ + length + “ , Manufacturer = “ + manufacturer +“ “ + “ , Year = “ + year + “ WHERE StateRegistrationNo = “ + stateRegistrationNo + “; try Boat b = find(stateRegistrationNo); int result = aStatement.executeUpdate(sqlUpdate); catch (SQLException e) System.out.println(e); ,13.1.5 定义BoatDA类,public static void delete(Boat aBoat) throws NotFoundException / retrieve the state registration no (key) stateRegistrationNo = aBoat.getStateRegistrationNo(); / create the SQL delete statement String sqlDelete = “DELETE FROM BoatTable “ + “WHERE StateRegistrationNo = “ + stateRegistrationNo +“; try Boat b = find(stateRegistrationNo); / if found, execute the SQL update statement int result = aStatement.executeUpdate(sqlDelete); catch (SQLException e) System.out.println(e); ,13.1.5 定义BoatDA类,在Boat类中增加4个静态方法： initialize, find, getAll, terminate。 3个实例方法：addNew, update, delete。,13.1.6 修改Boat类以协同使用BoatDA类,/ DA static methods public static void initialize(Connection c) BoatDA.initialize(c); public static Boat find(String key) throws NotFoundException return BoatDA.find(key); public static Vector getAll() return BoatDA.getAll(); public static void terminate() BoatDA.terminate(); / DA instance methods public void addNew() throws DuplicateException BoatDA.addNew(this); public void delete() throws NotFoundException BoatDA.delete(this); public void update() throws NotFoundException BoatDA.update(this);,13.1.6 修改Boat类以协同使用BoatDA类,支持连接BoatTable和CustomerTable中的信息； 包括船只引用变量和要代表船只属性的变量；,13.1.7 修改CustomerDA类,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 connection static Connection aConnection; static Statement aStatement; / declare variables for Customer attribute values static String name, address, phoneNumber; / declare variables for Boat attribute values static String stateRegistrationNo, manufacturer; static double length; static int year;,13.1.7 修改CustomerDA类,/ establish the database connection public static void initialize(Connection c) try aConnection=c; aStatement=aConnection.createStatement(); catch (SQLException e) System.out.println(e); / close the database connection public static void terminate() try / close the statement aStatement.close(); catch (SQLException e) System.out.println(e); ,13.1.7 修改CustomerDA类,public static Customer find(String key) throws NotFoundException / retrieve Customer and Boat data aCustomer = null; / define the SQL query statement using the phone number key String sqlQuery = “SELECT CustomerName, Address, PhoneNo, “ + “ StateRegistrationNo, BoatLength, “ + “ Manufacturer, Year “ + “ FROM CustomerTable, BoatTable “ + “ WHERE PhoneNo = CustomerPhoneNo“ + “ AND PhoneNo = “ + key +“ ; / execute the SQL query statement try ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor ,13.1.7 修改CustomerDA类,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 ,13.1.7 修改CustomerDA类,同以前一样，find方法仍返回一个单一的客户实例；不同的是，此实例包含对客户的船只实例的一个引用。 因此，不必执行单独的SQL语句在BoatTable中查找船只。,13.1.7 修改CustomerDA类,getAll 方法与find方法类似，但是它会返回客户引用的一个Vector。,13.1.7 修改CustomerDA类,CustomerDA类的addNew方法：每一条客户记录插入到CustomerTable中之后，CustomerDA类的addNew方法调用BoatDA类的addNew方法将相关的船只记录添加到BoatTable中。,13.1.7 修改CustomerDA类,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 values String sqlInsert = “INSERT INTO CustomerTable “ + “(Name, Address, PhoneNo)“ + “VALUES (“ + name + “, “ + address + “, “ + phoneNumber + “)“; / see if this customer already exists in the database try Customer c = find(phoneNumber); throw (new DuplicationException(“Customer Exists “); ,13.1.7 修改CustomerDA类,/ if NotFoundException, add customer to database catch (NotFoundException e) try / execute the SQL update statement, a 1 return good int result = aStatement.executeUpdate(sqlInsert); aBoat.addNew(); catch (SQLException ee) System.out.println(ee); ,13.1.7 修改CustomerDA类,CustomerDA类的delete方法：从数据库中删除某条客户记录时也必须将对应的船只记录删除。 public static void delete(Customer aCustomer) throws NotFoundException phoneNumber = aCustomer.getPhoneNo(); / create the SQL delete statement String sqlDelete = “DELETE FROM CustomerTable “ + “WHERE PhoneNo = “ + phoneNumber + “; try Customer c = Customer.find(phoneNumber); / if found, execute the SQL update statement, a 1 return is good / delete int result = aStatement.executeUpdate(sqlDelete); aCustomer.getBoat().delete(); catch (SQLException e) System.out.println(e); ,13.1.7 修改CustomerDA类,实现Dock和Slip之间存在的一对多关系。,Dock dockID location electricity water addSliptoDock,Slip slipID width slipLength,1*,1,13.2 实现一对多的关系,(1) DockTable表,1表示有电（或有水）； 0表示无电（或无水）,13.2.1 创建DoctTable,SlipTable,(2) SlipTable表,SlipNo和DockId组合在一起构成一个连接的主键。 DockId, BoatId是外键。,13.2.1 创建DoctTable,SlipTable,(3) DockTable与SlipTable之间的关系,13.2.1 创建DoctTable,SlipTable,/ Connect to DockAndSlipDatabase import java.sql.*; public class DockAndSlipDatabaseConnect / The Data Source name is “DockAndSlipDatabase“ static String url = “jdbc:odbc:DockAndSlipDatabase“; static Connection aConnection;,13.2.2 建立到DockAndSlipDatabase的通用连接,/ establish the database connection public 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, “, “); catch (ClassNotFoundException e) System.out.println(e); catch (SQLException e) System.out.println(e); return aConnection; ,13.2.2 建立到DockAndSlipDatabase的通用连接,/ close the database connection public static void terminate() try aConnection.close(); catch (SQLException e) System.out.println(e); ,13.2.2 建立到DockAndSlipDatabase的通用连接,在Dock类中导入java.sql程序包的Connection类； 定义标准的initialize、terminate、find和getAll方法。 Dock类不需要用于插入、更新或删除码头记录的方法。,13.2.3 修改Dock,import 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 修改Dock,/ constructor public Dock(int anId, String aLocation, boolean anElectricity, boolean aWater) setId(anId); setLocation(aLocation); setElectricity(anElectricity); setWater(aWater); slips = new Vector(10); / start with 10 slips ,13.2.3 修改Dock,/ DA static methods public static void initialize(Connection c) DockDA.initialize(c); public static Dock find(int key) throws NotFoundException return DockDA.find(key); public static Vector getAll() return DockDA.getAll(); public static void terminate() DockDA.terminate();,13.2.3 修改Dock,/ set accessor methods public void setId(int anId) id = anId; public void setLocation(String aLocation) location = aLocation; public void setElectricity(boolean anElectricity) electricity = anElectricity; public void setWater(boolean aWater) water = aWater;,13.2.3 修改Dock,/ get accessor methods public int getId() return id; public String getLocation() return location; public boolean getElectricity() return electricity; public boolean getWater() return water; public Vector getSlips() return slips;,13.2.3 修改Dock,public String tellAboutSelf() String hasElectricity = getElectricity()?“Has Electricity“: “Has No Electricity“; String hasWater = getWater()?“Has Water“:“Has No Water“; return (“Dock “ + getId() + “ Location is “ + getLocation() + “, “ + hasElectricity + “, “ + hasWater); / custom method addSlipToDock public void addSlipToDock(Slip aSlip) slips.addElement(aSlip); / connect dock to slip aSlip.setDock(this); / connect slip to dock ,13.2.3 修改Dock,import java.util.Vector; import java.sql.*; public class DockDA static Vector docks = new Vector(); static Slip aSlip; static Dock aDock; static Connection aConnection; static Statement aStatement; / declare variables for Slip attribute values static int slipNo; static int width; static double slipLength; static int dockId;,13.2.4 新建DockDA类,/ declare variables for Dock attribute values static int id; static String location; static boolean electricity; static boolean water; / Yes/No variables needed to convert boolean to text for dbms static int electricityYN; static int waterYN;,13.2.4 新建DockDA类,/ establish the database connection public 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 新建DockDA类,/ close the database connection public static void terminate() try aStatement.close(); catch (SQLException e) System.out.println(e); ,13.2.4 新建DockDA类,public static Dock find(int key) throws NotFoundException aDock = null; / define the SQL query statement 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“; / execute the SQL query statement try / get the dock ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor ,13.2.4 新建DockDA类,while(more) / loop for each row of result set if (dockCreated = false) / extract the Dock data dockId = rs.getInt(1); location = rs.getString(2); electricityYN = rs.getInt(3); waterYN = rs.getInt(4); / convert ints to booleans electricity = integerToBoolean(electricityYN); water = integerToBoolean(waterYN); / create Dock instance aDock = new Dock(dockId, location, electricity, water); dockCreated = true; ,13.2.4 新建DockDA类,/ extract the Slip data slipNo= rs.getInt(5);width = rs.getInt(6);slipLength = rs.getDouble(7); aSlip = new Slip(slipNo, width, slipLength, aDock); more = rs.next(); / set cursor to next row else slipNo = 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 NotFoundException(“Dock not found “); catch (SQLException e) System.out.println(e); return aDock; ,13.2.4 新建DockDA类,public static Vector getAll() Vector docks = new Vector(); / define the SQL query statement for get all String sqlQuery = “SELECT DockTable.DockId, Location, Electricity, Water,“ + “ SlipNo, Width, SlipLength FROM DockTable, SlipTable “ + “ WHERE SlipTable.DockId = DockTable.DockId“ + “ ORDER BY DockTable.DockId, SlipNo“; try / execute the SQL query statement ResultSet rs = aStatement.executeQuery(sqlQuery); / next method sets cursor ,13.2.4 新建DockDA类,while (moreData) / store value of control field int prevDockId = thisDockId; / extract the Dock data location = rs.getString(2); electricityYN = rs.getInt(3); waterYN = rs.getInt(4); / convert int to boolean electricity = integerToBoolean(electricityYN); water = integerToBoolean(waterYN); / create Dock instance aDock = new Dock(thisDockId, location, electricity, water); docks.addElement(aDock); / get slips for this dock,13.2.4 新建DockDA类,while ( prevDockId = thisDockId ,13.2.4 新建DockDA类,catch (SQLException e) System.out.println(e); return docks; private static boolean integerToBoolean(int i) boolean b = false; if(i = 1) b = true; return b; ,13.2.4 新建DockDA类,定义TesterTwo类； TesterTwo类首先定义变量并建立到数据库的连接； 查找Dock 1; 查找Dock 2; 调用getAll方法生成所有码头的列表及分别属于各个码头的船台;,13.2.5 测试DockAndSlipDatabase应用程序,import java.util.Vector; import java.sql.*; public class TesterTwo static Slip aSlip; s