1.数据库表设计
数据库表是应⽤程序开发中的⼀个重要环节, 数据库表的设计往往会决定我们的应⽤需求是否能顺利实现, 甚至决定我们的实现方式. 如何设计表以及这些表有哪些字段、关系也是非常重要的.
数据库表设计是依据业务需求来设计的,数据库表通常分两种: 实体表和关系表.
创建数据库 book_test
-
-- 创建数据库
-
DROP DATABASE IF EXISTS book_test;
-
CREATE DATABASE book_test DEFAULT CHARACTER SET utf8mb4;
-
USE book_test;
-
-- ⽤户表
-
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 CHARSET = 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 ('活着 ','Romised',100,12.2,'出版社');
-
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('不活着', 'Romised', 100, 22.2, '出版社');
-
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('想活着 ','Romised',100,32.2,'出版社');
-
INSERT INTO `book_info` (book_name,author,count, price, publish) VALUES ('要活着 ','Romised',100,42.2,'出版社');
2.引入MyBatis和MySQL驱动依赖
修改pom.xml文件:
<dependency>
-
<groupId>org.mybatis.spring.boot</groupId>
-
<artifactId>mybatis-spring-boot-starter</artifactId>
-
<version>2.3.1</version>
-
</dependency>
-
<dependency>
-
<groupId>com.mysql</groupId>
-
<artifactId>mysql-connector-j</artifactId>
-
<scope>runtime</scope>
-
</dependency>
3.配置数据库&日志
修改application.yml配置文件:
-
# 数据库连接配置
-
spring:
-
datasource:
-
url: jdbc:mysql://127.0.0.1:3306/book_test?characterEncoding=utf8&useSSL=false
-
username: root
-
password: 123456
-
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
-
# 设置日志文件的文件名
-
logging:
-
file:
-
name: logger/spring-book.log
4.Model创建
创建UserInfo类:
-
@Data
-
public class UserInfo {
-
private Integer id;
-
private String userName;
-
private String password;
-
private Integer deleteFlag;
-
private Date createTime;
-
private Date updateTime;
-
}
创建BookInfo类:
-
@Data
-
public class BookInfo {
-
private Integer id;
-
private String bookName;
-
private String author;
-
private Integer count;
-
//前端展示精度
-
@JsonFormat(shape = JsonFormat.Shape.STRING)
-
private BigDecimal price;
-
private String publish;
-
private Integer status; //0-删除 1-可借阅,2-不可借阅
-
private String statusCN;
-
}
5.用户登录功能实现
约定前后端交互接口:
-
[请求]
-
/user/login
-
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
-
[参数]
-
name=zhangsan&password=123456 8
-
[响应]
-
true //账号密码验证正确 , 否则返回false
浏览器给服务器发送/user/login这样的HTTP请求,服务器给浏览器返回Boolean类型的数据
实现服务器代码
控制层:从数据库中, 根据名称查询用户, 如果可以查到, 并且密码⼀致, 就认为登录成功
创建UserController:
-
@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;
-
}
-
//验证账号密码是否正确
-
//1. 根据用户名去查找用户信息
-
UserInfo userInfo = userService.getUserInfoByName(userName);
-
//2. 比对密码是否正确
-
if (userInfo==null || userInfo.getId()<=0){
-
return false;
-
}
-
if (password.equals(userInfo.getPassword())){
-
//账号密码正确
-
//存Session
-
userInfo.setPassword("");
-
session.setAttribute(Constants.SESSION_USER_KEY,userInfo);
-
return true;
-
}
-
return false;
-
}
-
}
业务层:
创建UserService:
-
@Service
-
public class UserService {
-
@Autowired
-
private UserInfoMapper userInfoMapper;
-
public UserInfo getUserInfoByName(String name){
-
return userInfoMapper.selectUserByName(name);
-
}
-
}
数据层:
创建UserInfoMapper:
-
@Mapper
-
public interface UserInfoMapper {
-
/**
-
* 根据用户名称查询用户信息
-
* @param name
-
* @return
-
*/
-
@Select("select * from user_info where user_name=#{name}")
-
UserInfo selectUserByName(String name);
-
}
这边使用*是为了方便观察,开发中需要挨个写出数据库字段名
测试:
部署程序,验证服务器是否能够正确返回数据,可以在Postman中输入URL进行测试,最好联动前端一起进行测试:
输⼊错误的⽤户名和密码, 页面弹窗警告
输入正确的用户名和密码, 页面正常跳转到booklist.html页面
6.实现添加图书功能
约定前后端交互接口:
-
[请求]
-
/book/addBook
-
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
-
[参数]
-
bookName=图书1&author=作者1&count=23&price=36&publish=出版社1&status=1
-
[响应]
-
"" //失败信息 , 成功时返回空字符串
我们约定,浏览器给服务器发送book/addBook这样的HTTP请求,以from表单的形式提交数据
服务器返回处理结果, 返回""表示添加图书成功, 否则, 返回失败信息.
实现服务器代码
控制层:
创建BookController:
-
@Slf4j
-
@RequestMapping("/book")
-
@RestController
-
public class BookController {
-
@Autowired
-
private BookService bookService;
-
@RequestMapping("/getBookListByPage")
-
public Result getBookListByPage(PageRequest pageRequest, HttpSession session){
-
log.info("查询翻页信息, pageRequest:{}",pageRequest);
-
//用户登录校验
-
UserInfo userInfo = (UserInfo) session.getAttribute(Constants.SESSION_USER_KEY);
-
if (userInfo==null|| userInfo.getId()<=0 || "".equals(userInfo.getUserName())){
-
//用户未登录
-
return Result.unlogin();
-
}
-
//校验成功
-
if (pageRequest.getPageSize()<0 || pageRequest.getCurrentPage()<1){
-
return Result.fail("参数校验失败");
-
}
-
PageResult<BookInfo> bookInfoPageResult = null;
-
try {
-
bookInfoPageResult = bookService.selectBookInfoByPage(pageRequest);
-
return Result.success(bookInfoPageResult);
-
}catch (Exception e){
-
log.error("查询翻页信息错误,e:{}",e);
-
return Result.fail(e.getMessage());
-
}
-
}
-
@RequestMapping(value = "/addBook", produces = "application/json")
-
public String 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 "参数校验失败, 请检查入参";
-
}
-
Integer result = bookService.addBook(bookInfo);
-
if (result<=0){
-
log.error("添加图书出错:bookInfo:{}",bookInfo);
-
return "添加图书出错, 请联系管理人";
-
}
-
return "";
-
}
-
}
业务层:
创建BookService:
-
@Slf4j
-
@Service
-
public class BookService {
-
@Autowired
-
private BookInfoMapper bookInfoMapper;
-
/**
-
* 添加图书
-
*
-
* @param bookInfo
-
* @return
-
*/
-
public Integer addBook(BookInfo bookInfo) {
-
Integer result = 0;
-
try {
-
result = bookInfoMapper.insertBook(bookInfo);
-
} catch (Exception e) {
-
log.error("添加图书出错, e:{}", e);
-
}
-
return result;
-
}
-
}
数据层:
创建BookInfoMapper文件:
-
@Mapper
-
public interface BookInfoMapper {
-
/**
-
* 获取当前页的信息
-
* @param offset
-
* @param pageSize
-
* @return
-
*/
-
@Select("select * from book_info where status !=0 " +
-
"order by id asc limit #{offset},#{pageSize}")
-
List<BookInfo> selectBookInfoByPage(Integer offset, Integer pageSize);
-
@Insert("insert into book_info (book_name,author, count, price, publish, status) " +
-
"values(#{bookName}, #{author}, #{count}, #{price},#{publish}, #{status})")
-
Integer insertBook(BookInfo bookInfo);
-
}
前端代码中补全add():
-
function add() {
-
$.ajax({
-
type:"post",
-
url: "/book/addBook",
-
data:$("#addBook").serialize(),//提交整个form表单
-
success:function(result){
-
if (result != null && result.code == "SUCCESS" && result.data=="") {
-
//图书添加成功
-
location.href = "book_list.html";
-
}else{
-
console.log(result.code);
-
alert(result);
-
}
-
},
-
error: function (error) {
-
console.log(error);
-
//用户未登录
-
if (error != null && error.status == 401) {
-
location.href = "login.html";
-
}
-
}
-
});
-
}
7.实现翻页功能
假设数据库中的数据有很多,一下子全部展示出来肯定不现实,我们可以使用分页来解决这个问题
分页时, 数据是如何展示的呢 第1页: 显⽰1-10 条的数据、第2页: 显⽰11-20 条的数据 第3页: 显⽰21-30 条的数据 以此类推...
要想实现这个功能, 从数据库中进行分页查询 ,我们要使用LIMIT关键字
查询第一页的SQL语句:
SELECT * FROM book_info LIMIT 0,10
查询第二页的SQL语句:
SELECT * FROM book_info LIMIT 10,10
查询第三页的SQL语句:
SELECT * FROM book_info LIMIT 20,10
观察以上SQL语句 ,发现: 开始索引⼀直在改变, 每页显⽰条数是固定的 开始索引的计算公式: 开始索引 = (当前页码 - 1) * 每页显示条数
前端在发起查询请求时 ,需要向服务端传递的参数 。
currentPage 当前页码,默认值为1
pageSize 每页显示条数,默认值为10
为了项⽬更好的扩展性, 通常不设置固定值,而是以参数的形式来进行传递 扩展性: 软件系统具备面对未来需求变化而进行扩展的能⼒
比如当前需求⼀页显示10条, 后期需求改为⼀页显示20条, 后端代码不需要任何修改
后端响应时, 需要响应给前端的数据
records 所查询到的数据列表(存储到List 集合中)
total 总记录数 (用于告诉前端显示多少页, 显示页数为:(total+pageSize-1)/pageSize
显示页数totalPage 计算公式为 : total % pagesize == 0 ? total / pagesize : (total / pagesize)+1 ;
pagesize - 1 是 total / pageSize 的最⼤的余数 ,所以(total + pagesize -1) / pagesize就得到总页数
翻页请求和响应部分, 我们通常封装在两个对象中
翻页请求对象:
-
@Data
-
public class PageRequest {
-
private int currentPage = 1; // 当前页
-
private int pageSize = 10; // 每页中的记录数
-
private int offset;
-
public int getOffset() {
-
return (currentPage-1) * pageSize;
-
}
-
}
翻页列表结果类:
-
import lombok.Data;
-
import java.util.List;
-
@Data
-
public class PageResult<T> {
-
private int total;//所有记录数
-
private List<T> records; // 当前页数据
-
public PageResult(Integer total, List<T> records) {
-
this.total = total;
-
this.records = records;
-
}
-
}
返回结果中, 使⽤泛型来定义记录的类型
约定前后端交互接口
-
[请求]
-
/book/getListByPage?currentPage=1
-
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
-
[参数]
-
[响应]
-
Content-Type: application/json 10
-
{
-
"total": 25,
-
"records": [{
-
"id": 25,
-
"bookName": "图书21",
-
"author": "作者2",
-
"count": 29,
-
"price": 22.00,
-
"publish": "出版社1",
-
"status": 1,
-
"statusCN": "可借阅 "
-
}, {
-
......
-
} ]
-
}
我们约定,浏览器给服务器发送book/getListByPage这样的HTTP请求,通过currentPage参数告诉服务器当前请求为第几页的数据, 后端根据请求参数, 返回对应页的数据
实现服务器代码
控制层:
完善 BookController:
-
@Slf4j
-
@RequestMapping("/book")
-
@RestController
-
public class BookController {
-
@Autowired
-
private BookService bookService;
-
@RequestMapping("/getBookListByPage")
-
public Result getBookListByPage(PageRequest pageRequest, HttpSession session){
-
log.info("查询翻页信息, pageRequest:{}",pageRequest);
-
// //用户登录校验
-
// UserInfo userInfo = (UserInfo) session.getAttribute(Constants.SESSION_USER_KEY);
-
// if (userInfo==null|| userInfo.getId()<=0 || "".equals(userInfo.getUserName())){
-
// //用户未登录
-
// return Result.unlogin();
-
// }
-
//校验成功
-
if (pageRequest.getPageSize()<0 || pageRequest.getCurrentPage()<1){
-
return Result.fail("参数校验失败");
-
}
-
PageResult<BookInfo> bookInfoPageResult = null;
-
try {
-
bookInfoPageResult = bookService.selectBookInfoByPage(pageRequest);
-
return Result.success(bookInfoPageResult);
-
}catch (Exception e){
-
log.error("查询翻页信息错误,e:{}",e);
-
return Result.fail(e.getMessage());
-
}
-
}
-
@RequestMapping(value = "/addBook", produces = "application/json")
-
public String 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 "参数校验失败, 请检查入参";
-
}
-
Integer result = bookService.addBook(bookInfo);
-
if (result<=0){
-
log.error("添加图书出错:bookInfo:{}",bookInfo);
-
return "添加图书出错, 请联系管理人";
-
}
-
return "";
-
}
-
@RequestMapping("/queryBookInfoById")
-
public BookInfo queryBookInfoById(Integer bookId){
-
// long start = System.currentTimeMillis();
-
log.info("根据ID查询图书, bookId:"+bookId);
-
BookInfo bookInfo = null;
-
try {
-
bookInfo = bookService.queryBookInfoById(bookId);
-
}catch (Exception e){
-
log.error("查询图书失败, e:{}",e);
-
}
-
// long end = System.currentTimeMillis();
-
// log.info("queryBookInfoById 执行耗时: "+ (end-start) + "ms");
-
return bookInfo;
-
}
-
@RequestMapping(value = "/updateBook", produces = "application/json")
-
public String updateBook(BookInfo bookInfo){
-
log.info("接收到更新图书的请求, bookInfo:{}",bookInfo);
-
Integer result = bookService.updateBook(bookInfo);
-
if (result == 0){
-
log.error("更新图书失败, 请联系管理员");
-
return "更新图书失败, 请联系管理员";
-
}
-
return "";
-
}
-
@RequestMapping(value = "/batchDelete", produces = "application/json")
-
public String batchDelete(@RequestParam List<Integer> ids){
-
log.info("接收请求, 批量删除图书, 图书ID:{}",ids);
-
Integer result = bookService.batchDelete(ids);
-
if (result<=0){
-
log.error("批量删除失败, ids:{}",ids);
-
return "批量删除失败, 请联系管理员";
-
}
-
return "";
-
}
-
}
业务层:
BookService
-
@Slf4j
-
@Service
-
public class BookService {
-
@Autowired
-
private 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());
-
if (bookInfos != null && bookInfos.size() > 0) {
-
for (BookInfo bookInfo : bookInfos) {
-
//根据status 获取状态的定义
-
bookInfo.setStatusCN(BookStatusEnum.getNameByCode(bookInfo.getStatus()).getName());
-
}
-
}
-
return new PageResult<>(bookInfos, count, pageRequest);
-
}
-
/**
-
* 添加图书
-
*
-
* @param bookInfo
-
* @return
-
*/
-
public Integer addBook(BookInfo bookInfo) {
-
Integer result = 0;
-
try {
-
result = bookInfoMapper.insertBook(bookInfo);
-
} catch (Exception e) {
-
log.error("添加图书出错, e:{}", e);
-
}
-
return result;
-
}
-
public BookInfo queryBookInfoById(Integer id) {
-
return bookInfoMapper.queryBookInfoById(id);
-
}
-
/**
-
* 更新图书
-
* @param bookInfo
-
* @return
-
*/
-
public Integer updateBook(BookInfo bookInfo) {
-
Integer result = 0;
-
try {
-
result = bookInfoMapper.updateBook(bookInfo);
-
} catch (Exception e) {
-
log.error("更新图书失败, e:{}", e);
-
}
-
return result;
-
}
-
public Integer batchDelete(List<Integer> ids){
-
Integer result =0;
-
try {
-
result = bookInfoMapper.batchDelete(ids);
-
}catch (Exception e){
-
log.error("批量删除图书失败, ids:{}",ids);
-
}
-
return result;
-
}
-
}
翻页信息需要返回数据的总数和列表信息, 需要查两次SQL
图书状态: 图书状态和数据库存储的status有⼀定的对应关系
如果后续状态码有变动, 我们需要修改项目中所有涉及的代码, 这种情况, 通常采用枚举类来处理映射关系
数据层:
翻页查询SQL
-
@Mapper
-
public interface BookInfoMapper {
-
/**
-
* 获取当前页的信息
-
* @param offset
-
* @param pageSize
-
* @return
-
*/
-
@Select("select * from book_info where status !=0 " +
-
"order by id asc limit #{offset},#{pageSize}")
-
List<BookInfo> selectBookInfoByPage(Integer offset, Integer pageSize);
-
/**
-
* 获取总记录数
-
* @return
-
*/
-
@Select("select count(1) from book_info where status !=0")
-
Integer count();
-
}
实现客户端代码:
-
function getBookList() {
-
$.ajax({
-
type: "get",
-
url: "/book/getBookListByPage" + location.search,
-
success: function (result) {
-
//真实的前端处理逻辑, 要比咱们代码复杂
-
if (result.code == "UNLOGIN") {
-
location.href = "login.html";
-
return;
-
}
-
var finalHtml = "";
-
//加载列表
-
var pageResult = result.data;
-
for (var book of pageResult.records) {
-
//根据每一条记录去拼接html, 也就是一个tr
-
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><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></td></tr>';
-
}
-
$("tbody").html(finalHtml);
-
//翻页信息
-
$("#pageContainer").jqPaginator({
-
totalCounts: pageResult.total, //总记录数
-
pageSize: 10, //每页的个数
-
visiblePages: 5, //可视页数
-
currentPage: pageResult.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;
-
}
-
}
-
});
-
},
-
error: function (error) {
-
console.log(error);
-
if (error.status == 401) {
-
console.log("401");
-
location.href = "login.html";
-
}
-
}
-
});
-
}