CODEWITHSUNDEEP
javahibernatehibernate-criteria
Cengiz
Cengiz

Reputation: 4867

Ignorecase for In-Criterion

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.

Upvotes: 4

Views: 6624

Answers (4)

Jagatheeswaran
Jagatheeswaran

Reputation: 1

        .add(Restrictions.sqlRestriction("upper({alias}.COLUMN_NAME) = upper(?)", VALUE_TO_COMPARE, Hibernate.STRING))

worked for me.

Upvotes: 0

lawal
lawal

Reputation: 992

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

Upvotes: 3

Cengiz
Cengiz

Reputation: 4867

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

Upvotes: 4

JB Nizet
JB Nizet

Reputation: 691715

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 (...)

Upvotes: 5

Related Questions