初学MySQL的一个小项目(图书管理系统)

接触java已经有将近一个半月了，也算是度过了新手上路阶段，已经进入了java第二阶段的学习，刚开始学习MySQL，接触到的一个小项目，分享给大家，有什么不准确的地方还请各位大佬谅解，毕竟是新手！

项目是写一个图书管理系统。

项目需要用到关系型数据库MySQL，JDBC数据库连接等。

首先分析图书管系统，画出ER图

先建立reader和book两个类：
reader类
public class Reader {
private int id;//读者编号
private String name;//读者姓名
private boolean gender;//性别
private String tel;//电话
private Date registerDate;//注册日期
private boolean available;//读者是否有效

public int getId() {
    return id;
}

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

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public boolean isGender() {
    return gender;
}

public void setGender(boolean gender) {
    this.gender = gender;
}

public String getTel() {
    return tel;
}

public void setTel(String tel) {
    this.tel = tel;
}

public Date getRegisterDate() {
    return registerDate;
}

public void setRegisterDate(Date registerDate) {
    this.registerDate = registerDate;
}

public boolean isAvailable() {
    return available;
}

public void setAvailable(boolean available) {
    this.available = available;
}

}
book类：
public class Book {
private int id;//图书编号
private String isbn;//图书国际编号
private String name;//书名
private double price;//价格
private String author;//作者
private String publisher;//出版社
private Date pubDate;//出版日期
private boolean lended;//是否借出
private int counter;//借书次数

public int getId() {
    return id;
}

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

public String getIsbn() {
    return isbn;
}

public void setIsbn(String isbn) {
    this.isbn = isbn;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public double getPrice() {
    return price;
}

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

public String getAuthor() {
    return author;
}

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

public String getPublisher() {
    return publisher;
}

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

public Date getPubDate() {
    return pubDate;
}

public void setPubDate(Date pubDate) {
    this.pubDate = pubDate;
}

public boolean isLended() {
    return lended;
}

public void setLended(boolean lended) {
    this.lended = lended;
}

public int getCounter() {
    return counter;
}

public void setCounter(int counter) {
    this.counter = counter;
}

}
物理模型图：

建立JDBC操作工具类：
public class JdbcUtil {
private static Properties props = new Properties();

static {
    try (InputStream inputStream = JdbcUtil.class.getClassLoader().getResourceAsStream(
    "jdbc.properties")) {
        props.load(inputStream);
        Class.forName(props.getProperty("drv"));
    } catch (IOException | ClassNotFoundException e) {
        e.printStackTrace();
    }
}

private JdbcUtil() {
    throw new AssertionError();
}

/**
 * 获得数据库连接
 * @return Connection对象
 */
public static Connection getConnection() {
    try {
        return DriverManager.getConnection(
                props.getProperty("url"), props.getProperty("uid"), props.getProperty("pwd"));
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 关闭数据库连接
 * @param connection 连接对象
 */
public static void closeConnection(Connection connection) {
    try {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 开启事务
 * @param connection 连接对象
 */
public static void beginTx(Connection connection) {
    try {
        connection.setAutoCommit(false);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 提交事务
 * @param connection 连接对象
 */
public static void commitTx(Connection connection) {
    try {
        connection.commit();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 回滚事务
 */
public static void rollbackTx(Connection connection) {
    try {
        connection.rollback();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 执行增删改操作
 * @param con 连接对象
 * @param sql SQL语句
 * @param params 替换占位符的参数
 * @return 受影响的行数
 * @throws SQLException
 */
public static int executeUpdate(Connection con, String sql, Object... params) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement(sql)) {
        for (int i = 0; i < params.length; ++i) {
            stmt.setObject(i + 1, params[i]);
        }
        return stmt.executeUpdate();
    }
}

}

为了方便维护，建立一个jdbc配置文件：
drv=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/booksys
uid=root
pwd=123456

读者管理系统：
public class ReaderManager {
/**
* 新增一个读者
* @param reader 读者
* @return 新增成功返回true 否则返回false
*/
public boolean createNewReader(Reader reader) {
try (Connection connection = JdbcUtil.getConnection()) {
return JdbcUtil.executeUpdate(connection,
“insert into tb_reader (readerid, rname, gender, tel, regdate) values (?,?,?,?,?)”,
reader.getId(), reader.getName(), reader.isGender(), reader.getTel(),
new Date()) == 1;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}

/**
 * 注销一个读者
 * @param id 读者编号
 * @return 注销成功返回true 否则返回false
 */
public boolean cancelReaderById(int id) {
    try (Connection connection = JdbcUtil.getConnection()) {
        return JdbcUtil.executeUpdate(connection, 
                "update tb_reader set available=0 where readerid=?", id) == 1;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

/**
 * 编辑读者电话
 * @param reader 读者
 * @return 编辑成功返回true 否则返回false
 */
public boolean editReaderInfo(Reader reader) {
    try (Connection connection = JdbcUtil.getConnection()) {
        return JdbcUtil.executeUpdate(connection, 
                "update tb_reader set tel=? where readerid=?", 
                reader.getTel(), reader.getId()) == 1;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

}

图书管理器：
public class BookManager {
private static final double PUNISH_PER_DAY = 0.2;
private static final int MAX_LEND_DAYS = 30;
private static final int MILLIS_PER_DAY = 24 * 60 * 60 * 1000; // 一天的毫秒数

/**
 * 添加新图书
 * @param book 图书
 * @return 添加成功返回true 否则返回false
 */
public boolean addNewBook(Book book) {
    try (Connection connection = JdbcUtil.getConnection()) {
        return JdbcUtil.executeUpdate(connection,
                "insert into tb_book (bookid, isbn, bname, price, author, publisher, pubdate) values (?,?,?,?,?,?,?)",
                book.getId(), book.getIsbn(), book.getName(), 
                book.getPrice(), book.getAuthor(), book.getPublisher(), 
                book.getPubDate()) == 1;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

/**
 * 根据编号移除图书(不可借阅)
 * @param id
 * @return 移除成功返回true 否则返回false
 */
public boolean removeBookById(int id) {
    try (Connection connection = JdbcUtil.getConnection()) {
        return JdbcUtil.executeUpdate(connection, 
                "update tb_book set lended=1 where bookid=? and lended=0",
                id) == 1;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

/**
 * 借出图书
 * @param bookId 图书编号
 * @param readerId 读者编号
 * @return 借出成功返回true 否则返回false
 */
public boolean lendOut(int bookId, int readerId) {
    boolean result = false;
    Connection connection = JdbcUtil.getConnection();
    try {
        PreparedStatement stmt = connection.prepareStatement(
                "select available from tb_reader where readerid=?");
        stmt.setInt(1, readerId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next() && rs.getBoolean(1)) {
            JdbcUtil.beginTx(connection);
            if (JdbcUtil.executeUpdate(connection,
                    "update tb_book set lended=1, counter=counter+1 where lended=0 and bookid=?", 
                    bookId) == 1) {
                result = JdbcUtil.executeUpdate(connection,
                        "insert into tb_record (bid, rid, lenddate) values (?,?,?)", 
                        bookId, readerId, new Date(System.currentTimeMillis())) == 1;
            }
            JdbcUtil.commitTx(connection);
        }
    } catch (SQLException e) {
        e.printStackTrace();
        JdbcUtil.rollbackTx(connection);
    } finally {
        JdbcUtil.closeConnection(connection);
    }
    return result;
}

/**
 * 归还图书
 * @param bookId 图书编号
 * @param readerId 读者编号
 * @return 如果超期则返回罚款金额 否则返回0 操作失败返回-1
 */
public double returnBack(int bookId, int readerId) {
    double pulishment = -1;
    Connection connection = JdbcUtil.getConnection();
    try  {
        PreparedStatement stmt = connection.prepareStatement(
                "select recordid, lenddate from tb_record where lenddate=("
                + "select max(lenddate) from tb_record where bid=? and rid=?) "
                + "and backdate is null and bid=? and rid=?");
        stmt.setInt(1, bookId);
        stmt.setInt(2, readerId);
        stmt.setInt(3, bookId);
        stmt.setInt(4, readerId);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            int recordId = rs.getInt("recordid");
            Date lendDate = rs.getDate("lenddate");
            Date backDate = new Date();
            double days = Math.ceil(
                    (backDate.getTime() - lendDate.getTime()) / (double) MILLIS_PER_DAY);
            pulishment = days > MAX_LEND_DAYS ? 
                    Math.round((days - MAX_LEND_DAYS) * PUNISH_PER_DAY * 100) / 100.0 : 0;
            JdbcUtil.beginTx(connection);
            if (JdbcUtil.executeUpdate(connection, 
                    "update tb_book set lended=0 where lended=1 and bookid=?", 
                    bookId) == 1) {
                JdbcUtil.executeUpdate(connection, 
                        "update tb_record set backdate=?, pulishment=? where recordid=?", 
                        backDate, pulishment, recordId);
            }
            JdbcUtil.commitTx(connection);
        }
    } catch (SQLException e) {
        e.printStackTrace();
        JdbcUtil.rollbackTx(connection);
    } finally {
        JdbcUtil.closeConnection(connection);
    }
    return pulishment;
}

/**
 * 根据图书编号查找图书
 * @param id 图书编号
 * @return 图书对象或null
 */
public Book searchBookById(int id) {
    Book book = null;
    try (Connection connection = JdbcUtil.getConnection()) {
        PreparedStatement stmt = connection.prepareStatement(
                "select bookid, isbn, bname, price, author, publisher, pubdate, lended, counter "
                + "from tb_book where bookid=?");
        stmt.setInt(1, id);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            book = handleResultSet(rs);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return book;
}

/**
 * 根据ISBN号查找图书
 * @param isbn
 * @return 保存查询结果的列表容器
 */
public List<Book> searchBookByIsbn(String isbn) {
    List<Book> bookList = new ArrayList<>();
    try (Connection connection = JdbcUtil.getConnection()) {
        PreparedStatement stmt = connection.prepareStatement(
                "select bookid, isbn, bname, price, author, publisher, pubdate, lended, counter "
                + "from tb_book where isbn=?");
        stmt.setString(1, isbn);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Book book = handleResultSet(rs);
            bookList.add(book);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return bookList.size() > 0 ? bookList : Collections.emptyList();
}

/**
 * 根据书名查找图书
 * @param name 书名(支持模糊查找)
 * @return 保存查询结果的列表容器
 */
public List<Book> searchBookByName(String name) {
    List<Book> bookList = new ArrayList<>();
    try (Connection connection = JdbcUtil.getConnection()) {
        PreparedStatement stmt = connection.prepareStatement(
                "select bookid, isbn, bname, price, author, publisher, pubdate, lended, counter "
                + "from tb_book where bname like ?");
        stmt.setString(1, "%" + name + "%");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Book book = handleResultSet(rs);
            bookList.add(book);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return bookList.size() > 0 ? bookList : Collections.emptyList();
}

/**
 * 根据作者查找图书
 * @param author  作者
 * @return 保存查询结果的列表容器
 */
public List<Book> searchBookByAuthor(String author) {
    List<Book> bookList = new ArrayList<>();
    try (Connection connection = JdbcUtil.getConnection()) {
        PreparedStatement stmt = connection.prepareStatement(
                "select bookid, isbn, bname, price, author, publisher, pubdate, lended, counter "
                + "from tb_book where author like ?");
        stmt.setString(1, "%" + author + "%");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Book book = handleResultSet(rs);
            bookList.add(book);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return bookList.size() > 0 ? bookList : Collections.emptyList();
}

/**
 * 查询借阅排行榜前10名
 * @return 保存查询结果的列表容器
 */
public List<Book> searchTop10Books() {
    List<Book> bookList = new ArrayList<>();
    try (Connection connection = JdbcUtil.getConnection()) {
        PreparedStatement stmt = connection.prepareStatement(
                "select bookid, isbn, bname, price, author, publisher, pubdate, lended, "
                + "sum(counter) as counter from tb_book group by isbn "
                + "order by counter desc limit 10");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            Book book = handleResultSet(rs);
            bookList.add(book);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return bookList.size() > 0 ? bookList : Collections.emptyList();
}

// 处理结果集
private Book handleResultSet(ResultSet rs) throws SQLException {
    Book book = new Book();
    book.setId(rs.getInt("bookid"));
    book.setIsbn(rs.getString("isbn"));
    book.setName(rs.getString("bname"));
    book.setPrice(rs.getDouble("price"));
    book.setAuthor(rs.getString("author"));
    book.setPublisher(rs.getString("publisher"));
    book.setPubDate(rs.getDate("pubdate"));
    book.setLended(rs.getBoolean("lended"));
    book.setCounter(rs.getInt("counter"));
    return book;
}

}

图书管理系统：
public class BookMIS {
private static BookManager bookManager = new BookManager();
private static ReaderManager readerManager= new ReaderManager();

private static Scanner scanner = new Scanner(System.in);

public static void main(String[] args) {
    boolean isRunning = true;
    System.out.println("===欢迎使用ABC图书管理系统===");
    while (isRunning) {
        int choice;
        System.out.println("1. 管理读者");
        System.out.println("2. 管理图书");
        System.out.println("3. 退出系统");
        do {
            System.out.print("请选择: ");
            choice = scanner.nextInt();
        } while (choice < 1 || choice > 3);
        switch (choice) {
        case 1:
            showReaderSubMenu();
            break;
        case 2:
            showBookSubMenu();
            break;
        case 3:
            System.out.println("感谢使用本系统, 欢迎下次再来");
            isRunning = false;
            break;
        }
    }
    scanner.close();
}

private static void showReaderSubMenu() {
    System.out.println("======读者管理子系统======");
    boolean flag = true;
    int choice;
    while (flag) {
        System.out.println("1. 新增读者");
        System.out.println("2. 注销读者");
        System.out.println("3. 修改信息");
        System.out.println("4. 返回主菜单");
        do {
            System.out.print("请选择: ");
            choice = scanner.nextInt();
        } while (choice < 1 || choice > 4);
        switch (choice) {
        case 1:
            addReader();
            break;
        case 2:
            cancelReader();
            break;
        case 3:
            editReader();
            break;
        case 4:
            flag = false;
            break;
        }
    }
}

private static void addReader() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    System.out.print("姓名: ");
    String name = scanner.next();
    System.out.print("性别(1. 男, 0. 女): ");
    int gender = scanner.nextInt();
    System.out.print("电话: ");
    String tel = scanner.next();
    Reader reader = new Reader();
    reader.setId(id);
    reader.setName(name);
    reader.setGender(gender == 1);
    reader.setTel(tel);
    if (readerManager.createNewReader(reader)) {
        System.out.println("添加读者成功!");
    } else {
        System.out.println("操作失败!!!");
    }
}

private static void cancelReader() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    if (readerManager.cancelReaderById(id)) {
        System.out.println("注销成功!");
    } else {
        System.out.println("注销失败!");
    }
}

private static void editReader() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    System.out.print("电话: ");
    String tel = scanner.next();
    Reader reader = new Reader();
    reader.setId(id);
    reader.setTel(tel);
    if (readerManager.editReaderInfo(reader)) {
        System.out.println("更新读者电话成功!");
    } else {
        System.out.println("更新读者电话失败!");
    }
}

private static void showBookSubMenu() {
    System.out.println("======图书管理子系统======");
    boolean flag = true;
    int choice;
    while (flag) {
        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. Top10排行榜");
        System.out.println("8. 返回主菜单");
        do {
            System.out.print("请选择: ");
            choice = scanner.nextInt();
        } while (choice < 1 || choice > 8);
        switch (choice) {
        case 1:
            addBook();
            break;
        case 2:
            removeBook();
            break;
        case 3:
            lendBook();
            break;
        case 4:
            returnBook();
            break;
        case 5:
            getBookById();
            break;
        case 6:
            listBooksByIsbn();
            break;
        case 7:
            listTop10Books();
            break;
        case 8:
            flag = false;
            break;
        }
    }
}

private static void addBook() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    System.out.print("ISBN: ");
    String isbn = scanner.next();
    System.out.print("书名: ");
    String name = scanner.next();
    System.out.print("价格: ");
    double price = scanner.nextDouble();
    System.out.print("作者: ");
    String author = scanner.next();
    System.out.print("出版社: ");
    String publisher = scanner.next();
    System.out.print("出版日期: ");
    Date pubDate = CommonUtil.stringToDate(scanner.next());
    Book book = new Book();
    book.setId(id);
    book.setIsbn(isbn);
    book.setName(name);
    book.setPrice(price);
    book.setAuthor(author);
    book.setPublisher(publisher);
    book.setPubDate(pubDate);
    if (bookManager.addNewBook(book)) {
        System.out.println("新增图书成功!");
    } else {
        System.out.println("新增图书失败!");
    }
}

private static void removeBook() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    if (bookManager.removeBookById(id)) {
        System.out.println("下架成功!");
    } else {
        System.out.println("下架失败!");
    }
}

private static void lendBook() {
    System.out.print("图书编号: ");
    int bookId = scanner.nextInt();
    System.out.print("读者编号: ");
    int readerId = scanner.nextInt();
    if (bookManager.lendOut(bookId, readerId)) {
        System.out.println("借书成功!!!");
    } else {
        System.out.println("借书失败!!!");
    }
}

private static void returnBook() {
    System.out.print("图书编号: ");
    int bookId = scanner.nextInt();
    System.out.print("读者编号: ");
    int readerId = scanner.nextInt();
    double pulishment = bookManager.returnBack(bookId, readerId);
    if (pulishment >= 0) {
        System.out.println("还书成功!!!");
        if (pulishment > 0) {
            System.out.printf("请缴纳罚款: %.1f元\n", pulishment);
        }
    } else {
        System.out.println("还书失败!!!");
    }
}

private static void getBookById() {
    System.out.print("编号: ");
    int id = scanner.nextInt();
    Book book = bookManager.searchBookById(id);
    displayTitle();
    displayBook(book);
}

private static void listBooksByIsbn() {
    System.out.print("书名: ");
    String name = scanner.next();
    List<Book> list = bookManager.searchBookByName(name);
    displayBookList(list);
}

private static void listTop10Books() {
    List<Book> list = bookManager.searchTop10Books();
    displayBookList(list);
}

private static void displayBookList(List<Book> list) {
    displayTitle();
    for (Book book : list) {
        displayBook(book);
    }
}

private static void displayTitle() {
    System.out.printf("%-50s%-20s%-10s\n", "书名", "作者", "借阅量");
}

private static void displayBook(Book book) {
    System.out.printf("%-50s%-20s%-10d\n", book.getName(),
            book.getAuthor(), book.getCounter());
}

}

常用工具类：
public class CommonUtil {
private static SimpleDateFormat formatter = new SimpleDateFormat(“yyyy-MM-dd”);

private CommonUtil() {
    throw new AssertionError();
}

/**
 * 字符串转日期
 * @param str 字符串形式的日期
 * @return 日期对象
 */
public static Date stringToDate(String str) {
    try {
        return formatter.parse(str);
    } catch (ParseException e) {
        throw new RuntimeException(e);
    }
}

/**
 * 日期转字符串
 * @param date 日期对象
 * @return 字符串形式的日期
 */
public static String dateToString(Date date) {
    return formatter.format(date);
}

}