Java+MySQL 图书管理系统

Java+MySQL 图书管理系统

一，功能

管理员登录

图书借阅信息管理

图书信息管理

管理员更改密码

退出系统

二，工具

Eclipse Version: 2018-09 (4.9.0)

mysql Workbench 8.0 CE

mysql-connector-java-8.0.13.jar

三、效果图：

登录界面：

主界面：

借阅书籍管理：

个人书库管理：

更改密码：

四、数据库设计

1）图书表

2）用户表

两个数据表间没有关联：

五、JAVA层次分析

（1）逻辑图

（2）包结构，采用MVC三层架构组织各个模块

六、主要Java代码分析

Dao类（以BookDao为例）

package pers.cyz.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import pers.cyz.model.Book;

import pers.cyz.util.DBUtil;

/**

* 数据库图书表信息数据访问对象类，包含增加图书信息、删除图书信息

* 、更新图书信息、查询图书信息、查询借阅信息和归还图书

*

* @author 1651200111 陈彦志

*/

public class BookDao {

/**

* 增加图书信息

*/

public void addBook(Book book) throws Exception{

// 首先拿到数据库的连接

Connection con = DBUtil.getConnection();

String sql="insert into tb_books"

+ "(ISBN, book_name, book_price, book_author, published_house,"

// 分类号、借书人姓名、借书人电话、借书日期，已借天数

+ "book_category, borrower_name, borrower_phone) "

+ "values("

/*

* 参数用?表示，相当于占位符，然后在对参数进行赋值。当真正执行时，

* 这些参数会加载在SQL语句中，把SQL语句拼接完整才去执行。这样就会减少对数据库的操作

*/

+ "?,?,?,?,?,?,?,?)";

/*

* prepareStatement这个方法会将SQL语句加载到驱动程序conn集成程序中，

* 但是并不直接执行,而是当它调用execute()方法的时候才真正执行；

*/

PreparedStatement psmt = con.prepareStatement(sql);

// 先对应SQL语句，给SQL语句传递参数

psmt.setString(1, book.getISBN());

psmt.setString(2, book.getBookName());

psmt.setFloat(3, book.getPrice());

psmt.setString(4, book.getAuthor());

psmt.setString(5, book.getPublishHouse());

psmt.setString(6, book.getBookCategory());

if (book.getBorrowerName() == null || book.getBorrowerName() == "") {

psmt.setString(7, null);

}

else {

psmt.setString(7, book.getBorrowerName());

}

if (book.getBorrowerPhone() == null || book.getBorrowerPhone() == "") {

psmt.setString(8, null);

}

else {

psmt.setString(8, book.getBorrowerPhone());

}

//执行SQL语句

psmt.execute();

}

/**

* 删除图书信息

*/

public void delBook(int ID) throws SQLException{

// 首先拿到数据库的连接

Connection con=DBUtil.getConnection();

String sql="" +

"DELETE FROM tb_books "+

// 参数用?表示，相当于占位符

"WHERE ID = ?";

// 预编译sql语句

PreparedStatement psmt = con.prepareStatement(sql);

// 先对应SQL语句，给SQL语句传递参数

psmt.setInt(1, ID);

// 执行SQL语句

psmt.execute();

}

/**

* 更新图书信息

*/

public void changeBook(Book book) throws SQLException{

// 首先拿到数据库的连接

Connection con=DBUtil.getConnection();

String sql="update tb_books "

+ "set ISBN = ?, book_name = ?, book_price = ?, book_author = ?"

+ ",published_house = ?, book_category = ?, borrower_name = ?, borrower_phone = ? "

// 参数用?表示，相当于占位符

+ "where ID = ?";

// 预编译sql语句

PreparedStatement psmt = con.prepareStatement(sql);

// 先对应SQL语句，给SQL语句传递参数

psmt.setString(1, book.getISBN());

psmt.setString(2, book.getBookName());

psmt.setFloat(3, book.getPrice());

psmt.setString(4, book.getAuthor());

psmt.setString(5, book.getPublishHouse());

psmt.setString(6, book.getBookCategory());

if (book.getBorrowerName().equals("")) {

psmt.setString(7, null);

}

else {

psmt.setString(7, book.getBorrowerName());

}

if (book.getBorrowerPhone().equals("")) {

psmt.setString(8, null);

}

else {

psmt.setString(8, book.getBorrowerPhone());

}

psmt.setInt(9, book.getID());

// 执行SQL语句

psmt.execute();

}

/**

* 查询书籍信息

*/

public List query() throws Exception{

Connection con = DBUtil.getConnection();

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("select "

+ "ID, ISBN, book_name, book_author, book_price, published_house, "

// 分类号、借书人姓名、借书人电话

+ "book_category, borrower_name, borrower_phone "

+ "from tb_books");

List bookList = new ArrayList();

Book book = null;

// 如果对象中有数据，就会循环打印出来

while (rs.next()){

book = new Book();

book.setID(rs.getInt("ID"));

book.setISBN(rs.getString("ISBN"));

book.setBookName(rs.getString("book_name"));

book.setAuthor(rs.getString("book_author"));

book.setPrice(rs.getFloat("book_price"));

book.setPublishHouse(rs.getString("published_house"));

book.setBookCategory(rs.getString("book_category"));

book.setBorrowerName(rs.getString("borrower_name"));

book.setBorrowerPhone(rs.getString("borrower_phone"));

bookList.add(book);

}

return bookList;

}

/**

* 查询借阅信息

*

* @return

* bookList

*/

public List borrowQuery() throws Exception{

Connection con = DBUtil.getConnection();

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(""

// ID、书名、借书人姓名、借书人电话

+ "SELECT ID, book_name, borrower_name, borrower_phone "

+ "FROM tb_books "

+ "WHERE borrower_name IS NOT NULL"

);

List bookList = new ArrayList();

Book book = null;

// 如果对象中有数据，就会循环打印出来

while (rs.next()){

book = new Book();

book.setID(rs.getInt("ID"));

book.setBookName(rs.getString("book_name"));

book.setBorrowerName(rs.getString("borrower_name"));

book.setBorrowerPhone(rs.getString("borrower_phone"));

bookList.add(book);

}

return bookList;

}

/**

* 更新图书信息，归还图书

*/

public void returnBook(Book book) throws SQLException{

// 首先拿到数据库的连接

Connection con=DBUtil.getConnection();

String sql="UPDATE tb_books "

+ "SET "

// 借书人姓名、借书人电话

+ "borrower_name = ?, borrower_phone = ? "

// 参数用?表示，相当于占位符

+ "WHERE ID = ?";

// 预编译sql语句

PreparedStatement psmt = con.prepareStatement(sql);

// 先对应SQL语句，给SQL语句传递参数

psmt.setString(1, book.getBorrowerName());

psmt.setString(2, book.getBorrowerPhone());

psmt.setInt(3, book.getID());

// 执行SQL语句

psmt.execute();

}

}

重点内容 ：

JDBC进行简单的数据库增删改查

详细参考：https://cnblogs.com/Qian123/p/5339164.html#_labelTop

Model类（以Book为例）

package pers.cyz.model;

/**

* 图书模型类,包含数据库图书表各对应的字段get、set方法

*

* @author 1651200111 陈彦志

*/

public class Book {

private int ID;

// ISBN号

private String ISBN;

// 图书名称

private String bookName;

// 图书价格

private float price;

private String author;

// 出版社

private String publishedHouse;

// 图书分类号

private String bookCategory;

// 借书人姓名

private String borrowerName;

// 借书人电话

private String borrowerPhone;

/**

* 获取ID

*/

public int getID() {

return ID;

}

/**

* 设置ID

*/

public void setID(int iD) {

ID = iD;

}

/**

* 获取ISBN

*/

public String getISBN() {

return ISBN;

}

/**

* 设置ISBN

*/

public void setISBN(String iSBN) {

ISBN = iSBN;

}

/**

* 获取图书名称

*/

public String getBookName() {

return bookName;

}

/**

* 设置图书名称

*/

public void setBookName(String bookName) {

this.bookName = bookName;

}

/**

* 获取图书价格

*/

public float getPrice() {

return price;

}

/**

* 设置图书价格

*/

public void setPrice(float price) {

this.price = price;

}

/**

*/

public String getAuthor() {

return author;

}

/**

*/

public void setAuthor(String author) {

this.author = author;

}

/**

* 获取出版社

*/

public String getPublishHouse() {

return publishedHouse;

}

/**

* 设置出版社

*/

public void setPublishHouse(String publishedHouse) {

this.publishedHouse = publishedHouse;

}

/**

* 获取图书分类信息

*/

public String getBookCategory() {

return bookCategory;

}

/**

* 设置图书分类信息

*/

public void setBookCategory(String bookCategory) {

this.bookCategory = bookCategory;

}

/**

* 获取借书人姓名

*/

public String getBorrowerName() {

return borrowerName;

}

/**

* 设置借书人姓名

*/

public void setBorrowerName(String borrowerName) {

this.borrowerName = borrowerName;

}

/**

* 获取借书人电话

*/

public String getBorrowerPhone() {

return borrowerPhone;

}

/**

* 设置借书人电话

*/

public void setBorrowerPhone(String borrowerPhone) {

this.borrowerPhone = borrowerPhone;

}

}

重点内容 ：

主要就是数据库对应表中各对应的字段get、set方法

Eclipse技巧：

Shift + alt + s  -> Generate Getters and Setters -> Select all -> Generate 自动生成set、get方法

Controller类（以BookAction为例）

package pers.cyz.controller;

import java.util.List;

import javax.swing.JTable;

import javax.swing.JTextField;

import pers.cyz.dao.BookDao;

import pers.cyz.model.Book;

/**

* 图书信息行为控制类，包含增加图书、删除图书

* 、 修改图书、和初始化个人书库管理窗体表格

*

* @author 1651200111 陈彦志

*/

public class BookAction {

/**

* 初始化窗体表格

* @return

* results

*/

@SuppressWarnings("rawtypes")

public Object[][] initializTable(String[] columnNames) throws Exception{

BookDao bookDao = new BookDao();

List list = bookDao.query();

Object[][] results = new Object[list.size()][columnNames.length];

for(int i = 0; i < list.size(); i++) {

Book book = (Book)list.get(i);

results[i][0] = book.getID();

results[i][1] = book.getBookName();

results[i][2] = book.getAuthor();

results[i][3] = book.getPrice();

results[i][4] = book.getISBN();

results[i][5] = book.getPublishHouse();

results[i][6] = book.getBookCategory();

String borrowerName = book.getBorrowerName();

if (borrowerName == null) {

borrowerName = "";

results[i][7] = borrowerName;

}

else {

results[i][7] = borrowerName;

}

String borrowerPhone = book.getBorrowerPhone();

if (borrowerPhone == null) {

borrowerPhone = "";

results[i][8] = borrowerPhone;

}

else {

results[i][8] = borrowerPhone;

}

}

return results;

}

/**

* 添加图书信息

*/

public void addBookInformation (JTextField textFieldISBN, JTextField textFieldName

,JTextField textFieldPrice, JTextField textFieldAuthor, JTextField textFieldPublishedHouse

, JTextField textFieldBookCategory, JTextField textFieldBorrowName

, JTextField textFieldBorrowPhone) throws Exception {

BookDao bookDao=new BookDao();

Book book=new Book();

book.setISBN(textFieldISBN.getText());

book.setBookName(textFieldName.getText());

float price = Float.parseFloat(textFieldPrice.getText());

book.setPrice(price);

book.setAuthor(textFieldAuthor.getText());

book.setPublishHouse(textFieldPublishedHouse.getText());

book.setBookCategory(textFieldBookCategory.getText());

if (textFieldBorrowName.getText() == null ||textFieldBorrowName.getText() == "" ) {

book.setBorrowerName(null);

}

else {

book.setBorrowerName(textFieldBorrowName.getText());

}

if (textFieldBorrowPhone.getText() == null || textFieldBorrowPhone.getText() == "") {

book.setBorrowerPhone(null);

}

else {

book.setBorrowerPhone(textFieldBorrowPhone.getText());

}

//添加图书

bookDao.addBook(book);

}

/**

* 删除图书信息

*/

public void delBookInformation (JTable table) throws Exception {

int selRow = table.getSelectedRow();

int ID = Integer.parseInt(table.getValueAt(selRow, 0).toString());

BookDao bookDao=new BookDao();

Book book=new Book();

book.setID(ID);

// 删除图书信息

bookDao.delBook(ID);

}

/**

* 修改图书信息

*/

public void changeBookInformation (JTextField textFieldISBN, JTextField textFieldName

,JTextField textFieldPrice, JTextField textFieldAuthor, JTextField textFieldPublishedHouse

, JTextField textFieldBookCategory, JTextField textFieldBorrowerName

, JTextField textFieldBorrowerPhone, JTable table) throws Exception{

BookDao bookDao=new BookDao();

Book book=new Book();

int selRow = table.getSelectedRow();

int ID = Integer.parseInt(table.getValueAt(selRow, 0).toString());

book.setID(ID);

book.setISBN(textFieldISBN.getText());

book.setBookName(textFieldName.getText());

book.setAuthor(textFieldAuthor.getText());

float price = Float.parseFloat(textFieldPrice.getText());

book.setPrice(price);

book.setPublishHouse(textFieldPublishedHouse.getText());

book.setBookCategory(textFieldBookCategory.getText());

book.setBorrowerName(textFieldBorrowerName.getText());

book.setBorrowerPhone(textFieldBorrowerPhone.getText());

//修改图书

bookDao.chajDMsCpSngeBook(book);

}

}

util类（以DBUtil为例）

package pers.cyz.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

/**

* 连接数据库类，包含一个对外提供获取数据库连接的方法

*

* @author 1651200111 陈彦志

*/

public class DBUtil {

// 数据库连接路径

private static final String URL = "jdbc:mysql://127.0.0.1:3306/db_books?"

+ "useUnicode = true & serverTimezone = GMT"

// MySQL在高版本需要指明是否进行SSL连接

+ "& characterEncoding = utf8 & useSSL = false";

private static final String NAME = "root";

private static final String PASSWORD = "root";

private static Connection conn = null;

// 静态代码块（将加载驱动、连接数据库放入静态块中）

static{

try {

// 加载驱动程序

Class.forName("com.mysql.cj.jdbc.Driver");

// 获取数据库的连接

conn = DriverManager.getConnection(URL, NAME, PASSWORD);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

// 对外提供一个方法来获取数据库连接

public static Connection getConnection(){

return conn;

}

}

util类（以BackgroundImage为例）

package pers.cyz.util;

import java.awt.Container;

import javax.swing.ImageIcon;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JPanel;

/**

* 设置背景图片类

*

* @author 1651200111 陈彦志

*/

public class BackgroundImage {

public BackgroundImage(JFrame frame,Container container,String ImageName) {

// 限定加载图片路径

ImageIcon icon= new ImageIcon("res/" + ImageName);

final JLabel labelBackground = new JLabel();

ImageIcon iconBookManageSystemBackground = icon;

labelBackground.setIcon(iconBookManageSystemBackground);

// 设置label的大小

labelBackground.setBounds(0,0,iconBookManageSystemBackground.getIconWidth()

,iconBookManageSystemBackground.getIconHeight());

// 将背景图片标签放入桌面面板的最底层

frame.getLayeredPane().add(labelBackground,new Integer(Integer.MIN_VALUE));

// 将容器转换为面板设置为透明

JPanel panel = (JPanel)container;

panel.setOpaque(false);

}

}

重点内容 ：

将图片标签放在窗体底层面板，然后将窗体转化为容器，将容器面板设为透明，背景图片就设置好了，之后就可以直接在该容器中添加组件

将所有两个或两个以上类需要用到的代码段全部封装到了公共类。

整体按照MVC三层架构组织

参考文章：https://cnblogs.com/Qian123/p/5339164.html#_labelTop

参考文章：https://blog.csdn.net/acm_hmj/article/details/52830920

源码打包下载地址：jb51.net/codes/769916.html

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。