Ignorzak for intra-criterion

Question

I would like to select case-insensitive items with the In-Criterion with Hibernate Criteria criterion. For instance.

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

Unfortunately, the Criterion class does not have an ignoreCase method. HQL is not an alternative.

Answer 1

Get the source code of the Criterion class returned by Restrictions.in() ( InExpression ), and create another, similar, but converting all the elements of the list of values ​​to lower case and creating an SQL query, for example:

 lower(prop) in (...) 

Answer 2

I implemented my own InExpression, which ignores the case. This is almost the same as InExpresion from hibernate-core-3.6.10. Final The only differences are "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; } } 

Using:

 final Criteria crit = session.createCriteria(Item.class); crit.add(new InExpressionIgnoringCase("prop", array)); 

Answer 3

An alternative would be to use sqlRestriction . This way you convert your values ​​to lowercase strings, separated by commas, and after that you call.

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

However, your request parameters must be sanitized.

You can also use disjunction .

 Junction j = Restrictions.disjunction(); for (String prop: props) { j.add(Restrictions.eq("prop", prop).ignoreCase()); } crit.add(j); 

Answer 4

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

worked for me.