CODEWITHSUNDEEP
javaspringspring-data-jpa
user3364181
user3364181

Reputation: 541

Spring data jpa custom repository with pageable

I created custom repository and one method in it. I would like to add pageable object to that query in order to limit items returned.

I created additional method that will extract only selected page from all items. But problem is if i have 50000 items, i would need to get them all and then get just selected page out of them which is very bad for performance.

public List<Team> findTeamsForUser(long userId, long statusId, TeamCriteria criteria, Pageable pageable) {
    StringBuilder sb = new StringBuilder("select team from Team team join TeamMember teamMember on teamMember.team.id=team.id where 
            teamMember.user.id=" + userId + " AND teamMember.status.id=" + statusId);

    if(criteria!=null) {
        if (criteria.getGameId() != null) {
            sb.append(" AND team.game.id IN " + criteria.getGameId().getIn().toString().replace("[", "(").replace("]", ")"));
        }
    }

    sb.append(" ORDER BY team.createdDate DESC");

    Query query = entityManager.createQuery(sb.toString());
    return query.getResultList();
}

I would like to include somehow Pageable parameter into this query so i get only limited items.

Upvotes: 3

Views: 2279

Answers (1)

Ken Chan
Ken Chan

Reputation: 90517

You can use setMaxResults() and setFirstResult() on the Query to restrict the number of records and the offset of the first returned record.

setMaxResults() is equivalent to LIMIT where setFirstResult() is equivalent to OFFSET in SQL.

Combining Query with Pageable from spring data , the codes looks like :

Query query = entityManager.createQuery(sql.toString());
query.setMaxResults(pageable.getPageSize());
query.setFirstResult(pageable.getOffset());

Assuming each time you want to return 10 records .The Pageable for each page is 

Pageable page1 = PageRequest.of(0, 10);  //Page 1 , offset=0  , limit=10
Pageable page2 = PageRequest.of(10, 10);  //Page 2 , offset=10 , limit=10
Pageable page3 = PageRequest.of(20, 10);  //Page 3 , offset=20 , limit=10
....
......
Pageable pageN = PageRequest.of((n-1)*10, 10);  //Page N , offset=(N-1)*10 , limit=10

Upvotes: 2

Related Questions