JdbcTemplate+PageImpl实现多表分页查询

一、基础实体　

@MappedSuperclass
public abstract class AbsIdEntity implements Serializable {

    private static final long serialVersionUID = 7988377299341530426L;

    public final static int IS_DELETE_YES = 1;// 标记删除
    public final static int IS_DELETE_NO = 0;// 未删除,保留的

    @Id
    @GenericGenerator(name="uuid", strategy="uuid")
    @GeneratedValue(generator="uuid")
    protected String id;

    @Column(name = "creationtime")
    protected Timestamp creationTimestamp = new Timestamp(System.currentTimeMillis());

    @Column(name = "lastmodifiedtime")
    protected Timestamp modificationTimestamp;
    
    @Column(name = "dr")
    protected int dr;// 是否删除。0:未删除;1:已删除

    /**
     * 主键，对应id字段
     */
    public String getId() { return id; }
    public void setId(String id) { this.id = id; }

    /**
     * 创建日期，对应ts_insert字段
     */
    public Timestamp getCreationTimestamp() { return creationTimestamp; }
    public void setCreationTimestamp(Timestamp creationTimestamp) { this.creationTimestamp = creationTimestamp; }

    /**
     * 修改日期，对应ts_update字段
     */
    public Timestamp getModificationTimestamp() { return modificationTimestamp; }
    public void setModificationTimestamp(Timestamp modificationTimestamp) { this.modificationTimestamp = modificationTimestamp; }

    /**
     * 是否删除，对应dr字段
     * @return
     */
    public int getDr() {
        return dr;
    }
    public void setDr(int dr) {
        this.dr = dr;
    }

}

二、扩展VO

import java.io.Serializable;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import xxx.entity.AbsIdEntity;

public class StaffUnionVO extends AbsIdEntity implements Serializable, RowMapper<StaffUnionVO>{
    /**
     * 
     */
    private static final long serialVersionUID = -5213225066818183479L;
    
    private String code;// 员工编码

    private String name;// 员工姓名

    private int sex; // 性别

    private Date birthday;// 出生年月日

    private String deptId;// 人员所属部门
    
    private String companyId;//人员所属公司表

    private EnumStatus status;// 员工状态，在职，离职

    private String nativePlace;// 籍贯

    private String education;// 学历

    private EnumPoliticalStatus politicalStatus;// 政治面貌

    private String major;// 所学专业

    private String school;// 毕业学校

    private Date graduationTime;// 毕业时间

    private Date workTime;// 参加工作时间

    private String credentialCode;// 证件号码

    private String mobile;// 移动电话

    private String signPic;// 签名图片
    
    private String landlineNum;// 座机号
    
    private String age;// 年龄
    
    private String post;// 职位  

    private String positionId;// 岗位Id
    
    private String dept;// 部门
    
    private String email;// 邮箱
    
    private String  userId;// 用户关联人员
    
    private int  isUser;// 是否关联人员
    
    private int credentialType;//证件类型
    
    private String address;//地址
    
    private String staffTypeId;//人员id
    
    private String property;//直属或隶属
    
    public String getProperty() {
        return property;
    }

    public void setProperty(String property) {
        this.property = property;
    }

    public String getPositionId() {
        return positionId;
    }

    public void setPositionId(String positionId) {
        this.positionId = positionId;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getStaffTypeId() {
        return staffTypeId;
    }

    public void setStaffTypeId(String staffTypeId) {
        this.staffTypeId = staffTypeId;
    }

    public int getCredentialType() {
        return credentialType;
    }

    public void setCredentialType(int credentialType) {
        this.credentialType = credentialType;
    }

    public int getIsUser() {
        return isUser;
    }

    public void setIsUser(int isUser) {
        this.isUser = isUser;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getLandlineNum() {
        return landlineNum;
    }

    public void setLandlineNum(String landlineNum) {
        this.landlineNum = landlineNum;
    }


    public String getDeptId() {
        return deptId;
    }

    public void setDeptId(String deptId) {
        this.deptId = deptId;
    }

    public String getCompanyId() {
        return companyId;
    }

    public void setCompanyId(String companyId) {
        this.companyId = companyId;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getPost() {
        return post;
    }

    public void setPost(String post) {
        this.post = post;
    }

    public String getPosition() {
        return positionId;
    }

    public void setPosition(String position) {
        this.positionId = position;
    }

    public String getDept() {
        return dept;
    }

    public void setDept(String dept) {
        this.dept = dept;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getBirthday() {
        return birthday;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }


    public EnumStatus getStatus() {
        return status;
    }

    public void setStatus(EnumStatus status) {
        this.status = status;
    }

    public String getNativePlace() {
        return nativePlace;
    }

    public void setNativePlace(String nativePlace) {
        this.nativePlace = nativePlace;
    }

    public String getEducation() {
        return education;
    }

    public void setEducation(String education) {
        this.education = education;
    }

    public EnumPoliticalStatus getPoliticalStatus() {
        return politicalStatus;
    }

    public void setPoliticalStatus(EnumPoliticalStatus politicalStatus) {
        this.politicalStatus = politicalStatus;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public String getSchool() {
        return school;
    }

    public void setSchool(String school) {
        this.school = school;
    }

    public Date getGraduationTime() {
        return graduationTime;
    }

    public void setGraduationTime(Date graduationTime) {
        this.graduationTime = graduationTime;
    }

    public Date getWorkTime() {
        return workTime;
    }

    public void setWorkTime(Date workTime) {
        this.workTime = workTime;
    }

    public String getCredentialCode() {
        return credentialCode;
    }

    public void setCredentialCode(String credentialCode) {
        this.credentialCode = credentialCode;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getSignPic() {
        return signPic;
    }

    public void setSignPic(String signPic) {
        this.signPic = signPic;
    }

    @Override
    public StaffUnionVO mapRow(ResultSet rs, int row) throws SQLException {
        StaffUnionVO vo = new StaffUnionVO();
        vo.setAddress(rs.getString("address"));
        vo.setAge(rs.getString("age"));
        vo.setBirthday(rs.getDate("birthday"));
        vo.setCode(rs.getString("code"));
        vo.setCompanyId(rs.getString("companyId"));
        vo.setCreationTimestamp(rs.getTimestamp("creationtime"));
        vo.setCredentialCode(rs.getString("credentialCode"));
        vo.setCredentialType(rs.getInt("credentialType"));
        vo.setDept(rs.getString("dept"));
        vo.setDeptId(rs.getString("deptId"));
        vo.setDr(rs.getInt("dr"));
        vo.setEducation(rs.getString("education"));
        vo.setEmail(rs.getString("email"));
        vo.setGraduationTime(rs.getDate("graduationTime"));
        vo.setId(rs.getString("id"));
        vo.setIsUser(rs.getInt("isUser"));
        vo.setLandlineNum(rs.getString("landlineNum"));
        vo.setMajor(rs.getString("major"));
        vo.setMobile(rs.getString("mobile"));
        vo.setModificationTimestamp(rs.getTimestamp("lastmodifiedtime"));
        vo.setName(rs.getString("name"));
        vo.setNativePlace(rs.getString("nativePlace"));
        vo.setPoliticalStatus(rs.getString("politicalStatus") == null || "".equals(rs.getString("politicalStatus").trim()) ? null : EnumPoliticalStatus.valueOf(rs.getString("politicalStatus")));
        vo.setPositionId(rs.getString("positionId"));
        vo.setPost(rs.getString("post"));
        vo.setProperty(rs.getString("property"));
        vo.setSchool(rs.getString("school"));
        vo.setSex(rs.getInt("sex"));
        vo.setSignPic(rs.getString("signPic"));
        vo.setStaffTypeId(rs.getString("staffTypeId"));
        vo.setStatus(rs.getString("status") == null || "".equals(rs.getString("status").trim()) ? null : EnumStatus.valueOf(rs.getString("status")));
        vo.setUserId(rs.getString("userId"));
        vo.setWorkTime(rs.getDate("workTime"));
        
        return vo;
    }
}

三、使用JdbcTemplate 进行联表查询操作

@Repository
public class StaffUnionDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<StaffUnionVO> getStaffUnion(String deptId, String param) {
　　　　 //sql中联表查询，自定义列名
        String sql = "select bs.id id, bs.creationtime creationtime, bs.lastmodifiedtime lastmodifiedtime, bs.dr dr, bs.code code, bs.name name, bs.sex sex, bs.birthday birthday, bsp.dept_id as deptId, bsp.company_id companyId,"
                   + "bs.status status, bs.native_place nativePlace, bs.education education, bs.political_status politicalStatus, bs.major major, bs.school school, bs.graduation_time graduationTime, bs.work_time workTime,"
                   + "bs.credential_code credentialCode, bs.mobilephone_num mobile, bs.sign_pic signPic, bs.landline_num landlineNum, bs.age age, bs.post post, bsp.position positionId, bs.dept dept, bs.is_user isUser,"
                   + "bs.email email, bs.userid userId, bs.credential_type credentialType, bs.address address, bs.staff_type_id staffTypeId, bsp.property property from bd_staff bs, bd_staff_partjob bsp where bs.id = bsp.staff_id and bsp.dr = 0";
        
        //String sqlx = "select bs.id id from bd_staff bs, bd_staff_partjob bsp where bs.id = bsp.staff_id and bsp.dr = 0";
        
        if(deptId != null) {
            sql += "and bsp.dept_id = " + deptId;
        }
        if(param != null) {
            sql += " and (bs.code like %" + param + "% or bs.name like %" + param + "%)";
        }
　　　　　(1)
    //该方法无法实现类中变量是对象时的转换，例如存在枚举对象。
        //List<StaffUnionVO> result = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(StaffUnionVO.class));
        (2)
//        可以自定义转化
//        @SuppressWarnings("unchecked")
//        List<StaffUnionVO> result = jdbcTemplate.query(sqlx,  new RowMapper() {  
//              @Override  
//              public Object mapRow(ResultSet rs, int n)  throws SQLException {  
//                  StaffUnionVO vo = new StaffUnionVO();
//                  vo.setId(rs.getString("id"));
//                  return vo;  
//              }
//        });
       (3)
    //自定义映射方式的实现写在了 StaffUnionVO类中
        List<StaffUnionVO> result = jdbcTemplate.query(sql, new StaffUnionVO());
        
        return result;
    }
}

四、使用PageImpl进行分页

　　@Autowired
    private StaffUnionDao newPageDao;
    
    @RequestMapping(path = "newpage", method = RequestMethod.GET)
    @ResponseBody
    public PageImpl<StaffUnionVO> getStaffInfoNew(@RequestParam(value = "deptId", required = false) String deptId,
            @RequestParam(value = "searchText", required = false) String param,
            @RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {
        List<StaffUnionVO> content = newPageDao.getStaffUnion(deptId, param);
        PageRequest pageRequest = new PageRequest(pageNumber - 1, pageSize);
        PageImpl<StaffUnionVO> ans = new PageImpl<StaffUnionVO>(content, pageRequest, content == null ? 0 : content.size());
        return ans;
    }

 

五、补充一种场景

　　进行分页查询的时候一般多是针对一个实体（对应一个表），通常使用的spring data jpa 的接口 JpaSpecificationExecutor<T>, 接口内容如下。

JpaSpecificationExecutor

public interface JpaSpecificationExecutor<T> {

    /**
     * Returns a single entity matching the given {@link Specification}.
     * 
     * @param spec
     * @return
     */
    T findOne(Specification<T> spec);

    /**
     * Returns all entities matching the given {@link Specification}.
     * 
     * @param spec
     * @return
     */
    List<T> findAll(Specification<T> spec);

    /**
     * Returns a {@link Page} of entities matching the given {@link Specification}.
     * 
     * @param spec
     * @param pageable
     * @return
     */
    Page<T> findAll(Specification<T> spec, Pageable pageable);

    /**
     * Returns all entities matching the given {@link Specification} and {@link Sort}.
     * 
     * @param spec
     * @param sort
     * @return
     */
    List<T> findAll(Specification<T> spec, Sort sort);

    /**
     * Returns the number of instances that the given {@link Specification} will return.
     * 
     * @param spec the {@link Specification} to count instances for
     * @return the number of instances
     */
    long count(Specification<T> spec);
}

　　分页查询的结果是实体类的集合，通常在服务层完成。但是我们一般不让业务层直接接触我们的实体类，所以就是产生了VO类，也就是服务层不再返回实体类的集合，而是返回VO类的集合，业务层操作的其实是VO类。这样就显得层次清晰，方便控制。例如分页查询，具体实现如下。

　　请参考：　后台json格式设计 PagableResponse（分页json格式）  ，生成查询Specification  QueryTool工具

 

Controller

　　@Autowired
    private RoleService roleService;
    
    @RequestMapping(path = "page")
    @ResponseBody
    public PagableResponse<RoleVO> getPageableRole(@RequestParam(value = "roleId", required = false) String roleId,
            @RequestParam(value = "searchText", required = false) String param,
            @RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {
        PagableResponse<RoleVO> response = new PagableResponse<RoleVO>();
        response.setPageNumber(pageNumber);
        response.setPageSize(pageSize);
        PageRequest pageRequest = new PageRequest(pageNumber, pageSize);
        try {
            List<RoleVO> data = roleService.findPageableRoles(param, roleId, pageRequest);
            long count = roleService.count(param, roleId);
            response.setList(data);
            response.setCount(count);
            response.setCode(ReturnCode.SUCCESS);
            response.setMsg("获取角色信息成功！");
        } catch(Exception e) {
            e.printStackTrace();
            response.setCode(ReturnCode.FAILURE);
            response.setMsg("获取角色信息失败！"); 
        }
        return response;
    }

Service

　　@Override
    public List<RoleVO> findPageableRoles(String param, String relyCondition, PageRequest pageRequest) {
        List<RoleVO> list = new ArrayList<RoleVO>();
        Map<String, Object> map = QueryTool.parseCondition(relyCondition); 
        //添加搜索添加
        if(StringUtils.isNotBlank(param)){
            map.put(Operator.LIKE+"_roleName", param);
            map.put(Operator.LIKE+"_roleCode", relyCondition);
        }
        if(StringUtils.isNotBlank(relyCondition)) {
            map.put(Operator.LIKE+"_id", relyCondition);
        }
        map.put(Operator.EQ+"_dr", "0");
        Specification<RoleEntity> spec = QueryTool.buildSpecification(map, RoleEntity.class);
        Page<RoleEntity> page = roleDao.findAll(spec, pageRequest);
        for (RoleEntity entity : page) {
            RoleVO roleVO = new RoleVO();
            BeanUtils.copyProperties(entity, roleVO);
            list.add(roleVO);
        }
        return list;
    }

六、自定义规范

　　实体类

@Entity
@Table(name="sm_role")
public class RoleEntity extends AbsIdEntity implements Serializable{

    private static final long serialVersionUID = 3717832885014711347L;
    
    @Column(name="role_name")
    private String roleName;
    
    @Column(name="role_code")
    private String roleCode;
    
    @Column(name="role_type")
    private String roleType;
    
    @Column(name="isactive")
    private String isactive;
    
    @Column(name="tenant_id")
    private String tenantId;
    
    @Column(name="dtype")
    private String dtype;
    
    @Column(name="remark")
    private String remark;
    
    @Column(name="creator")
    private String creator;
    
    @Column(name="reviser")
    private String reviser;
    
    @Column(name="role_property")
    private String roleProperty;
    
    @Column(name="related_post_id")
    private String relatedPostId;
    
    @Column(name = "instruction")
    private String instruction;

    public String getInstruction() {
        return instruction;
    }

    public void setInstruction(String instruction) {
        this.instruction = instruction;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleType() {
        return roleType;
    }

    public void setRoleType(String roleType) {
        this.roleType = roleType;
    }

    public String getIsactive() {
        return isactive;
    }

    public void setIsactive(String isactive) {
        this.isactive = isactive;
    }

    public String getTenantId() {
        return tenantId;
    }

    public void setTenantId(String tenantId) {
        this.tenantId = tenantId;
    }

    public String getDtype() {
        return dtype;
    }

    public void setDtype(String dtype) {
        this.dtype = dtype;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getCreator() {
        return creator;
    }

    public void setCreator(String creator) {
        this.creator = creator;
    }

    public String getReviser() {
        return reviser;
    }

    public void setReviser(String reviser) {
        this.reviser = reviser;
    }

    public String getRoleProperty() {
        return roleProperty;
    }

    public void setRoleProperty(String roleProperty) {
        this.roleProperty = roleProperty;
    }

    public String getRelatedPostId() {
        return relatedPostId;
    }

    public void setRelatedPostId(String relatedPostId) {
        this.relatedPostId = relatedPostId;
    }
}

　　自定义Specification

public class RoleSpecification implements Specification<RoleEntity> {
    private Integer delete = new Integer(0);// 默认查询未删除的数据
    private String searchParam;
    private String roleId;

    public RoleSpecification(String searchParam, String roleId) {
        super();
        this.searchParam = searchParam;
        this.roleId = roleId;
    }

    @Override
    public Predicate toPredicate(Root<RoleEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

        List<Predicate> list = new ArrayList<Predicate>();
        list.add(cb.equal(root.get("dr").as(Integer.class), delete));
        if (StringUtils.isNotBlank(searchParam)) {
            list.add(
                    cb.or(
                            cb.like(root.get("roleName").as(String.class), cb.literal("%" + searchParam + "%")),
                            cb.like(root.get("roleCode").as(String.class), cb.literal("%" + searchParam + "%")),
                            cb.like(root.get("roleType").as(String.class), cb.literal("%" + searchParam + "%"))
                    )
            );
        }
        //角色id
        if(roleId != null && !("".equals(roleId.trim()))) {
            list.add(cb.equal(root.get("roleId").as(String.class), roleId));
        }

        Predicate[] predicates = new Predicate[list.size()];
        return cb.and(list.toArray(predicates));
    }
    
    public Integer getDelete() {
        return delete;
    }

    public void setDelete(Integer delete) {
        this.delete = delete;
    }

    public String getSearchParam() {
        return searchParam;
    }

    public void setSearchParam(String searchParam) {
        this.searchParam = searchParam;
    }

    public String getRoleId() {
        return roleId;
    }

    public void setRoleId(String roleId) {
        this.roleId = roleId;
    }
}

　　注：规范中操作的 字段名称 都是 实体类的字段名称，不要写成数据库字段名称。

　　自定义规范可以自定义条件查询，比如 or， and等等，使用QueryTool工具建立的规范默认条件都是and，所以很多情况下不能满足我们的需求。总的来说，现在刚刚接触，Spring Data JPA 的 Specification 还要进一步学习。

七、其他通过Specification实现分页查询，请参考

    springmvc+jpa实现分页的两种方式