一,功能
- 管理员登录
- 图书借阅信息管理
- 图书信息管理
- 管理员更改密码
- 退出系统
二,工具
- 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、书名、图书价格、图书作者、出版社
-
+
"(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<Book> query() throws Exception{
-
Connection con = DBUtil.getConnection();
-
Statement stmt = con.createStatement();
-
ResultSet rs = stmt.executeQuery(
"select "
-
// ISBN、书名、作者、图书价格、出版社
-
+
"ID, ISBN, book_name, book_author, book_price, published_house, "
-
// 分类号、借书人姓名、借书人电话
-
+
"book_category, borrower_name, borrower_phone "
-
+
"from tb_books");
-
List<Book> bookList =
new ArrayList<Book>();
-
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<Book> 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<Book> bookList =
new ArrayList<Book>();
-
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 "
-
// ISBN、图书名称、作者、价格
-
+
"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://www.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.changeBook(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://www.cnblogs.com/Qian123/p/5339164.html#_labelTop
参考文章:https://blog.csdn.net/acm_hmj/article/details/52830920