实验七：JDBC进阶（2）

相关知识点

  1. JDBC基本概念
  2. 主从关系，分页查询，连接池

实验目的

​ 理解分页查询的概念和处理方法

实验内容

数据准备：编写程序，自动生成1000个读者和图书

public class Add1000Books {
    public static void main(String[] args) throws BaseException {
        BookManager bookManager = new BookManager();
        BeanBook book = new BeanBook();

        int barcode = 1005;

        for (int i = 0; i < 1000; i++) {
            book.setBarcode(String.valueOf(barcode + i));
            book.setBookname("book_" + (i + 1));
            book.setPubid(String.valueOf(1));
            book.setPrice(30);

            bookManager.createBook(book);
        }
    }
}

image-20220523145316921

public class Add1000Readers {
    public static void main(String[] args) throws BaseException {
        ReaderManager readerManager = new ReaderManager();
        BeanReader reader = new BeanReader();
        BeanSystemUser user = new BeanSystemUser();
        user.setUserid("admin");
        user.setUsername("系统管理员");
        user.setUsertype("管理员");

        SystemUserManager systemUserManager = new SystemUserManager();
        SystemUserManager.currentUser = user;

        int barcode = 32001281;
        for (int i = 0; i < 1000; i++) {
            reader.setReaderid(String.valueOf(barcode + i));
            reader.setReaderName("reader_" + (i + 1));
            reader.setReaderTypeId(4);
            reader.setCreatorUserId("admin");

            readerManager.createReader(reader);
        }
    }
}

image-20220523151537777

改造读者模块，在提取读者的同时，提取其未归还的图书信息

  1. 通过程序增加一些借阅纪录

    image-20220523152246062

  2. 改造读者javabean，使之包括借阅的图书信息

  3. 改造ReaderManager中的读者提取方法（public BeanReader loadReader(String readerid) throws DbException ），同时提取未归还图书；

  4. ReaderManager的main函数中调用该方法进行测试，要求输出指定读者的基本信息及其未归还的图书名称。

javabean类代码

BeanReader.java：

private ArrayList<BeanBook> beanBooks = new ArrayList<BeanBook>();

public ArrayList<BeanBook> getBeanBooks() {
	return beanBooks;
}

public void setBeanBooks(ArrayList<BeanBook> beanBooks) {
	this.beanBooks = beanBooks;
}

BeanBook.java：

 @Override
public String toString() {
  return "BeanBook{" +
    "barcode='" + barcode + '\'' +
    ", bookname='" + bookname + '\'' +
    ", pubid='" + pubid + '\'' +
    ", price=" + price +
    ", state='" + state + '\'' +
    ", pubName='" + pubName + '\'' +
    ", storageTime=" + storageTime +
    '}';
}

给出改造后ReaderManager类的方法代码

r.setRemoveDate(rs.getDate(10));   
// 增加行
r.setBeanBooks((ArrayList<BeanBook>)BookLendManager.loadReaderLentBooks(readerid));
return r;

image-20220523153950054

改造读者管理模块，将读者列表页面改造成分页查询方式。

  1. 自行设计PageData类，用于存放分页数据
  2. 改造ReaderManager类，将其中的查询读者方法改造成分页查询。

PageData类代码

public class PageData {
    private int totalRecordCount;
    private int pageSize = 10;
    private int pageIndex = 1;
    private ArrayList<BeanReader> readers = new ArrayList<>(pageSize);

    public PageData(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalRecordCount() {
        return totalRecordCount;
    }

    public void setTotalRecordCount(int totalRecordCount) {
        this.totalRecordCount = totalRecordCount;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(int pageIndex) {
        this.pageIndex = pageIndex;
    }
}

给出改造后ReaderManager类的方法代码

sql += "limit ?,?";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
if (keyword != null && !"".equals(keyword)) {
    pst.setString(1, "%" + keyword + "%");
    pst.setString(2, "%" + keyword + "%");
    pst.setObject(3, (PageData.getPageIndex()-1)*PageData.getPageSize());
    pst.setObject(4,PageData.getPageSize());
}else {
    pst.setObject(1, (PageData.getPageIndex()-1)*PageData.getPageSize());
    pst.setObject(2,PageData.getPageSize());
}

用C3P0连接池改造booklib应用

  1. 将mchange-commons-java-0.2.3.4、c3p0-0.9.2.1.jar引入工程

    image-20220523155832160

  2. 在cn.edu.zucc.booklib.util包下增加类DBUtil2，并模仿DBPool类实现DBUtil类中定义的功能

  3. 改造BookManager类，将其各方法中获取数据库的连接的方法改成用DBUtil2；

  4. BookManager中编写main函数，利用循环待用添加图书方法的形式增加1000本图书，并记录整体运行时间；分别测试利用DBUtil和DBUtil2获取数据库连接的耗时。

DBUtil2类代码

public class DBUtil2 {
    private static final String driverclass = "com.mysql.cj.jdbc.Driver";
    private static final String jdbcUrl = "jdbc:mysql://localhost:3306/booklib?useUnicode=true&characterEncoding=UTF-8";
    private static final String dbUser = "root";
    private static final String dbPwd = "12345678";

    public static Connection getConnection() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass(driverclass);
        cpds.setJdbcUrl(jdbcUrl);
        cpds.setUser(dbUser);
        cpds.setPassword(dbPwd);
        return cpds.getConnection();
    }
}

给出改造后BookManager类的main函数代码

    public void createBookByDBUtil2(BeanBook b, Connection conn) throws Exception {
        if (b.getBarcode() == null || "".equals(b.getBarcode()) || b.getBarcode().length() > 20) {
            throw new BusinessException("条码必须是1-20个字");
        }
        if (b.getBookname() == null || "".equals(b.getBookname()) || b.getBookname().length() > 50) {
            throw new BusinessException("图书名称必须是1-50个字");
        }
        try {
            String sql = "select * from BeanBook where barcode=?";
            java.sql.PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, b.getBarcode());
            java.sql.ResultSet rs = pst.executeQuery();
            if (rs.next()) throw new BusinessException("条码已经被占用");
            rs.close();
            pst.close();
            sql = "insert into BeanBook(barcode,bookname,pubid,price,state,storagetime) values(?,?,?,?,'在库',?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1, b.getBarcode());
            pst.setString(2, b.getBookname());
            pst.setString(3, b.getPubid());
            pst.setDouble(4, b.getPrice());
            pst.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));
            pst.execute();
            pst.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        }
    }


public class DBUtilAndDBUtil2Text {
    public static void main(String[] args) throws Exception {
        BookManager bookManager = new BookManager();
        BeanBook book = new BeanBook();
        int barcode;

        long startTime = System.currentTimeMillis();
        Connection conn = DBUtil2.getConnection();
        barcode = 2000;
        for (int i = 0; i < 1000; i++) {
            book.setBarcode(String.valueOf(barcode + i));
            book.setBookname("book_" + (barcode + i));
            book.setPubid(String.valueOf(1));
            book.setPrice(30);

            bookManager.createBookByDBUtil2(book, conn);
        }
        conn.close();
        long endTime = System.currentTimeMillis();
        System.out.println("DBUtil2使用时间： " + (endTime - startTime));

        startTime = System.currentTimeMillis();
        barcode = 3000;
        for (int i = 0; i < 1000 ; i++) {
            book.setBarcode(String.valueOf(barcode + i));
            book.setBookname("book_" + (barcode + i));
            book.setPubid(String.valueOf(1));
            book.setPrice(30);

            bookManager.createBookByDBUtil(book);
        }
        endTime = System.currentTimeMillis();
        System.out.println("DBUtil使用时间： " + (endTime - startTime));
    }
}

给出用DBUtil和DBUtil2获取数据库连接时，main函数的执行耗时

image-20220523170509187