javasqlhibernatehibernate-criteriadetachedcriteria

pass sqlRestriction into criteria - bad sql grammar

So I want to add a sqlRestriction in my criteria because my data field needs to be trimmed before I want to check it.

I tried adding it in my existing filter like this

public List<FooBarZ> findByFilter(Filter filter) {
 DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
 String alias = getAlias(criteria); // outputs "this."
    
 criteria.add(eq(alias + "foo", filter.getFoo());
 criteria.add(Restrictions.sqlRestriction("trim(" + alias + "bar) like %" + filter.getBar()));
    
return findByCriteria(criteria, 0, filter.getMaxRows());
}

so my criterionEntries look like this

criteria>criteria>criterionEntries=

this.foo=1
trim(this.bar) like %2

now If this query gets run (Hibernate) I get my bad sql grammar error

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar

java.sql.SQLSyntaxErrorException: unknown token:  in statement[
    select
        this_.IDFOO as IDFOO,
        this_.IDBAR as IDBAR
    from
        TABLE this_
        where trim(this.bar) like %2
]

So I can see why the syntax is wrong but I dont understand why. Why is my this.field not correctly mapped to the db field? Why is this where shown and not the previous one?

how to fix this?

ELI5: have data in my db that contains whitespace which i need to be removed before I can filter so my filter X should match dbvalue '0X ' (char(9))

My filter works perfectly fine without this trim one but I want to add this.

Solution

  • Answer:

    sqlRestriction should directly use {alias}.COLUMNNAME

    so

     criteria.add(Restrictions.sqlRestriction("trim(" + alias + "bar) like %" + filter.getBar()));

    becomes

     criteria.add(Restrictions.sqlRestriction(
                    "trim({alias}.IDBAR) like ?",
                    "%" + filter.getBar(),
                    Hibernate.STRING
            ));