The paging tool based on JPA supports multi table Association of native sql, and the generation of conditions only needs to be explicitly annotated on dto.

It can simplify development, improve development efficiency, and conform to the development habits of mybatis developers.

1. User defined condition generation annotation NativeQueryCondition constant class OperatorConstant

/**Annotation used to generate native sql conditions, applied to dto's fields
 * @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 "";
    // Extension type, field type requiring special handling
    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. Basic method of paging 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. Native sqlbuilder: a tool class for users to dynamically build sql conditions

/**JPA native sql Encapsulation
 * @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();

    /**
     *Conditions for building dynamic sql (rule part)
     * @param tableAliaName Table alias
     * @param dto Annotated DTO instance
     * @param <T>
     */
     public <T> NativeSqlBuilder buildConditionsStandard( String tableAliaName, T dto ){
         // Get dto instance fields with conditional generation annotation
         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));
         // Conditions for generating native sql based on fields
         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()) ){
             // The < = of the end date needs to be increased by 1 on the original basis
             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);
             }
         }

     }

    /** Build special conditions (irregular parts, single)
     *  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;
    }

    /**
     * Hump to underline
     *
     * @param line
     *            Source string
     * @return Converted String
     */
    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. Write a real paging business method (inherit BaseProfilePage)

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

    /**
     * Background management system -- business line list Pagination
     */
    @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");
        // Dynamic 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. Declaration of query conditions for DTO parameters @ NativeQueryCondition(operator = OperatorConstant.LIKE) supports fuzzy matching, which is exactly equal to. The default field name and database field name are hump to underline relationships. You can also customize the corresponding relationship. See native query condition and native sqlbuilder code for details. The tool class can be expanded by itself. Now, the tool class can meet the needs of the project temporarily.

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

    @ApiModelProperty(value = "state:0 Disabled 1 enabled")
    @NativeQueryCondition(operator = OperatorConstant.EQ)
    @EnumValidator(value = BusStatusEnum.class,message = "State optional:0 Disabled 1 enabled")
    private Integer busStatus;


}