1.实体类必须继承Serialzable接口
package com.pojo;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MappedVO implements Serializable {
//mapped表
private Integer mapId; //规则ID
private Integer manId; //人员ID
private String mapType; //映射类型(1业务员2出单员)
private String mapValue; //继承业务是否变更归属团队
private String mapRealtion; //是否继承
private String db;
private String de;
private String expstr; //映射关系描述
private String exp; //代码化(映射关系描述)
private String valid; //规则状态是否有效(0无效1有效)
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date joinTime; //入库/更新时间
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date executeTime; //最近执行时间
private String checksum; //校验(-1:规则冲突;0:通过未绩效1:通过已绩效;2:待校验)
private String mapName; //规则名称
private String channel;
private String business;
private String clashMaps;
private String extendMans; //指定继承对象
//dept表
private Integer deptId; //机构ID
private String deptName; //机构名称
private String comcode; //机构代码
//manpower表
private String manProp; //人物属性(主/副)
//users表
private String username; //用户名
private String enable; //人员是否有效
private int offset;
private int size;
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
public Date getJoinTime() {
return joinTime;
}
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
public Date getExecuteTime() {
return executeTime;
}
}
2.添加maven依赖
<!-- 导入pagehelper的包-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
3.mapper接口
package com.dao;
import com.github.pagehelper.Page;
import com.pojo.Mapped;
import com.pojo.MappedVO;
import com.pojo.PageBean;
import com.pojo.UserVO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface MappedMapper {
//分页查询
Page findByPage(MappedVO mappedVO);
}
4.mapper.xml
<resultMap id="MappedVO" type="com.pojo.MappedVO">
<!-- type是类路径 property实体类属性 column数据库字段 -->
<result property="mapId" column="mapId"/>
<result property="mapType" column="mapType"/>
<result property="mapValue" column="mapValue"/>
<result property="mapRealtion" column="mapRealtion"/>
<result property="db" column="db"/>
<result property="de" column="de"/>
<result property="expstr" column="expstr"/>
<result property="exp" column="exp"/>
<result property="valid" column="valid"/>
<result property="joinTime" column="joinTime"/>
<result property="executeTime" column="executeTime"/>
<result property="checksum" column="checksum"/>
<result property="mapName" column="mapName"/>
<result property="channel" column="channel"/>
<result property="business" column="business"/>
<result property="clashMaps" column="clashMaps"/>
<result property="extendMans" column="extendMans"/>
<!-- dept机构表-->
<result property="deptId" column="deptId"/>
<result property="deptName" column="deptName"/>
<result property="comcode" column="comcode"/>
<!-- manpower表-->
<result property="manProp" column="manProp"/>
<!-- users表-->
<result property="username" column="username"/>
<result property="enable" column="enable"/>
</resultMap>
<!-- 根据Mapped查-->
<select id="findByPage" parameterType="MappedVO" resultMap="MappedVO">
select map.*, dept.deptId, dept.deptName, man.manProp, u.username, u.enable
from gd_pay_mapped as map
join gd_pay_dept as dept
join gd_pay_manpower as man
join users as u
where map.manId = man.manId and man.id = u.id and man.deptId = dept.deptId
<if test="comcode != null and comcode != '' and comcode != '350200'">
and dept.comcode like concat('%', #{comcode, jdbcType=VARCHAR}, '%')
</if>
<if test="deptName != null and deptName != '' "> and dept.deptName=#{deptName} </if>
<if test="valid != null and valid != '' "> and map.valid=#{valid} </if>
<if test="checksum != null and checksum != '' "> and map.checksum=#{checksum} </if>
<if test="db != null and db != '' and de != null and de != ''">
and map.joinTime between #{db} and #{de}
</if>
<if test="expstr != null and expstr != '' ">
and map.expstr like concat('%', #{expstr}, '%')
</if>
<if test="username != null and username != '' ">
and u.username = #{username}
</if>
</select>
5.构建PageBean类
继承Serializable接口
package com.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.security.PrivateKey;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageBean implements Serializable {
//当前页
private long total;
//当前页记录
private List<MappedVO> rows;
}
6.Service接口
package com.service;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageInfo;
import com.pojo.Mapped;
import com.pojo.MappedVO;
import com.pojo.PageBean;
import com.pojo.UserVO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface MappedService {
//分页查全部
PageBean findByPage(MappedVO mappedVO, int pageCode, int pageSize);
}
7.Service实现类
package com.service;
import com.dao.MappedMapper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.pojo.MappedVO;
import com.pojo.PageBean;
import com.pojo.UserVO;
import java.util.List;
public class MappedServiceImpl implements MappedService {
private MappedMapper mappedMapper;
public void setMappedMapper(MappedMapper mappedMapper) {
this.mappedMapper = mappedMapper;
}
@Override
public PageBean findByPage(MappedVO mappedVO, int pageCode, int pageSize) {
//使用Mybatis分页插件
//pageHelper配置页码和每页数量
PageHelper.startPage(pageCode, pageSize);
//调用分页查询方法,其实就是查询所有数据,mybatis自动帮我们进行分页计算
Page<MappedVO> page = mappedMapper.findByPage(mappedVO);
return new PageBean(page.getTotal(), page.getResult());
}
}
8.controller类
@RequestMapping(value = "/findByPage", produces = "application/json;charset=utf-8")
@ResponseBody
public PageBean findByPage(MappedVO mappedVO, @RequestParam("pageCode") int pageCode, @RequestParam("pageSize") int pageSize){
// //页面需要展示的集合
return mappedService.findByPage(mappedVO, pageCode, pageSize);
}