自定义sqlf分页
自定义sqlf分页
说明:基于springboot+mybatis-plus实现自定义的sql语句的分页
- 配置分页插件
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>
- 在**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语句的分页查询;