I've have already implemented pagination using the following code:
public Paginacao<Anuncio> consultarPaginado(int pagina, Integer cidadeId) {
Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Anuncio.class);
criteria.add(Restrictions.eq("ativo", true));
criteria.add(Restrictions.eq("statusLiberacao", AnunciosUtil.STATUS_ANUNCIO_LIBERADO));
criteria.add(Restrictions.eq("statusVendaAnuncio", AnunciosUtil.STATUS_VENDA_ANUNCIO_DISPONIVEL));
if (cidadeId != null) {
criteria.add(Restrictions.eq("cidade.id", cidadeId));
}
criteria.addOrder(Order.desc("dataPostagem"));
criteria.setProjection(Projections.rowCount());
Long count = (Long) criteria.uniqueResult();
Paginacao<Anuncio> paginacao = new Paginacao<Anuncio>();
int qtdPaginas = (count.intValue() / 7) + 1;
paginacao.setQtdPaginas(qtdPaginas);
criteria.setProjection(null);// reseta a criteria sem a projeção
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
if (pagina > qtdPaginas) {
pagina = qtdPaginas;
}
pagina = pagina - 1;
criteria.setFirstResult(pagina * ConstantesGenericas.MAXIMO_OBJETOS_RETORNADOS);
criteria.setMaxResults(ConstantesGenericas.MAXIMO_OBJETOS_RETORNADOS);
paginacao.setRegistros(criteria.list());
return paginacao;
}
When I build the SQL query manually and submit it to the database, I get 8 results. However, when I try the above code, before setting the ResultTransformer to DISTINCT_ROOT_ENTITY e get 8 results (without distinct) and after setting it I get 4 results. But I should get 8 results (using DISTINCT), because when I build the SQL manually without distinct I get 11 results and when I use DISTINCT I get correctly, 8 distincts results.
What's wrong with the above code?
Pagination is a simple but important feature to limit the size of your result set to a number of records that can get efficiently processed by your application and the user. You can configure it with JPA and Hibernate by calling the setFirstResult and setMaxResults on the Query or TypedQuery interface.
DISTINCT_ROOT_ENTITY. Each row of results is a distinct instance of the root entity. static String. ROOT_ALIAS. The alias that refers to the "root" entity of the criteria query.
Hibernate's ResultTransformers provide various ways to map the result of your query to different data structures. They were commonly used in Hibernate 4, got deprecated in Hibernate 5 and got replaced by the functional interfaces TupleTransformer and ResultListTransformer in Hibernate 6.
To put it simple, Hibernate Projections are used in order to query only a subset of the attributes of an entity or group of entities you're querying with Criteria. You can also use Projections to specify distinct clauses and aggregate functions like max , sum and so on.
After a long time looking for a solution for my problem I managed to solve it. The problem that if you create a criteria or query that retrieves toMany associations using JOINS, and then you use setMaxResults and set the ResultTransformer to DISTINCT_ROOT_ENTITY the result won't be as you expected.
As JB Nizet said, suppose you have 4 A entities, each with 3 B entities, and suppose your query retrieves all the A entities with their Bs.
In that case, the SQL query will return 12 rows. If you use setMaxResults(7), it will retrieve (for example) three rows for A1 and its Bs, three rows for A2 and its Bs, and just 1 row for A3 and its first B.
And since you have used DISTINCT_ROOT_ENTITY, the criteria query will return only three entities: A1, A2, and A3 (which will have an incomplete set of Bs).
To solve this, you have to set the FETCH MODE for toMany (usually collections) relationships to SELECT or SUBSELECT, and you have basically 2 ways to achieve this:
The first way is to use @FetchMode(FetchMode.SUBSELECT) annotation on your attribute, and I don't like this approach because it causes every query to use SUBSELECT FETCH to retrieve the collection. But it will work.
The other way is to set fetch modes for relationships when you build your query. I prefer this way, because I can customize the query to my needs and I don't have to use SUBSELECTS to all queries. So, I've done this way:
public Paginacao<Anuncio> consultarPaginado(int pagina, Integer cidadeId) {
Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Anuncio.class);
criteria.add(Restrictions.eq("ativo", true));
criteria.add(Restrictions.eq("statusLiberacao", AnunciosUtil.STATUS_ANUNCIO_LIBERADO));
criteria.add(Restrictions.eq("statusVendaAnuncio", AnunciosUtil.STATUS_VENDA_ANUNCIO_DISPONIVEL));
criteria.setFetchMode("imagens", FetchMode.SELECT);
criteria.setFetchMode("pagamentos", FetchMode.SELECT);
if (cidadeId != null) {
criteria.add(Restrictions.eq("cidade.id", cidadeId));
}
criteria.addOrder(Order.desc("dataPostagem"));
criteria.setProjection(Projections.rowCount());
Long count = (Long) criteria.uniqueResult();
Paginacao<Anuncio> paginacao = new Paginacao<Anuncio>();
int qtdPaginas = (count.intValue() / 7) + 1;
paginacao.setQtdPaginas(qtdPaginas);
criteria.setProjection(null);// reseta a criteria sem a projeção
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
if (pagina > qtdPaginas) {
pagina = qtdPaginas;
}
pagina = pagina - 1;
criteria.setFirstResult(pagina * ConstantesGenericas.MAXIMO_OBJETOS_RETORNADOS);
criteria.setMaxResults(ConstantesGenericas.MAXIMO_OBJETOS_RETORNADOS);
paginacao.setRegistros(criteria.list());
return paginacao;
}
Hope that it helps anyone else. ;D
I'm not sure if I understand your question correctly, but if your query retrieves entities with toMany associations join-fetched, then pagination won't work as expected.
Indeed, suppose you have 4 A entities, each with 3 B entities, and suppose your query retrieves all the A entities with their Bs.
In that case, the SQL query will return 12 rows. If you use setMaxResults(7), it will retrieve (for example) three rows for A1 and its Bs, three rows for A2 and its Bs, and just 1 row for A3 and its first B.
And since you have used DISTINCT_ROOT_ENTITY, the criteria query will return only three entities: A1, A2, and A3 (which will have an incomplete set of Bs).
This was an issue for me, and it took some time to come up with a solution that works for all the scenarios I have.
What you want for each pagination page is 2 things, the total count of all results and your single page of results, but to do that you need to take 3 steps. 1) get the total count, 2) get the unique ids for your page, and 3) get the full data for the ids found in step 2. And you can do all that with a single criteria object:
1) get total count, using distinct ids (uniqueField = the name of your id in the entity class)
Criteria criteria = session.createCriteria(YourEntity.class);
Projection idCountProjection = Projections.countDistinct(uniqueField);
criteria.setProjection(idCountProjection);
//setup criteria, joins etc here
int totalResultCount = ((Long)criteria.uniqueResult()).intValue();
2) reset projection and set start and length (you want the distinct ids)
criteria.setProjection(Projections.distinct(Projections.property(uniqueField)));
criteria.setFirstResult(start);
criteria.setMaxResults(length);
List uniqueSubList = criteria.list();
3) reset projection and get distinct results that match ids
criteria.setProjection(null);
criteria.setFirstResult(0); criteria.setMaxResults(Integer.MAX_VALUE);
criteria.add(Restrictions.in(uniqueField, uniqueSubList));
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
List searchResults = criteria.list();
//and now, however you want to return your results
Map<String, Object> searchResultsMap = new HashMap<String, Object>();
searchResultsMap.put("searchResults", searchResults);
searchResultsMap.put("totalResultCount", totalResultCount);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With