期末作业
为了水一水期末作业,打算写一个简易的单体架构图书管理系统。以下为后端主要技术栈(后期可能更新,打算一个星期左右写完吧)。
- springboot
- redis
- mysql
- springcache
- springsecurity
…
数据库设计
第一次从0开始搭建后续可能还会多更新一些表。
-- 角色表
CREATE TABLE Role (role_id INT PRIMARY KEY AUTO_INCREMENT,role_name VARCHAR(50) NOT NULL,description VARCHAR(255)
);INSERT INTO Role (role_name, description) VALUES
('管理员', '拥有全部权限'),
('普通用户', '常规用户权限');-- 用户表
CREATE TABLE User (user_id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) UNIQUE NOT NULL,password VARCHAR(255) NOT NULL,contact_info VARCHAR(100),role_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY (role_id) REFERENCES Role(role_id)
);INSERT INTO User (username, password, contact_info, role_id) VALUES
('张三', 'hashed_password_1', 'zhangsan@example.com', 1),
('李四', 'hashed_password_2', 'lisi@example.com', 2),
('王五', 'hashed_password_3', 'wangwu@example.com', 2),
('赵六', 'hashed_password_4', 'zhaoliu@example.com', 2),
('孙七', 'hashed_password_5', 'sunqi@example.com', 2);-- 图书分类表
CREATE TABLE Category (category_id INT PRIMARY KEY AUTO_INCREMENT,category_name VARCHAR(100) NOT NULL,description TEXT
);INSERT INTO Category (category_name, description) VALUES
('小说', '小说类书籍'),
('非小说', '基于真实事实的信息书籍'),
('科幻', '未来和科学内容的书籍'),
('经典', '经典文学书籍'),
('历史', '基于历史事件的书籍');-- 作者表
CREATE TABLE Author (author_id INT PRIMARY KEY AUTO_INCREMENT,author_name VARCHAR(100) NOT NULL,bio TEXT
);INSERT INTO Author (author_name, bio) VALUES
('菲茨杰拉德', '美国小说家,代表作《了不起的盖茨比》'),
('哈珀·李', '美国小说家,著有《杀死一只知更鸟》'),
('乔治·奥威尔', '英国小说家,著有《1984》'),
('赫尔曼·梅尔维尔', '美国小说家,著有《白鲸》'),
('列夫·托尔斯泰', '俄罗斯作家,著有《战争与和平》');-- 图书表
CREATE TABLE Book (book_id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(255) NOT NULL,publisher VARCHAR(100),isbn VARCHAR(20) UNIQUE,category_id INT,author_id INT,stock INT DEFAULT 0,status ENUM('可借', '借出', '丢失', '损坏') DEFAULT '可借',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY (category_id) REFERENCES Category(category_id),FOREIGN KEY (author_id) REFERENCES Author(author_id)
);INSERT INTO Book (title, publisher, isbn, category_id, author_id, stock, status) VALUES
('了不起的盖茨比', '上海译文出版社', '9787544265477', 1, 1, 5, '可借'),
('杀死一只知更鸟', '南海出版公司', '9787544291179', 2, 2, 2, '借出'),
('1984', '人民文学出版社', '9787020115106', 3, 3, 10, '可借'),
('白鲸', '译林出版社', '9787544734804', 1, 4, 3, '损坏'),
('战争与和平', '北京燕山出版社', '9787544283792', 2, 5, 7, '可借');-- 图书标签表
CREATE TABLE Tag (tag_id INT PRIMARY KEY AUTO_INCREMENT,tag_name VARCHAR(50) NOT NULL
);INSERT INTO Tag (tag_name) VALUES
('经典'), ('戏剧'), ('反乌托邦'), ('冒险'), ('历史小说');-- 图书标签关联表
CREATE TABLE BookTags (book_id INT,tag_id INT,PRIMARY KEY (book_id, tag_id),FOREIGN KEY (book_id) REFERENCES Book(book_id),FOREIGN KEY (tag_id) REFERENCES Tag(tag_id)
);INSERT INTO BookTags (book_id, tag_id) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);-- 用户收藏表
CREATE TABLE UserFavorites (user_id INT,book_id INT,favorited_at DATETIME DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (user_id, book_id),FOREIGN KEY (user_id) REFERENCES User(user_id),FOREIGN KEY (book_id) REFERENCES Book(book_id)
);INSERT INTO UserFavorites (user_id, book_id, favorited_at) VALUES
(1, 1, '2024-06-01'),
(2, 2, '2024-06-02'),
(3, 3, '2024-06-03'),
(4, 4, '2024-06-04'),
(5, 5, '2024-06-05');-- 图书评价表
CREATE TABLE BookReview (review_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,book_id INT,rating INT,review_text TEXT,review_date DATE,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES User(user_id),FOREIGN KEY (book_id) REFERENCES Book(book_id)
);INSERT INTO BookReview (user_id, book_id, rating, review_text, review_date) VALUES
(1, 1, 5, '经典之作,令人难忘', '2024-06-01'),
(2, 2, 4, '情感丰富,发人深省', '2024-06-02'),
(3, 3, 5, '震撼而深刻', '2024-06-03'),
(4, 4, 3, '充满冒险的经典之作', '2024-06-04'),
(5, 5, 4, '引人深思', '2024-06-05');-- 借阅记录表
CREATE TABLE BorrowRecord (borrow_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,book_id INT,borrow_date DATE,due_date DATE,return_date DATE,is_overdue BOOLEAN DEFAULT FALSE,overdue_fine DECIMAL(5, 2),FOREIGN KEY (user_id) REFERENCES User(user_id),FOREIGN KEY (book_id) REFERENCES Book(book_id)
);INSERT INTO BorrowRecord (user_id, book_id, borrow_date, due_date, return_date, is_overdue, overdue_fine) VALUES
(1, 1, '2024-01-01', '2024-01-15', NULL, FALSE, NULL),
(2, 2, '2024-02-01', '2024-02-15', NULL, TRUE, 10.00),
(3, 3, '2024-03-01', '2024-03-15', '2024-03-20', TRUE, 5.00),
(4, 4, '2024-04-01', '2024-04-15', NULL, FALSE, NULL),
(5, 5, '2024-05-01', '2024-05-15', NULL, FALSE, NULL);
实体类的创建
1. Role 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "角色实体")
public class Role {@ApiModelProperty(value = "角色ID", required = true)private Integer roleId;@ApiModelProperty(value = "角色名称", required = true)private String roleName;@ApiModelProperty(value = "角色描述")private String description;
}
2. User 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;
import java.time.LocalDateTime;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "用户实体")
public class User {@ApiModelProperty(value = "用户ID", required = true)private Integer userId;@ApiModelProperty(value = "用户名", required = true)private String username;@ApiModelProperty(value = "用户密码", required = true)private String password;@ApiModelProperty(value = "联系信息")private String contactInfo;@ApiModelProperty(value = "角色ID")private Integer roleId;@ApiModelProperty(value = "创建时间")private LocalDateTime createdAt;@ApiModelProperty(value = "更新时间")private LocalDateTime updatedAt;
}
3. Category 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "图书分类实体")
public class Category {@ApiModelProperty(value = "分类ID", required = true)private Integer categoryId;@ApiModelProperty(value = "分类名称", required = true)private String categoryName;@ApiModelProperty(value = "分类描述")private String description;
}
4. Author 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "作者实体")
public class Author {@ApiModelProperty(value = "作者ID", required = true)private Integer authorId;@ApiModelProperty(value = "作者名称", required = true)private String authorName;@ApiModelProperty(value = "作者简介")private String bio;
}
5. Book 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;
import java.time.LocalDateTime;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "图书实体")
public class Book {@ApiModelProperty(value = "书籍ID", required = true)private Integer bookId;@ApiModelProperty(value = "书名", required = true)private String title;@ApiModelProperty(value = "出版社")private String publisher;@ApiModelProperty(value = "ISBN", required = true)private String isbn;@ApiModelProperty(value = "分类ID")private Integer categoryId;@ApiModelProperty(value = "作者ID")private Integer authorId;@ApiModelProperty(value = "库存", required = true)private Integer stock;@ApiModelProperty(value = "状态", allowableValues = "可借, 借出, 丢失, 损坏", required = true)private String status;@ApiModelProperty(value = "创建时间")private LocalDateTime createdAt;@ApiModelProperty(value = "更新时间")private LocalDateTime updatedAt;
}
6. Tag 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "图书标签实体")
public class Tag {@ApiModelProperty(value = "标签ID", required = true)private Integer tagId;@ApiModelProperty(value = "标签名称", required = true)private String tagName;
}
7. BookTags 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "图书与标签关联实体")
public class BookTags {@ApiModelProperty(value = "书籍ID", required = true)private Integer bookId;@ApiModelProperty(value = "标签ID", required = true)private Integer tagId;
}
8. UserFavorites 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;
import java.time.LocalDateTime;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "用户收藏实体")
public class UserFavorites {@ApiModelProperty(value = "用户ID", required = true)private Integer userId;@ApiModelProperty(value = "书籍ID", required = true)private Integer bookId;@ApiModelProperty(value = "收藏时间")private LocalDateTime favoritedAt;
}
9. BookReview 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;
import java.time.LocalDate;
import java.time.LocalDateTime;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "图书评价实体")
public class BookReview {@ApiModelProperty(value = "评价ID", required = true)private Integer reviewId;@ApiModelProperty(value = "用户ID", required = true)private Integer userId;@ApiModelProperty(value = "书籍ID", required = true)private Integer bookId;@ApiModelProperty(value = "评分", required = true)private Integer rating;@ApiModelProperty(value = "评价内容")private String reviewText;@ApiModelProperty(value = "评价日期")private LocalDate reviewDate;@ApiModelProperty(value = "创建时间")private LocalDateTime createdAt;@ApiModelProperty(value = "更新时间")private LocalDateTime updatedAt;
}
10. BorrowRecord 实体类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Builder;
import java.math.BigDecimal;
import java.time.LocalDate;@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ApiModel(description = "借阅记录实体")
public class BorrowRecord {@ApiModelProperty(value = "借阅ID", required = true)private Integer borrowId;@ApiModelProperty(value = "用户ID", required = true)private Integer userId;@ApiModelProperty(value = "书籍ID", required = true)private Integer bookId;@ApiModelProperty(value = "借阅日期")private LocalDate borrowDate;@ApiModelProperty(value = "到期日期")private LocalDate dueDate;@ApiModelProperty(value = "归还日期")private LocalDate returnDate;@ApiModelProperty(value = "是否逾期", required = true)private Boolean isOverdue;@ApiModelProperty(value = "逾期罚金")private BigDecimal overdueFine;
}
说明
@ApiModel
:用于描述类的作用,提供整体说明。@ApiModelProperty
:用于描述类属性的具体信息,包括必需性、描述等。
这些注解将帮助你在 Knife4j 中更好地可视化和理解你的实体类及其属性。确保在项目中引入相关的 Swagger 依赖,以便这些注解能够正常工作。
配置文件
spring:datasource:# druid:解除注释时记得把下面几行按 TAB 往后推进一下driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/mylibrary?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghaiusername: rootpassword: 123456
# redis:
# host: localhost
# port: 6379
# password: 123321
# database: 2
mybatis:#mapper配置文件mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.advanced.mylibrary.pojoconfiguration:#开启驼峰命名map-underscore-to-camel-case: true# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:level:com:homework:mapper: debugservice: infocontroller: info
依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com</groupId><artifactId>MyLibrary</artifactId><version>0.0.1-SNAPSHOT</version><name>MyLibrary</name><description>MyLibrary</description><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.6.13</spring-boot.version><swagger.version>3.0.3</swagger.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>${swagger.version}</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version> 5.8.16</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-redis</artifactId></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>${spring-boot.version}</version><type>pom</type><scope>import</scope></dependency></dependencies></dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>${spring-boot.version}</version><configuration><mainClass>com.mylibrary.MyLibraryApplication</mainClass><skip>true</skip></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins></build></project>