图书管理系统（SpringBoot+SpringMVC+MyBatis）

最新推荐文章于 2026-03-27 13:12:50 发布

原创

最新推荐文章于 2026-03-27 13:12:50 发布 · 1.5k 阅读

标签

#mybatis #数据库

1.数据库表设计

2.引入MyBatis和MySQL驱动依赖

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;

    }
}

业务层:

最低0.47元/天解锁文章