使用Hibernate Criteria和DISTINCT_ROOT_ENTITY进行分页 [英] Pagination with Hibernate Criteria and DISTINCT_ROOT_ENTITY
问题描述
我已经使用以下代码实现了分页：
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 =新Paginacao< Anuncio>（）;
int qtdPaginas =（count.intValue（）/ 7）+1;
paginacao.setQtdPaginas（qtdPaginas）;
criteria.setProjection（null）; //重新设置标准sem aprojeçã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;
}
当我手动构建SQL查询并将其提交给数据库时， 8结果。但是，当我尝试上面的代码时，在将ResultTransformer设置为DISTINCT_ROOT_ENTITY之前，得到8个结果（没有不同），并在设置它之后得到4个结果。但是我应该得到8个结果（使用DISTINCT），因为当我手动构建SQL而没有明确的时候，我得到了11个结果，而当我使用DISTINCT时，我得到了正确的结果，8个结果不同。
<上面的代码出了什么问题？
经过很长时间寻找我的问题的解决方案，我设法解决它。如果您使用JOINS和
创建一个条件或查询来检索许多关联，则使用setMaxResults并将ResultTransformer设置为DISTINCT_ROOT_ENTITY，结果将不会如您期望的那样。
正如JB Nizet所说，假设您有4个实体，每个实体都有3个B实体，并且假设您的查询检索了所有具有B的实体。
在这种情况下，SQL查询将返回12行。如果使用setMaxResults（7），它将检索（例如）A1和它的Bs的三行，A2和它的Bs的三行，以及A3和它的第一个B的一行。
由于您已经使用了DISTINCT_ROOT_ENTITY，因此条件查询将只返回三个实体：A1，A2和A3（其中包含一组不完整的Bs）。
为了解决这个问题，你必须将FETCH MODE设置为toMany（通常是集合）关系到SELECT或SUBSELECT，并且你基本上有两种方法可以实现这一点：
第一种方法是在属性上使用@FetchMode（FetchMode.SUBSELECT）注释，我不喜欢这种方法，因为它会导致每个查询都使用SUBSELECT FETCH来检索集合。但它会起作用的。
另一种方法是在构建查询时为关系设置获取模式。我更喜欢这种方式，因为我可以根据自己的需要定制查询，而且我不必使用SUBSELECTS来查询所有查询。所以，我这样做了：
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 =新Paginacao< Anuncio>（）;
int qtdPaginas =（count.intValue（）/ 7）+1;
paginacao.setQtdPaginas（qtdPaginas）;
criteria.setProjection（null）; //重新设置标准sem aprojeçã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;
}
希望它可以帮助其他人。 ; D
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?
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
这篇关于使用Hibernate Criteria和DISTINCT_ROOT_ENTITY进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
