not all named parameters have been set hibernate in createSQLQuery

Question

I am getting the error of not all named parameters have been set. Below is my code.

my SqlQuery which is running fine at mysql prompt, You can refer schema in the question SQL Query

SELECT  t.*
FROM    (
    SELECT  @lim := 2,
            @cg := ''
    ) vars,
    (select * from Table1 order by product,amount, make)  t
WHERE   CASE WHEN @cg <> product THEN @r := @lim ELSE 1 END > 0
    AND (@r := @r - 1) >= 0
    AND (@cg := product) IS NOT NULL
ORDER BY
    product,amount, make

my java code

try {
             context.dbl.startTransaction();
             Session session = context.dbl.getSession();

             //String sqlQuery = "from com.infibeam.inventoryservice.dbObjects.PopularBrandDO";
             String sqlQuery = "SELECT  t.* ";
             sqlQuery=sqlQuery + "FROM    (";
             sqlQuery=sqlQuery + "SELECT  @lim := 2,";
             sqlQuery=sqlQuery + "@cg := ''";
             sqlQuery=sqlQuery + ") vars, ";
             sqlQuery=sqlQuery + "(select * from Table1 order by product,amount, make) t";
             sqlQuery=sqlQuery + " WHERE   CASE WHEN @cg <> product THEN @r := @lim ELSE 1 END > 0";
             sqlQuery=sqlQuery + " AND (@r := @r - 1) >= 0 ";
             sqlQuery=sqlQuery + " AND (@cg := product) IS NOT NULL ";
             sqlQuery=sqlQuery + " ORDER BY product,amount, make";
             //Query query = session.createQuery(sqlQuery);
             SQLQuery query = session.createSQLQuery(sqlQuery);
             listItems = query.list();


            }catch(RuntimeException e) {
                e.printStackTrace();
            }

Below is the exception i am getting

org.hibernate.QueryException: Not all named parameters have been set: [] [SELECT  t.* FROM    (SELECT  @lim := 2,@cg := '') vars, (select * from Table1 order by product,amount, make) t WHERE   CASE WHEN @cg <> product THEN @r := @lim ELSE 1 END > 0 AND (@r := @r - 1) >= 0  AND (@cg := product) IS NOT NULL  ORDER BY product,amount, make]
    at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:291)
    at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:199)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:143)
    at com.infibeam.weaverbird.helper.PopularBrandFacetHelper.bootstrap(PopularBrandFacetHelper.java:48)

Thanks in advance...

Answer 1

The problem is the assignments with :=, which are by the way no standard SQL.

In SQL after a : always a parameter is expected, like in where value = :param and :param has the be set as a parameter then. Now hibernate is scanning the select and find colons where no set parameters follow.

Solution: Redesign your selection using hibernate standards.

You can use two different HQL queries.

First: Select all product: select distinct product from Table1

Second: For each product you do from Table1 where product = :prod, :prod you set as a parameter with the actual product, and with setMaxResults(2) you can limit the number of rows as you need.

Now it is many selects and not a single one, but nevertheless they might be faster than the single query (the single query is complicated and risks an inefficient search strategy in the database). And a big advantage, now it is purely HQL and so your program is portable to different databases.