设计并实现一个多线程图书馆管理系统，涉及数据库操作
2023-12-13 04:44:10
没有实现全部功能，希望路过的大佬，可以实现全部功能，在评论区聊聊
创建数据库library-demo
CREATE DATABASE `library-demo`
创建图书表book
CREATE TABLE `book` (
`bookId` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书ID',
`bookName` varchar(15) DEFAULT NULL COMMENT '书名',
`author` varchar(10) DEFAULT NULL COMMENT '作者',
`publish` varchar(100) DEFAULT NULL COMMENT '出版社',
`isbn` varchar(30) DEFAULT NULL COMMENT 'ISBN',
`introduction` varchar(50) DEFAULT NULL COMMENT '简介',
`language` varchar(10) DEFAULT NULL COMMENT '语言',
`price` varchar(10) DEFAULT NULL COMMENT '价格',
`bookStatus` tinyint(4) DEFAULT NULL COMMENT '图书状态',
`bookImage` varchar(30) DEFAULT NULL COMMENT '封面',
`bookCount` int(11) DEFAULT NULL COMMENT '存有量',
`borrowDate` date DEFAULT NULL COMMENT '借阅日期',
`returnDate` date DEFAULT NULL COMMENT '返还日期',
`borrowTime` int(11) DEFAULT NULL COMMENT '借阅时长',
PRIMARY KEY (`bookId`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='图书表';
创建用户表user
CREATE TABLE `user` (
`userId` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`userName` varchar(10) DEFAULT NULL COMMENT '用户名字',
`password` varchar(10) DEFAULT NULL COMMENT '密码',
`email` varchar(10) DEFAULT NULL COMMENT '邮箱',
`phone` varchar(15) DEFAULT NULL COMMENT '电话',
`address` varchar(20) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表';
创建用户图书关联表bookuser
CREATE TABLE `bookuser` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '关系id',
`book_id` int(11) DEFAULT NULL COMMENT '图书id',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户图书关系表';
创建借阅记录表borrowslog
CREATE TABLE `borrowslog` (
`logId` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志id',
`borrowsFlag` varchar(10) DEFAULT NULL COMMENT '借阅标志',
`bookId` varchar(10) DEFAULT NULL COMMENT '书籍Id',
`userId` varchar(10) DEFAULT NULL COMMENT '用户Id',
`borrowsDate` date DEFAULT NULL COMMENT '借阅日期',
`returnFlag` varchar(10) DEFAULT NULL COMMENT '归还标志',
`returnDate` date DEFAULT NULL COMMENT '归还日期',
`borrowsTime` int(11) DEFAULT NULL COMMENT '借阅时长',
`realityBorrowsTime` int(11) DEFAULT NULL COMMENT '实际借阅时长',
`borrowNumber` varchar(20) DEFAULT NULL COMMENT '借阅单号',
PRIMARY KEY (`logId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='借阅记录表';
到此数据库创建完成
下边是Demo的结构
book.java
package com.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
/**
* @program: LibraryDemo
* @description: 图书实体
* @author: Casey Hu
* @create: 2023-12-06 11:52
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
private Integer BookId;
private String BookName;
private String Author;
private String Publish;
private String ISBN;
private String Introduction;
private String Language;
private String Price;
private Integer bookStatus;
private String bookImage;
private Integer bookCount;
private Date borrowDate;
private Date returnDate;
private Integer borrowTime;
}
BorrowsLog.java
package com.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
/**
* @program: LibraryDemo
* @description:
* @author: Casey Hu
* @create: 2023-12-07 14:15
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BorrowsLog {
private Integer logId;
private String borrowsFlag;
private String bookId;
private String userId;
private Date borrowsDate;
private String returnFlag;
private Date returnDate;
private String borrowsTime;
private String realityBorrowsTime;
private String borrowNumber;
}
User.java
package com.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: LibraryDemo
* @description: 用户
* @author: Casey Hu
* @create: 2023-12-06 14:17
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer UserId;
private String UserName;
private String Password;
private String Email;
private String Phone;
private String Address;
}
LibraryService.java
package com.service;
import com.domain.Book;
import com.domain.BorrowsLog;
import com.domain.User;
import com.util.JDBCUtil;
import java.sql.ResultSet;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.TimeUnit;
/**
* @program: LibraryDemo
* @description:
* @author: Casey Hu
* @create: 2023-12-06 16:25
**/
public class LibraryService {
//添加图书
public synchronized void addBook(Book book) {
JDBCUtil jdbcUtil = new JDBCUtil();
int i = jdbcUtil.executeUpdate("INSERT INTO book (bookName, author, publish, isbn, introduction, `language`, price, bookStatus, bookImage, bookCount)" +
" VALUES('" + book.getBookName() + "', '" + book.getAuthor() + "', '" + book.getPublish() + "', '" + book.getISBN() + "', '" + book.getIntroduction() + "', '" + book.getLanguage() + "', '" + book.getPrice() + "', " + book.getBookStatus() + ", '" + book.getBookImage() + "', " + book.getBookCount() + ");");
if (i==1) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
jdbcUtil.closeConnection();
}
//删除图书
public synchronized void deleteBook(Book book) {
JDBCUtil jdbcUtil = new JDBCUtil();
jdbcUtil.executeUpdate("update book set bookStatus = 0 where bookName = " + book.getBookName() + ";");
jdbcUtil.closeConnection();
}
//搜索图书
public synchronized List<Book> queryBookList(Book book) {
List<Book> bookList =new ArrayList<>();
JDBCUtil jdbcUtil = new JDBCUtil();
ResultSet resultSet=jdbcUtil.executeQuery("select * from book where bookName = " + book.getBookName() + " and bookStatus = 1;");
try {
while (resultSet.next()) {
Book bookRes=new Book();
bookRes.setBookName(resultSet.getString("bookName"));
bookRes.setAuthor(resultSet.getString("author"));
bookRes.setPublish(resultSet.getString("publish"));
bookRes.setISBN(resultSet.getString("isbn"));
bookRes.setIntroduction(resultSet.getString("introduction"));
bookRes.setLanguage(resultSet.getString("language"));
bookRes.setPrice(resultSet.getString("price"));
bookRes.setBookStatus(resultSet.getInt("bookStatus"));
bookRes.setBookImage(resultSet.getString("bookImage"));
bookRes.setBookCount(resultSet.getInt("bookCount"));
bookRes.setBorrowDate(resultSet.getDate("borrowDate"));
bookRes.setReturnDate(resultSet.getDate("returnDate"));
bookRes.setBorrowTime(resultSet.getInt("borrowTime"));
bookList.add(bookRes);
}
}catch (Exception e){
e.printStackTrace();
}
jdbcUtil.closeConnection();
return bookList;
}
public synchronized Book queryBook(Book book) {
JDBCUtil jdbcUtil = new JDBCUtil();
Book bookRes=new Book();
ResultSet resultSet=jdbcUtil.executeQuery("select * from book where isbn = '" + book.getISBN() + "' and bookStatus = 1;");
try {
while (resultSet.next()) {
bookRes.setBookName(resultSet.getString("bookName"));
bookRes.setAuthor(resultSet.getString("author"));
bookRes.setPublish(resultSet.getString("publish"));
bookRes.setISBN(resultSet.getString("isbn"));
bookRes.setIntroduction(resultSet.getString("introduction"));
bookRes.setLanguage(resultSet.getString("language"));
bookRes.setPrice(resultSet.getString("price"));
bookRes.setBookStatus(resultSet.getInt("bookStatus"));
bookRes.setBookImage(resultSet.getString("bookImage"));
bookRes.setBookCount(resultSet.getInt("bookCount"));
bookRes.setBorrowDate(resultSet.getDate("borrowDate"));
bookRes.setReturnDate(resultSet.getDate("returnDate"));
bookRes.setBorrowTime(resultSet.getInt("borrowTime"));
}
}catch (Exception e){
e.printStackTrace();
}
jdbcUtil.closeConnection();
return bookRes;
}
//借阅图书
public synchronized void borrowBook(Book book, User user,Integer days) {
//根据图书ISBN查询 图书是否可以借阅，且查看库存量是否有，
JDBCUtil jdbcUtil = new JDBCUtil();
ResultSet resultSet = jdbcUtil.executeQuery("select bookStatus,bookCount,bookName,bookId from book where isbn='"+book.getISBN()+"' ;");
Instant instant = Instant.now();
long timestamp = instant.toEpochMilli();
try {
while (resultSet.next()) {
Integer bookStatus = resultSet.getInt("bookStatus");
if(bookStatus==1){
Integer bookCount = resultSet.getInt("bookCount");
if(bookCount>0){
//借阅 减少库存
int i = jdbcUtil.executeUpdate("update book set bookCount = bookCount - 1 where isbn='" + book.getISBN() + "' ;");
if(i==1){
//新增借阅日志
i = jdbcUtil.executeUpdate("INSERT INTO borrowslog (borrowsFlag, bookId, userId, borrowsDate, borrowsTime, borrowNumber) " +
"VALUES('1', '" + resultSet.getInt("bookId") + "', '" + user.getUserId() + "', NOW(), '" + days + "', '" + timestamp + "');");
if(i==1){
System.out.println("借阅成功！借阅单号："+timestamp);
}else {
System.out.println("借阅异常，请联系管理员");
}
}
}else {
System.out.println("此书 "+resultSet.getInt("bookName")+"库存不足");
}
}else {
System.out.println("此书 "+resultSet.getInt("bookName")+"已下架");
}
}
}catch (Exception e){
System.out.println("查不到此书");
e.printStackTrace();
}
jdbcUtil.closeConnection();
}
public synchronized void returnBook(String borrowNumber) {
JDBCUtil jdbcUtil = new JDBCUtil();
//通过借阅单号， 更新借阅标志和时间
ResultSet resultSet = jdbcUtil.executeQuery("SELECT logId, borrowsFlag, bookId, userId, borrowsDate, returnFlag, returnDate, \n" +
"borrowsTime, realityBorrowsTime, borrowNumber FROM borrowslog where borrowNumber='"+borrowNumber+"';");
BorrowsLog borrowsLog = new BorrowsLog();
try {
while (resultSet.next()) {
borrowsLog.setLogId(resultSet.getInt("logId"));
borrowsLog.setUserId(resultSet.getString("userId"));
borrowsLog.setBookId(resultSet.getString("bookId"));
//计算实际时间
Date borrowsDate= resultSet.getDate("borrowsDate");
long differenceInMilliseconds = new Date().getTime() - borrowsDate.getTime();
// 转换毫秒差值为天数
Integer differenceInDays = (int) TimeUnit.MILLISECONDS.toDays(differenceInMilliseconds);
//更新
jdbcUtil.executeUpdate("UPDATE borrowslog SET returnFlag='1', returnDate=NOW(), realityBorrowsTime="+differenceInDays+" WHERE logId="+borrowsLog.getLogId()+" and borrowNumber='"+borrowNumber+"';");
int i = jdbcUtil.executeUpdate("update book set bookCount = bookCount + 1 where bookId=" + borrowsLog.getBookId() + " ;");
if(i==1){
System.out.println("此书归还成功");
}
}
}catch (Exception e){
e.printStackTrace();
}
jdbcUtil.closeConnection();
}
}
UserService.java(没有实现)
package com.service;
/**
* @program: LibraryDemo
* @description:
* @author: Casey Hu
* @create: 2023-12-06 16:49
**/
public class UserService {
}
JDBCUtil.java
package com.util;
import javax.sql.DataSource;
import java.sql.*;
/**
* @program: LibraryDemo
* @description: JDBC工具类
* @author: Casey Hu
* @create: 2023-12-06 14:31
**/
public class JDBCUtil {
public static Connection connection;
public static String url="jdbc:mysql://localhost:3306/library-demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
public static String user="root";
public static String password="123456";
public static Connection getConnection(){
try {
connection = DriverManager.getConnection(url,user,password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//执行查询
public static ResultSet executeQuery(String sql) {
System.out.println("执行SQL："+sql);
Connection conn=getConnection();
ResultSet resultSet=null;
try {
Statement stmt=conn.createStatement();
resultSet=stmt.executeQuery(sql);
}catch (Exception e){
e.printStackTrace();
}
return resultSet;
}
// 执行更新操作（插入、更新、删除）
public static int executeUpdate(String sql) {
System.out.println("执行SQL："+sql);
Connection conn = getConnection();
int result = 0;
try {
// 开始事务
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
result = statement.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
// 回滚事务
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
}
return result;
}
// 关闭数据库连接
public static void closeConnection() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
StatementTest.java
package com.util;
import com.service.LibraryService;
import java.sql.*;
/**
* @program: LibraryDemo
* @description:
* @author: Casey Hu
* @create: 2023-12-06 14:45
**/
public class StatementTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// //加载驱动
// Class.forName("com.mysql.cj.jdbc.Driver");
// //输入url、用户名、密码创建连接
// //jdbc:mysql:是固定的写法，后面跟主机名localhost，3306是默认的MySQL端口号demo是数据库名称
// //useUnicode=true是指是否使用Unicode字符集，赋值为true
// //serverTimezone=UTC是指定时区时间为世界统一时间
// //characterEncoding=utf-8是指定字符编码格式为UTF8
// Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library-demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
// //sql查询语句
// String sql = "select * from user";
// Statement statement = connection.createStatement();
// //执行查询语句
// ResultSet resultSet = statement.executeQuery(sql);
// //结果遍历
// while (resultSet.next()) {
// Object id = resultSet.getObject("userId");
// Object name = resultSet.getObject("userName");
// Object password = resultSet.getObject("password");
// Object phone = resultSet.getObject("phone");
// System.out.println(id + "-" + name + "-" + password + "-" + phone);
// }
// //关闭资源
// resultSet.close();
// statement.close();
// connection.close();
// JDBCUtil jdbcUtil = new JDBCUtil();
// User user=new User();
// user.setUserName("李四");
// user.setPassword("123456");
// user.setEmail("XXXXXXXXXX");
// user.setPhone("1235586865");
// jdbcUtil.executeUpdate("INSERT INTO `user` (userName, password, email, phone, address) " +
// "VALUES('"+user.getUserName()+"', '"+user.getPassword()+"', '"+user.getEmail()+"', '"+user.getPhone()+"', '"+user.getAddress()+"');");
// ResultSet resultSet = jdbcUtil.executeQuery("select * from user");
// //结果遍历
// while (resultSet.next()) {
// Object id = resultSet.getObject("userId");
// Object name = resultSet.getObject("userName");
// Object password = resultSet.getObject("password");
// Object phone = resultSet.getObject("phone");
// System.out.println(id + "-" + name + "-" + password + "-" + phone);
// }
// jdbcUtil.closeConnection();
// Book book=new Book();
// book.setBookName("晚熟的人");
// book.setAuthor("莫言");
// book.setPrice("100");
// book.setBookImage("1111111");
// book.setIntroduction("《晚熟的人》是中国当代作家莫言创作的中短篇小说集");
// book.setISBN("978-7020-164-776");
// book.setBookStatus(1);
// book.setBookCount(10);
LibraryService libraryService=new LibraryService();
// libraryService.addBook(book);
// Book book1 = libraryService.queryBook(book);
// System.out.println(book1.toString());
// User user=new User();
// user.setUserId(1);
//借阅图书
// libraryService.borrowBook(book1,user,30);
//归还
libraryService.returnBook("1701968793273");
}
}
结束
写个总结吧，有的功能，比如登录，多线程的那部分没有实现，因为不会啊。我整体做完最后特别大的感受就是，对于持久层的框架是非常有必要的，如果没有mybatis，这样一点一点的封装是非常麻烦的
文章来源:https://blog.csdn.net/qq_43658218/article/details/134887196
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！