【Spring+MyBatis】_图书管理系统（上篇）

目录

1. MyBatis与MySQL配置

1.1 创建数据库及数据表

 1.2  配置MyBatis与数据库

1.2.1 增加MyBatis与MySQL相关依赖

1.2.2 配置application.yml文件

1.3 增加数据表对应实体类

2. 功能1：用户登录

2.1 约定前后端交互接口

2.2 后端接口

2.3 前端页面

2.4 单元测试

3. 功能2：图书列表

3.1 约定前后端交互接口

3.2 后端接口

3.3 前端页面

3.4 单元测试

---

1. MyBatis与MySQL配置

1.1 创建数据库及数据表

创建数据库book_test，建议选择编码为utf8mb4；

创建用户表user_info和图书表book_info；

-- 用户表
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 ( "admin", "admin" );
INSERT INTO user_info ( user_name, PASSWORD ) VALUES ( "zhangsan", "123456" );
-- 初始化图书数据
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, '民主与建设出版社');

 1.2  配置MyBatis与数据库

1.2.1 增加MyBatis与MySQL相关依赖

1.2.2 配置application.yml文件

# 端口配置
server:port: 8080
# 数据库连接配置
spring:datasource:url: jdbc:mysql://127.0.0.1:3306/book_test?characterEncoding=utf8&useSSL=falseusername: rootpassword: xxxxxxdriver-class-name: com.mysql.cj.jdbc.Driver
mybatis:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  #配置打印MyBatis日志map-underscore-to-camel-case: true   #配置转换驼峰
# 设置日志文件的文件名
logging:file:name: /logger/spring-book.log

1.3 增加数据表对应实体类

创建model包并在其下创建BookInfo和UserInfo类：

package com.example.bookmanagementsystem.model;
import lombok.Data;
import java.math.BigDecimal;
@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-不可借阅private String statusCN; // status的中文展示
}

package com.example.bookmanagementsystem.model;
import lombok.Data;
import java.util.Date;
@Data
public class UserInfo {private Integer id;private String userName;private String password;private Integer deleteFlag;private Date createTime;private Date updateTime;
}

2. 功能1：用户登录

2.1 约定前后端交互接口

请求：/User/login

参数：userName=admin&&password=admin

响应：成功返回true，失败返回false

2.2 后端接口

UserController类：

package com.example.bookmanagementsystem.controller;import com.example.bookmanagementsystem.model.UserInfo;
import com.example.bookmanagementsystem.service.UserService;
import jakarta.servlet.http.HttpSession;
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;@RequestMapping("/User")
@RestController
public class UserController {@Autowiredprivate UserService userService;@RequestMapping("/login")public Boolean login(String userName, String password, HttpSession session){// 校验参数合法性if(!StringUtils.hasLength(userName)||!StringUtils.hasLength(password)){return false;}
//        1、根据用户名查找用户信息UserInfo userInfo =userService.getUserInfoByName(userName);//        2、校验密码正确性if(userInfo == null || userInfo.getId()<=0){return false;}if(password.equals(userInfo.getPassword())){// 存SessionuserInfo.setPassword("");session.setAttribute("userName",userInfo);return true;}return false;}
}

UserService类：

package com.example.bookmanagementsystem.service;import com.example.bookmanagementsystem.mapper.UserInfoMapper;
import com.example.bookmanagementsystem.model.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service
public class UserService {@Autowiredprivate UserInfoMapper userInfoMapper;public UserInfo getUserInfoByName(String name){return userInfoMapper.selectUserByName(name);}}

 UserMapper接口：

package com.example.bookmanagementsystem.mapper;import com.example.bookmanagementsystem.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;@Mapper
public interface UserInfoMapper {
//    根据用户名查询用户信息@Select("select* from user_info where user_name=#{name}")UserInfo selectUserByName(String name);
}

2.3 前端页面

<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>Document</title><link rel="stylesheet" href="css/bootstrap.min.css"><link rel="stylesheet" href="css/login.css"><script type="text/javascript" src="js/jquery.min.js"></script>
</head><body><div class="container-login"><div class="container-pic"><img src="pic/computer.png" width="350px"></div><div class="login-dialog"><h3>登录</h3><div class="row"><span>用户名</span><input type="text" name="userName" id="userName" class="form-control"></div><div class="row"><span>密码</span><input type="password" name="password" id="password" class="form-control"></div><div class="row"><button type="button" class="btn btn-info btn-lg" onclick="login()">登录</button></div></div></div><script src="js/jquery.min.js"></script><script>function login() {$.ajax({url:"/User/login",type:"post",data:{"userName":$("#userName").val(),"password":$("#password").val()},success:function(result){if(result){location.href = "book_list.html";}else{alert("用户名或密码错误");}}});}</script>
</body></html>

2.4 单元测试

1、后端接口测试：

可在服务器日志处查看相关信息：

2、前端页面测试：

输入账号admin和密码admin后，即可成功登录；

3. 功能2：图书列表

3.1 约定前后端交互接口

1、前端发送给后端：（封装为对象：PageRequest）

（1）当前页currentPage；

（2）每页显示的条数pageSize；

2、后端发送给前端：（封装为对象：PageResult）

（1）当前页的内容records（类型为List<BookInfo>）；

（2）总条数total；

关于SQL语句的分页查询关键字：limit与offset，常用方式如下：

1、select* from table_name limit num2,num1;

2、select* from table_name limit num1 offset num2;

以上两句都表示从num2+1行开始返回num1行数据；

3.2 后端接口

BookController类：

package com.example.bookmanagementsystem.controller;import com.example.bookmanagementsystem.model.BookInfo;
import com.example.bookmanagementsystem.model.PageRequest;
import com.example.bookmanagementsystem.model.PageResult;
import com.example.bookmanagementsystem.service.BookService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@Slf4j
@RequestMapping("/Book")
@RestController
public class BookController {@Autowiredprivate BookService bookService;@RequestMapping("/getBookListByPage")public PageResult<BookInfo> getBookListByPage(PageRequest pageRequest){log.info("接收到查询翻页信息：pageRequest:{}",pageRequest);if(pageRequest.getPageSize()<0 || pageRequest.getCurrentPage()<1) {return null;}PageResult<BookInfo> bookInfoPageResult=null;try{bookInfoPageResult = bookService.selectBookInfoByPage(pageRequest);}catch (Exception e){log.error("查询翻页信息错误：e{}",e);}return bookInfoPageResult;}
}

BookService类：

package com.example.bookmanagementsystem.service;import com.example.bookmanagementsystem.mapper.BookInfoMapper;
import com.example.bookmanagementsystem.model.BookInfo;
import com.example.bookmanagementsystem.model.PageRequest;
import com.example.bookmanagementsystem.model.PageResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;import java.util.List;
@Service
public class BookService {@Autowiredprivate BookInfoMapper bookInfoMapper;public PageResult<BookInfo> selectBookInfoByPage(PageRequest pageRequest){if(pageRequest == null){return null;}
//         获取总记录数Integer count = bookInfoMapper.count();
//         获取当前页内容List<BookInfo> bookInfos = bookInfoMapper.selectBookInfoByPage(pageRequest.getOffset(), pageRequest.getPageSize());return new PageResult<>(bookInfos,count,pageRequest);}
}

BookInfoMapper接口：

package com.example.bookmanagementsystem.mapper;import com.example.bookmanagementsystem.model.BookInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;import java.util.List;@Mapper
public interface BookInfoMapper {
//    获取当前页信息@Select("select* from book_info where status!=0 " +"order by id desc limit #{offset}, #{pageSize}")List<BookInfo> selectBookInfoByPage(@Param("offset") Integer offset, @Param("pageSize") Integer pageSize);//    获取总记录数@Select("select count(1) from book_info where status !=0")Integer count();
}

3.3 前端页面

<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>图书列表展示</title><link rel="stylesheet" href="css/bootstrap.min.css"><link rel="stylesheet" href="css/list.css"><script type="text/javascript" src="js/jquery.min.js"></script><script type="text/javascript" src="js/bootstrap.min.js"></script><script src="js/jq-paginator.js"></script></head><body><div class="bookContainer"><h2>图书列表展示</h2><div class="navbar-justify-between"><div><button class="btn btn-outline-info" type="button" onclick="location.href='book_add.html'">添加图书</button><button class="btn btn-outline-info" type="button" onclick="batchDelete()">批量删除</button></div></div><table><thead><tr><td>选择</td><td class="width100">图书ID</td><td>书名</td><td>作者</td><td>数量</td><td>定价</td><td>出版社</td><td>状态</td><td class="width200">操作</td></tr></thead><tbody></tbody></table><div class="demo"><ul id="pageContainer" class="pagination justify-content-center"></ul></div><script>getBookList();function getBookList() {$.ajax({url:"/Book/getBookListByPage"+location.search,type:"get",success:function(result){var finalHtml="";// 加载列表// 根据每一条记录拼接HTML，即一个<tr>for(var book of result.records){finalHtml+='<tr>';finalHtml+='<td><input type="checkbox" name="selectBook" value="'+book.id+'" id="selectBook" class="book-select"></td>';finalHtml+='<td>'+book.id+'</td>';finalHtml+='<td>'+book.bookName+'</td>';finalHtml+='<td>'+book.author+'</td>';finalHtml+='<td>'+book.count+'</td>';finalHtml+='<td>'+book.price+'</td>';finalHtml+='<td>'+book.publish+'</td>';finalHtml+='<td>'+book.statusCN+'</td>';finalHtml+='<td>';finalHtml+='<div class="op">';finalHtml+='<a href="book_update.html?bookId='+book.id+'">修改</a>';finalHtml+='<a href="javascript:void(0)" onclick="deleteBook('+book.id+')">删除</a>';finalHtml+='</div>';finalHtml+=' <td>';finalHtml+=' <tr>';}$("tbody").html(finalHtml)//翻页信息$("#pageContainer").jqPaginator({totalCounts: result.total, //总记录数pageSize: 10,    //每页的个数visiblePages: 5, //可视页数currentPage: result.pageRequest.currentPage,  //当前页码first: '<li class="page-item"><a class="page-link">首页</a></li>',prev: '<li class="page-item"><a class="page-link" href="javascript:void(0);">上一页<\/a><\/li>',next: '<li class="page-item"><a class="page-link" href="javascript:void(0);">下一页<\/a><\/li>',last: '<li class="page-item"><a class="page-link" href="javascript:void(0);">最后一页<\/a><\/li>',page: '<li class="page-item"><a class="page-link" href="javascript:void(0);">{{page}}<\/a><\/li>',//页面初始化和页码点击时都会执行onPageChange: function (page, type) {console.log("第"+page+"页, 类型:"+type);if(type=="change"){location.href="book_list.html?currentPage="+page;}}});}});}function deleteBook(id) {var isDelete = confirm("确认删除?");if (isDelete) {//删除图书alert("删除成功");}}function batchDelete() {var isDelete = confirm("确认批量删除?");if (isDelete) {//获取复选框的idvar ids = [];$("input:checkbox[name='selectBook']:checked").each(function () {ids.push($(this).val());});console.log(ids);alert("批量删除成功");}}</script></div>
</body></html>

3.4 单元测试

由于设有默认值，不传参数进行测试：

也可传参进行测试