基于my Batis优化图书管理系统

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:本文的内容就到这里结束了,如果对你有所帮助的话就请一键三连哦!!!

  • 11
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值