JdbcTemplate+PageImpl实现多表分页查询

2021-08-21 13:11:47

v一、基础实体　

 View Code

v二、扩展VO

 View Code

v三、使用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;
    }
}

v四、使用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;
    }

v五、补充一种场景

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

vJpaSpecificationExecutor

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工具

vController

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

vService

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

v六、自定义规范

v　　实体类

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

v　　自定义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 还要进一步学习。

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

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

本文转自小眼儿博客园博客，原文链接：http://www.cnblogs.com/hujunzheng/p/5782659.html，如需转载请自行联系原作者

码农公寓

v一、基础实体

v二、扩展VO

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

v四、使用PageImpl进行分页

v五、补充一种场景

vJpaSpecificationExecutor

vController

vService

v六、自定义规范

v 实体类

v 自定义Specification

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

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

相关文章

v一、基础实体　

v　　实体类

v　　自定义Specification