带条件的分页排序查询

一、使用原生sql语句
1、service层

        //分页排序信息，注意pageNum从0开始计数，排序字段"created_time"和数据表中字段一致
        Pageable pageable = new PageRequest(pageNum, pageSize,
                new Sort(Sort.Direction.DESC, "created_time"));
        
        //T为实体对象类
        Page<T> page = dao.findAllByPage(pageable);

2、dao层

    //可以在SQL中增加更多的条件查询
    @Query(value = "SELECT * FROM table_name ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM table_name",
            nativeQuery = true)
    Page<T> findAllByPage(Pageable pageable);

二、使用jpa
1、dao层

    //elements为查询条件，注意排序字段"createdTime"应该和T实体类中定义一致
    default Page<T> findAll(List<FilterElement> elements, int pageNum,
                                                         int pageSize) {
        PageRequest page = new PageRequest(pageNum, pageSize,
                new Sort(Sort.Direction.DESC, "createdTime"));
        return findAll(listClause(elements), page);
    }

    
    default Specification<T> listClause(List<FilterElement> elements) {
        return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            T po = new T();
            VisitorContext<T> context = new VisitorContext<>(root, cb, po.fetchJoinMap(elements, root));
            if (!CommonUtil.listIsNullOrSizeEqualZero(elements)) {
                query.where(ControllerUtil.traverseFilterElements(elements, context, po));
            }
            return query.getRestriction();
        };
    }

2、T实体类

public class T implements
        Serializable, VisitorExecutor<T> {
    private static final long serialVersionUID = 1L;

    @Transient
    private static Map<String, ExpressionVisitor> vts;

    static {
        vts = new HashMap<>();
        //name字段可以做模糊查询
        vts.put("name", new LikeVisitor());
    }

    @Id
    @GeneratedValue
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(name = "created_time")
    private Date createdTime;

    @Override
    public Map<String, ExpressionVisitor> visitors() {
        return vts;
    }

    @Override
    public Map<String, Join<?, ?>> fetchJoinMap(List<FilterElement> elements, Root<T> root) {
        return null;
    }

}

三、没有数据的Page的构造

      List<T> resultList = new ArrayList<>();
      Page<T> page = new PageImpl(resultList, pageable, 0L);