ZUCC数据库原理作业5

实验5  JDBC进阶（2）

一、相关知识点

        1、JDBC基本概念

        2、视图、索引

3、java集合框架

二、实验目的：

        理解视图和索引的概念，并通过视图设计简化程序设计，通过索引设计优化查询性能；

理解java集合框架

三、实验内容：

1. 设计读者视图view_reader，并修改readerManager类中相关代码。

第一步：建立读者视图，要求视图中包含读者类别名称；

第二步：改造ReaderManager类，将其中的连接查询用视图代替。

第三步：运行图书管理系统，进行各个功能的测试（读者类别管理、读者管理）

【实验结果与分析】

1. 写出视图创建代码。

CREATE VIEW view_reader as select * from beanreader

1. 给出改造后ReaderManager类的各个方法的代码。

1. 设计图书视图view_book，并修改BookManager类中相关代码。

第一步：建立图书视图，要求视图中包含出版社名称；

第二步：改造BookManager类，将其中的连接查询用视图代替。

第三步：运行图书管理系统，进行各个功能的测试

【实验结果与分析】

1. 写出视图创建代码。

CREATE VIEW view_book as select b.barcode,b.bookname,b.pubid,b.price,p.publisherName from beanbook b left join beanpublisher p on b.pubid = p.pubid;

1. 给出改造后BookManager类的各个方法的代码。

1. 设计读者借阅情况统计视图view_reader_static，并在BookLendManager类中添加根据读者ID提取其借阅数量的代码。

第一步：建立读者统计视图，要求视图中包含读者ID、读者姓名、借阅数量；

第二步：在BookLendManager中添加方法 public int loadReaderLendCount(String readerid) throws DbException。并编写其代码

第三步：在BookLendManager类中添加main函数，并编写上述方法的测试代码。进行功能的测试

【实验结果与分析】

1. 写出视图创建代码。

CREATE view view_reader_static as select b.readerid,b.readerName,count(*) lendcount from beanreader b,beanbooklendrecord l where b.readerid = l.readerid group by l.readerid

1. 给出改造后BookLendManager类的各个方法的代码。

1. 索引实验：

第一步：完成IndexTest_initData类中的代码，并测试。

第二步：完成IndexTest类中的代码，并测试，记录执行结果

第三步：通过查询分析器，在BeanBookLendRecord表的readerid上建立索引

第四步：再次执行IndexTest类，记录执行结果

1. 1. 给出两个类的代码和索引建立的代码
2. package cn.edu.zucc.booklib;
   
   import java.sql.Connection;
   import java.sql.SQLException;
   
   import cn.edu.zucc.booklib.control.BookLendManager;
   import cn.edu.zucc.booklib.util.DBUtil;
   import cn.edu.zucc.booklib.util.DbException;
   
   public class IndexTest {
   
      
       public static void main(String[] args) {
           Connection conn=null;
           try {
               conn=DBUtil.getConnection();
               long begin=System.currentTimeMillis();
               //编写代码，随意选择一个读者，查询该读者的借阅的图书总量，并输出
               BookLendManager blm = new BookLendManager();
               try {
                   System.out.println(blm.loadReaderLendCount("R001"));
               } catch (DbException e) {
                   throw new RuntimeException(e);
               }
   
               System.out.println("耗时："+(System.currentTimeMillis()-begin)+"毫秒");
   
           } catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }
           finally{
               if(conn!=null)
                   try {
                       conn.close();
                   } catch (SQLException e) {
                       // TODO Auto-generated catch block
                       e.printStackTrace();
                   }
           }
   
       }
   
   }

package cn.edu.zucc.booklib;

import java.sql.Connection;

import java.sql.SQLException;

import cn.edu.zucc.booklib.control.BookLendManager;

import cn.edu.zucc.booklib.control.BookManager;

import cn.edu.zucc.booklib.control.ReaderManager;

import cn.edu.zucc.booklib.control.SystemUserManager;

import cn.edu.zucc.booklib.model.BeanBook;

import cn.edu.zucc.booklib.model.BeanReader;

import cn.edu.zucc.booklib.model.BeanSystemUser;

import cn.edu.zucc.booklib.util.BaseException;

import cn.edu.zucc.booklib.util.DBUtil;

public class IndexTest_initData {

   

    public static void main(String[] args) throws BaseException {

        Connection conn=null;

        try {

         SystemUserManager sum = new SystemUserManager();

           BeanSystemUser admin = sum.loadUser("admin");

           sum.currentUser = admin;

          ReaderManager rm = new ReaderManager();

          BookManager bm = new BookManager();

          conn=DBUtil.getConnection();

         //编写代码，在数据库中添加1000个读者，读者id为:r1-r1000，

         // 读者名称为：读者1-读者1000，读者类别随意从数据库中取一个

        for(int i=1;i<=1000;i++){

              Integer it = i;

                BeanReader br = new BeanReader();

                br.setReaderTypeId(4);

                br.setReaderName(it.toString());

              br.setReaderid("r"+it.toString());

               br.setCreatorUserId(SystemUserManager.currentUser.getUserid());

            rm.createReader(br);

           }

          System.out.println("sussesfully created");

           //编写代码，在数据库中随机添加1000本，规则自定义

          for(int i=1;i<=1000;i++){

                Integer it = i;

              BeanBook bb = new BeanBook();

               bb.setBookname("BOOK"+it.toString());

               bb.setBarcode("B1"+it.toString());

              bb.setPrice(100);

              bm.createBook(bb);

         }

          System.out.println("sussesfully created");

            //编写代码，给所有用户借阅所有书籍，其中returnDate也用当前时间

            BookLendManager blm = new BookLendManager();

            for(int i=1;i<=1000;i++){

                Integer it = i;

                blm.lend("B1"+it.toString(),"r"+it.toString());

            }

            System.out.println("success");

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        finally{

            if(conn!=null)

                try {

                    conn.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

        }

    }

}

ALTER  TABLE  `BeanBookLendRecord`  ADD  INDEX inx_1 (  `readerid`  )

1. 比较IndexTest类两次执行的结果，并说明索引的作用。并思考：如果我们需要经常查询某用户近期借阅的n本图书，请问，应该如何建立索引？【提示：多列索引】

变快了

1. 数据统计实验：

A、在ReaderManager中，添加函数Map staticTypeReaderCount()...

要求通过读者类别名称统计其读者数量，请给出代码：

    Map staticTypeReaderCount() throws SQLException {

        HashMap map = new HashMap<>();

        Connection conn = DBUtil.getConnection();

        String sql = " select readerTypeName,count(*) from beanreader r left join beanreadertype t on r.readerTypeId = t.readerTypeId GROUP BY readerTypeName";

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){

            String name = rs.getString(1);

            Integer count = rs.getInt(2);

            map.put(name,count);

        }

        conn.close();

        rs.close();

        return map;

    }

    public static void main(String[] args) throws SQLException {

        BookManager bm = new BookManager();

        System.out.println(bm.staticTypeReaderCount());

    }

B、在BookManager中，添加函数Map staticPublisherBookAvgPrice()...  。要求统计各出版社名称及其图书的平均价格，请给出代码：

    Map staticPublisherBookAvgPrice(){

        HashMap map = new HashMap<>();

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "select publisherName,avg(price) from beanbook,beanpublisher where beanbook.pubid = beanpublisher.pubid group by beanbook.pubid";

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){

                String name = rs.getString(1);

                Double avg = rs.getDouble(2);

                map.put(name,avg);

            }

        } catch (SQLException e) {

            throw new RuntimeException(e);

        }finally {

            if(conn!= null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        }

        return map;

    }

    public static void main(String[] args) throws SQLException {

        BookManager bm = new BookManager();

        System.out.println(bm.staticPublisherBookAvgPrice());

    }

C、在BookLendManager中，添加函数Map staticReaderBookCount()... 。要求统计读者为归还的图书数量，返回结果的key为读者ID。请给出代码：

    Map staticReaderBookCount(){

        HashMap map = new HashMap<>();

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "select readerid,count(*) from beanbooklendrecord where returnDate is NULL GROUP BY readerid";

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){

                String id = rs.getString(1);

                Integer count = rs.getInt(2);

                map.put(id,count);

            }

           

        } catch (SQLException e) {

            throw new RuntimeException(e);

        }finally {

            if(conn!=null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        }

        return map;

    }

1. 集合对象的遍历实验：

1. 编写批量借阅读书函数： public void lendbooks(String readerId,Collection barcodes) .... 。其中第二个参数为图书条码集合。

    public void lendbooks(String readerId,Collection barcodes){

        barcodes = new ArrayList();

        BookLendManager blm = new BookLendManager();

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "select barcode from beanbook";

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){

                String code = rs.getString(1);

                barcodes.add(code);

            }

            Iterator iterator = barcodes.iterator();

            while(iterator.hasNext()){

                String code = iterator.next();

                try {

                    blm.lend(code,readerId);

                } catch (BaseException e) {

                    throw new RuntimeException(e);

                }

            }

        } catch (SQLException e) {

            throw new RuntimeException(e);

        }finally {

            if(conn!=null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        }

    }

B、编写批量设置罚金函数：public void setPenalSum(String readerId,Map penalSums) .... 。其中第二个参数的key为barcode，value为改读者尚未归还图书的罚金（注意，不要设置已经归还图书的罚金）。

       public void setPenalSum(String readerId,Map penalSums){

       

        Set> es = penalSums.entrySet();

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "update beanbooklendrecord set penalSum = ? where readerid = ? and bookbarcode = ?";

            PreparedStatement ps = conn.prepareStatement(sql);

            for(Map.Entry entry:es){

                String key = entry.getKey();

                Double value = entry.getValue();

                ps.setDouble(1,value);

                ps.setString(2,readerId);

                ps.setString(3,key);

                ps.execute();

            }}

            catch (SQLException e) {

            throw new RuntimeException(e);

        }finally {

            if(conn!=null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        }

    }