Second replacement of a named parameter fails in HQL

Question

I have an HQL statement of this sort:

update versioned MyTable t 
set t.myCol = case 
   when ((t.myCol + :myColChange) < 0) then 0 
   else (t.myCol + :myColChange) end

I see that this is not working due to the second occurrence of :myColChange. The following exception is thrown:

org.hibernate.QueryException: Not all named parameters have been set: [myColChange] [ update versioned MyTable t set t.myCol = case when ((t.myCol + :myColChange) < 0) then 0 else (t.myCol + :myColChange) end]

Would appreciate any ideas to resolve this problem. Thanks!

Answer 1

How about

.... set t.myCol = max(0, t.myCol + :myColChange)

Answer 2

A work around would be to use two different names/parameter.

update versioned MyTable t 
set t.myCol = case 
   when ((t.myCol + :myColChange1) < 0) then 0 
   else (t.myCol + :myColChange2) end

Answer 3

Did you actually

setXXXXX("myColChange", myColChangeValue);

on the Query object before executing it?

I'm just asking because you didn't post your code.

Answer 4

I have this problem too. My current workaround is NOT to use named parameters, but used positions instead.

For me this is not a big problem because I store the queryparameters in my own object in a list.

    public void apply( final CategoryFilter f )
    {
        final String jsonFilter = f.getFilter();
        final QueryParameters queryParams = QueryParameters.createFromJsonString( jsonFilter );

        final StringBuilder queryStringBuilder =
            new StringBuilder( "UPDATE Transaction t SET t.category = 'myCatergory' WHERE " );

        boolean isFirst = true;
        for ( final QueryParameter queryParam : queryParams.getQueryParams() )
        {
            if (!isFirst)
            {
                queryStringBuilder.append( " AND " );
            }
            else
            {
                isFirst = false;
            }
            queryStringBuilder.append( "t." );
            queryStringBuilder.append( queryParam.getField() );
            queryStringBuilder.append( " " );
            queryStringBuilder.append( queryParam.getOp() );
            queryStringBuilder.append( " ? " );
        }
        final Query q = getSession().createQuery( queryStringBuilder.toString() );

        int index = 0;
        for ( final QueryParameter queryParam : queryParams.getQueryParams() )
        {
            q.setParameter( index++, queryParam.getValue() );
        }
        q.executeUpdate();

    }