Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pagination with Hibernate Criteria and DISTINCT_ROOT_ENTITY

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?

like image 347
jguilhermemv Avatar asked Jun 14 '12 17:06

jguilhermemv


People also ask

How does pagination work in hibernate?

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.

What is criteria Distinct_root_entity?

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.

What is Setresulttransformer in hibernate?

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.

How projection is used in hibernate criteria?

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.


3 Answers

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

like image 177
jguilhermemv Avatar answered Oct 17 '22 04:10

jguilhermemv


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

like image 41
JB Nizet Avatar answered Oct 17 '22 04:10

JB Nizet


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);
like image 38
MattC Avatar answered Oct 17 '22 04:10

MattC