分享自定义的JAP列表分页查询工具

基于JPA的分页工具，支持原生sql的多表关联，条件的生成只需要在dto上声明式地注解。

可简化开发，提高开发效率，符合习惯了mybatis开发者的开发习惯。

1.自定义条件生成注解NativeQueryCondition 常量类OperatorConstant 

/**用于生成native sql条件的注解，作用于dto的字段
 * @author tlj
 * @date 2019/5/31
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface NativeQueryCondition {
    int order() default 0;
    String operator();
    String tbColumn() default "";
    String classColumn() default "";
    String tbAlias() default "";
    // 扩展类型，需要特殊处理的字段类型
    String expandType() default "";
}



/**
 * @author tlj
 * @date 2019/10/17
 */
public class OperatorConstant {
    private OperatorConstant(){}
    public static final String EQ = "=";
    public static final String GRATER_EQ = ">=";
    public static final String LESS_EQ = "<=";
    public static final String LIKE = "like";

}

2.分页的基础方法BaseProfilePage

/**
 * @author tlj
 * @date 2019/7/13
 */
@Component
public class BaseProfilePage {
    @Autowired
    protected EntityManager entityManager;

    public <T> Page<T> queryPage(String sql, Map<String, Object> params, Pageable pageable, Class<T> clazz){
        Query query = entityManager.createNativeQuery(sql);
        PageUtil.setParameters(query, params);
        Integer currentPage = pageable.getPageNumber();
        int total = query.getResultList().size();
        int pageSize = pageable.getPageSize();
        int startCurrentPage = currentPage * pageSize;
        query.setFirstResult(startCurrentPage);
        query.setMaxResults(pageSize);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> list = query.getResultList();
        List<T> data = BeanUtil.convertListMapToListT(list, clazz);
        return (Page<T>) new PageImpl(data, pageable, total);
    }
    public <T> List<T> queryList(String sql, Map<String, Object> params, Class<T> clazz){
        Query query =entityManager.createNativeQuery(sql);
        PageUtil.setParameters(query, params);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return  BeanUtil.convertListMapToListT(query.getResultList(), clazz);
    }
}

3.用户动态构建sql条件的工具类NativeSqlBuilder

/**JPA native sql的封装
 * @author tlj
 * @date 2019/5/31
 */
@Getter
@Slf4j
public class NativeSqlBuilder {
    private final StringBuilder sql = new StringBuilder();
    private final List<String> where = Lists.newArrayList();
    private final Map<String, Object> params = Maps.newHashMap();

    /**
     *构建动态sql的条件（规则部分）
     * @param tableAliaName 表别名
     * @param dto 加了注解的DTO实例
     * @param <T>
     */
     public <T> NativeSqlBuilder buildConditionsStandard( String tableAliaName, T dto ){
         // 获取dto实例加了条件生成注解的字段
         List<Field> fs =Arrays.stream(dto.getClass().getDeclaredFields()).filter(it->it.isAnnotationPresent(NativeQueryCondition.class))
                 .sorted(Comparator.comparingInt(it->it.getAnnotation(NativeQueryCondition.class).order())).collect(Collectors.toList());
         fs.forEach(it->it.setAccessible(true));
         // 根据字段生成native sql的条件
         for(Field it : fs){
             Object columnObj = null;
             try {
                 columnObj = it.get(dto);
             } catch (IllegalAccessException e) {
                 log.error("{}",e);
             }
             if( null == columnObj || (String.class.equals(columnObj.getClass()) && StringUtils.isBlank(String.valueOf(columnObj))) ){
                 continue;
             }
             NativeQueryCondition condition = it.getAnnotation(NativeQueryCondition.class);
             buildFieldCondition(tableAliaName,columnObj,condition,it.getName());
         }
         return this;
     }
     private void buildFieldCondition(String tableAliaName, Object columnObj, NativeQueryCondition condition, String fieldName ){
         String tbColumn = condition.tbColumn();
         if(StringUtils.isBlank(tbColumn)){
             tbColumn =camel2Underline( fieldName );
         }
         String classColumn = condition.classColumn();
         if(StringUtils.isBlank(classColumn)){
             classColumn = fieldName;
         }
         if( StringUtils.isNotBlank(condition.tbAlias()) ){
             tableAliaName = condition.tbAlias();
         }
         where.add( tableAliaName+"." + tbColumn + " " + condition.operator() + ":" + classColumn );
         if( LocalDate.class.equals(columnObj.getClass()) && OperatorConstant.LESS_EQ.equals(condition.operator()) ){
             // 结束日期的<= 需要在原来基础上加1
             LocalDate localDate = (LocalDate)columnObj;
             params.put( classColumn, localDate.plusDays(1));
         }else{
             if("like".equals(condition.operator())){
                 params.put( classColumn, "%" + columnObj + "%");
             }else{
                 params.put( classColumn, columnObj);
             }
         }

     }

    /** 构建特殊条件（不规则部分，单个）
     *  where.add("store.status=:status");
     *  params.put("status", BaseStatusEnum.VALID.getCode());
     * @return
     */
     public NativeSqlBuilder buildConditionSpecial(String whereItem,String paramKey,Object paramValue){
         where.add( whereItem );
         params.put(paramKey, paramValue);
         return this;
     }


    public  NativeSqlBuilder joinWhereList(){
        if (!where.isEmpty()) {
            sql.append(" where ").append(String.join(" and ", where));
        }
         return this;
    }
    public  NativeSqlBuilder orderBy(String tabAlias,String orderBy, String  xsc){
        sql.append(" order by ").append(tabAlias).append(".").append(orderBy).append(" ").append(xsc);
         return this;
    }
    public  NativeSqlBuilder orderBy(String tabAlias,String orderBy, String  xsc,Class clazz){
        sql.append(" order by ").append(tabAlias).append(".");
        if (StringUtils.isNotBlank(orderBy) && StringUtils.isNotBlank(xsc)) {
            sql.append(ColumnUtil.getColumn(clazz,orderBy)).append(" ").append(xsc);
        } else {
            sql.append("date_created").append(" ").append("desc");
        }
         return this;
    }
    public NativeSqlBuilder append(String str){
        sql.append(str);
        return this;
    }

    /**
     * 驼峰法转下划线
     *
     * @param line
     *            源字符串
     * @return 转换后的字符串
     */
    private static String camel2Underline(String line) {
        if (line == null || "".equals(line)) {
            return "";
        }
        line = String.valueOf(line.charAt(0)).toUpperCase()
                .concat(line.substring(1));
        StringBuilder sb = new StringBuilder();
        Pattern pattern = Pattern.compile("[A-Z]([a-z\\d]+)?");
        Matcher matcher = pattern.matcher(line);
        while (matcher.find()) {
            String word = matcher.group();
            sb.append(word.toUpperCase());
            sb.append(matcher.end() == line.length() ? "" : "_");
        }
        return sb.toString();
    }

    @Override
    public String toString() {
        return sql.toString();
    }
}

4.编写真正的分页业务方法（继承BaseProfilePage）

/**
 * @author tlj
 * @date 2019/6/17
 */
@Component
public class LineMainProfilePage extends BaseProfilePage{

    /**
     * 后台管理系统--业务线列表分页
     */
    @Transactional(readOnly = true)
    public Page<LinePageItemDTO> page(LinePageQueryDTO req) {
        NativeSqlBuilder nativeSqlBuilder = new NativeSqlBuilder();
        nativeSqlBuilder.append("SELECT line.id, line.`code`, line.bus_status busStatus, line.`name`, line.first_category firstCategory, line.second_category secondCategory, c1.`name` firstCategoryName, c2.`name` secondCategoryName FROM t_line_main line LEFT JOIN t_base_category c1 ON line.first_category = c1.id LEFT JOIN t_base_category c2 ON line.second_category = c2.id");
        // 动态sql
        nativeSqlBuilder.buildConditionsStandard("line",req ).joinWhereList()
                .orderBy("line",req.getOrderBy(),req.getXsc(),LineMainEntity.class);
        Page<LinePageItemDTO> page = super.queryPage(nativeSqlBuilder,req.getPage(),LinePageItemDTO.class);
        page.getContent().forEach(it->it.setBuStatusName(StepEnum.map.get(it.getBusStatus())));
        return page;
    }


}

5.对DTO参数进行查询条件的声明 @NativeQueryCondition(operator = OperatorConstant.LIKE) 支持模糊匹配，精确等于。默认字段名和数据库字段名是驼峰对下划线的关系，也可以自定义对应关系。详见NativeQueryCondition和NativeSqlBuilder的代码。工具类可自行扩展，现工具类暂时能满足项目的需求。

/**
 * @author tlj
 * @date 2019/10/17
 */
@Data
@ApiModel
public class LinePageQueryDTO extends QueryPagingDTO implements Serializable {
    private static final long serialVersionUID = -1181156497389378741L;
    @ApiModelProperty(value = "业务线名称")
    @NativeQueryCondition(operator = OperatorConstant.LIKE)
    private String name;
    @ApiModelProperty(value = "业务线代码")
    @NativeQueryCondition(operator = OperatorConstant.LIKE)
    private String code;

    @ApiModelProperty(value = "状态:0已禁用1已启用")
    @NativeQueryCondition(operator = OperatorConstant.EQ)
    @EnumValidator(value = BusStatusEnum.class,message = "状态可选值:0已禁用1已启用")
    private Integer busStatus;


}

6.其他，基础分页器DTO  QueryPagingDTO

@ApiModel
public class QueryPagingDTO implements Serializable {
    private static final long serialVersionUID = -1500626651502386429L;
    @ApiModelProperty("当前页码（ 默认从1开始，后台将自动减1）")
    private Integer currentPage = 0;
    @ApiModelProperty("每页数量 ( 默认每一页10条)")
    protected Integer pageSize = 10;
    @ApiModelProperty("排序字段")
    protected String orderBy = "id";
    @ApiModelProperty("升序 ASC / 降序 DESC")
    protected String xsc;

    @JsonIgnore
    public Pageable getPage() {
        Sort sort;
        if ("asc".equalsIgnoreCase(this.xsc)) {
            sort = new Sort(Direction.ASC, new String[]{this.orderBy});
        } else {
            sort = new Sort(Direction.DESC, new String[]{this.orderBy});
        }

        return PageRequest.of(this.currentPage, this.pageSize, sort);
    }

    public void setCurrentPage(Integer currentPage) {
        if (currentPage == 0) {
            this.currentPage = 0;
        } else {
            this.currentPage = currentPage - 1;
        }

    }

    public void setCurrentPageActual(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public QueryPagingDTO() {
    }

    public Integer getCurrentPage() {
        return this.currentPage;
    }

    public Integer getPageSize() {
        return this.pageSize;
    }

    public String getOrderBy() {
        return this.orderBy;
    }

    public String getXsc() {
        return this.xsc;
    }

    public void setPageSize(final Integer pageSize) {
        this.pageSize = pageSize;
    }

    public void setOrderBy(final String orderBy) {
        this.orderBy = orderBy;
    }

    public void setXsc(final String xsc) {
        this.xsc = xsc;
    }

    public boolean equals(final Object o) {
        if (o == this) {
            return true;
        } else if (!(o instanceof QueryPagingDTO)) {
            return false;
        } else {
            QueryPagingDTO other = (QueryPagingDTO)o;
            if (!other.canEqual(this)) {
                return false;
            } else {
                label59: {
                    Object this$currentPage = this.getCurrentPage();
                    Object other$currentPage = other.getCurrentPage();
                    if (this$currentPage == null) {
                        if (other$currentPage == null) {
                            break label59;
                        }
                    } else if (this$currentPage.equals(other$currentPage)) {
                        break label59;
                    }

                    return false;
                }

                Object this$pageSize = this.getPageSize();
                Object other$pageSize = other.getPageSize();
                if (this$pageSize == null) {
                    if (other$pageSize != null) {
                        return false;
                    }
                } else if (!this$pageSize.equals(other$pageSize)) {
                    return false;
                }

                Object this$orderBy = this.getOrderBy();
                Object other$orderBy = other.getOrderBy();
                if (this$orderBy == null) {
                    if (other$orderBy != null) {
                        return false;
                    }
                } else if (!this$orderBy.equals(other$orderBy)) {
                    return false;
                }

                Object this$xsc = this.getXsc();
                Object other$xsc = other.getXsc();
                if (this$xsc == null) {
                    if (other$xsc != null) {
                        return false;
                    }
                } else if (!this$xsc.equals(other$xsc)) {
                    return false;
                }

                return true;
            }
        }
    }

    protected boolean canEqual(final Object other) {
        return other instanceof QueryPagingDTO;
    }

    public int hashCode() {
        int PRIME = true;
        int result = 1;
        Object $currentPage = this.getCurrentPage();
        int result = result * 59 + ($currentPage == null ? 43 : $currentPage.hashCode());
        Object $pageSize = this.getPageSize();
        result = result * 59 + ($pageSize == null ? 43 : $pageSize.hashCode());
        Object $orderBy = this.getOrderBy();
        result = result * 59 + ($orderBy == null ? 43 : $orderBy.hashCode());
        Object $xsc = this.getXsc();
        result = result * 59 + ($xsc == null ? 43 : $xsc.hashCode());
        return result;
    }

    public String toString() {
        return "QueryPagingDTO(currentPage=" + this.getCurrentPage() + ", pageSize=" + this.getPageSize() + ", orderBy=" + this.getOrderBy() + ", xsc=" + this.getXsc() + ")";
    }
}