java之分页的实现

java之同时实现分页排序模糊搜索全局搜索

五个实体类

通用返回类

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
public class PlatformResult implements Result {
	private static final long serialVersionUID = -4759913324592501472L;
	private Integer code;
	private String msg;
	private Object data;
	public static PlatformResult success(Object data) {
		PlatformResult result = new PlatformResult();
		result.setCode(200);
		result.setMsg("成功");
		result.setData(data);
		return result;
	}
}

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ApplyLog {
    private String name;
    /**
     * 主键
     */
    private Integer id;
    /**
     * 操作日期
     */
    private String operateTime;
    /**
     * 操作人
     */
    private String operator;
    /**
     * 操作，1.申请，2，同意，3，拒绝
     */
    private Integer operate;
    /**
     * 申请类型
     */
    private Integer type;
    /**
     * 内容
     */
    private String detail;
}

前端查询类

package cn.bz.backlog.dto;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Objects;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ApplyLogQuery extends  ApplyLog{
    // 前端查询条件
    /**
     * 全局查询条件
     */
    private String query;
    /**
     * 排序字段
     */
    private String orderBy;
    /**排序方式
     *
     */
    private String orderType;
    /**
     * 查询总数
     */
    private Integer count;
    /**
     * 起始页
     */
    private Integer currPage;
    /**
     * 页面大小
     */
    private Integer pageSize;
    /**
     * 起始地址
     */
    private Integer startIndex;
}

返回结果集，包括count和页码信息

public class ApplyListVo<T> {
    /**
     * 数据总量
     */
    private Integer count;
    /**
     * 当前页
     */
    private Integer currPage;
    /**
     * 页面大小
     */
    private Integer pageSize;
    /**
     * 返回数据
     */
    private List<T> list;
}

排序字段转换map

因为数据库中的字段采用下划线命名，所以将驼峰命名进行转换

@UtilityClass
public class ManagerTableMap {
    private static final HashMap<String,String> APPLY_LOG_MAP = new HashMap<>();
    static {
        APPLY_LOG_MAP.put("id","id");
        APPLY_LOG_MAP.put("operateTime","operate_time");
        APPLY_LOG_MAP.put("operator","operator");
        APPLY_LOG_MAP.put("operate","operate");
        APPLY_LOG_MAP.put("type","type");
        APPLY_LOG_MAP.put("detail","detail");
    }
    public static String getOrDefault2(String orderBy) {
        return APPLY_LOG_MAP.getOrDefault(orderBy,"id");
    }
}

dao层

mapper

package cn.bz.backlog.mapper;

import cn.bz.backlog.dto.ApplyLog;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ApplyLogMapper {
    /**
     * 插入操作日志
     * @param applyLog
     * @return
     */
    Integer insertApplyLog(ApplyLog applyLog);
    /**
     * 日志回显
     */
    List<ApplyLog> listApplyLog(@Param("applyLog") ApplyLog applyLog);
    /**
     * 列表页展示统计
     */
    Integer countListApplyLog(@Param("applyLog") ApplyLog applyLog);
}

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="cn.bz.backlog.mapper.ApplyLogMapper">

    <resultMap id="ApplyLog" type="cn.bz.backlog.dto.ApplyLog">
        <id column="id" property="id"></id>
        <result column="operate_time" property="operateTime"></result>
        <result column="operator" property="operator"></result>
        <result column="operate" property="operate"></result>
        <result column="type" property="type"></result>
        <result column="detail" property="detail"></result>
    </resultMap>
    <sql id="BaseFiled">
        operate_time,operator,operate,`type`,detail,`name`
    </sql>
    <sql id="BaseSelectFiled">
        operate_time,operator,operate,
        case `type` when 1 then '审批' when 2 then '同意' when 3 then '拒绝' end as `type`
        ,detail
    </sql>

    <insert id="insertApplyLog" useGeneratedKeys="true" keyProperty="id" >
        INSERT INTO `tb_bi_operate_log`(<include refid="BaseFiled"></include>)
        VALUE(#{operateTime},#{operator},#{operate},#{type},#{detail},#{name})
    </insert>

    <select id="listApplyLog" resultMap="ApplyLog">
        select id,<include refid="BaseSelectFiled"></include> from tb_bi_operate_log
        <include refid="BaseQuery"></include>
        <include refid="BaseOrder"></include>
        <include refid="BaseLimit"></include>
    </select>

    <select id="countListApplyLog" resultType="java.lang.Integer">
        select count(1) from tb_bi_operate_log
        <include refid="BaseQuery"></include>
    </select>

    <sql id="BaseQuery">
        <where>
                and type = 40
            <if test="applyLog.operateTime!='' and applyLog.operateTime!= null">
                and substring(operate_time,1,10) = #{applyLog.operateTime}
            </if>
            <if test="applyLog.operator !='' and applyLog.operator != null">
                and operator like concat('%',#{applyLog.operator},'%')
            </if>
            <if test="applyLog.operate!='' and applyLog.operate!=null">
                and operate like concat('%',#{applyLog.operate},'%')
            </if>
            <if test="applyLog.detail!='' and applyLog.detail!=null">
                and `detail` like concat('%',#{applyLog.detail},'%')
            </if>
            <if test="applyLog.query != null and applyLog.query != ''">
                and (
                operate_time like concat('%',#{applyLog.query},'%') or
                operator like concat('%',#{applyLog.query},'%') or
                operate like concat('%',#{applyLog.query},'%') or
                detail like concat('%',#{applyLog.query},'%')
                )
            </if>
        </where>
    </sql>
    <sql id="BaseOrder">
        order by ${applyLog.orderBy} ${applyLog.orderType}
    </sql>
    <sql id="BaseLimit">
        limit #{applyLog.startIndex},#{applyLog.pageSize}
    </sql>

</mapper>

service层

package cn.bz.backlog.service;

import cn.bz.backlog.dto.ApplyLog;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ApplyLogService {
    /**
     * 插入操作日志，返回主键
     * @param applyLog
     * @return
     */
    Integer insertApplyLog(ApplyLog applyLog);

    /**
     * 操作日志列表展示
     * @param applyLog
     * @return
     */
    List<ApplyLog> listApplyLog(ApplyLog applyLog);
    /**
     * 操作日志总数
     */
    Integer countListApplyLog(ApplyLog applyLog);
}

package cn.bz.backlog.service;

import cn.bz.backlog.dto.ApplyLog;
import cn.bz.backlog.mapper.ApplyLogMapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ApplyLogServiceImpl implements ApplyLogService{
    @Autowired
    ApplyLogMapper applyLogMapper;

    @Override
    public Integer insertApplyLog(ApplyLog applyLog) {
        applyLogMapper.insertApplyLog(applyLog);
        return applyLog.getId();
    }

    @Override
    public List<ApplyLog> listApplyLog(ApplyLog applyLog) {
        return applyLogMapper.listApplyLog(applyLog);
    }

    @Override
    public Integer countListApplyLog(ApplyLog applyLog) {
        return applyLogMapper.countListApplyLog(applyLog);
    }
}

controller层

起始页码大于总数据量直接return

    @RequestMapping(value = "listApplyLog",method = {RequestMethod.POST},produces = {"application/json;charset=UTF-8"})
    public PlatformResult listApplyLog(@RequestBody ApplyLog applyLog) {
        // 转换排序字段,设置默认排序字段为id
        applyLog.setOrderBy(Optional.ofNullable(ManagerTableMap.getOrDefault2(applyLog.getOrderBy())).orElse("id"));
        // 设置默认排序方式
        applyLog.setOrderType(Optional.ofNullable(applyLog.getOrderType()).orElse("desc"));
        // 设置默认页面大小
        applyLog.setCurrPage(Optional.ofNullable(applyLog.getCurrPage()).orElse(1));
        applyLog.setPageSize(Optional.ofNullable(applyLog.getPageSize()).orElse(10));
        Integer begin = (applyLog.getCurrPage() - 1) * applyLog.getPageSize();
        applyLog.setStartIndex(begin);
        Integer count = null;
        try {
            count = applyLogService.countListApplyLog(applyLog);
        } catch (Exception e) {
            e.printStackTrace();
            return PlatformResult.success("页码计算出错");
        }
        if (Objects.isNull(count) || begin > count) {
            return PlatformResult.failure("页码有误");
        }
        ManageVo<ApplyLog> applyLogList = new ManageVo<>();
        List<ApplyLog> applyLogs = null;
        try {
            applyLogs = applyLogService.listApplyLog(applyLog);
        } catch (Exception e) {
            e.printStackTrace();
            return PlatformResult.success("列表展示出错");
        }
        applyLogList.setList(applyLogs);
        applyLogList.setCount(count);
        applyLogList.setCurrPage(applyLog.getCurrPage());
        applyLogList.setPageSize(applyLog.getPageSize());
        return PlatformResult.success(applyLogList);
    }

噢，业务代码应该写在service层，懒得改了！！
变量的命名应该是根据query返回page