Transform SQL to Hibernate criteria

Question

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?

when i add DetachedCriteria in own Criteria i get this error. — Aleksei, Dec 29 '17 at 11:24
If you remove DetachedCriteria in own Criteria then it won't work in any way. — Roman C, Dec 29 '17 at 11:34
Yeah, i know it. The problem in query.list() size. If this size is more than 2000, Restrictions.in() don't work. — Aleksei, Dec 29 '17 at 11:41
It's not possible to have a list with more than limited size. — Roman C, Dec 29 '17 at 12:27
Why does my question get a negative evaluation? I do not understand what's wrong.. — Aleksei, Dec 29 '17 at 12:59
I think your solution is not very good. I suspect that it is possible to write this query completely with criteria, or with the nested select (with what I asked to help). Thus, you do not have to always check list size, break it into pieces and after that combine all parts. — Aleksei, Dec 29 '17 at 13:10

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

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

You can learn more what this error means when you cannot have the list of input parameters to be more than 2000. The solution is that you need to either split the large list into smaller ones or optimize your SQL query to have less than limited size of parameters.

You have to fix this issue by partitioning the list into small lists of size less than 2000 and then run the query for each of those smaller lists. Then combine the results into one list again. Now you can partition the list the way you want.

score 0 · Accepted Answer · answered Jan 02 '18 at 10:24

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));
        }

Transform SQL to Hibernate criteria

2 Answers2

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

Transform SQL to Hibernate criteria

2 Answers2

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

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