Skip to content

Transform SQL to Hibernate criteria

I am writing a method for retrieving clients with sum of their orders (order.total) higher and less than input values.

Criteria criteria = DetachedCriteria.forClass(Clients.class, "cl");

if (clOrdsTtlPrcFrom != -1 && clOrdsTtlPrcTo != -1) {
            String sql = "select OwnerID from Orders group by OwnerID having sum(Total) >= :clOrdsTtlPrcFrom and sum(Total) <= :clOrdsTtlPrcTo";
            SQLQuery query = sess.createSQLQuery(sql).addScalar("OwnerID", LongType.INSTANCE);
            query.setParameter("clOrdsTtlPrcFrom", clOrdsTtlPrcFrom);
            query.setParameter("clOrdsTtlPrcTo", clOrdsTtlPrcTo);
            criteria.add(Restrictions.in("id", query.list()));
        }


Criteria criteria2 = sess.createCriteria(Clients.class);
        criteria2.add(Subqueries.propertyIn("id", criteria));
List<Clients> clients = (List<Clients>) criteria2.list();

All its okay, but, sometimes i am get an error:

java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.

How can i correcting this method, or, maybe, convert this in full criteria style?

Answer

In the end, i solved this problem by this way, using sql restriction:

DetachedCriteria dtcrt = DetachedCriteria.forClass(Clients.class);
        dtcrt.setProjection(Projections.distinct(Projections.id()));    
if (clOrdsTtlPrcFrom != -1 && clOrdsTtlPrcTo != -1) {
            dtcrt.add(Restrictions.sqlRestriction("OwnerID in(select OwnerID from Orders group by " +
                            "OwnerID having sum(Total) >= ? and sum(Total) <= ?)", new Integer[]{clOrdsTtlPrcFrom, clOrdsTtlPrcTo},
                    new Type[]{StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER}));
            criteria.add(Subqueries.propertyIn("id", dtcrt));
        }