MP分页查询

一、MP分页插件，实现物理分页；

分页插件配置，添加MybatisPlusConfig类：

package com.mp.first.configuration;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

二、实现分页代码

第一种分页方法selectPage：生成2条sql语句

//    SELECT COUNT(1) FROM user WHERE (age >= ?)
//    SELECT id,name,age,email FROM user WHERE (age >= ?) LIMIT ?,?
    @Test
    public void selectPage(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.ge("age",10);

        Page<User> page = new Page<>(1, 2);

        Page<User> iPage = userMapper.selectPage(page, queryWrapper);

        System.out.println("总页数："+iPage.getPages());
        System.out.println("总记录数："+iPage.getTotal());
        List<User> userList = iPage.getRecords();
        userList.forEach(System.out::println);
    }

第二种分页方法selectMapsPage：生成2条sql语句

//    SELECT COUNT(1) FROM user WHERE (age >= ?)
//    SELECT id,name,age,email FROM user WHERE (age >= ?) LIMIT ?,?
    @Test
    public void selectPageMap(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>();

        queryWrapper.ge("age",10);

//        这里Page加了<User>后，下面一条语句报错
        Page page = new Page<User>(1, 2);

        IPage<Map<String,Object>> iPage = userMapper.selectMapsPage(page, queryWrapper);

        System.out.println("总页数："+iPage.getPages());
        System.out.println("总记录数："+iPage.getTotal());
        List<Map<String, Object>> userList = iPage.getRecords();
        userList.forEach(System.out::println);
    }

只需要生成一条sql语句的场景（不需要查询总记录数）

//    只生成一条sql，不查总记录数
//    SELECT id,name,age,email FROM user WHERE (age >= ?) LIMIT ?,?
    @Test
    public void selectPageMap2(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>();

        queryWrapper.ge("age",10);

    //        这里Page加了<User>后，下面一条语句报错
        Page page = new Page<User>(1, 2,false);

        IPage<Map<String,Object>> iPage = userMapper.selectMapsPage(page, queryWrapper);

        System.out.println("总页数："+iPage.getPages());
        System.out.println("总记录数："+iPage.getTotal());
        List<Map<String, Object>> userList = iPage.getRecords();
        userList.forEach(System.out::println);
    }

多表联查场景，使用自定义sql（案例只提供了单表的自定义）

• pom中添加依赖(重要)

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

• 先在dao层的UserMapper类中创建自定义方法selectUserPage

package com.mp.first.dao;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mp.first.entity.User;
import org.apache.ibatis.annotations.Param;
//import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {

//    @Select("select * from user ${ew.customSqlSegment}")
    List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper);

    IPage<User> selectUserPage(Page<User> page,@Param(Constants.WRAPPER) Wrapper<User> wrapper);

}

• 再在Mapper包中的UserMapper.xm文件中写方法的实现

<?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.mp.first.dao.UserMapper">
    <select id="selectAll" resultType="com.mp.first.entity.User">
        select * from user ${ew.customSqlSegment}
    </select>

    <select id="selectUserPage" resultType="com.mp.first.entity.User">
        select * from user ${ew.customSqlSegment}
    </select>
</mapper>

• 最后进行测试selectUserPage方法

    @Test
    public void selectMyPage(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>();

        queryWrapper.ge("age",10);

    //        这里Page加了<User>后，下面一条语句报错
        Page page = new Page<User>(1, 2,false);

        IPage<Map<String,Object>> iPage = userMapper.selectUserPage(page, queryWrapper);

        System.out.println("总页数："+iPage.getPages());
        System.out.println("总记录数："+iPage.getTotal());
        List<Map<String, Object>> userList = iPage.getRecords();
        for (int i = 0; i < userList.size(); i ++){
            System.out.println(userList.get(i));
        }

//        不能使用forEach循环，会报类型转换异常
//        for (Map<String,Object> hash : userList) {
//            System.out.println(hash.get("id"));
//            System.out.println(hash.get("name"));
//            System.out.println(hash.get("age"));
//            System.out.println(hash.get("email"));
//        }
    }