SQL 'LIKE' operator in Hibernate Criteria

Question

I want to implement some universal filter with Hibernate Criteria. It should work like LIKE operator from SQL:

SELECT * FROM table WHERE table.ANYCOLOUMNHERE LIKE '%'||anyvaluehere||'%'

I have Map<String, String> where key is a column name, and value is its value.

I tried something like this:

for (Entry<String, String> filter : filters.entrySet()) {
    crit.add(Restrictions.ilike(filter.getKey(), filter.getValue(), MatchMode.ANYWHERE));
}

But when field type is not String, it causes java.lang.ClassCastException:

[com.nsn.util.LoggerUtilerror] (http-localhost-127.0.0.1-8080-1) Error while getting alarms: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Long
    at org.hibernate.type.descriptor.java.LongTypeDescriptor.unwrap(LongTypeDescriptor.java:36) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$1.doBind(BigIntTypeDescriptor.java:57) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:92) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:305) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:300) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1891) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1862) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1737) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:828) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2447) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2433) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2258) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:122) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1535) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) [hibernate-core-4.1.1.Final.jar:4.1.1.Final]
    at com.nsn.entities_proccess.AlarmDAOImpl.getAlarms(AlarmDAOImpl.java:93) [classes:]
    at com.nsn.boundary_process.LazyAlarmDataModel.load(LazyAlarmDataModel.java:50) [classes:]
    at org.primefaces.component.datatable.DataTable.loadLazyData(DataTable.java:677) [primefaces-3.3.1.jar:]
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:92) [primefaces-3.3.1.jar:]
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1786) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at com.sun.faces.context.PartialViewContextImpl$PhaseAwareVisitCallback.visit(PartialViewContextImpl.java:518) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at com.sun.faces.component.visit.PartialVisitContext.invokeVisitCallback(PartialVisitContext.java:183) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at javax.faces.component.UIData.visitTree(UIData.java:1411) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIForm.visitTree(UIForm.java:371) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIComponent.visitTree(UIComponent.java:1623) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at com.sun.faces.context.PartialViewContextImpl.processComponents(PartialViewContextImpl.java:376) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at com.sun.faces.context.PartialViewContextImpl.processPartial(PartialViewContextImpl.java:297) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at javax.faces.context.PartialViewContextWrapper.processPartial(PartialViewContextWrapper.java:183) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIViewRoot.encodeChildren(UIViewRoot.java:981) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1779) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:391) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:125) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:288) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139) [jsf-impl-2.1.7-jbossorg-2.jar:]
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594) [jboss-jsf-api_2.1_spec-2.0.1.Final.jar:2.0.1.Final]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]
    at org.jboss.weld.servlet.ConversationPropagationFilter.doFilter(ConversationPropagationFilter.java:62) [weld-core-1.1.5.AS71.Final.jar:2012-02-10 15:31]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]
    at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]
    at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_05]

Is there a way to resolve this?

Answer 1

You can use DetachedCriteria also when the hibernate session is not present.

DetachedCriteria criteria = DetachedCriteria.forClass(Pojo.class);
criteria.add(Restrictions.like("column", value, MatchMode.ANYWHERE));

It will match the value anywhere in the column string. You can use different types of modes.

Reference page: https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/criterion/MatchMode.html

DetachedCriteria:

- Detached criteria is very good alternate when the hibernate session is not present.

Using a DetachedCriteria is exactly the same as a Criteria except you can do the initial creation and setup of your query without having access to the session. When it comes time to run your query, you must convert it to an executable query with getExecutableCriteria(session).

This is useful if you are building complicated queries, possibly through a multi-step process, because you don't need access to the Session everywhere. You only need the Session at the final step when you run the query.

Under the hood, DetachedCriteria uses a CriteriaImpl which is the same class you get if you call session.createCriteria().

Answer 2

Use the enum MatchMode to help you with it:

Criterion c1 = Restrictions.like("AttributeName", "AttributeValue", MatchMode.END);

Don't use any special character, like * Referencce Read This Like

Answer 3

I recommend using a Query object instead of the Criteria object, in this case.

I can't remember if Criteria passes things it doesn't understand straight through to the database like a Query object will do. Basically what this would mean is that if you used a function that was database specific that the Hibernate parser didn't understand, it would pass it "as is" to the database.

Example:

Query queryObject = session.createQuery("from ClassName where VARIABLENAME like '%'||anyvaluehere||'%' order by VARIABLENAME");
List<YourClass> resultList= queryObject.list();

For more see here

Answer 4

You can use like() restriction criteria like this:

session = sessionFactory.openSession();
Criteria query = session.createCriteria(Pojo.class);
query.add(Restrictions.like("anycolumn", "anyvalue", MatchMode.START));

It will give you a list of strings that start with "anyvalue".

Answer 5

when field type is not String, it couse java.lang.ClassCastException

thats because it only works with string/varchar fields/columns.