【Java】实现图书借阅系统

God Wei

已于 2024-11-04 13:15:38 修改

阅读量709

点赞数 7

分类专栏： java基础文章标签： java 开发语言

于 2024-10-30 19:56:12 首次发布

本文链接：https://blog.csdn.net/weixin_44163023/article/details/143372705

版权

java基础专栏收录该内容

4 篇文章

订阅专栏

使用jdbc实现

数据库 mysql 5.7
jdk 1.8 以上
maven v 3.6.3 以上

<!-- jdbc依赖 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>

需求如下

图书借阅系统

数据库表：
用户表：id,用户名，密码
图书表：id,图书名称，图书价格，出版社，作者，可借阅数量
借阅表：id,用户id,图书id,借阅时间,归还时间，时间使用datetime（java对应类型为Date）

主要功能：
1）添加图书
图书名不能相同
2）删除图书
如果删除的图书有人借阅，不能删除
3）搜索图书
根据书名模糊查询
4）用户登录
输入用户名和密码，如果登录成功，保存登录的用户的信息
如果登录失败，需要提示是用户名错误还是密码错误
5）用户注册
输入用户名、密码、二次确认密码
需要判断两次输入密码是否一致
注册时，用户名不能相同
6）已登录用户借阅图书
同一用户归还图书之前，不能再次借阅相同图书
需要判断可借阅数量是否足够
注意：需要判断是否登录
7）已登录用户查看自己所有的借阅信息
返回数据包括：图书id,图书名称，作者，价格，借阅时间，是否归还(显示是/否)
根据借阅时间倒序显示记录
注意：需要判断是否登录
8）已登录用户查询未归还图书信息
返回数据包括：图书id,图书名称，作者，价格，借阅时间
根据借阅时间倒序显示记录
注意：需要判断是否登录
9）已登录用户还书
修改图书的可借阅数量
修改归还时间
注意：需要判断是否登录

在main方法中，选择相应功能进行操作
通过Scanner输入需要处理的数据
如果用户未登录，功能列表中不显示当前登录用户信息

功能列表显示如下：

图书借阅系统当前登录用户:XXXX

1 添加图书
2 删除图书
3 根据书名搜索图书
4 用户注册
5 用户登录
6 查看已借阅图书信息
7 查看未归还图书信息
8 借书
9 还书
0 退出系统

请选择需要执行的功能：

建一个数据库

名字为exam(自定义叫啥都行)

再建表

用户表

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `username` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_user` VALUES (1, 'admin', '123456');
INSERT INTO `tb_user` VALUES (2, 'zhangsan', '123456');
INSERT INTO `tb_user` VALUES (3, 'wangwu', '123456');
INSERT INTO `tb_user` VALUES (4, 'zhangwei', '123');

图书表

DROP TABLE IF EXISTS `tb_book`;
CREATE TABLE `tb_book`  (
  `bid` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书编号',
  `bname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图书名称',
  `price` double(10, 2) NULL DEFAULT NULL COMMENT '图书价格',
  `press` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',
  `author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者',
  `nums` int(11) NULL DEFAULT NULL COMMENT '可借阅数量',
  PRIMARY KEY (`bid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_book` VALUES (1, '时间简史', 100.00, '人民出版社', '霍金', 50);
INSERT INTO `tb_book` VALUES (2, 'java从入门到放弃', 110.00, '清华出版社', '大佬', 2);
INSERT INTO `tb_book` VALUES (3, '西游记', 80.00, '人民出版社', '吴承恩', 10);
INSERT INTO `tb_book` VALUES (4, '水浒传', 11.00, '新华出版社', '施耐庵', 9);

借阅表

DROP TABLE IF EXISTS `tb_user_read`;
CREATE TABLE `tb_user_read`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
  `bid` int(20) NULL DEFAULT NULL COMMENT '图书id',
  `create_time` datetime NULL DEFAULT NULL COMMENT '借阅时间',
  `return_time` datetime NULL DEFAULT NULL COMMENT '归还时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_user_read` VALUES (1, 2, 1, '2024-10-28 10:43:30', '2024-10-29 10:43:35');
INSERT INTO `tb_user_read` VALUES (2, 3, 2, '2024-10-28 10:50:00', '2024-11-01 19:23:32');
INSERT INTO `tb_user_read` VALUES (3, 2, 3, '2024-10-29 11:13:25', '2024-11-01 19:23:41');

实现ORM

封装用户实体类

package com.ljw.entity;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/10/29 9:18
 */
public class User {
    private int uid;
    private String username;
    private String password;

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public User() {
    }

    public User(int uid, String username, String password) {
        this.uid = uid;
        this.username = username;
        this.password = password;
    }
}

封装图书实体类

package com.ljw.entity;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/10/29 9:18
 */
public class Book {
    private int bid;
    private String bname;
    private double price;
    private String press;
    private String author;
    private int nums;

    @Override
    public String toString() {
        return
                "图书编号" + bid +
                ", 图书名称 " + bname + '\'' +
                ", 图书价格 " + price +
                ", 出版社'" + press + '\'' +
                ", 作者 " + author + '\'' +
                ", 可借阅数量 " + nums ;
    }

    public Book() {
    }

    public Book(int bid, String bname, double price, String press, String author, int nums) {
        this.bid = bid;
        this.bname = bname;
        this.price = price;
        this.press = press;
        this.author = author;
        this.nums = nums;
    }

    public int getBid() {
        return bid;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    public String getBname() {
        return bname;
    }

    public void setBname(String bname) {
        this.bname = bname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getPress() {
        return press;
    }

    public void setPress(String press) {
        this.press = press;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public int getNums() {
        return nums;
    }

    public void setNums(int nums) {
        this.nums = nums;
    }
}

封装借阅表实体类

package com.ljw.VO;

import java.util.Date;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/10/29 9:20
 */
public class BookUserVO {
    private int id;
    private int uid;
    private int bid;
    private Date createTime;
    private Date returnTime;

    @Override
    public String toString() {
        return
                "ID" + id +
                ", 用户id " + uid +
                ", 图书id " + bid +
                ", 借阅时间 " + createTime +
                ", 归还时间 " + returnTime ;
    }

    public BookUserVO() {
    }

    public BookUserVO(int id, int uid, int bid, Date createTime, Date returnTime) {
        this.id = id;
        this.uid = uid;
        this.bid = bid;
        this.createTime = createTime;
        this.returnTime = returnTime;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public int getBid() {
        return bid;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getReturnTime() {
        return returnTime;
    }

    public void setReturnTime(Date returnTime) {
        this.returnTime = returnTime;
    }
}

实现系统功能主方法

package com.ljw;

import com.ljw.VO.BookUserVO;
import com.ljw.entity.Book;
import com.ljw.entity.User;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/10/29 9:17
 */
public class BooksManageSystem {
    static Connection connection=null;
    static Scanner scanner=new Scanner(System.in);
    static PreparedStatement preparedStatement=null;
    static ResultSet resultSet ;
    static String LoginUserName="";  //当前登录人
    private static User user;
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/exam?useSSL=false";
        String username="root";
        String password="123456";
        connection = DriverManager.getConnection(url, username, password);
        Welcome();
        connection.close();
        preparedStatement.close();
        resultSet.close();
    }
    public static void Welcome() throws SQLException {
        while(true){
            System.out.println("****************************");
            System.out.println("欢迎来到图书管理系统 当前登录用户:"+LoginUserName+"^_^");
            System.out.println("****************************");
            System.out.println("1.添加图书");
            System.out.println("2.删除图书");
            System.out.println("3.查找书名搜索图书");
            System.out.println("4.用户注册");
            System.out.println("5.用户登录");
            System.out.println("6.查看已借阅图书信息");
            System.out.println("7.查看未归还图书信息");
            System.out.println("8.借书");
            System.out.println("9.还书");
            System.out.println("0.退出系统");
            System.out.println("****************************");
            System.out.print("请选择需要执行的功能:");
            int i = scanner.nextInt();
            switch (i){
                case 1:
                    Book book = new Book();
                    if(!Objects.equals(LoginUserName, "")){
                        int i1 = addBook(book);
                        if(i1>0){
                            System.out.println("添加图书成功");
                        }else{
                            System.out.println("添加图书失败");
                        }
                    }else{
                        System.out.println("请先登录用户！");
                    }
                    break;
                case 2:
                    if(!Objects.equals(LoginUserName, "")){
                        System.out.println("请输入删除图书名称");
                        String name = scanner.next();
                        boolean b = delBook(name);
                        if(b){
                            System.out.println("删除成功");
                        }else{
                            System.out.println("删除失败");
                        }
                    }else{
                        System.out.println("请先登录用户！");
                    }
                    break;
                case 3:
                    if(!Objects.equals(LoginUserName, "")){
                        System.out.print("请输入需要查询的图书名称:");
                        String Bname = scanner.next();
                        Book book1= searchBookByBname(Bname);
                        System.out.println("---  图书信息如下  ---");
                        if(book1!=null){
                            System.out.print("图书编号:"+book1.getBid()+"\t");
                            System.out.print("图书名称:"+book1.getBname()+"\t");
                            System.out.print("图书价格:"+book1.getPrice()+"\t");
                            System.out.print("出版社:"+book1.getPress()+"\t");
                            System.out.print("作者:"+book1.getAuthor()+"\t");
                            System.out.print("可借阅图书数量:"+book1.getNums()+"\t");
                        }
                    }else{
                        System.out.println("请先登录用户！");
                    }
                    break;
                case 4:
                    User user1 = new User();
                    boolean register = register(user1);
                    if(register){
                        System.out.println("注册成功");
                    }else{
                        System.out.println("注册失败，请重新注册");
                    }
                    break;
                case 5:
                    user = login();
                    String username = user.getUsername();
                    LoginUserName=username;
                    break;
                case 6:
                    borrowedList();
                    break;
                case 7:
                    notReturnBookMessage();
                    break;
                case 8:
                    System.out.print("请输入借阅的图书名称:");
                    String Bname = scanner.next();
                    borrowBook(Bname);
                    break;
                case 9:
                    returnBooks();
                    break;
                case 0:
                    System.out.println("谢谢~");
                    System.exit(0);
                default:
                    System.out.println("输入错误！");
                    break;
            }
        }
    }
    //添加图书
    public static int addBook(Book book){
            String sql1="select * from tb_book where Bname=?";
            int i=0;
            System.out.println("请输入图书编号");
            int bid = scanner.nextInt();
            System.out.println("请输入图书名称");
            String bname = scanner.next();
            System.out.println("请输入图书价格");
            double price = scanner.nextDouble();
            System.out.println("请输入出版社");
            String press = scanner.next();
            System.out.println("请输入作者");
            String author = scanner.next();
            System.out.println("请输入可借阅数量");
            int nums = scanner.nextInt();

            book.setBid(bid);
            book.setBname(bname);
            book.setPrice(price);
            book.setPress(press);
            book.setAuthor(author);
            book.setNums(nums);
            try {
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setString(1,book.getBname());
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    System.out.println("图书已存在不能插入");
                }else{
                    String sql2="insert into tb_book (bid,bname,price,press,author,nums) values(?,?,?,?,?,?)";
                    try {
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setInt(1,book.getBid());
                        preparedStatement.setString(2,book.getBname());
                        preparedStatement.setDouble(3,book.getPrice());
                        preparedStatement.setString(4,book.getPress());
                        preparedStatement.setString(5,book.getAuthor());
                        preparedStatement.setInt(6,book.getNums());
                        i = preparedStatement.executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        return i;
    }
    //删除图书
    public static boolean delBook(String name) throws SQLException {
        //根据书名查id
        Integer bid = searchByName(name);
        if(bid!=null){
//            int bid = resultSet.getInt("bid");
            //根据id查最大的归还时间
            preparedStatement = connection.prepareStatement("select max(return_time) return_time from tb_user_read where bid = ?");
            preparedStatement.setInt(1,bid);
            ResultSet rs = preparedStatement.executeQuery();
            System.out.println(rs.next());
            if(rs.next()){
                java.sql.Date returnTime = rs.getDate("return_time");
                java.util.Date now = new java.util.Date( );
                System.out.println(now);
                // 如果已经归还,可以删除
                if (now.after(returnTime)) {
                    System.out.println("--- 该书已经全部归还,可以删除 ---");
                    preparedStatement = connection.prepareStatement("delete from tb_book where bid = ?");
                    preparedStatement.setInt(1, bid);
                    int i = preparedStatement.executeUpdate( );
                    if (i > 0) {
                        return true;
                    }
                } else {
                    System.out.println("该书正在借阅未归还,不能删除!");
                    return false;
                }
            }else{
                System.out.println("表中记录为空");
                return false;
            }
        }else{
            System.out.println("删除图书不存在！");
        }
        return false;
    }
    //根据书名搜索图书
    public static Book searchBookByBname(String name){
        String sql="select * from tb_book where bname like concat('%',?,'%')";
        Book book=new Book();
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,name);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    int bid = resultSet.getInt("bid");
                    String bname = resultSet.getString("bname");
                    double price = resultSet.getDouble("price");
                    String press = resultSet.getString("press");
                    String author = resultSet.getString("author");
                    int nums = resultSet.getInt("nums");
                    book.setBid(bid);
                    book.setBname(bname);
                    book.setPrice(price);
                    book.setPress(press);
                    book.setAuthor(author);
                    book.setNums(nums);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        return book;
    }
    //用户登录
    public static User login(){
        System.out.print("请输入用户名:");
        String user = scanner.next();
        System.out.print("请输入密码:");
        String pwd = scanner.next();
        String sql="select * from tb_user where username=?";
        User user1 = new User();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String sql2="select * from tb_user where password=?";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1,pwd);
                resultSet = preparedStatement.executeQuery();
                if(!resultSet.next()){
                    System.out.println("密码错误，请重新登录!");
                    user1.setUsername("");
                }else{
                    String sql3="select * from tb_user where username=? and password=?";
                    preparedStatement = connection.prepareStatement(sql3);
                    preparedStatement.setString(1,user);
                    preparedStatement.setString(2,pwd);
                    preparedStatement.executeQuery();
                    if(resultSet.next()){
                        String uid = resultSet.getString("uid");
                        String username1 = resultSet.getString("username");
                        String password1 = resultSet.getString("password");
                        if(username1.equals(user)&&password1.equals(pwd)){
                            user1.setUid(Integer.parseInt(uid));
                            user1.setUsername(username1);
                            user1.setPassword(password1);
                            System.out.println("登录成功！");
                        }else{
                            System.out.println("登录失败！");
                            user1.setUsername("");
                        }
                    }
                }
            }else{
                System.out.println("用户名错误，请重新登录!");
                user1.setUsername("");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user1;
    }
    //用户注册
    public static boolean register(User user1){
        System.out.println("请输入用户名:");
        String user = scanner.next();
        System.out.println("请输入密码:");
        String pwd = scanner.next();
        System.out.println("请再次确认密码:");
        String pwd2 = scanner.next();

        user1.setUsername(user);
        user1.setPassword(pwd);
        int i;
        //首先判断密码第一次输入密码和第二次密码是否一致
        if(pwd.equals(pwd2)){
            //再判断用户存在不
            String sql1="select * from tb_user where username=?";
            try {
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setString(1,user1.getUsername());
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    String username1 = resultSet.getString("username");
                    if(username1.equals(user)){
                        System.out.println("用户名已存在！");
                        return false;
                    }
                }else{
                    //再插入用户
                    String sql2="insert into tb_user (username,password) values(?,?)";
                    preparedStatement = connection.prepareStatement(sql2);
                    preparedStatement.setString(1,user1.getUsername());
                    preparedStatement.setString(2,user1.getPassword());
                    i = preparedStatement.executeUpdate();
                    if(i>0){
                        return true;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else{
            System.out.println("两次输入不一致");
            return false;
        }
        return false;
    }
    /**
     * 根据书名 指定查询,查到返回书的id,查不到返回null
     * @param bookName
     * @return
     * @throws SQLException
     */
    public static Integer searchByName(String bookName)throws SQLException {
        preparedStatement = connection.prepareStatement("select * from tb_book where bname = ?");
        preparedStatement.setString(1, bookName);
        ResultSet rs = preparedStatement.executeQuery( );

        if (rs.next( )) {
            return rs.getInt("bid");
        } else {
            System.out.println("查无此书" );
        }
        return null;
    }
    //借书
    public static void borrowBook(String bookName) throws SQLException {
         if(!Objects.equals(LoginUserName, "")){
            Integer bid = searchByName(bookName);
            int uid = user.getUid();
            //查这个人有无借过这本书
            preparedStatement = connection.prepareStatement("select * from tb_user_read where uid = ? and bid = ? and return_time > now()");
            preparedStatement.setInt(1,uid);
            preparedStatement.setInt(2,bid);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                System.out.println("你之前结果这本书还未归还，不能在再借");
            }else{
                //可以借书
                preparedStatement = connection.prepareStatement("update tb_book set nums = nums - 1 where bid = ?");
                preparedStatement.setInt(1,bid);
                preparedStatement.executeUpdate();

                preparedStatement = connection.prepareStatement("insert into tb_user_read (uid, bid, create_time, return_time) value (?,?,?,?)");
                preparedStatement.setInt(1,uid);
                preparedStatement.setInt(2,bid);
                Calendar calendar = Calendar.getInstance();
                // 在当前日期上增加两个月
                calendar.add(Calendar.MONTH, 2);
                // 获取当前时间
                Timestamp currentTime = new Timestamp(new Date().getTime());
                Timestamp calenderTime = new Timestamp(calendar.getTimeInMillis());
                preparedStatement.setTimestamp(3,currentTime);
                preparedStatement.setTimestamp(4,calenderTime);
                preparedStatement.executeUpdate();
                System.out.println("--- 借书成功 ---" );
            }
        }else{
            System.out.println("请先登录用户！");
        }
    }
    //查看未归还图书信息
    public static void notReturnBookMessage(){

         if(!Objects.equals(LoginUserName, "")){
            //先查当前用户未归还图书id
            int uid = user.getUid();
            String sql1="select * from tb_user_read where uid = ? and return_time > now()";
            try {
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setInt(1,uid);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    int bid = resultSet.getInt("bid");
                    //再根据图书id查到当前图书
                    preparedStatement = connection.prepareStatement("select tb.*,create_time  from tb_book tb,tb_user_read tur where tb.bid = ? and tb.bid=tur.bid order by tur.create_time desc");
                    preparedStatement.setInt(1,bid);
                    resultSet = preparedStatement.executeQuery();
                    System.out.println("---  未归还图书信息如下  ---");
                    System.out.println("图书id\t图书名称\t作者\t价格\t借阅时间\t");
                    while(resultSet.next()){
                        String bname = resultSet.getString("bname");
                        double price = resultSet.getDouble("price");
                        String author = resultSet.getString("author");
                        Date createTime = resultSet.getTimestamp("create_time");
                        System.out.println(bid+"\t"+bname+"\t"+author+"\t"+price+"\t"+createTime );
                    }
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else{
            System.out.println("请先登录用户！");
        }
    }
    //查看所有借阅信息
    public static void borrowedList(){
        if(!Objects.equals(LoginUserName, "")){
            String sql1="select tb.*,tur.create_time,if(tur.return_time>now(),'否','是') is_return from tb_user_read as tur,tb_book as tb where tur.bid=tb.bid GROUP BY tur.id ORDER BY tur.create_time desc";
            try {
                preparedStatement = connection.prepareStatement(sql1);
                resultSet = preparedStatement.executeQuery();
                System.out.println("---  借书信息如下  ---");
                System.out.println("图书id\t图书名称\t作者\t价格\t借阅时间\t是否归还");
                while(resultSet.next()){
                    int bid = resultSet.getInt("bid");
                    String bname = resultSet.getString("bname");
                    double price = resultSet.getDouble("price");
                    String author = resultSet.getString("author");
                    Date createTime = resultSet.getTimestamp("create_time");
                    String isReturn = resultSet.getString("is_return");
                    System.out.println(bid+"\t"+bname+"\t"+author+"\t"+price+"\t"+createTime+"\t"+isReturn );
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else{
            System.out.println("请先登录用户！");
        }
    }
    //还书2
    public static void returnBooks() throws SQLException {
        System.out.println("请输入需要归还的图书名称");
        String bookName = scanner.next();
        if(!Objects.equals(LoginUserName, "")){
            Integer bid = searchByName(bookName);
            if(bid==null){
                System.out.println("你查的书不存在");
            }
            int uid = user.getUid();
            PreparedStatement ps = connection.prepareStatement("update tb_book set nums=nums-1 where bname=?");
            ps.setString(1,bookName);
            int i = ps.executeUpdate();

            ps = connection.prepareStatement("update tb_user_read set return_time=now() where uid=?");
            ps.setInt(1,uid);
            int i1 = ps.executeUpdate();
            if(i>0 && i1>0){
                System.out.println("还书成功！");
            }
        }
    }
}

使用DButils 改写

新增一个BookAndBorrow实体类，就是需要一个可以接收List 集合的一个实体类

package com.ljw.entity;

import java.util.Date;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/11/1 9:56
 */
public class BookAndBorrow {
    private int bid;
    private String bname;
    private double price;
    private String press;
    private String author;
    private int nums;
    private Date createTime;
    private String isReturn;

    @Override
    public String toString() {
        return
                "bid=" + bid +
                ", bname='" + bname + '\'' +
                ", price=" + price +
                ", press='" + press + '\'' +
                ", author='" + author + '\'' +
                ", nums=" + nums +
                ", createTime=" + createTime +
                ", isReturn='" + isReturn + '\'';
    }

    public int getBid() {
        return bid;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    public String getBname() {
        return bname;
    }

    public void setBname(String bname) {
        this.bname = bname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getPress() {
        return press;
    }

    public void setPress(String press) {
        this.press = press;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public int getNums() {
        return nums;
    }

    public void setNums(int nums) {
        this.nums = nums;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getIsReturn() {
        return isReturn;
    }

    public void setIsReturn(String isReturn) {
        this.isReturn = isReturn;
    }
}

实现主要功能代码

package com.ljw;

import com.ljw.entity.Book;
import com.ljw.entity.BookAndBorrow;
import com.ljw.entity.Borrow;
import com.ljw.entity.User;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;

/**
 * @author ljw
 * @version 1.0
 * @Date 2024/10/31 20:19
 */
public class BookBorrowMangerSystem {
    private static String LoginUserName="";
    private static User user;
    private static final Scanner scanner=new Scanner(System.in);
    public static void main(String[] args) throws Exception {
        Welcome();
//        borrowBook();

    }
    public static void Welcome() throws Exception {
        while(true){
            System.out.println("****************************");
            System.out.println("欢迎来到图书管理系统 当前登录用户:"+LoginUserName+"^_^");
            System.out.println("****************************");
            System.out.println("1.添加图书");
            System.out.println("2.删除图书");
            System.out.println("3.查找书名搜索图书");
            System.out.println("4.用户注册");
            System.out.println("5.用户登录");
            System.out.println("6.查看已借阅图书信息");
            System.out.println("7.查看未归还图书信息");
            System.out.println("8.借书");
            System.out.println("9.还书");
            System.out.println("0.退出系统");
            System.out.println("****************************");
            System.out.print("请选择需要执行的功能:");
            int i = scanner.nextInt();
            switch (i){
                case 1:
                    addBook();
                    break;
                case 2:
                    delBook();
                    break;
                case 3:
                    System.out.print("请输入查询的图书名称:");
                    String name = scanner.next();
                    searchBookName(name);
                    break;
                case 4:
                    register();
                    break;
                case 5:
                    System.out.print("请输入用户名:");
                    String username = scanner.next();
                    System.out.print("请输入密码:");
                    String password = scanner.next();
                    user=login(username,password);
                    if(user!=null){
                        LoginUserName=user.getUsername();
                    }
                    break;
                case 6:
                    borrowMessage();
                    break;
                case 7:
                    noReturnMessage();
                    break;
                case 8:
                    borrowBook();
                    break;
                case 9:
                    returnBook();
                    break;
                case 0:
                    System.out.println("谢谢~");
                    System.exit(0);
                default:
                    System.out.println("输入错误！");
                    break;
            }
        }
    }
    /**
     * 添加图书
     * @throws SQLException
     */
    public static void addBook() throws SQLException {
        System.out.print("请输入你要添加的图书名称:");
        String bookName = scanner.next();
//        int id = getIdByBookName(bookName);
        //查图书是否已存在
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<Book> handler = new BeanHandler<>(Book.class);
        Book book = queryRunner.query(connection, "select * from tb_book where bname=?", handler, bookName);
        if(book!=null){
            System.out.println("图书已存在!");
        }else{
            System.out.print("请输入图书价格");
            double price = scanner.nextDouble();
            System.out.print("请输入出版社");
            String press = scanner.next();
            System.out.print("请输入作者");
            String author = scanner.next();
            System.out.print("请输入可借阅数量");
            int nums = scanner.nextInt();
            Object[] params={bookName,price,press,author,nums};
            int i = queryRunner.update(connection, "insert into tb_book(bname,price,press,author,nums) values(?,?,?,?,?)", params);
            if(i>0){
                System.out.println("图书添加成功！");
            }
        }
        DbUtils.closeQuietly(connection);
    }

    /**
     * 删除图书
     * @throws SQLException
     */
    public static void delBook() throws SQLException {
        System.out.print("请输入你要删除的图书名称:");
        String bookName = scanner.next();
        int id = getIdByBookName(bookName);
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<Borrow> handler = new BeanHandler<>(Borrow.class);
        Borrow borrow = queryRunner.query(connection, "select return_time from tb_user_read where bid=? order by return_time desc limit 1", handler, id);
        Date returnTime = borrow.getReturnTime();
        java.util.Date now = new java.util.Date( );
        if(returnTime.after(now)){
            System.out.println("该书已全部还清，可以删除!");
            BeanHandler<Book> Beanhandler = new BeanHandler<>(Book.class);
            int i = queryRunner.update(connection, "delete from tb_book where bid=?", Beanhandler, id);
            if(i>0){
                System.out.println("删除成功！");
            }else{
                System.out.println("删除失败！");
            }
        }else{
            System.out.println("该书正在借阅不能删除！");
        }
        DbUtils.closeQuietly(connection);
    }

    /**
     * 登录用户
     * @param username
     * @param password
     * @return
     * @throws SQLException
     */
    public static User login(String username,String password) throws SQLException {
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<User> handler = new BeanHandler<>(User.class);
        User user1 = queryRunner.query(connection, "select * from tb_user where username=?", handler, username);
        if(user1!=null){
            User user2 = queryRunner.query(connection, "select * from tb_user where username=? and password=?", handler,username, password);
            if(user2!=null && user2.getPassword().equals(password)){
                User user3 = queryRunner.query(connection, "select * from tb_user where username=? and password=?", handler, username, password);
                if(user3.getUsername().equals(username)&&user3.getPassword().equals(password)){
                    System.out.println("登录成功！");
                    return user3;
                }
            }else{
                System.out.println("密码错误");
            }
        }else{
            System.out.println("用户名错误！");
        }
        DbUtils.closeQuietly(connection);
        return null;
    }

    /**
     * 注册用户
     * @throws SQLException
     */
    public static void register() throws SQLException {
        System.out.print("请输入用户名:");
        String username = scanner.next();
        System.out.print("请输入密码:");
        String password = scanner.next();
        System.out.print("请再次输入密码:");
        String password2 = scanner.next();
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<User> handler = new BeanHandler<>(User.class);
        if(password.equals(password2)){
            User user1 = queryRunner.query(connection, "select * from tb_user where username=?", handler, username);
            if(user1==null){
                int i = queryRunner.update(connection, "insert into tb_user (username,password) values (?,?)",username,password);
                if(i>0){
                    System.out.println("注册成功！");
                }else{
                    System.out.println("注册失败！");
                }
            }else{
                System.out.println("当前用户已存在！");
            }
        }else{
            System.out.println("两次输入密码不一致！");
        }
        DbUtils.closeQuietly(connection);
    }
    /**
     * 根据名字模糊查询
     * @param bookName
     * @return
     */
     public static void searchBookName(String bookName) throws SQLException {
         Connection connection = getConnection();
         QueryRunner queryRunner = new QueryRunner();
         BeanHandler<Book> handler = new BeanHandler<>(Book.class);
         Book book = queryRunner.query(connection, "select *  from tb_book where bname like concat('%',?,'%')", handler, bookName);
         if(book==null){
             System.out.println("查询图书失败");
         }else{
             System.out.println("查询成功！");
             System.out.print("图书编号:"+book.getBid()+"\t");
             System.out.print("图书名称:"+book.getBname()+"\t");
             System.out.print("图书价格:"+book.getPrice()+"\t");
             System.out.print("出版社:"+book.getPress()+"\t");
             System.out.print("作者:"+book.getAuthor()+"\t");
             System.out.println("可借阅图书数量:"+book.getNums()+"\t");
         }
         DbUtils.closeQuietly(connection);
     }

    /**
     * 获取自己所有借阅信息
     */
    public static void borrowMessage() throws SQLException {
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
         if(!Objects.equals(LoginUserName, "")){
             int uid = user.getUid();
             BeanListHandler<BookAndBorrow> handler = new BeanListHandler<>(BookAndBorrow.class);
             List<BookAndBorrow> list = queryRunner.query(connection, "select tb.*,tur.create_time createTime,if(return_time>now(),'否','是') isReturn from tb_book tb,tb_user_read tur where tb.bid=tur.bid and tur.uid=? ORDER BY tur.create_time desc", handler, uid);
             list.stream().forEach(System.out::println);
         }else{
             System.out.println("请先登录！");
         }
         DbUtils.closeQuietly(connection);
     }

    /**
     * 未返还图书信息
     * @throws SQLException
     */
     public static void noReturnMessage() throws SQLException {
         Connection connection = getConnection();
         QueryRunner queryRunner = new QueryRunner();
         if(!Objects.equals(LoginUserName, "")){
             int uid = user.getUid();
             BeanListHandler<BookAndBorrow> handler = new BeanListHandler<>(BookAndBorrow.class);
             List<BookAndBorrow> list = queryRunner.query(connection, "select tb.*,tur.create_time createTime,return_time from tb_book tb,tb_user_read tur where tb.bid=tur.bid and tur.uid=? and return_time>now() ORDER BY tur.create_time desc", handler, uid);
             System.out.println("---  未归还图书信息如下  ---");
             System.out.println("图书id\t图书名称\t作者\t价格\t借阅时间\t");
             if(list!=null){
                 list.stream().forEach(e->{
                     System.out.println(e.getBid()+"\t"+e.getBname()+"\t"+e.getAuthor()+"\t"+e.getPrice()+"\t"+e.getCreateTime());
                 });
             }else{
                 System.out.println("暂无未归还图书！");
             }

         }else{
             System.out.println("请先登录！");
         }
         DbUtils.closeQuietly(connection);
     }

    /**
     * 借书
     */
    public static void borrowBook() throws SQLException {
        System.out.print("请输入借阅图书名称:");
        String bookName = scanner.next();
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<Book> handler = new BeanHandler<>(Book.class);
        int id = getIdByBookName(bookName);
        int uid = user.getUid();
        Book book = queryRunner.query(connection, "select * from tb_user_read where return_time>now() and uid=? and bid=?", handler,uid,id);
        if(book!=null){
            System.out.println("你之前借过这个本书,还未归还!");
        }else{
            Book book1 = queryRunner.query(connection, "select * from tb_book where bid=?", handler, id);
            if(book1.getNums()>0){
                Calendar calendar = Calendar.getInstance();
                // 在当前日期上增加两个月
                calendar.add(Calendar.MONTH, 2);
                // 获取当前时间
                //TODO 这里必须传java.sql.Date 类型数据
//                Timestamp currentTime = new java.sql.Timestamp(new Date().getTime());
//                Timestamp calenderTime = new java.sql.Timestamp(calendar.getTimeInMillis());
                java.sql.Date currentTime = new java.sql.Date(new Date().getTime());
                java.sql.Date calenderTime = new java.sql.Date(calendar.getTimeInMillis());
//                Object[] params = {uid,id,currentTime,calenderTime};
                int i = queryRunner.update(connection, "insert into tb_user_read (uid,bid,create_time,return_time) values (?,?,?,?)", uid,id,currentTime,calenderTime);
                if(i>0){
                    System.out.println("借阅成功！");
                }else{
                    System.out.println("借阅失败！");
                }
            }
        }
        DbUtils.closeQuietly(connection);
     }

    /**
     * 还书
     */
    public static void returnBook() throws SQLException {
        System.out.print("请输入要归还图书名称:");
        String bookName = scanner.next();
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        Integer id = getIdByBookName(bookName);
        if(id==null){
            System.out.println("所查图书不存在");
        }
        int uid = user.getUid();
        int i = queryRunner.update(connection, "update tb_book set nums=nums-1 where bid=?", id);
        int i1 = queryRunner.update(connection, "update tb_user_read set return_time=now() where uid=?", uid);
        if(i>0&&i1>0){
            System.out.println("还书成功！");
        }else{
            System.out.println("还书失败");
        }
        DbUtils.closeQuietly(connection);
    }
    /**
     * 根据书名获得id
     * @param bookName
     * @return
     * @throws SQLException
     */
    public static Integer getIdByBookName(String bookName) throws SQLException {
        Connection connection = getConnection();
        QueryRunner queryRunner = new QueryRunner();
        BeanHandler<Book> handler = new BeanHandler<>(Book.class);
        Book book = queryRunner.query(connection, "select * from tb_book where bname=?", handler, bookName);
        try {
            int bid = book.getBid();
            return bid;
        } catch (Exception e) {
            System.out.println("查无此书！");
        }
        DbUtils.closeQuietly(connection);
        return null;
    }
    /**
     * 获得连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?useSSL=false", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}