SpringBoot中如何利用JPA进行动态分页查询？

SpringBoot中如何进行动态分页查询？

一、创建一个实体类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 */
@Entity
@Data
@Table(name = "good")
@GenericGenerator(name = "jpa-uuid", strategy = "uuid")
public class Good implements Serializable {

    @Id
    @GeneratedValue(generator = "jpa-uuid")
    private String id;

    private String openId;

    private String name;

    private String sort;

    private String description;

    private BigDecimal oldPrice;

    private BigDecimal currentPrice;

    private String imageUrl;

    @Column(insertable = false)
    private Date date;

    @Column(insertable = false)
    private Integer browseNumber;

    @Column(insertable = false)
    private Integer commentNumber;

    private String avatarUrl;

    private String school;

    private String phone;

    private String qq;

    private String wechat;

    @Column(insertable = false)
    private Integer status;

    @Column(insertable = false)
    private Integer examine;

    @Column(insertable = false)
    private Integer del;

}

二、用于接收查询参数的实体类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 */
@Data
@ApiModel(value = "商品信息查询实体")
public class GoodSearchForm implements Serializable {

    @ApiModelProperty(value = "商品分类")
    private String sort;

    @ApiModelProperty(value = "学校")
    private String school;

    @ApiModelProperty(value = "搜索的内容（商品关键字或全称）")
    private String search;

}

三、用于展示数据的VO类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 */
@Data
public class GoodVO implements Serializable {

    private String id;

    private String openId;

    private String name;

    private String sort;

    private String description;

    private BigDecimal oldPrice;

    private BigDecimal currentPrice;

    private String imageUrl;

    @JsonFormat(pattern  ="yyyy-MM-dd",timezone = "Asia/Shanghai")
    private Date date;

    private Integer browseNumber;

    private Integer commentNumber;

    private String avatarUrl;

    private String school;

    private String phone;

    private String qq;

    private String wechat;

    private Integer status;

    private Integer examine;

    private Integer del;

    private String tag;

}

四、创建相应的Repository类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 */
public interface GoodRepository extends JpaRepository<Good, String> , JpaSpecificationExecutor<Good> {
}

• 要想使用SpringBoot中的动态查询方法，必须继承 JpaSpecificationExecutor 类

五、如何进行动态查询并分页？

两种方法

• SpringBoot 封装的一套方法 （仅限于单表简单查询）
• 自行拼接SQL语句（可用于多表多条件复杂查询）

1、SpringBoot 封装的一套方法（仅限于单表简单查询）

• 代码如下：

@Override
public Page<GoodVO> page(GoodSearchForm form, Pageable pageable) {
    Specification<Good> specification = (Specification<Good>) (root, criteriaQuery, criteriaBuilder) -> {
        List<Predicate> predicates =new ArrayList<>();
        if(form.getSort() != null){
            predicates.add(criteriaBuilder.equal(root.get("sort").as(String.class), form.getSort()));
        }
        if(form.getSchool() != null){
            predicates.add(criteriaBuilder.equal(root.get("school").as(String.class), form.getSchool()));
        }
        if(form.getSearch() != null){
            predicates.add(criteriaBuilder.like(root.get("name").as(String.class), "%"+form.getSearch()+"%"
        }
        predicates.add(criteriaBuilder.equal(root.get("del").as(Integer.class), 0));
        Predicate[] pre = new Predicate[predicates.size()];
        return criteriaQuery.where(predicates.toArray(pre)).getRestriction();
    };
    Page<Good> goods = goodRepository.findAll(specification, pageable);
    List<GoodVO> list = goods.stream().map(e->{
        GoodVO goodVO = new GoodVO();
        BeanUtils.copyProperties(e, goodVO);
        return goodVO;
    }).collect(Collectors.toList());
    Page<GoodVO> page = new PageImpl<>(list, goods.getPageable(), goods.getTotalElements());
    return page;
}

2、自行拼接SQL语句（可用于多表多条件复杂查询）

• 代码如下：

@Override
public Page<GoodVO> page(GoodSearchForm form, Pageable pageable) {
    String pageSql = this.getPageSql(form, pageable, false);
    List<Tuple> tuples = entityManager.createNativeQuery(pageSql, Tuple.class).getResultList();
    //processTuple(数据源, 需要映射成的类, 忽略哪些属性？); 进行映射时，属性名称一定要与数据库返回的列名保持一致
    List<GoodVO> list = NativeResultProcessUtil.processTuple(tuples, GoodVO.class, "tag");
    String countSql = getPageSql(form, null, true);
    BigInteger count = (BigInteger) entityManager.createNativeQuery(countSql).getSingleResult();
    Page<GoodVO> page = new PageImpl<>(list, pageable, count.longValue());
    return page;
}

• 其中用到的 getPageSql 方法如下：

public String getPageSql(GoodSearchForm form, Pageable pageable, Boolean isCount){
    StringBuilder selectSql = new StringBuilder();
    selectSql.append("select id,open_id as openId, name, old_price as oldPrice, " +
            "current_price as currentPrice, description, image_url as imageUrl, sort, " +
            "browse_number as browseNumber,comment_number as commentNumber," +
            "avatar_url as avatarUrl,school,phone,qq," +
            "wechat,date,status,examine,del from good");
    StringBuilder whereSql = new StringBuilder();
    whereSql.append(" where del=0");
    if(form.getSearch() != null){
        whereSql.append(" and name like '%").append(form.getSearch()).append("%'");
    }
    if(form.getSchool() != null){
        whereSql.append(" and school = '").append(form.getSchool()).append("'");
    }
    if(form.getSort() != null){
        whereSql.append(" and sort = '").append(form.getSort()).append("'");
    }
    //是否生成计算总条数的SQL
    if(isCount){
        StringBuilder countSql = new StringBuilder();
        countSql.append("select count(*) from good");
        countSql.append(whereSql);
        return countSql.toString();
    }else {
        StringBuilder result = new StringBuilder();
        result.append(selectSql).append(whereSql);
        if(pageable != null){
            StringBuilder pageSql = new StringBuilder();
            int pageNumber = pageable.getPageNumber();
            int pageSize = pageable.getPageSize();
            pageSql.append(" limit ").append(pageNumber*pageSize).append(",").append(pageSize);
            result.append(pageSql);
        }
        return result.toString();
    }
}

• 其中用到的 映射工具类 NativeResultProcessUtil

/**
 * @Author 刘路生
 * @Date 2019/12/4 14:20
 */
public class NativeResultProcessUtil {

    public static List processTuple(List<Tuple> tupleList, Class targetClass, String... ignoreProperties) {
        List voList = new ArrayList();
        if (null != tupleList && tupleList.size() > 0) {
            for (Tuple tuple : tupleList) {
                Object vo = processResult(tuple, targetClass, ignoreProperties);
                voList.add(vo);
            }
        }
        return voList;
    }

    /**
     * tuple转实体对象
     *
     * @param source      tuple对象
     * @param targetClass 目标实体class
     * @param <T>         目标实体类型
     * @return 目标实体
     */
    public static <T> T processResult(Tuple source, Class<T> targetClass) {
        Object instantiate = BeanUtils.instantiate(targetClass);
        convertTupleToBean(source, instantiate, null);
        return (T) instantiate;
    }

    /**
     * tuple转实体对象
     *
     * @param source           tuple对象
     * @param targetClass      目标实体class
     * @param <T>              目标实体类型
     * @param ignoreProperties 要忽略的属性
     * @return 目标实体
     */
    public static <T> T processResult(Tuple source, Class<T> targetClass, String... ignoreProperties) {
        Object instantiate = BeanUtils.instantiate(targetClass);
        convertTupleToBean(source, instantiate, ignoreProperties);
        return (T) instantiate;
    }

    /**
     * 把tuple中属性名相同的值复制到实体中
     *
     * @param source tuple对象
     * @param target 目标对象实例
     */
    public static void convertTupleToBean(Tuple source, Object target) {
        convertTupleToBean(source, target, null);
    }

    /**
     * 把tuple中属性名相同的值复制到实体中
     *
     * @param source           tuple对象
     * @param target           目标对象实例
     * @param ignoreProperties 要忽略的属性
     */
    public static void convertTupleToBean(Tuple source, Object target, String... ignoreProperties) {
        //目标class
        Class<?> actualEditable = target.getClass();
        //获取目标类的属性信息
        PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable);
        //忽略列表
        List<String> ignoreList = (ignoreProperties != null ? Arrays.asList(ignoreProperties) : null);

        //遍历属性节点信息
        for (PropertyDescriptor targetPd : targetPds) {
            //获取set方法
            Method writeMethod = targetPd.getWriteMethod();
            //判断字段是否可以set
            if (writeMethod != null && (ignoreList == null || !ignoreList.contains(targetPd.getName()))) {
                //获取source节点对应的属性
                String propertyName = targetPd.getName();
                Object value = source.get(propertyName);
                if (value != null && ClassUtils.isAssignable(writeMethod.getParameterTypes()[0], value.getClass())) {
                    try {
                        //判断target属性是否private
                        if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {
                            writeMethod.setAccessible(true);
                        }
                        //写入target
                        writeMethod.invoke(target, value);
                    } catch (Throwable ex) {
                        throw new FatalBeanException(
                                "Could not copy property '" + targetPd.getName() + "' from source to target", ex);
                    }
                }
            }
        }
    }
}

六、创建相应的 Controller

@ApiOperation(value = "分页获取商品", notes = "分页获取商品")
@GetMapping("/page")
public Result page(GoodSearchForm form,
                   @RequestParam(value = "page", defaultValue = "0") Integer page,
                   @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize){
    PageRequest pageable = new PageRequest(page, pageSize);
    Page<GoodVO> goodVOPage = goodService.page(form, pageable);
    TableListData tableListData = Page2TableListResult.convert2TableList(goodVOPage);
    return Result.success(tableListData);
}

• 结果：

• 其中用到的 Page2TableListResult 类 （对分页信息进行包装）

/**
 * @Author 刘路生
 * @Date 2019/12/4 14:36
 */
@Slf4j
@Data
public class Page2TableListResult {

    public static TableListData convert2TableList(Page page) {
        TableListData tableListData = new TableListData();
        TableListPagination pagination = new TableListPagination();
        if (page != null) {
            pagination.setTotal(page.getTotalElements());
            pagination.setPageSize(page.getPageable().getPageSize());
            pagination.setCurrent(page.getPageable().getPageNumber() + 1);
            tableListData.setList(page.getContent());
        }
        tableListData.setPagination(pagination);
        return tableListData;
    }
}

• 其中依赖的 tableListData 和 TableListPagination 类

/**
 * @Author 刘路生
 * @Date 2019/12/4 14:31
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TableListData<T> {

    /** 列表数据 */
    private List<T> list;

    /** 分页数据描述 */
    private TableListPagination pagination;
}

/**
 * @Author 刘路生
 * @Date 2019/12/4 14:31
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TableListPagination {

    /** 总条数 */
    private Long total;

    /** 每页条数 */
    private Integer pageSize;

    /** 当前页码 */
    private Integer current;

}

七、对响应结果进行封装的类

• Result 类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 * @Description TODO 相应结果统一封装
 * @Date: 2019/9/27 13:26
 */
@Data
public class Result<T> extends HashMap<String, Object> implements Serializable {
	
	/**
	 *  成功时候的调用
	 * */
	public static <T> Result<T> success(T data){
		return new Result<T>(data);
	}

	/**
	 *  成功时候的调用
	 * */
	public static <T> Result<T> success(){
		return new Result<T>();
	}

	@Override
	public Result put(String key, Object value) {
		super.put(key, value);
		return this;
	}

	
	/**
	 *  失败时候的调用
	 * */
	public static <T> Result<T> error(CodeMsg codeMsg){
		return new Result<T>(codeMsg);
	}

	public static Result error() {
		return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, "未知异常，请联系管理员");
	}

	public static Result unAuthorized() {
		return error(HttpStatus.SC_UNAUTHORIZED, "");
	}

	public static Result error(String msg) {
		return error(HttpStatus.SC_INTERNAL_SERVER_ERROR, msg);
	}

	public static Result error(int code, String msg) {
		return new Result(code, msg, false);
	}

	public Result (){
		put("code", 200);
		put("flag", true);
		put("msg", "success");
	}

	private Result(T data) {
		put("code", 200);
		put("msg", "success");
		put("flag", true);
		put("data", data);
	}
	
	private Result(int code, String msg) {
		put("code", code);
		put("msg", msg);
	}

	private Result(int code, String msg, Boolean flag) {
		put("code", code);
		put("msg", msg);
		put("flag", flag);
	}
	
	private Result(CodeMsg codeMsg) {
		if(codeMsg != null) {
			put("code", codeMsg.getCode());
			put("msg", codeMsg.getMsg());
			put("flag", false);
		}
	}

}

• CodeMsg 类

/**
 * @Author 刘路生
 * @Date 2019/11/28 10:23
 * @Description TODO 通用错误码
 * @Date: 2019/9/27 13:26
 */
public class CodeMsg implements Serializable {
    private int code;
	private String msg;

    /**
     * 通用的错误码
     */
	public static CodeMsg SERVER_ERROR = new CodeMsg(500100, "服务器异常");
	public static CodeMsg NOT_EXIST_RECORD = new CodeMsg(500200, "不存在此纪录");
	public static CodeMsg SQL_ERROR = new CodeMsg(500300, "SQL语句异常");
	public static CodeMsg PARAM_ERROR = new CodeMsg(500400, "参数异常");
	public static CodeMsg PARAM_EMPTY = new CodeMsg(500500, "参数为空");
	public static CodeMsg DELETE_FAIL = new CodeMsg(500600, "删除失败");
	public static CodeMsg SAVE_FAIL = new CodeMsg(500700, "保存失败");
	public static CodeMsg UPDATE_FAIL = new CodeMsg(500800, "更新失败");
	public static CodeMsg QUERY_FAIL = new CodeMsg(500900, "查询失败");
	public static CodeMsg METHOD_ARGUMENT_NOT_PRESENT = new CodeMsg(500115, "方法参数不存在：%s");
	public static CodeMsg METHOD_ARGUMENT_NOT_VALID = new CodeMsg(500104, "方法参数无效：%s");
	public static CodeMsg METHOD_ARGUMENT_TYPE_MISMATCH = new CodeMsg(500105, "方法参数类型不匹配：%s");
	public static CodeMsg BIND_ERROR = new CodeMsg(500101, "参数校验异常：%s");

	public static CodeMsg FORBIDDEN_REQUEST = new CodeMsg(403100, "权限不足：%s");

	private CodeMsg() {
	}
			
	private CodeMsg( int code,String msg ) {
		this.code = code;
		this.msg = msg;
	}
	
	public int getCode() {
		return code;
	}
	public void setCode(int code) {
		this.code = code;
	}
	public String getMsg() {
		return msg;
	}
	public void setMsg(String msg) {
		this.msg = msg;
	}
	
	public CodeMsg fillArgs(Object... args) {
		int code = this.code;
		String message = String.format(this.msg, args);
		return new CodeMsg(code, message);
	}

	@Override
	public String toString() {
		return "CodeMsg [code=" + code + ", msg=" + msg + "]";
	}
	
}