-实验5-补充
- 格式：doc
- 大小：110.00 KB
- 文档页数：11
实验5 JDBC小结一、相关知识点1、JDBC基本概念2、JDBC数据增、删、改，事务控制等3、OR映射4、完整的实现一个数据库应用程序二、实验目的：理解Java连接数据库的基本概念。
理解利用Statement对象、PreparedStatement对象进行增、删、改操作，理解事务的概念和JDBC编程方式。
理解OR映射的基本概念三、实验内容：1、第一步：在Eclipse中创建一个工程Northwind，并包含如下包第二步：在.zucc.northwind.util中，添加如下类（可参考booklib），并连接NorthWind数据库在.zucc.northwind下创建testSystem类，用于测试连接package .zucc.northwind;import java.sql.Connection;import java.sql.SQLException;import .zucc.northwind.util.DBUtil;public class testSystem {public static void main(String[] args){(new testSystem()).testConnection();}public void testConnection(){Connection conn=null;try {conn=DBUtil.getConnection();System.out.println("connected!");} catch (SQLException e) {System.out.println("connect failed!");e.printStackTrace();}finally{if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}第三步：在.zucc.northwind.model中，添加类Customers，用于映射数据库中的Customers表。
第四步：在.zucc.northwind.dao中，实现接口ICustomersDAO，要求定义如下方法：public void createCustomer(Customers c) throws BaseException;//创建客户public void modifyCustomer(Customers customer) throws BaseException;//修改客户信息public void deleteCustomer(Customers customer)throws BaseException;//删除客户public Customers getCustomer(String customerId) throws DbException;//根据客户编号，获取客户信息public List< Customers > qryCustomers(String companyName)throws BaseException;//根据公司姓名模糊查询客户信息第五步：在.zucc.northwind.dao中，定义CustomersDAO类，实现ICustomersDAO接口中的方法；第六步：在.zucc.northwind.control中，定义CustomersManager类，实现public List<Customers> loadAllCustomers() throws BaseException //查询所有顾客public void createCustomer(Customers c) throws BaseException //新增顾客public void deleteCustomer(String customerId) throws BaseException //根据顾客号，删除顾客public void changeCompanyName(String customerId, String companyName)throws BaseException//根据顾客号，修改公司名第七步：在testSystem中添加方法，测试CustomersManager类，要求：●添加三个客户●打印出所有客户的信息●将其中一名客户的公司名字改为“金叶”●删除公司名中包含“中介”的客户信息【实验结果与分析】A、给出Customers类的代码package .zucc.northwind.model;public class Customers {private String CustomerID;private String CompanyName;private String ContactName;private String ContactTitle;private String Address;private String City;private String Region;private String PostalCode;private String Country;private String Phone;private String Fax;public String getCustomerID() {return CustomerID;}public void setCustomerID(String customerID) {CustomerID = customerID;}public String getCompanyName() {return CompanyName;}public void setCompanyName(String companyName) {CompanyName = companyName;}public String getContactName() {return ContactName;}public void setContactName(String contactName) { ContactName = contactName;}public String getContactTitle() {return ContactTitle;}public void setContactTitle(String contactTitle) { ContactTitle = contactTitle;}public String getAddress() {return Address;}public void setAddress(String address) {Address = address;}public String getCity() {return City;}public void setCity(String city) {City = city;}public String getRegion() {return Region;}public void setRegion(String region) {Region = region;}public String getPostalCode() {return PostalCode;}public void setPostalCode(String postalCode) { PostalCode = postalCode;}public String getCountry() {return Country;}public void setCountry(String country) {Country = country;}public String getPhone() {return Phone;}public void setPhone(String phone) {Phone = phone;}public String getFax() {return Fax;}public void setFax(String fax) {Fax = fax;}}B、给出CustomersDAO的代码package .zucc.northwind.dao;import java.util.List;import java.sql.Connection;import java.sql.SQLException;import java.util.ArrayList;import .zucc.northwind.util.BusinessException;import .zucc.northwind.util.DBUtil;import .zucc.northwind.model.Customers;import .zucc.northwind.util.BaseException;import .zucc.northwind.util.DbException;public class CustomersDAO implements ICustomersDAO {@Overridepublic void createCustomer(Customers c) throws BaseException { // TODO Auto-generated method stubConnection conn=null;try {conn=DBUtil.getConnection();String sql="select * from Customers where CustomerID=?";java.sql.PreparedStatement pst=conn.prepareStatement(sql);pst.setString(1,c.getCustomerID());sql="insert intoCustomers(CustomerID,CompanyName,ContactName,ContactTitle,Address,Cit y,Region,PostalCode,Country,Phone,Fax)values(?,?,?,?,?,?,?,?,?,?,?)";pst=conn.prepareStatement(sql);pst.setString(1, c.getCustomerID());pst.setString(2, c.getCompanyName());pst.setString(3,c.getAddress());pst.setString(4,c.getCity());pst.setString(5,c.getRegion());pst.setString(6,c.getPostalCode());pst.setString(7,c.getCountry());pst.setString(8,c.getPhone());pst.setString(9,c.getCountry());pst.setString(10,c.getFax());pst.setString(11,c.getContactName());pst.execute();pst.close();} catch (SQLException e) {e.printStackTrace();throw new DbException(e);}finally{if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}@Overridepublic void deleteCustomer(Customers customer) throws BaseException {// TODO Auto-generated method stubConnection conn=null;try {conn=DBUtil.getConnection();String sql="select * from Customers where CustomerID=?";java.sql.PreparedStatement pst=conn.prepareStatement(sql);pst=conn.prepareStatement("delete from Customers where CustomerID=?");pst.setString(1, customer.getCustomerID());pst.execute();} catch (SQLException e) {e.printStackTrace();throw new DbException(e);}finally{if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}@Overridepublic Customers getCustomer(String customerId) throws DbException {// TODO Auto-generated method stubreturn null;}@Overridepublic void modifyCustomer(Customers customer) throws BaseException {// TODO Auto-generated method stubConnection conn=null;try {conn=DBUtil.getConnection();String sql="select * from Customers where CustomerID=?";java.sql.PreparedStatement pst=conn.prepareStatement(sql);sql="update Customers set CustomerID=?,CompanyName=?,ContactName=?,ContactTitle=?,Address=?,City=?,Region =?,PostalCode=?,Country=?,Phone=?,Fax=? where CustomerID=?";pst=conn.prepareStatement(sql);pst.setString(1, customer.getCustomerID());pst.setString(2, customer.getCompanyName());pst.setString(3,customer.getAddress());pst.setString(4,customer.getCity());pst.setString(5,customer.getRegion());pst.setString(6,customer.getPostalCode());pst.setString(7,customer.getCountry());pst.setString(8,customer.getPhone());pst.setString(9,customer.getCountry());pst.setString(10,customer.getFax());pst.setString(11,customer.getContactName());pst.execute();} catch (SQLException e) {e.printStackTrace();throw new DbException(e);}finally{if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}@Overridepublic List<Customers> qryCustomers(String companyName) throws BaseException {List<Customers> result=new ArrayList<Customers>();// TODO Auto-generated method stubConnection conn=null;try {conn=DBUtil.getConnection();String sql="selectCustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,P ostalCode,Country,Phone,Fax from Customers where CompanyName like ? order by CustomerID";java.sql.PreparedStatement pst=conn.prepareStatement(sql);pst.setString(1,"%"+companyName+"%");java.sql.ResultSet rs=pst.executeQuery();while(rs.next()){Customers p=new Customers();p.setCustomerID(rs.getString(1));p.setCompanyName(rs.getString(2));p.setContactName(rs.getString(3));p.setContactTitle(rs.getString(4));p.setAddress(rs.getString(5));p.setCity(rs.getString(6));p.setRegion(rs.getString(7));p.setPostalCode(rs.getString(8));p.setCountry(rs.getString(9));p.setPhone(rs.getString(10));p.setFax(rs.getString(11));result.add(p);}} catch (SQLException e) {e.printStackTrace();throw new DbException(e);}finally{if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return result;}}C、给出CustomersManager的代码package .zucc.northwind.control;import java.util.List;import .zucc.northwind.util.*;import .zucc.northwind.dao.CustomersDAO;import .zucc.northwind.model.Customers;public class CustomersManager {public List<Customers> loadAllCustomers()throws BaseException{ List<Customers> result=(new CustomersDAO()).qryCustomers("");return result;}}D、给出testSystem的代码package .zucc.northwind;import java.sql.Connection;import java.sql.SQLException;import .zucc.northwind.model.Customers;import .zucc.northwind.util.DBUtil;import .zucc.northwind.dao.*;import .zucc.northwind.util.*;import .zucc.northwind.control.*;import java.util.List;public class testSystem {public static void main(String[] args) {//(new testSystem()).testConnection();(new testSystem()).printAllCustomers();}public void testConnection() {Connection conn = null;try {conn = DBUtil.getConnection();System.out.println("connected!");} catch (SQLException e) {System.out.println("connect failed!");e.printStackTrace();} finally {if (conn != null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}public void printAllCustomers(){try{List<Customers> result=(newCustomersManager()).loadAllCustomers();for(int i=0;i<result.size();i++){Customers c = result.get(i);System.out.println(c.getCustomerID()+";"+c.getCompanyName() );}}catch(BaseException e){e.printStackTrace();}}}E、如果需要添加对表region的操作，需要添加几个类？分别怎么命名？。
