4

i'd like to select items case insensitive with an In-Criterion with Hibernate Criteria API. E.g.

Criteria crit = session.createCriteria(Item.class);
crit.add(Restrictions.in("prop", valueList).ignoreCase());

Unfortunately the Criterion class doesn't has an ignoreCase method. HQL is not an alternative.

Cengiz
  • 5,375
  • 6
  • 52
  • 77

4 Answers4

5

Get the source code of the Criterion class returned by Restrictions.in() (InExpression), and create another one which is similar but transforms all the elements of the value list to lowercase, and generates a SQL query like:

lower(prop) in (...)
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
4

I implemented my own InExpression which ignores case. It is almost the same as InExpresion from hibernate-core-3.6.10.Final. Only differences are the 'lower(..)'.

import java.util.ArrayList;
import org.hibernate.Criteria;
import org.hibernate.EntityMode;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.InExpression;
import org.hibernate.engine.TypedValue;
import org.hibernate.type.CompositeType;
import org.hibernate.type.Type;
import org.hibernate.util.StringHelper;

public class InExpressionIgnoringCase implements Criterion {

private final String propertyName;
private final Object[] values;

public InExpressionIgnoringCase(final String propertyName, final Object[] values) {
    this.propertyName = propertyName;
    this.values = values;
}

public String toSqlString(final Criteria criteria, final CriteriaQuery criteriaQuery) throws HibernateException {
    final String[] columns = criteriaQuery.findColumns(this.propertyName, criteria);
    final String[] wrappedLowerColumns = wrapLower(columns);
    if (criteriaQuery.getFactory().getDialect().supportsRowValueConstructorSyntaxInInList() || columns.length <= 1) {

        String singleValueParam = StringHelper.repeat("lower(?), ", columns.length - 1) + "lower(?)";
        if (columns.length > 1)
            singleValueParam = '(' + singleValueParam + ')';
        final String params = this.values.length > 0 ? StringHelper.repeat(singleValueParam + ", ",
                this.values.length - 1) + singleValueParam : "";
        String cols = StringHelper.join(", ", wrappedLowerColumns);
        if (columns.length > 1)
            cols = '(' + cols + ')';
        return cols + " in (" + params + ')';
    } else {
        String cols = " ( " + StringHelper.join(" = lower(?) and ", wrappedLowerColumns) + "= lower(?) ) ";
        cols = this.values.length > 0 ? StringHelper.repeat(cols + "or ", this.values.length - 1) + cols : "";
        cols = " ( " + cols + " ) ";
        return cols;
    }
}

public TypedValue[] getTypedValues(final Criteria criteria, final CriteriaQuery criteriaQuery)
        throws HibernateException {
    final ArrayList<TypedValue> list = new ArrayList<TypedValue>();
    final Type type = criteriaQuery.getTypeUsingProjection(criteria, this.propertyName);
    if (type.isComponentType()) {
        final CompositeType actype = (CompositeType) type;
        final Type[] types = actype.getSubtypes();
        for (int j = 0; j < this.values.length; j++) {
            for (int i = 0; i < types.length; i++) {
                final Object subval = this.values[j] == null ? null : actype.getPropertyValues(this.values[j],
                        EntityMode.POJO)[i];
                list.add(new TypedValue(types[i], subval, EntityMode.POJO));
            }
        }
    } else {
        for (int j = 0; j < this.values.length; j++) {
            list.add(new TypedValue(type, this.values[j], EntityMode.POJO));
        }
    }
    return list.toArray(new TypedValue[list.size()]);
}

@Override
public String toString() {
    return this.propertyName + " in (" + StringHelper.toString(this.values) + ')';
}

private String[] wrapLower(final String[] columns) {
    final String[] wrappedColumns = new String[columns.length];
    for (int i = 0; i < columns.length; i++) {
        wrappedColumns[i] = "lower(" + columns[i] + ")";
    }
    return wrappedColumns;
}
}

Usage:

final Criteria crit = session.createCriteria(Item.class);
            crit.add(new InExpressionIgnoringCase("prop", array));
Cengiz
  • 5,375
  • 6
  • 52
  • 77
  • This solved my issue, thanks! It would be great if you could provide a patch file so people can base this on whatever hibernate version they use. – Tim Büthe Jun 04 '20 at 08:09
3

An alternative will be to use an sqlRestriction. So you convert your values to comma-separated lower case strings and thereafter you call. 

crit.add(Restrictions.sqlRestriction("lower({alias}.prop) in ( " + q + " )"));

However your query parameters will have to be sanitized.

You could also use disjunction. 

Junction j = Restrictions.disjunction();
for (String prop: props) {
    j.add(Restrictions.eq("prop", prop).ignoreCase());
}
crit.add(j);
lawal
  • 952
  • 10
  • 19
0
        .add(Restrictions.sqlRestriction("upper({alias}.COLUMN_NAME) = upper(?)", VALUE_TO_COMPARE, Hibernate.STRING))

worked for me.

Jagatheeswaran
  • 1