SpringBoot+Mybatis实现分页查询（原生查询和使用PageHelper查询)_springboot+mybatis分页查询

SpringBoot+Mybatis实现分页查询（原生查询和使用PageHelper查询)

postman测试查询结果

依赖配置

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.2</version>
        </dependency>
1
2
3
4
5

数据库对应student实体类和Page类

@Data
public class student {
    private BigInteger STUDENT_ID;
    private String STUDENT_NAME;
    private String CREATE_TIME;
}
1
2
3
4
5
6

@Data
public class page {
    private Integer total;
    private List<student> stu;

    public page(Integer total, List<student> stu) {
        this.total = total;
        this.stu = stu;
    }
}
1
2
3
4
5
6
7
8
9
10

Controller,使用pagehelper和原生查询这里是相同的

@RestController
@RequestMapping("/page")
public class test {

    @Autowired
    private StuService ss;

    @GetMapping("/see")
    //传参为当前页码数page和每页所要展示的记录数pageSize
    public error see(@RequestParam Integer page, Integer pageSize){

        page stus = ss.stus(page, pageSize);
        //做判断是否查询到数据
        boolean empty = stus.getStu().isEmpty();
        return error.is(stus,empty);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

返回结果类和枚举类

@Data
public class error<T> {
    private int code;
    private String msg;
    private T data;

    public static <T> error<T> is(T object,boolean a){
        error<T> res = new error<>();
        //controller查询为空返回fail，不为空返回success
        if(a){
            res.code=result.FAIL.getCode();
            res.msg= result.FAIL.getMsg();
        }else{
            res.code=result.SUCCESS.getCode();
            res.msg= result.SUCCESS.getMsg();
            res.data=object;
        }
        return res;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

@Getter
public enum result {
    SUCCESS(200,"成功"),
    FAIL(300,"失败");

    private Integer code;
    private String msg;

    result(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

StuMapper

@Mapper
public interface StuMapper {

    //原生分页查询
//    List<student> stus(Integer page, Integer pageSize);

    //pagehelper分页查询
    List<student> stus();

    //查询总记录数
    int selectCount();
}
1
2
3
4
5
6
7
8
9
10
11
12

StuMapper.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.mapper.StuMapper">
    <!--      原生分页查询     -->
<!--    <select id="stus" resultType="com.domain.student">-->
<!--        select STUDENT_ID,STUDENT_NAME,CREATE_TIME from school_student limit #{page},#{pageSize};-->
<!--    </select>-->

<!--    pagehelper分页查询，pagehelper会自动拼接limit，所以这里查询全部记录即可，注意sql末尾不要加";",否则会报错 -->
    <select id="stus" resultType="com.domain.student">
        select STUDENT_ID,STUDENT_NAME,CREATE_TIME from school_student
    </select>


    <select id="selectCount" resultType="java.lang.Integer">
        select count(*) from school_student;
    </select>

</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Service

public interface StuService {
    page stus(Integer page, Integer pageSize);
}
1
2
3

@Service
public class StuServiceImpl implements StuService {

    @Autowired
    private StuMapper sm;

    @Override
    public page stus(Integer page, Integer pageSize) {
        //原生分页查询
//        int count=sm.selectCount();
//        Integer startIndex=(page-1)*pageSize;
//        List<student> stus = sm.stus(startIndex, pageSize);
//        page p=new page(count,stus);
//        return p;

        //pagehelper分页查询
        int count=sm.selectCount();//总记录数
        //开启分页
        PageHelper.startPage(page,pageSize);
        
        List<student> stus = sm.stus();
        
        page p=new page(count,stus);
        return p;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

整体目录结构

注意StuMapper和StuMapper.xml的目录要相同，否则可能会报错，比如StuMapper在包com.mapper目录下,那么StuMapper.xml也一定要在com/mapper/目录下来创建

扫描src/main/java目录下的所有配置文件，放在pom的build标签里

<resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.yml</include>
                    <include>**/*.ini</include>
                </includes>
            </resource>
        </resources>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16