JDBC实现图书管理系统的（简化版）

实现图书管理系统，首先需要建模。

分析参与图书馆借书的故事的实体和动作，并找到实体之间的联系，由于用户和书籍之间是多对多的关系，一个用户可以借阅多本书籍，一本书籍也可以被多个用户借阅。所以需要借助 借阅记录表来讲多对多关系转成两个一对多关系，学生借阅书籍和书籍被学生借阅都可以通过借阅关系来对应。

 

 接下来就是建表了，只要思路清晰，建表还是很容易的。

书籍表

用户表

借阅记录表

 接下来打开IDEA，配置好maven后，就开始进行对sql的增删查改动作了

首先根据建立的三张表，相应的在IDEA建立好各种类，并添加相应字段，重写toString（）和equals（）方法。

用户类

public class User {
    public Integer uid;
    public String username;

    public User(){};

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

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return uid.equals(user.uid);
    }

    @Override
    public int hashCode() {
        return Objects.hash(uid);
    }

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

书籍类

import java.util.Objects;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User:24986
 * Date:2021-11-26
 * Time:11:03
 */
public class Book {
    public Integer bid;
    public String name;
    public Integer totalCount;
    public Integer currentCount;

    public Book() {};

    public Book(Integer bid, String name, Integer totalCount, Integer currentCount) {
        this.bid = bid;
        this.name = name;
        this.totalCount = totalCount;
        this.currentCount = currentCount;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Book book = (Book) o;
        return bid.equals(book.bid);
    }

    @Override
    public int hashCode() {
        return Objects.hash(bid);
    }

    @Override
    public String toString() {
        return "Book{" +
                "bid=" + bid +
                ", name='" + name + '\'' +
                ", totalCount=" + totalCount +
                ", currentCount=" + currentCount +
                '}';
    }
}

 借阅记录类

import java.time.LocalDateTime;
import java.util.Objects;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User:24986
 * Date:2021-11-26
 * Time:11:02
 */
public class Record {
    public Integer rid;
    public User user;
    public Book book;

    public LocalDateTime borrowedAt;
    public boolean returned;
    public LocalDateTime returnedAt;

    public Record() {}

    public Record(Integer rid, User user, Book book, LocalDateTime borrowedAt, boolean returned, LocalDateTime returnedAt) {
        this.rid = rid;
        this.user = user;
        this.book = book;
        this.borrowedAt = borrowedAt;
        this.returned = returned;
        this.returnedAt = returnedAt;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Record record = (Record) o;
        return rid.equals(record.rid);
    }

    @Override
    public int hashCode() {
        return Objects.hash(rid);
    }

    @Override
    public String toString() {
        return "Record{" +
                "rid=" + rid +
                ", user=" + user +
                ", book=" + book +
                ", borrowedAt=" + borrowedAt +
                ", returned=" + returned +
                ", returnedAt=" + returnedAt +
                '}';
    }
}

 新建一个操作类（library），完成增删查改语句的实现

public Record borrowBook (int uid,int bid) throws SQLException {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setServerName("127.0.0.1");
        ds.setPort(3306);
        ds.setUser("root");
        ds.setPassword("******");//这里填自己的密码即可
        ds.setDatabaseName("db_11_26");
        ds.setCharacterEncoding("utf8");
        ds.setServerTimezone("Asia/Shanghai");
        ds.setUseSSL(false);

        Record record = new Record();
        User user = new User();
        Book book = new Book();

        try (Connection c = ds.getConnection()) {
            String sql = String.format("SELECT current_count FROM books WHERE bid = %d", bid);
            System.out.println("DEBUG: " + sql);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    int currentCount = rs.getInt("current_count");
                    if (currentCount == 0) {
                        return null;
                    }
                }
            }


            sql = String.format("UPDATE books SET current_count = current_count - 1 WHERE bid = %d",bid);
            System.out.println("DEBUG: " + sql);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.executeUpdate();
            }

            LocalDateTime now = LocalDateTime.now();
            String nowStr = now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            sql = String.format("INSERT INTO records (uid,bid,borrow_at) VALUES (%d,%d,'%s')",uid,bid,nowStr);
            System.out.println("DEBUG: " + sql);
            try (PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                ps.executeUpdate();
                try (ResultSet rs = ps.getResultSet()) {
                    rs.next();

                    user.uid = uid;
                    user.username = rs.getString("username");
                }
            }

            sql = String.format("SELECT * FROM books where bid = %d",bid);
            System.out.println("DEBUG: " + sql);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    book.bid = bid;
                    book.name = rs.getString("name");
                    book.totalCount = rs.getInt("total_count");
                    book.currentCount = rs.getInt("current_count");
                }
            }

            record.user = user;
            record.book = book;
            record.borrowedAt = now;
            record.returned = false;
            record.returnedAt = null;

        }
        return  record;
    }

今天粗略就放这么多，基本上能看懂就没问题了。