Login/Register
SQL 'LIKE' operator in Hibernate Criteria API
Asked Answered
Solved javahibernatecriteriasql-likehibernate-criteria
P

5

14

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?

Pack answered 4/7, 2012 at 11:41 Comment(1)
What is the exception message? – Pyroligneous
A
-1

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

Appointment answered 4/7, 2012 at 12:9 Comment(1)
Can you show small example please. Especially how to implement sorting – Pack
T
29

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".

Thief answered 5/3, 2013 at 12:4 Comment(0)
G
12

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

Ginni answered 18/3, 2015 at 1:8 Comment(1)
MatchMode.ANYWHERE will match the value anywhere in the column string... but what does the DetachedCriteria do? – Shepard
P
0

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

Purdah answered 2/11, 2016 at 11:27 Comment(0)
O
-1

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

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

Outwork answered 4/7, 2012 at 11:58 Comment(4)
So is there a way to implement sql 'like' behavior with criteria? – Pack
yeah loads of different ways, but how would you "like" a boolean for example ? Or an Integer ? Or Foo ? – Outwork
Like it working in sql. Here you can select anything dont care about column type. – Pack
@Pack Its oracle specific then, and certainly not applicable to hibernate – Outwork
A
-1

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

Appointment answered 4/7, 2012 at 12:9 Comment(1)
Can you show small example please. Especially how to implement sorting – Pack

© 2022 - 2024 — McMap. All rights reserved.