How do you get “real” sql different from hibernation requests?

Question

I have a Hibernate criteria query that incorrectly displays maximum results. In many cases, when I specify 20 maximum results, the query actually returns only 1 or 5 results, because restrictions return a lot of duplicates.

Criteria c = session.createCriteria(DomainObject.class);
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.createAlias("tags.list", "tag");
c.createAlias("names", "name");
c.createAlias("site", "site");
c.createAlias("site.tags.list", "siteTag");

// loads of or/and eq/like restrictions.

c.setFirstResult(0);
c.setMaxResults(20);

return c.list();

Is there a way to fix this query so that if I say 20 maximum results, it really returns 20 results in the county? It seems pretty crazy that hibernate limits the query to 20 results and does a separate AFTER filtering instead of the database level.

reference

Answer 1

, , .

:

criteria.setProjection(Projections.distinct(Projections.property("Id")));

, sql , , ResultTransformer , sql .

300491

Answer 2

, . (DetachedCriteria). . .

SQL, , , .

Criteria criteria = session().createCriteria(Employee.class);
criteria.add(Property.forName("id").in(dc)); 
criteria.setMaxResults(maxLength);
criteria.setFirstResult((int)rowNum);


DetachedCriteria dc = DetachedCriteria.forClass(Employee.class);
dc.createAlias("location", "location");
dc.createAlias("location.dept", "department");
dc.add(
    Restrictions.or(
        Restrictions.eq("location.id", locationId),
        Restrictions.eq("department.name", departmentName)));
dc.setProjection(Projections.distinct(Property.forName("id")));

Answer 3

Projection ID , ?

EDIT: ( , , , Oracle 11.2.0.1.0, , , , .)

Answer 4

Distinct

    package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.type.Type;

/**
* A count for style :  count (distinct (a || b || c))
* @author Deepak Surti
*/
public class MultipleCountProjection extends AggregateProjection {

   private boolean distinct;

   protected MultipleCountProjection(String prop) {
      super("count", prop);
   }

   public String toString() {
      if(distinct) {
         return "distinct " + super.toString();
      } else {
         return super.toString();
      }
   }

   public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      return new Type[] { Hibernate.INTEGER };
   }

   public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      StringBuffer buf = new StringBuffer();
      buf.append("count(");
      if (distinct) buf.append("distinct ");
        String[] properties = propertyName.split(";");
        for (int i = 0; i < properties.length; i++) {
           buf.append( criteriaQuery.getColumn(criteria, properties[i]) );
             if(i != properties.length - 1) 
                buf.append(" || ");
        }
        buf.append(") as y");
        buf.append(position);
        buf.append('_');
        return buf.toString();
   }

   public MultipleCountProjection setDistinct() {
      distinct = true;
      return this;
   }

}

ExtraProjections.java

package org.hibernate.criterion; 

public final class ExtraProjections
{ 
    public static MultipleCountProjection countMultipleDistinct(String propertyNames) {
        return new MultipleCountProjection(propertyNames).setDistinct();
    }
}

:

String propertyNames = "titleName;titleDescr;titleVersion"

criteria countCriteria = ....

countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);

https://forum.hibernate.org/viewtopic.php?t=964506