Implementing Pagination using entity manager in spring

Question

How can I implement pagination in Spring + hibernate project ? Following is the code. I will get PageRequest object and I want to return Page of item

 @Repository
    public class ItemRepository {

      @PersistenceContext
      EntityManager entityManager;

      public Page<Item> findItems(PageRequest pageRequest) {
        // TODO: Implement me
        return new Page<>(new ArrayList<>(), 0, 0);
      }
    }


    public class PageRequest {

      private final int count;
      private final int pageNumber;

      public PageRequest(int pageNumber, int count) {
        assert pageNumber >= 0;
        assert count > 0;

        this.pageNumber = pageNumber;
        this.count = count;
      }

      public int getCount() {
        return count;
      }

      public int getPageNumber() {
        return pageNumber;
      }
    }

Are you aware that, although you tagged the question with spring-data-jpa you are not really using it and it would do most of the stuff for you for free? — Jens Schauder, Mar 17 '20 at 08:30

score 19 · Accepted Answer · answered Mar 16 '20 at 11:29

I found the solution

public Page<Item> findItems(PageRequest pageRequest) {

    Query query = entityManager.createQuery("From Item");
    int pageNumber =pageRequest.getPageNumber();
    int pageSize = pageRequest.getCount();
    query.setFirstResult((pageNumber) * pageSize);
    query.setMaxResults(pageSize);
    List <Item> fooList = query.getResultList();

    Query queryTotal = entityManager.createQuery
            ("Select count(f.id) From Item f");
    long countResult = (long)queryTotal.getSingleResult();
    int i=(int)countResult;
    return new Page<>(fooList, pageRequest.getPageNumber(),i);
  }

I had to query two times to DB once to get records and then to get all the count

score 1 · Answer 2 · answered Mar 16 '20 at 09:53

One way to do it is to add logic in your PageRequest class to "slice" an incoming list depending on its Pageable method parameter and return it as org.springframework.data.domain.PageImpl.

Here is a static method that you can use in your PageRequest class:

public static <E> Page<E> returnPagedList(Pageable pageable, List<E> listOfEntities) {
    List<E> listToReturn = listOfEntities;
    if (pageable.isPaged()) {
        int pageSize = pageable.getPageSize();
        int currentPage = pageable.getPageNumber();
        int startItem = currentPage * pageSize;
        if (listOfEntities.size() < startItem) {
            listToReturn = Collections.emptyList();
        } else {
            int toIndex = Math.min(startItem + pageSize, listOfEntities.size());
            listToReturn = listOfEntities.subList(startItem, toIndex);
        }
    }
    return new PageImpl<>(listToReturn, pageable, listOfEntities.size());
}

Then in your repository you can do this (notice you receive a Pageable object):

  public Page<Item> findItems(Pageable pageable) {
   EntityManager em = getEntityManager();
   List<Item> list = ... // get list of all Items
   return PageRequest.pagedList(pageable, list);
    }
  }

This won't do it since all the dataset will be brought into the code, defeating the purpose of pagination. — Daniel Pop, Nov 11 '22 at 11:41

score 0 · Answer 3 · answered Apr 06 '23 at 09:33

In a project that uses Spring Data JPA, you can implement pagination with the EntityManager quite efficiently, because it allows you to directly map the result set values into DTOs, skipping redundant entity to DTO mapping:

@Service
public class ItemService {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional(readOnly = true)
    public Page<ItemDTO> getItemsPage(Pageable page) {
        var itemList = entityManager
                .unwrap(Session.class)
                .createNamedQuery("Item.availableItems", Object[].class)
                .setFirstResult(page.getPageNumber() * page.getPageSize())
                .setMaxResults(page.getPageSize())
                .setTupleTransformer((tuples, aliases) -> {
                    var item = new ItemDTO();
                    item.setId((Long) tuples[0]);
                    item.setName((String) tuples[1]);
                    //...
                    return item;
                })
                .getResultList();

        long totalCount = itemList.size() < page.getPageSize()
                ? page.getPageSize()
                : entityManager
                    .createQuery("SELECT count(i) FROM Item i", Long.class)
                    .getSingleResult();

        return new PageImpl<>(itemList, page, totalCount);
    }
}

In this example, the query is defined in the Item entity class:

@NamedQuery(
        name = "Items.availableItems",
        query = "SELECT i.id, i.name FROM Item i"
)
@Entity
@Table(name = "items")
public class Item {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    // ...
}

With this you can easily return the paginated result:

public ResponseEntity<List<ItemDTO>> getAvailableItems(
        @Min(0) @Valid @RequestParam(value = "pageNumber", defaultValue = "0") Integer pageNumber,
        @Min(1) @Valid @RequestParam(value = "pageSize",  defaultValue = "10") Integer pageSize) {
    Page<ItemDTO> items = itemService.getAvailableItemsPage(PageRequest.of(pageNumber, pageSize));
    return ResponseEntity
            .ok()
            .header("x-total-count", String.valueOf(items.getTotalElements()))
            .header("x-page-number", String.valueOf(pageNumber))
            .header("x-page-size", String.valueOf(pageSize))
            .body(items.toList());
}

Implementing Pagination using entity manager in spring

3 Answers3

Linked