Java：采用mybatis+pagehealper优雅的实现分页功能

       在实现分页功能时，通常的做法是提供一个查总记录数据的SQL，再提供一个带分页参数的SQL，如果系统需要，可能还需要提供一个查全量记录的SQL。这种实现方式实在是繁琐，理想的实现方式是只提供一个查全量的SQL，分页功能自己能根据这这个基础的SQL生成查总记录数据的SQL和分页参数的SQL。pagehelper组件就能实现这样的功能，但是它需要结合mybatis一起使用。下面就实现一个简单的例子。

1.maven配置pom.xml

<dependency>
    <groupId>org.mybatis.spring.boot<groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.3.2</version>
</dependency>

<dependency>
    <groupId>com.github.pagehelper<groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.7</version>
</dependency>

2.spring配置application.yaml

mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    logImpl: org.apache.ibatis.logging.slf4j.Slf4jImpl

3.mybatis配置roleInfo.mapper.xml

<mapper namespace="org.test.core.dao.RoleDao">
    <resultMap id="RoleInfo" type="org.test.core.model.RoleInfo">
        <result property="id" column="role_id" />
        <result property="name" column="role_name" />
        <result property="type" column="type" />
        <result property="state" column="state" />
        <result property="desc" column="description" />
    </resultMap>

    <select id="getRoleInfoById" parameterType="string" resultMap="RoleInfo">
        select role_id, role_name, type, state, description
        from t_role
        where role_id = #{id}
    </select>

    <select id="getRoleInfoByMap" parameterType="hashmap" resultMap="RoleInfo">
        select role_id, role_name, type, state, description
        from t_role
        <where>
            <if test="id != null and id != ''">
                and role_id = #{id}
            </if>
            <if test="name != null and name != ''">
                and role_name = #{name}
            </if>
            <if test="state != null and state != ''">
                and state = #{state}
            </if>
    </select>

    <select id="getRoleInfoByObj" parameterType="org.test.core.model.RoleInfo" resultMap="RoleInfo">
        select role_id, role_name, type, state, description
        from t_role
        <where>
            <if test="id != null and id != ''">
                and role_id = #{id}
            </if>
            <if test="name != null and name != ''">
                and role_name = #{name}
            </if>
            <if test="state != null and state != ''">
                and state = #{state}
            </if>
    </select>
</mapper>

4.java代码

(1)RoleDao.java

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotation.Param;

public interface RoleDao {

    RoleInfo getRoleInfoById (@Param(value = "id") String roleId);

    List<RoleInfo> getRoleInfoByMap (Map<String, Object> map);

    List<RoleInfo> getRoleInfoByObj (RoleInfo roleInfo);

}

(2)PagingKit

import java.util.List;
import java.util.Map;
import java.util.function.Function;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

public class PagingKit {

    public static <T> PageInfo<T> makePageInfoUseMap(Map<String, Object> map, int pageNum, int pageSize, Function<Map<String, Object>, List<T>> query) {
        PageHelper.startPage(pageNum, pageSize);

        List<T> list = query.apply(map);

        PageInfo<T> pageInfo = new PageInfo<T>(list);

        return pageInfo;
    }

    public static <T> PageInfo<T> makePageInfoUseObj(T condition, int pageNum, int pageSize, Function<T, List<T>> query) {
        PageHelper.startPage(pageNum, pageSize);

        List<T> list = query.apply(condition);

        PageInfo<T> pageInfo = new PageInfo<T>(list);

        return pageInfo;
    }

}

(3)HeathController.java

import java.util.HashMap;
import java.util.Map;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.github.pagehelper.PageInfo;

@Component
@Path("/sys")
public class HealthController {

    @Autowired
    private RoleDao roleDao;

    @GET
    @Path("/role/{id}")
    @Produces({"application/json"})
    public RoleInfo getRoleById (@PathParam("id") String id) {
        return roleDao.getRoleInfoById(id);
    }

    @GET
    @Path("/role/byMap")
    @Produces({"application/json"})
    public RoleInfo pagingRoleByMap (@queryParam("pageNum") int pageNum, @queryParam("pageSize") int pageSize) {
        Map<String, Object> map = new HashMap<>();
        map.put("state", "0");
        map.put("name", "管理员");

        PageInfo<RoleInfo> pageInfo = PagingKit.makePageInfoUseMap(map, pageNum, pageSize, roleDao::getRoleInfoByMap); 

        return pageInfo;
    }

    @GET
    @Path("/role/byObj")
    @Produces({"application/json"})
    public RoleInfo pagingRoleByObj (@queryParam("pageNum") int pageNum, @queryParam("pageSize") int pageSize) {
        RoleInfo  role = new RoleInfo();
        role.setState("0");
        role.setName("管理员");

        PageInfo<RoleInfo> pageInfo = PagingKit.makePageInfoUseObj(role, pageNum, pageSize, roleDao::getRoleInfoByObj); 

        return pageInfo;
    }

}

5.执行如果

可以查看日志

getRoleInfoByMap_COUNT|==>Preparing: select count(0) from (select role_id,role_name,type,state,description from t_role where role_name like "%"?"%" and state = ? ) tmp_count

getRoleInfoByMap_COUNT|==>Parameters: 管理员(String), 0(String)

getRoleInfoByMap_COUNT|==>   Total: 1

getRoleInfoByMap==>Preparing: select role_id,role_name,type,state,description from t_role where role_name like "%"?"%" and state = ? LIMIT ?,?

getRoleInfoByMap|==>Parameters: 管理员(String), 0(String), 3(Long),3(Integer)

getRoleInfoByMap|==>   Total: 1