@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;
    }
}

　　@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;
    }

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);
}

　　@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;
    }

　　@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;
    }
}

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;
    }
}