简易版图书借阅系统（可操作数据库）

最新推荐文章于 2024-07-07 13:00:43 发布

鱟鲥鳚

最新推荐文章于 2024-07-07 13:00:43 发布

阅读量588

点赞数 25

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_61898502/article/details/139970560

版权

图书借阅系统

数据库表：
   用户表：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 退出系统
******************************
请选择需要执行的功能：

本图书管理系统设置了三张表books，borrows，users

数据库如下：

链接：https://pan.baidu.com/s/1N6cnp2M-MtgWigK5WQBnYw?pwd=nglw
提取码：nglw

Java代码也可直接下载：

链接：https://pan.baidu.com/s/1XkKHMg9Grfnx8vluB0TQgg?pwd=xytr
提取码：xytr

Java代码部分由主函数和实现ROM的三个实体类组成。

主函数如下：

import java.sql.*;
import java.util.Scanner;

public class system {
    public static void main(String[] args) {

        users user=null;
        boolean i=true;
        Scanner scanner=new Scanner(System.in);
        while (i){
            System.out.println("功能列表显示如下");
            System.out.println("******************");
            System.out.print("图书借阅系统   ");
            if (user!=null){
                System.out.println("当前登录用户"+user.getUsername());
            }
            else {
                System.out.println("当前未登录");
            }
            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.println("请输入您的选择：");
            int i1 = scanner.nextInt();
            switch (i1){
                case 1:
                    System.out.println("请输入书名:");
                    String title1 = scanner.next();
                    System.out.println("请输入价格:");
                    double price1 = scanner.nextDouble();
                    System.out.println("请输入出版社:");
                    String publisher1 = scanner.next();
                    System.out.println("请输入作者:");
                    String author1 = scanner.next();
                    addbook(title1,price1,publisher1,author1,1);
                    break;
                case 2:
                    System.out.println("请输入书的id号:");
                    int id1 = scanner.nextInt();
                    deletebook(id1);
                    break;
                case 3:
                    System.out.println("请输入书名:");
                    String title2 = scanner.next();
                    selectbook(title2);
                    break;
                case 4:
                    System.out.println("请输入用户名:");
                    String username = scanner.next();
                    System.out.println("请输入密码:");
                    String password = scanner.next();
                    System.out.println("请再次输入密码");
                    String password1 = scanner.next();
                    register(username,password,password1);
                    break;
                case 5:
                    if (user!=null){
                        System.out.println("您已登录，请勿重复登录");
                    }else {
                        System.out.println("请输入用户名:");
                        String username1 = scanner.next();
                        System.out.println("请输入密码:");
                        String password2 = scanner.next();
                        user=login(username1,password2);
                    }
                    break;
                case 6:
                    if (user!=null){
                        System.out.println("请输入你的id");
                        int id = scanner.nextInt();
                        selectselfborrow(id);
                    }else {
                        System.out.println("您还未登录，请先登录");
                    }
                    break;
                case 7:
                    if (user!=null){
                        selectwhbooks();
                    }else {
                        System.out.println("您还未登录，请先登录");
                    }
                    break;
                case 8:
                    if (user!=null){
                        System.out.println("请输入您的id和需要的书的id");
                        int id = scanner.nextInt();
                        int id3 = scanner.nextInt();
                        borrow(id,id3);
                    }else {
                        System.out.println("您还未登录，请先登录");
                    }
                    break;
                case 9:
                    if (user!=null){
                        System.out.println("请输入您的id和需要还书的id");
                        int id = scanner.nextInt();
                        int id3 = scanner.nextInt();
                        returnbook(id,id3);
                    }else {
                        System.out.println("您还未登录，请先登录");
                    }
                    break;
                case 0:
                    return;
            }
        }
    }

    public static void addbook(String title,double price,String publisher,String author,int num){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="insert into books(title, price, publisher, author, num) values (?, ?, ?, ?, ?)";
            pps = conn.prepareStatement(sql);
            pps.setString(1,title);
            pps.setDouble(2,price);
            pps.setString(3,publisher);
            pps.setString(4,author);
            pps.setInt(5,num);
            int i = pps.executeUpdate();
            if (i>0){
                System.out.println("数据插入成功！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    //添加图书

    public static void deletebook(int id){
        Connection conn=null;
        PreparedStatement pps=null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select return_time from Borrows where book_id=?";
            pps = conn.prepareStatement(sql);
            pps.setInt(1,id);
            ResultSet rs = pps.executeQuery();
            if (rs.next()){
                java.sql.Date rt = rs.getDate("return_time");
                if (rt==null){
                    System.out.println("该图书有人借阅，不能删除");
                }else {
                    String sql3="delete from borrows where book_id=?";
                    pps = conn.prepareStatement(sql3);
                    pps.setInt(1,id);
                    int i2 = pps.executeUpdate();
                    String sql2="delete from books where id=?";
                    pps = conn.prepareStatement(sql2);
                    pps.setInt(1,id);
                    int i = pps.executeUpdate();
                    if (i>0 && i2>0){
                        System.out.println("删除成功");
                    }
                }
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    //删除图书

    public static void selectbook(String title){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select * from books where title like ?";
            pps = conn.prepareStatement(sql);
            pps.setString(1, "%"+title+"%");
            ResultSet rs = pps.executeQuery();
            if (rs.next()){
                int id = rs.getInt("id");
                String title1 = rs.getString("title");
                double decimal = rs.getDouble("price");
                String publisher = rs.getString("publisher");
                String author = rs.getString("author");
                int num = rs.getInt("num");
                books book = new books(id,title1,decimal,publisher,author,num);
                System.out.println("书的信息为"+book);
            }
            else {
                System.out.println("没有找到该图书");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    //搜索图书

    public static void register(String username,String password,String password1){
        Connection conn=null;
        PreparedStatement pps=null;
        PreparedStatement pps2=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC", "root", "123456");
            pps = conn.prepareStatement("select id from users where username=?");
            pps.setString(1,username);
            ResultSet rs = pps.executeQuery();
            if (rs.next()){
                System.out.println("该用户名已存在");
            }else {
                if (password.equals(password1)){
                    pps2 = conn.prepareStatement("insert into users(username, password) values (?, ?)");
                    pps2.setString(1,username);
                    pps2.setString(2,password);
                    int i = pps2.executeUpdate();
                    if (i>0){
                        System.out.println("注册成功");
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps2.close();
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
    //用户注册

    public static users login(String username,String password){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select * from users where username=?";
            pps = conn.prepareStatement(sql);
            pps.setString(1,username);
            ResultSet rs = pps.executeQuery();
            if (rs.next()){
                String password1 = rs.getString("password");
                if (password.equals(password1)){
                    System.out.println("登录成功");
                    return new users(username,password);
                }else {
                    System.out.println("密码错误");
                }
            }else {
                System.out.println("用户名不存在");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return null;
}
    //用户登录

    public static void  borrow(int user_id,int book_id){
        Connection conn=null;
        PreparedStatement pps=null;
        ResultSet rs=null;
        ResultSet rs2=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select book_id from Borrows where user_id=? and return_time is null";
            pps = conn.prepareStatement(sql);
            pps.setInt(1,user_id);
            rs = pps.executeQuery();
            while (rs.next()){
                int book_id1 = rs.getInt("book_id");
                if (book_id1==book_id){
                    System.out.println("该用户已借过该书，不能再借书了");
                }else {
                    String sql2="select num from books where id=?";
                    pps = conn.prepareStatement(sql2);
                    pps.setInt(1,book_id);
                    rs2 = pps.executeQuery();
                    if (rs2.next()){
                        int num = rs2.getInt("num");
                        if (num>0){
                            String sql3="insert into borrows(user_id, book_id, borrow_time) values (?, ?, ?)";
                            pps = conn.prepareStatement(sql3);
                            pps.setInt(1,user_id);
                            pps.setInt(2,book_id);
                            pps.setDate(3,new Date(System.currentTimeMillis()));
                            int i = pps.executeUpdate();
                            if (i>0){
                                String sql4="update books set num=num-1 where id=?";
                                pps = conn.prepareStatement(sql4);
                                pps.setInt(1,book_id);
                                int i2 = pps.executeUpdate();
                                if (i2>0){
                                    System.out.println("借书成功");
                                }
                            }else {
                                System.out.println("借书失败");
                            }
                        }
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    //借书
    public static void selectselfborrow(int user_id){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select books.`id`,books.`title`,books.`publisher`,books.`price`,borrows.`borrow_time`,borrows.`return_time` from books,Borrows where books.`id`=borrows.`book_id` and borrows.`user_id`=?";
            pps = conn.prepareStatement(sql);
            pps.setInt(1,user_id);
            ResultSet rs = pps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String s = rs.getString("title");
                String s1 = rs.getString("publisher");
                double decimal = rs.getDouble("price");
                java.sql.Date borrowTime = rs.getDate("borrow_time");
                java.sql.Date returnTime = rs.getDate("return_time");
                if (returnTime==null){
                    System.out.println(id+" "+s+" "+s1+" "+decimal+" "+borrowTime+" "+"是");
                }else {
                    System.out.println(id+" "+s+" "+s1+" "+decimal+" "+borrowTime+" "+"否");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //查询自己借阅书籍
    public static void selectwhbooks(){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="select books.`id`,books.`title`,books.`publisher`,borrows.`borrow_time` from books,Borrows where books.`id`=borrows.`book_id` and borrows.`return_time` is null";
            pps = conn.prepareStatement(sql);
            ResultSet rs = pps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String title = rs.getString("title");
                String publisher = rs.getString("publisher");
                Date borrow_time = rs.getDate("borrow_time");
                System.out.println(id+" "+title+" "+publisher+" "+borrow_time);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    //查找没有归还的图书

    public static void returnbook(int user_id,int book_id){
        Connection conn=null;
        PreparedStatement pps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2403?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC",
                    "root", "123456");
            String sql="update borrows set return_time=? where book_id=? and user_id=?";
            pps = conn.prepareStatement(sql);
            pps.setDate(1,new Date(System.currentTimeMillis()));
            pps.setInt(2,book_id);
            pps.setInt(3,user_id);
            int i = pps.executeUpdate();
            if (i>0){
                String sql2="update books set num=num+1 where id=?";
                pps = conn.prepareStatement(sql2);
                pps.setInt(1,book_id);
                int i2 = pps.executeUpdate();
                if (i2>0){
                    System.out.println("还书成功");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                pps.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //还书



}

实体类books：

public class books {
    private int id;
    private String title;
    private double decimal;
    private String publisher;
    private String author;
    private int num;

    public books() {
    }

    public books(int id, String title, double decimal, String publisher, String author, int num) {
        this.id = id;
        this.title = title;
        this.decimal = decimal;
        this.publisher = publisher;
        this.author = author;
        this.num = num;
    }

    @Override
    public String toString() {
        return "books{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", decimal=" + decimal +
                ", publisher='" + publisher + '\'' +
                ", author='" + author + '\'' +
                ", num=" + num +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public double getDecimal() {
        return decimal;
    }

    public void setDecimal(double decimal) {
        this.decimal = decimal;
    }

    public String getPublisher() {
        return publisher;
    }

    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }

    public String getAuthor() {
        return author;
    }

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

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }
}

实体类borrows：

import java.util.Date;

public class borrows {
    private int id;
    private int user_id;
    private int book_id;
    private Date borrow_time;
    private Date return_time;

    public borrows() {
    }

    public borrows(int id, int user_id, int book_id, Date borrow_time, Date return_time) {
        this.id = id;
        this.user_id = user_id;
        this.book_id = book_id;
        this.borrow_time = borrow_time;
        this.return_time = return_time;
    }

    @Override
    public String toString() {
        return "borrows{" +
                "id=" + id +
                ", user_id=" + user_id +
                ", book_id=" + book_id +
                ", borrow_time=" + borrow_time +
                ", return_time=" + return_time +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public int getBook_id() {
        return book_id;
    }

    public void setBook_id(int book_id) {
        this.book_id = book_id;
    }

    public Date getBorrow_time() {
        return borrow_time;
    }

    public void setBorrow_time(Date borrow_time) {
        this.borrow_time = borrow_time;
    }

    public Date getReturn_time() {
        return return_time;
    }

    public void setReturn_time(Date return_time) {
        this.return_time = return_time;
    }
}

实体类users：

public class users {
    private int id;
    private String username;
    private String password;

    public users() {
    }

    public users(String username, String password) {
        this.username = username;
        this.password = password;
    }

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

    public int getId() {
        return id;
    }

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

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