Use SQL pagination

[blootsvoets]

Currently, a lot of pagination happens in-memory, which is very slow. This is shown by the error message

WARN 1 --- [  XNIO-1 task-6] o.h.h.internal.ast.QueryTranslatorImpl   : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

This happens for any HQL query that has a join or left join fetch statement.

According to https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/, to avoid this message a two-query approach is preferred:

1. query all ID's that match the criteria with a pageable
2. query the entities that match given ids it.id IN (:ids), returning a List
3. convert the result list into a page

This takes advantage of the SQL paging. A downside is that now two queries are necessary, and that some complexity is added.