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/*.xmlconfiguration:logImpl: org.apache.ibatis.logging.slf4j.Slf4jImpl3.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, descriptionfrom t_rolewhere role_id = #{id}</select><select id="getRoleInfoByMap" parameterType="hashmap" resultMap="RoleInfo">select role_id, role_name, type, state, descriptionfrom 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, descriptionfrom 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 {@Autowiredprivate 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