1. 数据库表设计
创建数据库 book_test,SQL如下:
-- 创建数据库
DROP DATABASE IF EXISTS book_manage;
CREATE DATABASE book_manage DEFAULT CHARACTER SET utf8mb4;
use book_manage;
-- 用户表
DROP TABLE IF EXISTS user_info;
CREATE TABLE user_info (
`id` INT NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR ( 128 ) NOT NULL,
`password` VARCHAR ( 128 ) NOT NULL,
`delete_flag` TINYINT ( 4 ) NULL DEFAULT 0,
`create_time` DATETIME DEFAULT now(),
`update_time` DATETIME DEFAULT now() ON UPDATE now(),
PRIMARY KEY ( `id` ),
UNIQUE INDEX `user_name_UNIQUE` ( `user_name` ASC )) ENGINE = INNODB DEFAULT CHARACTER
SET = utf8mb4 COMMENT = '用户表';
-- 图书表
DROP TABLE IF EXISTS book_info;
CREATE TABLE `book_info` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`book_name` VARCHAR ( 127 ) NOT NULL,
`author` VARCHAR ( 127 ) NOT NULL,
`count` INT ( 11 ) NOT NULL,
`price` DECIMAL (7,2 ) NOT NULL,
`publish` VARCHAR ( 256 ) NOT NULL,
`status` TINYINT ( 4 ) DEFAULT 1 COMMENT '0-无效, 1-正常, 2-不允许借阅',
`create_time` DATETIME DEFAULT now(),
`update_time` DATETIME DEFAULT now() ON UPDATE now(),
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
-- 初始化数据
INSERT INTO user_info ( user_name, PASSWORD ) VALUES ( "shenmengyao", "111111" );
INSERT INTO user_info ( user_name, PASSWORD ) VALUES ( "yuanyiqi", "222222" );
-- 初始化图书数据
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('十日游戏', '天猫精灵', 29, 22.00, '同人文出版社');
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('人生解答书', '群像', 5, 98.56, '老福特出版社');
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('大冤种回忆录', '杨薄宁', 9, 102.67, '胡晓慧出版社');
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('仲夏是荔枝味', '诗情画奕', 16, 178.00, '塞纳河出版社');
2. 引入驱动依赖
在pom.xml文件中,引入MyBatis 和MySQL 驱动依赖:
3. 配置数据库 & 日志
在properties.yml下添加如下内容:
server:
port: 8082
# 数据库连接配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/ book_manage?characterEncoding=utf8&useSSL=false
username: root
password: ******
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
configuration:
map-underscore-to-camel-case: true #配置驼峰自动转换
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #打印sql语句
mapper-locations: classpath:mapper/**Mapper.xml
4. Model创建
BookInfo类 实体书类:
package com.smallye.springbook.model;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
@Data
public class BookInfo {
private Integer id;
private String bookName;
private String author;
private Integer count;
private BigDecimal price;
private String publish;
private Integer status;// 映射--> 1-可借阅 2-不可借阅 0-已删除
private String stateCN;
private Date createTime;
private Date updateTime;
}
UserInfo类 用户类:
package com.example.book_manage_240827.model;
import lombok.Data;
import java.util.Date;
@Data
public class UserInfo {
private Integer id;
private String userName;
private String password;
private Integer delete_flag;
private Date createTime;
private Date updateTime;
}
PageRequest类 查询图书列表前端发来的请求类:
package com.example.book_manage_240827.model;
import lombok.Data;
@Data
public class PageRequest {
//当前页
private Integer currentPage =1;
//每页显示个数
private Integer pageSize =10;
/**
* 从多少条记录开始查询
*/
private Integer offset;
public Integer getOffset() {
return (currentPage-1) * pageSize;
}
}
PageResult类 查询图书列表返回的结果类:
package com.example.book_manage_240827.model;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@AllArgsConstructor//全参构造
@NoArgsConstructor//无参构造
@Data
public class PageResult<T> {
private List<T> records;
//当前的t是bookinfo类型
private Integer count;
private PageRequest pageRequest;
}
Result类 查询图书列表返回的结果类(对的PageResult 进行扩展):
package com.bite.book.model;
import lombok.Data;
@Data
public class Result<T> {
private Integer code;//后端响应状态, 业务状态码 200-成功, -1失败, -2表示未登录
private String errmsg;//后端发生错误的原因
private T data;
/**
* 成功时
*/
public static <T> Result<T> success(T data){
Result<T> result = new Result<T>();
result.setData(data);
result.setCode(200);
return result;
}
/**
* 失败时
*/
public static <T> Result<T> fail(T data, String errMsg){
Result<T> result = new Result<T>();
result.setData(data);
result.setCode(-1);
result.setErrmsg(errMsg);
return result;
}
public static <T> Result<T> fail(String errMsg){
Result<T> result = new Result<T>();
result.setCode(-1);
result.setErrmsg(errMsg);
return result;
}
/**
* 未登录时
*/
public static <T> Result<T> unlogin(){
Result<T> result = new Result<T>();
result.setCode(-2);
result.setErrmsg("用户未登录");
return result;
}
}
5. 枚举类 + 常量类用户登录
BookStatus枚举类-->枚举图书的状态(删除、可借阅、不可借阅):
package com.example.book_manage_240827.enums;
public enum BookStatusEnums {
DELETE(0,"无效"),
NORMAL(1,"可借阅"),
FORBIDDEN(2,"不可借阅"),
;
private int code;
private String desc;
BookStatusEnums(int code, String desc) {
this.code = code;
this.desc = desc;
}
//根据code, 获取描述
public static BookStatusEnums getDescByCode(int code){
switch (code){
case 0: return BookStatusEnums.DELETE;
case 1: return BookStatusEnums.NORMAL;
case 2: return BookStatusEnums.FORBIDDEN;
}
return BookStatusEnums.DELETE;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
}
ResultStatus枚举类-->枚举返回结果的code(SUCCESS、FAIL、NOLOGIN):
public enum ResultStatus {
SUCCESS(200),
FAIL(-1),
NOLOGIN(-2),
;
private int code;
ResultStatus(int code) {
this.code = code;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
}
Constant常量类-->session的常量值
6. 用户登录
UserController类:
package com.example.book_manage_240827.controller;
import com.example.book_manage_240827.constonts.Constants;
import com.example.book_manage_240827.model.UserInfo;
import com.example.book_manage_240827.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpSession;
@RequestMapping("/user")
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/login")
public boolean login(String userName, String password, HttpSession session) {
//账号或密码为空
if (!StringUtils.hasLength(userName) || !StringUtils.hasLength(password)) {
return false;
}
//判断数据库的密码和用户输入的密码是否一致
//查询数据库, 得到数据库的密码
UserInfo userInfo = userService.queryByName(userName);
if (userInfo == null) {
return false;
}
if (password.equals(userInfo.getPassword())) {
userInfo.setPassword("");
//密码正确
session.setAttribute(Constants.USER_SESSION_KEY, userInfo);
return true;
}
return false;
}
}
UserService类:
package com.example.book_manage_240827.service;
import com.example.book_manage_240827.mapper.UserInfoMapper;
import com.example.book_manage_240827.model.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserService {
@Autowired
private UserInfoMapper userInfoMapper;
/**
* 从数据中查询用户信息
* @return
*/
public UserInfo queryByName(String userName){
return userInfoMapper.queryByName(userName);
}
}
UserInfoMapper类:
package com.example.book_manage_240827.mapper;
import com.example.book_manage_240827.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
@Mapper
public interface UserInfoMapper {
@Select("select * from user_info where delete_flag=0 and user_name=#{userName}")
UserInfo queryByName(String userName);
}
7. 添加图书
BookController类:
public class BookController {
@Autowired
private BookService bookService;
@RequestMapping(value = "/addBook", produces = "application/json")
public Result addBook(BookInfo bookInfo) {
log.info("添加图书, bookInfo:{}", bookInfo);
//参数校验
if (!StringUtils.hasLength(bookInfo.getBookName())
|| !StringUtils.hasLength(bookInfo.getAuthor())
|| bookInfo.getCount() <= 0
|| bookInfo.getPrice() == null
|| !StringUtils.hasLength(bookInfo.getPublish())) {
// return "参数错误";
return Result.fail("参数错误");
}
//添加图书
try {
bookService.insertBook(bookInfo);
} catch (Exception e) {
log.error("添加图书失败, e:{}", e);
// return "内部发生错误, 请联系管理员";
return Result.fail("内部发生错误, 请联系管理员");
}
return Result.success("");
}
}
BookService类:
@Slf4j
@Service
public class BookService {
@Autowired
BookMapper bookMapper;
public Integer insertBook(BookInfo bookInfo) {
return bookMapper.insertBook(bookInfo);
}
}
BookMapper类:
@Mapper
public interface BookMapper {
/**
* 插入图书
*/
@Insert("insert into book_info(book_name, author, count, price, publish, `status`) " +
"values (#{bookName}, #{author}, #{count}, #{price}, #{publish}, #{status})")
Integer insertBook(BookInfo bookInfo);
}
8. 图书列表
BookController类:
@Slf4j
@RestController
@RequestMapping("/book")
public class BookController {
@Autowired
BookService bookService;
/**
* 查询图书列表
*/
@RequestMapping("/getBookListByPage")
public Result<PageResult<BookInfo>> getBookListByPage(PageRequest pageRequest, HttpSession session) {
log.info("查询图书的列表, 请求参数pageRequest: {}", pageRequest);
//从session中获取用户信息
//如果用户信息为空, 说明用户未登录
UserInfo loginUserInfo = (UserInfo) session.getAttribute(Constant.USER_SESSION_KEY);
if(loginUserInfo == null || loginUserInfo.getId() < 0) {
return Result.nologin();
}
//参数校验
if(pageRequest.getPageNum() == null) {
//返回默认第一页,如果pageSize也没设置,则会使用默认的10
pageRequest.setPageNum(1);
}
PageResult<BookInfo> bookList = bookService.getBookListByPage(pageRequest);
return Result.success(bookList);
}
}
BookService类:
@Slf4j
@Service
public class BookService {
@Autowired
BookMapper bookMapper;
public PageResult<BookInfo> getBookListByPage(PageRequest pageRequest) {
//1、获取总记录数
Integer count = bookMapper.count();
//2、获取当前页的记录
List<BookInfo> bookInfos = bookMapper.queryBookByPage(pageRequest.getOffset(), pageRequest.getPageSize());
//3、处理状态
for(BookInfo bookInfo : bookInfos) {
bookInfo.setStatusCN(BookStatus.getDescByCode(bookInfo.getStatus()).getDesc());
return new PageResult<BookInfo>(bookInfos, count, pageRequest);
}
}
BookMapper类:
@Mapper
public interface BookMapper {
/**
* 查询列表
*/
@Select("select * from book_info where status != 0 order by id desc limit #{offset}, #{limit}")
List<BookInfo> queryBookByPage(Integer offset, Integer limit);
}
9. 修改图书
BookController类:
@Slf4j
@RestController
@RequestMapping("/book")
public class BookController {
@Autowired
BookService bookService;
/**
* 更新图书
*/
@RequestMapping("/updateBook")
public String updateBook(BookInfo bookInfo) {
log.info("更新图书, bookInfo: {}", bookInfo);
try {
Integer result = bookService.updateBookById(bookInfo);
if(result > 0) {
return "";
}
return "内部错误, 请练习管理员11111";
}catch (Exception e) {
log.error("更新图书失败, e: " + e);
return "内部错误, 请练习管理员";
}
}
/**
* 查询图书信息
* @param bookId
* @return
*/
@RequestMapping("/queryBookById")
public BookInfo queryBookById(Integer bookId) {
log.info("根据ID查询图书信息, id:" + bookId);
return bookService.queryBookById(bookId);
}
}
BookService类:
@Slf4j
@Service
public class BookService {
@Autowired
BookMapper bookMapper;
public Integer updateBookById(BookInfo bookInfo) {
log.info("更新图书, bookInfo: {}", bookInfo);
return bookMapper.updateBookById(bookInfo);
}
/**
* 根据ID查询图书信息
* @param bookId
* @return
*/
public BookInfo queryBookById(Integer bookId) {
BookInfo bookInfo = bookMapper.queryBookById(bookId);
bookInfo.setStatusCN(BookStatus.getDescByCode(bookInfo.getStatus()).getDesc());
return bookInfo;
}
}
BookMapper类:
@Mapper
public interface BookMapper {
/**
* 更新图书
*/
Integer updateBookById(BookInfo bookInfo);
/**
* 查询图书信息
*/
@Select("select * from book_info where id = #{bookId}")
BookInfo queryBookById(Integer bookId);
}
更新图书的xml文件:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.book_manage_240827.mapper.BookInfoMapper">
<update id="updateBook">
update book_info
<set>
<if test="bookName!=null">
book_name = #{bookName},
</if>
<if test="author!=null">
author =#{author},
</if>
<if test="count!=null">
count = #{count},
</if>
<if test="price!=null">
price =#{price},
</if>
<if test="publish">
publish = #{publish},
</if>
<if test="status!=null">
status =#{status},
</if>
</set>
where id = #{id}
</update>
<update id="batchDelete">
update book_info
SET `status`=0
where id in
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</update>
</mapper>
10. 批量删除图书
BookController类:
@Slf4j
@RestController
@RequestMapping("/book")
public class BookController {
@Autowired
BookService bookService;
/**
* 批量删除图书
*/
@RequestMapping("/batchDeleteBook")
public String batchDeleteBook(@RequestParam List<Integer> ids) {
log.info("批量删除图书, ids: {}", ids);
try{
Integer result = bookService.batchDeleteBookById(ids);
if(result > 0) {
return "";
}
return "内部错误, 请练习管理员11111";
}catch (Exception e) {
log.error("批量删除图书失败, e: " + e);
return "内部错误, 请练习管理员";
}
}
}
BookService类:
@Slf4j
@Service
public class BookService {
@Autowired
BookMapper bookMapper;
public Integer batchDeleteBookById(List<Integer> ids) {
return bookMapper.batchDeleteBookByIds(ids);
}
}
BookMapper类:
@Mapper
public interface BookMapper {
/**
* 删除图书
*/
Integer deleteBookById(BookInfo bookInfo);
}
删除图书的xml配置文件:
<update id="batchDelete">
update book_info
SET `status`=0
where id in
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</update>
11. 强制登录
强制登录是指不让用户在没登录的情况下访问某一列表,会使用到session,如果没登录直接访问图书某一列表的url,则让用户跳转到登录界面进行强制登录,所以不用这里每一个接口都要增添一个强制登录的功能,但已经写好了代码的接口,逐一添加会耗时,这里只给 图书列表接口增添这个功能,后面学习了SpringBoot 统一功能处理再解决其他接口,添加强制登录。
图书列表也就是在Controller层进行修改,增添强制登录的功能,和上面图书列表的Controller代码一样:
@RequestMapping("/getBookListByPage")
public Result<PageResult<BookInfo>> getBookListByPage(PageRequest pageRequest, HttpSession session) {
log.info("查询图书的列表, 请求参数pageRequest: {}", pageRequest);
//从session中获取用户信息
//如果用户信息为空, 说明用户未登录
UserInfo loginUserInfo = (UserInfo) session.getAttribute(Constant.USER_SESSION_KEY);
if(loginUserInfo == null || loginUserInfo.getId() < 0) {
return Result.nologin();
}
//参数校验
if(pageRequest.getPageNum() == null) {
//返回默认第一页,如果pageSize也没设置,则会使用默认的10
pageRequest.setPageNum(1);
}
PageResult<BookInfo> bookList = bookService.getBookListByPage(pageRequest);
return Result.success(bookList);
}
12. 前端代码
12. 测试
1、登录界面
浏览器访问:http://127.0.0.1:8082/login.html;
输入错误的密码,或者不输入密码:
输入正确的账号和密码进入到图书列表页面:
2、添加图书
3、修改图书
4、删除图书
点击删除图书:
点击确定;
如图,已经成功删除;
5、批量删除图书
批量删除id为6,8的图书:
点击确定:
成功删除;
ps:本文的内容就到这里结束了,如果对你有所帮助的话就请一键三连哦!!!