自定义sql 实现分页查询

自定义sqlf分页

自定义sqlf分页

说明：基于springboot+mybatis-plus实现自定义的sql语句的分页

1. 配置分页插件
   2.在**.xml文件里面 写好自己定义的sql:
   说明： <![CDATA[**]]> 在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="tfgic.tfipm.dao.LogOrgDtoMapper">
    <select id="selectlogbyorg"  resultType="tfgic.tfipm.pojo.dto.LogOrgDto" >
        SELECT
        tb_log_visit.f_log_id AS logId,
        tb_log_visit.f_user_id AS userId,
        tb_log_visit.f_time AS time,
        tb_log_visit.f_system AS system,
        tb_log_visit.f_module AS module,
        tb_log_visit.f_function AS function,
        tb_log_visit.f_desc AS desc,
        tb_log_visit.f_data_id AS dataId,
        tb_log_visit.f_log_place AS logPlace,
        tb_log_visit.f_log_ip AS logIp,
        tb_log_visit.f_browser_version AS browserVersion,
        tb_org_info.f_iu_fullname AS iuFullname,
        tb_user_info.f_user_name AS userName
        FROM
        tb_org_info
        JOIN tb_user_info ON tb_org_info.f_org_id = tb_user_info.f_org_id
        JOIN tb_log_visit ON tb_user_info.f_user_id = tb_log_visit.f_user_id
        <where>
            <if test="iuFullname != null and iuFullname != '' ">
                 tb_org_info.f_iu_fullname = #{iuFullname,jdbcType=VARCHAR}
            </if>
            <if test="startTime != null ">
               AND <![CDATA[tb_log_visit.f_time > #{startTime,jdbcType=TIMESTAMP}]]>
            </if>
            <if test="endTime != null  ">
               AND <![CDATA[tb_log_visit.f_time <  #{endTime,jdbcType=TIMESTAMP}]]>
            </if>
        </where>
    </select>


</mapper>

2. 在**mapper的文件中 --》如下
   *小编这里是一个dto类 就类似一个entity

/**
 * Author: earlyMo
 * Email: 2529661446@qq.com
 * Date: 2019/11/27
 */

public interface LogOrgDtoMapper extends BaseMapper<LogOrgDto> {
    /**
     *
     * @param page
     * @param iuFullname
     * @param startTime
     * @param endTime
     * @return
     */
    IPage<LogOrgDto> selectlogbyorg(IPage<LogOrgDto> page, @Param("iuFullname") String iuFullname, @Param("startTime") Date startTime, @Param("endTime") Date endTime);
}

3.controller 自定义分页查询

 public ResponseResult listByOrgDepartTime(@JsonParam("page") Page<LogOrgDto> page, @JsonParam("query") LogOrgDto project, @JsonParam("sort") SortParam sort){
        IPage<LogOrgDto> mypage  =  logOrgDtoMapper.selectlogbyorg(page, project.getIuFullname(), project.getStartTime(), project.getEndTime());
        List<LogOrgDto> records = mypage.getRecords();
        return ResponseResult.success(records);
    }

就这样可以实现自定义的sql语句的分页查询；