图书管理系统 ——mysql数据库

java图形化 图书管理系统 使用mysql数据库

此博客是本人一次Java实验课作业：

1、管理员操作：能够实现图书的增、删、改、查操作

2、普通注册用户操作：

(1)借阅图书（借阅成功后，对应图书在馆数量相应减1）

(2)归还图书（归还成功后，对应图书在馆数量加1）

(3)查阅自己借阅的图书

(4)续借图书（借阅图书以3个月为限，可以在期间内续借1个月）

(5)如果预借图书在馆数量为零，或者图书不存在，则无法借阅

(6)可以为所借阅的图书进行评论

3自行设计数据库表

此项目已经更新，修改了所有反馈的bug，增加了新功能，增加可玩性，调整了代码结构。欢迎访问另一篇博客

https://blog.csdn.net/m0_52889702/article/details/127230912

结果展示：

普通用户

管理员：

源码包地址：

https://gitee.com/wang-yongyan188/experimental-code.git

库表设计：

代码：引入jdbc-mysql的jar包 添加到库

创建实体类：目的是将数据库记录映射为Java对象

book：

public class Book { String bname;
    Integer bid;
    Integer allnum;
    Integer borrownum;
    String type;
    public Book() { }
    public Book(String bname, Integer bid, Integer allnum, Integer borrownum, String type) { this.bname = bname;
        this.bid = bid;
        this.allnum = allnum;
        this.borrownum = borrownum;
        this.type = type;
    }
    public String getType() { return type;
    }
    public void setType(String type) { this.type = type;
    }
    public String getBname() { return bname;
    }
    public void setBname(String bname) { this.bname = bname;
    }
    public Integer getBid() { return bid;
    }
    public void setBid(Integer bid) { this.bid = bid;
    }
    public Integer getAllnum() { return allnum;
    }
    public void setAllnum(Integer allnum) { this.allnum = allnum;
    }
    public Integer getBorrownum() { return borrownum;
    }
    public void setBorrownum(Integer borrownum) { this.borrownum = borrownum;
    }
    @Override
    public String toString() { return "Book{" +
                "bname='" + bname + '\'' +
                ", bid=" + bid +
                ", allnum=" + allnum +
                ", borrownum=" + borrownum +
                '}';
    }
}

User:

public class User { Integer id;
    String passwd;
    String uname;
    Integer ismanger;
    public User(Integer id, String passwd, String uname, Integer ismanger) { this.id = id;
        this.passwd = passwd;
        this.uname = uname;
        this.ismanger = ismanger;
    }
    public User() { }
    public Integer getId() { return id;
    }
    public void setId(Integer id) { this.id = id;
    }
    public String getPasswd() { return passwd;
    }
    public Integer getIsmanger() { return ismanger;
    }
    public void setIsmanger(Integer ismanger) { this.ismanger = ismanger;
    }
    public void setPasswd(String passwd) { this.passwd = passwd;
    }
    public String getUname() { return uname;
    }
    public void setUname(String uname) { this.uname = uname;
    }
    @Override
    public String toString() { return "User{" +
                "id=" + id +
                ", passwd='" + passwd + '\'' +
                ", uname='" + uname + '\'' +
                ", ismanger=" + ismanger +
                '}';
    }
}

Rent：

public class Rent { Integer rid;
    String btime;
    String days;
    Integer uid;
    Integer bid;
    public Rent() { }
    public Rent(Integer rid, String btime, String days, Integer uid, Integer bid) { this.rid = rid;
        this.btime = btime;
        this.days = days;
        this.uid = uid;
        this.bid = bid;
    }
    public Integer getRid() { return rid;
    }
    public void setRid(Integer rid) { this.rid = rid;
    }
    public String getBtime() { return btime;
    }
    public void setBtime(String btime) { this.btime = btime;
    }
    public String getDays() { return days;
    }
    public void setDays(String days) { this.days = days;
    }
    public Integer getUid() { return uid;
    }
    public void setUid(Integer uid) { this.uid = uid;
    }
    public Integer getBid() { return bid;
    }
    public void setBid(Integer bid) { this.bid = bid;
    }
    @Override
    public String toString() { return "Rent{" +
                "rid=" + rid +
                ", btime='" + btime + '\'' +
                ", days='" + days + '\'' +
                ", uid=" + uid +
                ", bid=" + bid +
                '}';
    }
}

与数据库进行交互 封装成的工具类：

import code6.entity.Book;
import code6.entity.Rent;
import code6.entity.User;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
public class SqlUtils { public static Connection getCon() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/code6", "root", "1234");// 注意：这里要修改为自己的！！！
        return con;
    }
    public static int AutoId() { int orderId = UUID.randomUUID().toString().hashCode();
        orderId = orderId < 0 ? -orderId : orderId;
        return orderId;
    }
    public static User getUserById(int id) throws SQLException, ClassNotFoundException { Connection con = getCon();
        Statement statement =  con.createStatement();
        String sql = "SELECT * from `user` where uid=" + id;
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println(resultSet.next());
        User user = new User();
        user.setId(resultSet.getInt(1));
        user.setPasswd(resultSet.getString(2));
        user.setUname(resultSet.getString(3));
        user.setIsmanger(resultSet.getInt(4));
        statement.close();
        con.close();
        return user;
    }
    public static List getAll() throws SQLException, ClassNotFoundException { Connection con = getCon();
        Statement statement =  con.createStatement();
        String sql = "SELECT * FROM book";
        ResultSet resultSet = statement.executeQuery(sql);
        ArrayList books = new ArrayList<>();
        Book book = new Book();
        while (resultSet.next()) { book.setBname(resultSet.getString(1));
            book.setBid(resultSet.getInt(2));
            book.setAllnum(resultSet.getInt(3));
            book.setBorrownum(resultSet.getInt(4));
            book.setType(resultSet.getString(5));
            books.add(book);
        }
      statement.close();
      con.close();
      return books;
    }
    public static int addBook(Book book) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql = "INSERT book VALUES(?,?,?,?,?);";
        PreparedStatement prepareStatement = con.prepareStatement(sql);
        prepareStatement.setString(1, book.getBname());
        prepareStatement.setInt(2, AutoId());
        prepareStatement.setInt(3, book.getAllnum());
        prepareStatement.setInt(4, book.getBorrownum());
        prepareStatement.setString(5, book.getType());
        int i = prepareStatement.executeUpdate();
        prepareStatement.close();
        con.close();
        return i;
    }
    public static int delBook(int bid) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql="DELETE from book WHERE bid=?";
        PreparedStatement prepareStatement = con.prepareStatement(sql);
        prepareStatement.setInt(1,bid);
        return prepareStatement.executeUpdate();
    }
    public static int upBook(Book book) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql="UPDATE book SET bname=?,allnum=?,borrownum=?,type=? WHERE bid=?";
        PreparedStatement prepareStatement = con.prepareStatement(sql);
        prepareStatement.setString(1,book.getBname());
        prepareStatement.setInt(2,book.getAllnum());
        prepareStatement.setInt(3,book.getBorrownum());
        prepareStatement.setString(4,book.getType());
        prepareStatement.setInt(5,book.getBid());
        int i = prepareStatement.executeUpdate();
        prepareStatement.close();
        con.close();
        return i;
    }
    public static int addRent(int uid,int bid,int days,int month) throws SQLException, ClassNotFoundException { Connection con = getCon();
        con.setAutoCommit(false);
        String sql="INSERT INTO rent VALUES(?,?,?,?,?)";
        PreparedStatement prepareStatement = con.prepareStatement(sql);
        prepareStatement.setInt(1,AutoId());
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sbegin= format.format(date);
        prepareStatement.setString(2,sbegin);
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        cal.add(Calendar.MONTH,month);
        Date temptime = cal.getTime();
        SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
        String endday = format2.format(temptime.getTime() + days * 24 * 60 * 60 * 1000);
        prepareStatement.setString(3,endday);
        prepareStatement.setInt(4,uid);
        prepareStatement.setInt(5,bid);
        String sql2="UPDATE book set borrownum=borrownum+1 where bid="+bid;
        int i1=0;
        int i=0;
            Statement statement = con.createStatement();
            try{ i1= statement.executeUpdate(sql2);
                i= prepareStatement.executeUpdate();
                con.commit();
            }
            catch (Exception e){ con.rollback();
            }
            finally { statement.close();
                prepareStatement.close();
                con.close();
            }
        return (i==1&&i==1)?1:0;
    }
    public static Boolean isAddRend(int bid) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql="SELECT allnum,borrownum from book WHERE bid="+bid;
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        resultSet.next();
        int all=resultSet.getInt(1);
        int rent=resultSet.getInt(2);
        statement.close();
        con.close();
       	return all>rent?true:false;
    }
    public static List getAllRentByUid(int uid) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql="SELECT * from rent where uid="+uid;
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        ArrayList rents = new ArrayList<>(5);
        while (resultSet.next()){ Rent rent = new Rent();
            rent.setRid(resultSet.getInt(1));
            rent.setBtime(resultSet.getString(2));
            rent.setDays(resultSet.getString(3));
            rent.setUid(resultSet.getInt(4));
            rent.setBid(resultSet.getInt(5));
            rents.add(rent);
        }
        return rents;
    }
    public static Book getBookByBid(int bid) throws SQLException, ClassNotFoundException { Connection con = getCon();
        String sql="SELECT * from book where bid="+bid;
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        Book book = new Book();
        while (resultSet.next()){ book.setBname(resultSet.getString(1));
            book.setBid(resultSet.getInt(2));
            book.setAllnum(resultSet.getInt(3));
            book.setBorrownum(resultSet.getInt(4));
            book.setType(resultSet.getString(5));
        }
    return book;
    }
    public static List