栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

ZUCC

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

ZUCC

实验四
课程名称数据库系统概论实验
实验项目名称JDBC进阶
相关知识点
  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);
        }
    }
}

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);
        }
    }
}

改造读者模块,在提取读者的同时,提取其未归还的图书信息
  1. 通过程序增加一些借阅纪录

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

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

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

javabean类代码

BeanReader.java:

private ArrayList beanBooks = new ArrayList();

public ArrayList getBeanBooks() {
	return beanBooks;
}

public void setBeanBooks(ArrayList 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)BookLendManager.loadReaderLentBooks(readerid));
return r;

改造读者管理模块,将读者列表页面改造成分页查询方式。
  1. 自行设计PageData类,用于存放分页数据
  2. 改造ReaderManager类,将其中的查询读者方法改造成分页查询。
PageData类代码
public class PageData {
    private int totalRecordCount;
    private int pageSize = 10;
    private int pageIndex = 1;
    private ArrayList 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引入工程

  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函数的执行耗时

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/973235.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号