SpringBoot整合MyBatis分页组件PageHelper

介绍

SpringBoot整合MyBatis插件PageHelper实现业务分页逻辑

POM

添加MyBatis，PageHelper，FastJSON，MySQL依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis</name>
    <description>mybatis</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <!--PageHelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.13</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.69</version>
        </dependency>
    </dependencies>

</project>

Yml

需要配置pagehelper

server:
  port: 80

#mysql
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/mysql_test?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
    username: root
    password: root
  main: #允许循环依赖
    allow-circular-references: true

#mybatis
mybatis:
  mapperLocations: classpath:mapper/*.xml
  configuration: # 配置驼峰命名映射
    map-underscore-to-camel-case: true

#pageHelper
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true

Mapper

注意namespace，参数，动态SQL的编写，

<?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.mybatis.mapper.UserMapper">

    <select id="selectAll" resultType="com.example.mybatis.entity.User">
        select *
        from user
    </select>

    <select id="selectByKey" parameterType="com.example.mybatis.entity.SearchDto"
            resultType="com.example.mybatis.entity.User">
        select * from user
        where 1=1
        <if test="searchDto.username != null and searchDto.username != ''">
            and username = #{searchDto.username}
        </if>
        <if test="searchDto.birthday != null">
            and birthday = #{searchDto.birthday}
        </if>
    </select>

</mapper>

Dao

定义查询接口

package com.example.mybatis.mapper;

import com.example.mybatis.entity.SearchDto;
import com.example.mybatis.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserMapper {

    List<User> selectAll();

    List<User> selectByKey(@Param("searchDto") SearchDto searchDto);

}

Controller

使用PageHelper分页，通过PageInfo对象封装结果

package com.example.mybatis.controller;

import com.example.mybatis.config.R;
import com.example.mybatis.entity.SearchDto;
import com.example.mybatis.entity.User;
import com.example.mybatis.mapper.UserMapper;
import com.example.mybatis.util.DateUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @description: page info
 * @author: zj
 * @date: 2022-07-26 10:13
 */

@RestController
public class PageInfoController {

    @Resource
    private UserMapper userMapper;

    @PostMapping("/page")
    public String getPageData(@RequestBody SearchDto searchDto, @RequestParam Integer pageStart, @RequestParam Integer pageSize) throws ParseException {
		//核心分页代码
        PageHelper.startPage(pageStart, pageSize);

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Date birthDay = searchDto.getBirthday();

        sdf.format(birthDay);
        Date finalDate = DateUtil.getDateByString(sdf.format(birthDay));

        searchDto.setBirthday(finalDate);
		
		//查询数据
        List<User> users = userMapper.selectByKey(searchDto);
		
		//分页信息封装
        PageInfo<User> pageInfo = new PageInfo<>(users);

        return R.ok(pageInfo);
    }

}

通用返回类

返回JSON数据

package com.example.mybatis.config;

import com.alibaba.fastjson.JSON;
import java.util.HashMap;

/**
 * @description: 返回对象
 * @author: zj
 * @date: 2022-07-14 13:40
 */
public class R {

    private static JSON json;

    /**
     * 请求成功
     * @param obj
     * @return
     */
    public static String ok(Object obj) {
        HashMap<String, Object> res = new HashMap<>();
        res.put("status", "200");
        res.put("data", obj);
        return json.toJSONString(res);
    }

    /**
     * 请求失败
     * @param obj
     * @return
     */
    public static String fail(Object obj) {
        HashMap<String, Object> res = new HashMap<>();
        res.put("status", "500");
        res.put("data", obj);
        return json.toJSONString(res);
    }

}

验证

传递Params参数和Body参数，后台分别用@RequestParam和@RequestBody注解接收

Params参数

Body参数

Body参数使用JSON格式传递，日期按照yyyy-MM-dd传，后台可以解析为Date类型，但不是0时0分0秒，需要转化后才能和数据库的yyyy-MM-dd日期比较