spring-boot - 如何在 Spring 中将可分页对象与自定义查询一起使用

Question

我正在尝试使用带有 pageble 对象的自定义查询，但我没有得到正确的输出。总之，它对我不起作用。每次我调用 Api 时，它都会给我不正确的结果。我发布了我到目前为止所做的代码

@Repository
public class kBookHistoryCustomRepository {


@PersistenceContext
private EntityManager entityManager;


public Page<KookHistoryEntity> searchkMatchedCriteria(KookSearchDto kookSearchDto,
        Pageable pageable) {


    
    List<KookHistoryEntity> kookHistoryDetailsList = new ArrayList<>();
    Page<KookHistoryEntity> page = null;
    int st = Integer.parseInt(kookSearchDto.getStoreNumber());
    LocalDate from= kookSearchDto.getFromdate();
    LocalDate to= kookSearchDto.getTodate();
    int lk_id = 0;


    String query = "SELECT CH.mar,CH.st , CH.so, CH.k_date , CH.ck_date , "
            + "CH.corr_code,CH.line_id,CH.line_description ,"
            
            + " FROM ck.k_history CH WHERE 1 = 1  ";

    if (!StringUtils.isEmpty(store)) {
        query = query + "AND    CH.st = :st ";
    }



    query = query + "AND CH.kbook_date BETWEEN  :to AND :from";

    if (!StringUtils.isEmpty(kBookSearchDto.getLk())) {

        line_id = Integer.parseInt(kBookSearchDto.getLineId());
        query = query + " AND    CH.line_id = :lk_id ";
    }

    Query sqlQuery = entityManager.createNativeQuery(query); //creating the Query here 
    sqlQuery.setParameter("st", st);
    sqlQuery.setParameter("from", from);
    sqlQuery.setParameter("to",to);
    if(lk_id>0)
    sqlQuery.setParameter("lk_id",lk_id);
    List<Object[]> objectList = sqlQuery.getResultList();

    objectList.forEach(glObject -> {
        KookHistoryEntity kookHistoryEntity = KookHistoryEntity.builder()
                .market((BigDecimal)glObject[0])
                .stNumber((BigDecimal) glObject[1])
                .so((String)glObject[2])
                .eDate((Date)glObject[3])
                .pDate((Date)glObject[4])
                .cCode((String)glObject[5])
                .....some more field
                .build();

        kookHistoryDetailsList.add(kookHistoryEntity);

    });
    page = new PageImpl<>(kookHistoryDetailsList,pageable,objectList.size()); //setting the page object here 

    return page;
}

}

我无法使用此代码进行分页，任何人都可以帮助我。

标签： spring-boothibernate

Answer 1

您必须在查询中传递分页参数并让查询将分页数据返回给应用程序。设置setFirstResult为从该偏移量返回数据并用于setMaxResults获取页面大小的数据。您还需要一个计数查询来设置 Page 对象中的 totalCount。您的代码将如下所示。

    public Page<KookHistoryEntity> searchkMatchedCriteria(KookSearchDto kookSearchDto,
                                                          Pageable pageable) {

        List<KookHistoryEntity> kookHistoryDetailsList = new ArrayList<>();
        Page<KookHistoryEntity> page = null;
        int st = Integer.parseInt(kookSearchDto.getStoreNumber());
        LocalDate from= kookSearchDto.getFromdate();
        LocalDate to= kookSearchDto.getTodate();
        int lk_id = 0;


        String whereClaus = " FROM ck.k_history CH WHERE 1 = 1  ";

        if (!StringUtils.isEmpty(store)) {
            whereClaus = whereClaus + "AND    CH.st = :st ";
        }

        whereClaus = whereClaus + "AND CH.kbook_date BETWEEN  :to AND :from";

        if (!StringUtils.isEmpty(kBookSearchDto.getLk())) {

            line_id = Integer.parseInt(kBookSearchDto.getLineId());
            whereClaus = whereClaus + " AND    CH.line_id = :lk_id ";
        }

        String query = "SELECT CH.mar,CH.st , CH.so, CH.k_date , CH.ck_date , "
                + "CH.corr_code,CH.line_id,CH.line_description ,"
                + whereClaus;

        String countQueryString = "SELECT count(*) "
                + whereClaus;

        Query sqlQuery = entityManager.createNativeQuery(query); //creating the Query here
        sqlQuery.setParameter("st", st);
        sqlQuery.setParameter("from", from);
        sqlQuery.setParameter("to",to);
        sqlQuery.setFirstResult(pageable.getPageNumber()*pageable.getPageSize());
        sqlQuery.setMaxResults(pageable.getPageSize());
        if(lk_id>0)
            sqlQuery.setParameter("lk_id",lk_id);
        List<Object[]> objectList = sqlQuery.getResultList();

        Query countQuery = entityManager.createNativeQuery(countQueryString);
        sqlQuery.setParameter("st", st);
        sqlQuery.setParameter("from", from);
        sqlQuery.setParameter("to",to);
        if(lk_id>0)
            sqlQuery.setParameter("lk_id",lk_id);
        Long count = (Long) countQuery.getSingleResult();

        objectList.forEach(glObject -> {
            KookHistoryEntity kookHistoryEntity = KookHistoryEntity.builder()
                    .market((BigDecimal)glObject[0])
                    .stNumber((BigDecimal) glObject[1])
                    .so((String)glObject[2])
                    .eDate((Date)glObject[3])
                    .pDate((Date)glObject[4])
                    .cCode((String)glObject[5])
                .....some more field
                    .build();

            kookHistoryDetailsList.add(kookHistoryEntity);

        });

        page = new PageImpl<>(kookHistoryDetailsList,pageable,count); //setting the page object here

        return page;
    }