今天就跟大家聊聊有关怎么在Java中利用MySQL编写一个图书管理系统,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
一,功能
管理员登录
图书借阅信息管理
图书信息管理
管理员更改密码
退出系统
Eclipse Version: 2018-09 (4.9.0)
MySQL Workbench 8.0 CE
mysql-connector-java-8.0.13.jar
登录界面:
主界面:
借阅书籍管理:
个人书库管理:
更改密码:
1)图书表
2)用户表
两个数据表间没有关联:
(1)逻辑图
(2)包结构,采用MVC三层架构组织各个模块
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.jb51.net/article/204374.htm
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);
}
}
看完上述内容,你们对怎么在Java中利用MySQL编写一个图书管理系统有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。