Return page object from JPA query

Question

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;
    }

You don't implement the method. You let spring-data-jpa implement it for you. Read the documentaton. https://docs.spring.io/spring-data/jpa/docs/2.1.3.RELEASE/reference/html/ — JB Nizet, Dec 13 '18 at 20:47
I agree but in my case I want to add some custom SQL statements — Peter Penzov, Dec 13 '18 at 20:48
Then why don't you ask about that? Anyway, it doesn't change anything. You specify the query, and let spring-data-jpa implement it for you. https://docs.spring.io/spring-data/jpa/docs/2.1.3.RELEASE/reference/html/#jpa.query-methods.at-query — JB Nizet, Dec 13 '18 at 20:50
Further to @JBNizet's comments, your query doesn't do very much - the class name will be a compile time constant. — Boris the Spider, Dec 16 '18 at 23:43

score 7 · Answer 1 · edited Jan 17 '20 at 21:22

7

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

Create SQL for list objects and add page parameters:

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

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);

edited Jan 17 '20 at 21:22

marc_s

732,580
175
1,330
1,459

answered Jan 15 '20 at 16:19

ssp

294
1
3
7

Your answer seems correct too, because the question contains custom EntityManager queries as one of the solution attempts, and this type doesn't have Spring Data facilities. – Thiago Sperandio Jun 14 '23 at 13:42
Sort is not working with this solution – Estimate Jul 28 '23 at 13:32

score 2 · Accepted Answer · answered Dec 22 '18 at 19:53

2

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.

answered Dec 22 '18 at 19:53

Vivek Kurmi

138
1
11

I get `UserRepositoryImpl is not abstract and does not override abstract method findAll(org.springframework.data.domain.Pageable) in org.datalis.admin.backend.repo.UserRepository` – Peter Penzov Dec 23 '18 at 23:34
This may be because you are extending "CrudRepository" interface in your repository, Please try extending "JpaRepository".https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html – Vivek Kurmi Dec 24 '18 at 09:47
I updated the post. I will test it later when I have FE app for request. Do you know how I can skip this casting `(Page) query.getResultList();`? – Peter Penzov Dec 24 '18 at 14:05
I don't think so there is because getResultList() has signature like "List getResultList();" which always return list not Page. – Vivek Kurmi Dec 24 '18 at 14:54
Well, I get unexpected token: LIMIT near line 1, column 49 [select e from org.plugin.entity.Users e LIMIT :limit OFFSET :offset] Any idea how I can fix this? – Peter Penzov Dec 26 '18 at 22:02
There was issue with Oracle Database because oracle doesn't support LIMIT and OFFSET keywords in SQL. Which database you are using? Also please tell me the Spring and JPA version. It would be good if you could provide the repository method code. – Vivek Kurmi Dec 28 '18 at 07:44
One more thing whichever database we are using we should set the database property with the correct hibernate dialect in properties or xml configuration file. – Vivek Kurmi Dec 28 '18 at 07:57

score 0 · Answer 3 · answered Dec 20 '18 at 11:52

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.

Thanks. I updated the post and I see 2 problems: I receive from angular application params `?page=0&size=10` whew I need to put them into the query? Also is this `\n-- #pageable\n"` used for something? — Peter Penzov, Dec 20 '18 at 19:30
In your above layer, where you are using this repository, you can call method as: `paymentTransactionsRepo.findAll(new PageRequest(page, size));` Also please read following article to understand completely: [link](https://www.baeldung.com/spring-data-jpa-query) — Muhammad Asher Toqeer, Dec 21 '18 at 05:46

Return page object from JPA query

3 Answers3