How to get Page<Object[]> like stuff in Spring Data Jpa when using native query

Question

I am stuck at a point. I have a query that I am using like the following

@Query(value="SELECT bha.activity_code_id, ac.activity_code_full_name, pt.plan_name " + 
        " FROM bp_bid_history_activity bha, activity_code ac, plan_template pt where rownum < 201", nativeQuery = true)
List<Object[]> getContractReceivedAddedActivityCodes(@Param("bidId")Long bidId, @Param("historyId")Long historyId, Pageable pageable);

// Trying to achieve this
@Query(value="SELECT bha.activity_code_id, ac.activity_code_full_name, pt.plan_name " + 
        " FROM bp_bid_history_activity bha, activity_code ac, plan_template pt where rownum < 201", nativeQuery = true)
Page<Object[]> getContractReceivedAddedActivityCodesPageable(@Param("bidId")Long bidId, @Param("historyId")Long historyId, Pageable pageable);

Actually to see the results I limit the query to 200 results. Becasue the actual query was not returning the results. In actual query there is proper where clause. But anyways.

Here is how I am using it

//@Test
public void getContractReceivedAddedActivityCodes() {

    Long bidId = 178820L;
    Long historyId = 783520L;

    Pageable pageable = PageRequest.of(0, 10, Sort.by("activity_code_id"));

    List<Object[]> addedActivitiesList  = bpBidActivityRepository.getContractReceivedAddedActivityCodes(bidId, historyId, pageable);
    List<ContractReceivedActivityCodesModel> addedActivityCodes =  getContractReceivedActivityCodesModel(addedActivitiesList);
    printList(addedActivityCodes);

}

private List<ContractReceivedActivityCodesModel> getContractReceivedActivityCodesModel(List<Object[]> activitiesList) {

    List<ContractReceivedActivityCodesModel> activityCodes = null;
    if (CollectionUtils.isNotEmpty(activitiesList)) {
        activityCodes = activitiesList.stream()
                .map(ContractReceivedActivityCodesModel::new).collect(Collectors.toList()); 
    }
    return activityCodes;
}

public class ContractReceivedActivityCodesModel {
    private Long activeCodeId;
    private String activityCodeFullName;
    private String planName;

    public ContractReceivedActivityCodesModel(Object[] object) {
        if (ArrayUtils.isNotEmpty(object)) {
            BigDecimal activeCodeId = (BigDecimal) object[0];
            if (activeCodeId != null) {
                this.activeCodeId = activeCodeId.longValue();
            }

            this.activityCodeFullName = (String) object[1];
            this.planName = (String) object[2];
        }
    }
}

The query is returning 200 results. Now the problem is when the List<Object[]> query runs then I am just getting 10 results. Because I am passing 10 page size. But I also want to get total records size. So I can show the pagination on the UI that showing 10 of 200 records, showing 30 of 200 records.

When we use Page<Person>, or Page<T> return type then we also get the total count. How can I get the total count in this case. Or I want to get some thing like Page<Object[]>. I tried things like Page<List<Object[]>>, Page<Object[]> but getting error.

Thanks

Answer 1

You have to provide a countQuery

@Query(value="SELECT bha.activity_code_id, ac.activity_code_full_name, pt.plan_name " + 
        " FROM bp_bid_history_activity bha, activity_code ac, plan_template pt where rownum < 201", 
countQuery="SELECT count(*) " + 
        " FROM bp_bid_history_activity bha, activity_code ac, plan_template pt where rownum < 201"
nativeQuery = true)
Page<Object[]> getContractReceivedAddedActivityCodesPageable(@Param("bidId")Long bidId, @Param("historyId")Long historyId, Pageable pageable);