08-SpringBoot集成MyBatis和分页插件PageHelper

0、建表以及测试数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 23);
INSERT INTO `user` VALUES (2, '李四', 24);
INSERT INTO `user` VALUES (3, '王五', 25);

SET FOREIGN_KEY_CHECKS = 1;

1、创建SpringBoot的web项目

一路next，然后finish

2、完成项目结构，最终如下

3、集成MyBatis

1、在pom.xml文件中添加依赖

       <!-- mybatis依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

        <!-- mysql依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2、在application.properties添加配置文件内容

spring.datasource.url=jdbc:mysql://localhost:3306/test01?characterEncoding=UTF-8&serverTimezone=GMT
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# mapper包地址
mybatis.type-aliases-package=com.springboot.demo.mapper
# mapping文件映射地址
mybatis.mapper-locations=classpath:mapping/*.xml

3、在entity包下创建User类

package com.springboot.demo.entity;

public class User {

    private Long id;
    private String name;
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

4、在mapper包下创建UserMapper接口

package com.springboot.demo.mapper;

import com.springboot.demo.entity.User;
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 UserMapper {

    //方式1，不使用.xml文件
    @Select("select * from user where id = #{uesid}")
    User queryUserById(@Param("uesid") Long id);

    //方式2，使用.xml文件
    User queryUserByName(@Param("username") String name);

}

注意：

方式1中两个useid取名任意，但是一定要一致

方式2中使用.xml文件，那么就在resources目录下mapping文件中创建UserMapper.xml文件(路径已经在application.xml文件中指明了)

UserMapper.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.springboot.demo.mapper.UserMapper">

    <select id="queryUserByName" parameterType="java.lang.String" resultType="com.springboot.demo.entity.User">
            select * from user where name = #{username}
    </select>

</mapper>

方式2注意事项，两个地方的usename取名可以任意，但是一定要一致

5、在controller包下创建UserController类

package com.springboot.demo.controller;

import com.springboot.demo.entity.User;
import com.springboot.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/queryUserById")
    public User queryUserById(Long id){
        return userMapper.queryUserById(id);
    }

    @GetMapping("/queryUserByName")
    public User queryUserByName(String name){
        return userMapper.queryUserByName(name);
    }

}

注意：

如果在注入UserMapper时编译报错，IDEA在编辑时提示could not autowire....，但项目能正常运行的话，可以在Idea的设置中做如下设置

6、启动项目测试

测试方式1方法

测试方式2方法

 

4、集成PageHelper分页插件(在上面的基础上)

1、在pom.xml文件新增pageHelper的依赖

        <!-- pagehelper依赖 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

2、在application.xml文件中新增以下配置

# 日志,注意路径
logging.level.com.springboot.demo.mapper=DRBUG
# 使用的字典
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
pagehelper.page-size-zero=true

3、在UserMapper接口中新增方法

 List<User> findAllUser();

4、在UserMapper.xml文件中新增查询语句

    <select id="findAllUser" resultType="com.springboot.demo.entity.User">
        select * from user
    </select>

5、在UserController中新增方法

    @GetMapping("/findAllUser")
    public Object findAllUser(Integer pageNum, Integer pageSize){
        //使用PageHelper
        //pageNum是当前页数，pageSize是每页显示的条数
        PageHelper.startPage(pageNum,pageSize);
        List<User> userList = userMapper.findAllUser();
        //将查询结果放到PageInfo中并返回
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        return pageInfo;
    }

6、测试使用PageHelper的效果