数据库原理及应用课程上机实验 - 实验8：（2学时） - 《编程知识》

一、实验目的
1、掌握JAVA连接和操作MySQL数据库的方法；
2、掌握程序调试方法；

二、实验环境
装有软件MySQL5.7或更高版本的PC电脑一台；

三、实验步骤及实验要求
按照下面的实验内容要求完成作业，将作业结果的每一步截图粘贴到word文档中即可。每一个实验都必须编写实验报告，要求如下：
1、实验报告用word编写；
2、word文件命名的格式统一要求：为以“杨健”同学19号为例，他所做的第4次实验的实验报告应该命令为：“DB实验4— 19号—杨健”（即格式是：实验序号—课内序号—姓名）；课内序号现在是一个人一门课一个号，同一个人在不同课序号会不同，回头我会将课程名单发到群里，自己查阅你自己在本门课中的序号。
3、实验报告用统一的封面，封面模板到时发给大家；
4、报告中截取每题主要步骤结果的截图、实验结果截图；
5、实验报告最后要加上实验总结，总结部分必须写出自己的切身体会；
6、实验报告如有雷同、抄袭现象，后果自负；
7、实验报告上交截止时间：上机后一周之内；
8、实验上交方式：由学委收集齐全后，统一交付老师：

四、实验内容
1、基于上述“简易图书管理系统”数据库，利用java语言实现如下功能：
首先写四个实体类

package org.example.bean;
import java.util.Date;
/**
 * 图书信息对象 book
 *
 * @author 翁修林
 * @date 2022-10-14
 */
public class Book {
    private static final long serialVersionUID = 1L;
    /** 图书序号 */
    private String bkId;
    /** 书名 */
    private String bkName;
    /** 作者 */
    private String bkAuthor;
    /** 出版社 */
    private String bkPress;
    /** 价格 */
    private Double bkPrice;
    /** 图书状态 */
    private Integer bkStatus;
    public Book() {
    }
    public Book(String bkId, String bkName, String bkAuthor, String bkPress, Double bkPrice, Integer bkStatus) {
        this.bkId = bkId;
        this.bkName = bkName;
        this.bkAuthor = bkAuthor;
        this.bkPress = bkPress;
        this.bkPrice = bkPrice;
        this.bkStatus = bkStatus;
    }
    public String getBkId() {
        return bkId;
    }
    public void setBkId(String bkId) {
        this.bkId = bkId;
    }
    public String getBkName() {
        return bkName;
    }
    public void setBkName(String bkName) {
        this.bkName = bkName;
    }
    public String getBkAuthor() {
        return bkAuthor;
    }
    public void setBkAuthor(String bkAuthor) {
        this.bkAuthor = bkAuthor;
    }
    public String getBkPress() {
        return bkPress;
    }
    public void setBkPress(String bkPress) {
        this.bkPress = bkPress;
    }
    public Double getBkPrice() {
        return bkPrice;
    }
    public void setBkPrice(Double bkPrice) {
        this.bkPrice = bkPrice;
    }
    public Integer getBkStatus() {
        return bkStatus;
    }
    public void setBkStatus(Integer bkStatus) {
        this.bkStatus = bkStatus;
    }
    @Override
    public String toString() {
        return "Book{" +
                "bkId='" + bkId + '\'' +
                ", bkName='" + bkName + '\'' +
                ", bkAuthor='" + bkAuthor + '\'' +
                ", bkPress='" + bkPress + '\'' +
                ", bkPrice=" + bkPrice +
                ", bkStatus=" + bkStatus +
                '}';
    }
}

package org.example.bean;
import java.util.Date;
/**
 * 借阅信息对象 borrow
 *
 * @author 翁修林
 * @date 2022-10-14
 */
public class Borrow {
    private static final long serialVersionUID = 1L;
    /** 读者序号 */
    private String rdId;
    /** 图书序号 */
    private String bkId;
    /**
     * 借书日期
     */
    private Date DateBorrow;
    /**
     * 应还日期
     */
    private Date DateLendPlan;
    /**
     * 实际还书日期
     */
    private Date DateLendAct;
    public Borrow() {
    }
    public Borrow(String rdId, String bkId, Date dateBorrow, Date dateLendPlan, Date dateLendAct) {
        this.rdId = rdId;
        this.bkId = bkId;
        DateBorrow = dateBorrow;
        DateLendPlan = dateLendPlan;
        DateLendAct = dateLendAct;
    }
    public String getRdId() {
        return rdId;
    }
    public void setRdId(String rdId) {
        this.rdId = rdId;
    }
    public String getBkId() {
        return bkId;
    }
    public void setBkId(String bkId) {
        this.bkId = bkId;
    }
    public Date getDateBorrow() {
        return DateBorrow;
    }
    public void setDateBorrow(Date dateBorrow) {
        DateBorrow = dateBorrow;
    }
    public Date getDateLendPlan() {
        return DateLendPlan;
    }
    public void setDateLendPlan(Date dateLendPlan) {
        DateLendPlan = dateLendPlan;
    }
    public Date getDateLendAct() {
        return DateLendAct;
    }
    public void setDateLendAct(Date dateLendAct) {
        DateLendAct = dateLendAct;
    }
    @Override
    public String toString() {
        return "Borrow{" +
                "rdId='" + rdId + '\'' +
                ", bkId='" + bkId + '\'' +
                ", DateBorrow=" + DateBorrow +
                ", DateLendPlan=" + DateLendPlan +
                ", DateLendAct=" + DateLendAct +
                '}';
    }
}

package org.example.bean;
import java.util.Date;
/**
 * 读者信息对象 reader
 *
 * @author 翁修林
 * @date 2022-10-14
 */
public class Reader {
    private static final long serialVersionUID = 1L;
    /** 读者编号 */
    private String rdId;
    /** 读者类别 */
    private Long rdType;
    /** 读者姓名 */
    private String rdName;
    /** 单位代码 */
    private String rdDept;
    /**
     * QQ
     */
    private String rdQQ;
    /**
     * 借书数量
     */
    private Integer rdBorrowQty;
    public Reader() {
    }
    public Reader(String rdId, Long rdType, String rdName, String rdDept, String rdQQ, Integer rdBorrowQty) {
        this.rdId = rdId;
        this.rdType = rdType;
        this.rdName = rdName;
        this.rdDept = rdDept;
        this.rdQQ = rdQQ;
        this.rdBorrowQty = rdBorrowQty;
    }
    public String getRdId() {
        return rdId;
    }
    public void setRdId(String rdId) {
        this.rdId = rdId;
    }
    public Long getRdType() {
        return rdType;
    }
    public void setRdType(Long rdType) {
        this.rdType = rdType;
    }
    public String getRdName() {
        return rdName;
    }
    public void setRdName(String rdName) {
        this.rdName = rdName;
    }
    public String getRdDept() {
        return rdDept;
    }
    public void setRdDept(String rdDept) {
        this.rdDept = rdDept;
    }
    public String getRdQQ() {
        return rdQQ;
    }
    public void setRdQQ(String rdQQ) {
        this.rdQQ = rdQQ;
    }
    public Integer getRdBorrowQty() {
        return rdBorrowQty;
    }
    public void setRdBorrowQty(Integer rdBorrowQty) {
        this.rdBorrowQty = rdBorrowQty;
    }
    @Override
    public String toString() {
        return "Reader{" +
                "rdId='" + rdId + '\'' +
                ", rdType=" + rdType +
                ", rdName='" + rdName + '\'' +
                ", rdDept='" + rdDept + '\'' +
                ", rdQQ='" + rdQQ + '\'' +
                ", rdBorrowQty=" + rdBorrowQty +
                '}';
    }
}

package org.example.bean;
/**
 * 读者类别对象 readertype
 *
 * @author 翁修林
 * @date 2022-10-14
 */
public class Readertype {
    private static final long serialVersionUID = 1L;
    /** 读者类别 */
    private Integer rdtType;
    /** 读者类别名称 */
    private String rdtTypeName;
    /** 可借书天数 */
    private Integer rdtLendDay;
    private Integer canlendDay;
    public Readertype() {
    }
    public Readertype(Integer rdtType, String rdtTypeName, Integer rdtLendDay, Integer canlendDay) {
        this.rdtType = rdtType;
        this.rdtTypeName = rdtTypeName;
        this.rdtLendDay = rdtLendDay;
        this.canlendDay = canlendDay;
    }
    public Integer getRdtType() {
        return rdtType;
    }
    public void setRdtType(Integer rdtType) {
        this.rdtType = rdtType;
    }
    public String getRdtTypeName() {
        return rdtTypeName;
    }
    public void setRdtTypeName(String rdtTypeName) {
        this.rdtTypeName = rdtTypeName;
    }
    public Integer getRdtLendDay() {
        return rdtLendDay;
    }
    public void setRdtLendDay(Integer rdtLendDay) {
        this.rdtLendDay = rdtLendDay;
    }
    public Integer getCanlendDay() {
        return canlendDay;
    }
    public void setCanlendDay(Integer canlendDay) {
        this.canlendDay = canlendDay;
    }
    @Override
    public String toString() {
        return "Readertype{" +
                "rdtType=" + rdtType +
                ", rdtTypeName='" + rdtTypeName + '\'' +
                ", rdtLendDay=" + rdtLendDay +
                ", canlendDay=" + canlendDay +
                '}';
    }
}

写一个通用CRUD

package org.example.base;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
 * 定义一个用来被继承的对数据库进行基本操作的Dao
 *
 * 引入 pom commons.dbutils
 *
 * @author HanYanBing
 *
 * @param <T>
 */
public abstract class BaseDao<T> {
    private QueryRunner queryRunner = new QueryRunner();
    // 定义一个变量来接收泛型的类型
    private Class<T> type;
    // 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
    public BaseDao() {
        // 获取子类的类型
        Class clazz = this.getClass();
        // 获取父类的类型
        // getGenericSuperclass()用来获取当前类的父类的类型
        // ParameterizedType表示的是带泛型的类型
        ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
        // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
        // 这个方法会返回一个Type的数组
        Type[] types = parameterizedType.getActualTypeArguments();
        // 获取具体的泛型的类型·
        this.type = (Class<T>) types[0];
    }
    /**
     * 通用的增删改操作
     *
     * @param sql
     * @param params
     * @return
     */
    public int update(Connection conn,String sql, Object... params) {
        int count = 0;
        try {
            count = queryRunner.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    /**
     * 获取一个对象
     *
     * @param sql
     * @param params
     * @return
     */
    public T getBean(Connection conn,String sql, Object... params) {
        T t = null;
        try {
            t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t;
    }
    /**
     * 获取所有对象
     *
     * @param sql
     * @param params
     * @return
     */
    public List<T> getBeanList(Connection conn,String sql, Object... params) {
        List<T> list = null;
        try {
            list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 获取一个但一值得方法，专门用来执行像 select count(*)...这样的sql语句
     *
     * @param sql
     * @param params
     * @return
     */
    public Object getValue(Connection conn,String sql, Object... params) {
        Object count = null;
        try {
            // 调用queryRunner的query方法获取一个单一的值
            count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}

写一个JDBC连接池

package org.example.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
 * Druid连接池工具类,将来dao层调用
 */
public class JDBCUtils {
    private static DataSource dataSource;   //定义成员变量DataSource
    static {
        try {
            //加载配置文件
            Properties properties = new Properties();
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //获取DataSource
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    /**
     * 释放资源
     */
    public static void close(Statement statement,Connection connection) {
        close(null,statement,connection);
    }
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();//归还连接
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 获取连接池方法
     */
    public static DataSource getDataSource() {
        return dataSource;
    }
}

（1）读者类别管理：添加读者类别、修改读者类别、删除读者类别；

    /**
     * 添加读者类别
     */
    @Test
    public void saveReadertypeTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Readertype readertype = new Readertype(5,"测试添加读者类别",20,50);
            readertypeDaoimpl.saveReadertype(conn,readertype);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 修改读者类别
     */
    @Test
    public void updateReadertypeTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Readertype readertype = new Readertype(5,"测试修改读者类别",66666666,200);
            readertypeDaoimpl.updateReadertype(conn,readertype);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    /**
     * 删除读者类别
     */
    @Test
    public void deleteReadertypeByIdTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
//            Readertype readertype = new Readertype(5,"测试修改读者类别",66666666,200);
            readertypeDaoimpl.deleteReadertypeById(conn,"5");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

（2）图书管理：添加图书、修改图书、查找图书、删除图书；

    /**
     * 添加图书
     */
    @Test
    public void saveBookTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Book book = new Book("bk2017009","测试添加图书","翁修林","长江大学",100.0,1);
            bookDaoimpl.saveBook(conn,book);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 修改图书
     */
    @Test
    public void updateBookTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Book book = new Book("bk2017009","测试修改图书","修改图书","修改图书",100.0,1);
            bookDaoimpl.updateBook(conn,book);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 查找图书
     */
    @Test
    public void getBookByIdTest(){
        Book book =null;
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            book = bookDaoimpl.getBookById(conn,"bk2017009");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        System.out.println(book);
    }

    /**
     * 删除图书
     */
    @Test
    public void deleteBookByIdTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            bookDaoimpl.deleteBookById(conn,"bk2017009");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

（3）读者管理：添加读者、修改读者、删除读者；

/**
* 添加读者
*/
public void AddReaderTest(){
    try {
        //1.获取连接的操作（
        conn = JDBCUtils.getConnection();
        //2.对数据表进行一系列CRUD操作
        Reader reader = new Reader("rd2017010",1L,"JDBC测试","计算机科学学院","123456",1);
        readerDaoimpl.saveReader(conn,reader);
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //回滚数据
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }finally{
        //3.关闭连接等操作
        try {
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
}

    /**
     * 修改读者
     */
    @Test
    public void updateTbReaderTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            Reader reader = new Reader("rd2017010",1L,"JDBC测试修改读者","计算机科学学院","123456",1);
            //2.对数据表进行一系列CRUD操作
            readerDaoimpl.updateReader(conn,reader);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 删除读者
     */
    @Test
    public void deleteReaderByIdTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            readerDaoimpl.deleteReaderById(conn,"rd2017010");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

（4）借书、还书

    /**
     * 借书
     */
    @Test
    public void LendBookTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Book book = null;
            Reader reader = null;
            book = bookDaoimpl.getBookById(conn,"bk2017001");
            reader = readerDaoimpl.getReaderById(conn,"rd2017001");
            Date date = new Date();
            Borrow borrow = new Borrow(reader.getRdId(),book.getBkId(),date,date,date);
            if (book != null && reader != null){
                borrowDaoimpl.saveBorrow(conn,borrow);
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 还书
     */
    @Test
    public void huanshuTest(){
        try {
            //1.获取连接的操作（
            conn = JDBCUtils.getConnection();
            //2.对数据表进行一系列CRUD操作
            Book book = null;
            Reader reader = null;
            book = bookDaoimpl.getBookById(conn,"bk2017001");
            reader = readerDaoimpl.getReaderById(conn,"rd2017001");
            Date date = new Date();
            Borrow borrow = new Borrow(reader.getRdId(),book.getBkId(),date,date,date);
            if (book != null && reader != null){
                borrowDaoimpl.deleteBorrowById(conn,book.getBkId(),reader.getRdId());
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //回滚数据
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //3.关闭连接等操作
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }