CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
Peter Penzov
Peter Penzov

Reputation: 1754

Return page object from JPA query

I want to create JPA with pagination. I tried to implement this:

@Override
    public Page<PaymentTransactions> findAll(Specification<PaymentTransactions> spec, Pageable pageable) {

        int pageNumber = pageable.getPageNumber();
        int pageSize = pageable.getPageSize();      

        String hql = "select e from " + PaymentTransactions.class.getName() + " e LIMIT :limit OFFSET :offset";
        TypedQuery<PaymentTransactions> query = entityManager.createQuery(hql, PaymentTransactions.class).setParameter("limit", pageSize).setParameter("offset", pageNumber);
        Page<PaymentTransactions> paymentTransactions = (Page<PaymentTransactions>) query.getResultList();
        return paymentTransactions;
    }

How I can return Page Object without using casting?

EDIT I also tried this:

End point:

@Autowired
private PaymentTransactionRepository transactionRepository;

@GetMapping
public Page<PaymentTransactionsDTO> page(@PathVariable int page, @PathVariable int size) {
        return transactionRepository
                .findAll(page, size)
                .map(mapper::toDTO);
    }

Repository:

public interface PaymentTransactionRepository extends CrudRepository<PaymentTransactions, Integer>, JpaSpecificationExecutor<PaymentTransactions> {

    @Query(nativeQuery=true, 
            value="SELECT * FROM payment_transactions \n-- #pageable\n",
            countQuery="SELECT count(*) FROM payment_transactions")
    Page<PaymentTransactions> findAll(Pageable page);
}

EDIT 2. I also tried this:

@GetMapping("page")
    public Page<PaymentTransactionsDTO> page(@PathVariable int page, @PathVariable int size) {
        PageRequest pageRequest = PageRequest.of(page, size);
        return transactionRepository.findAll(pageRequest).map(mapper::toDTO);
    }

Interface:

public interface PaymentTransactionRepository extends CrudRepository<PaymentTransactions, Integer>, JpaSpecificationExecutor<PaymentTransactions> {

    Page<PaymentTransactions> findAll(Pageable page);
}

Implementation:

@Override
    public Page<PaymentTransactions> findAll(Pageable page) {
        int pageNumber = page.getPageNumber();
        int pageSize = page.getPageSize();

        String hql = "select e from " + PaymentTransactions.class.getName() + " e LIMIT :limit OFFSET :offset";
        TypedQuery<PaymentTransactions> query = entityManager.createQuery(hql, PaymentTransactions.class)
                .setParameter("limit", pageSize).setParameter("offset", pageNumber);
        Page<PaymentTransactions> paymentTransactions = (Page<PaymentTransactions>) query.getResultList();
        return paymentTransactions;
    }

Upvotes: 6

Views: 14223

Answers (3)

ssp
ssp

Reputation: 314

I have done with 2 SQL queries. May be it will useful.

  1. Create SQL for list objects and add page parameters:

    query.setMaxResults(pageable.getPageSize());
query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());

  2. Create Same SQL with wrapped Select count(*)

    countQuery.getSingleResult();

And then make it for paga object

Page<ViewContract> page = (Page<ViewContract>) new PageImpl(
              selectQueryResultsList, 
              pageable, 
              countQueryResult);

Upvotes: 8

Vivek Kurmi
Vivek Kurmi

Reputation: 158

To get the functionality of Paging and Sorting we have to extend either PagingAndSortingRepository or JpaRepository interfaces.

In your case we can achieve the same by below code:

End Point:

@Autowired
private PaymentTransactionRepository transactionRepository;

@GetMapping
public Page<PaymentTransactionsDTO> page(@PathVariable int page, @PathVariable int size) {
    PageRequest pageRequest = PageRequest.of(page, size);
    return transactionRepository
            .findAll(pageRequest)
            .map(mapper::toDTO);
}

Repository:

public interface PaymentTransactionRepository extends JpaRepository<PaymentTransactions, Integer> {
    Page<PaymentTransactions> findAll(Pageable page);
}

Please try with this, it should work.

Upvotes: 2

Muhammad Asher Toqeer
Muhammad Asher Toqeer

Reputation: 61

If you are using Sepcification API properly then you don't need to do any thing. Spring data JPA would do it for you. Anyway in your implemented method, I am not able to see use of "spec" variable, so I believe it is there by mistake.

For rest of your code, just follow "Spring way", define a repository. Write your query with @Query Annotation, set native = true if you want to execute Native queries. Spring data JPA supports pagination in native queries too, you just need to provide a countQuery.

With a native SQL query and pagination, your interface could look like:

public interface PaymentTransactionsRepository extends JpaRepository<PaymentTransactions, Integer>{


@Query(nativeQuery=true, 
        value="SELECT * FROM payment_transactions \n-- #pageable\n",
        countQuery="SELECT count(*) FROM payment_transactions")
Page<PaymentTransactions> findAll(Pageable page);

}

For JPA queries, process is straight, just write query with @Query annotation.

Upvotes: 0

Related Questions