在条件中实现sql'like'。过冬 [英] Implement sql 'like' in criteria. Hibernate

问题描述

我想用hibernate Criteria 来实现一些通用的过滤器。它应该像一样工作，如命令是sql：

 从表中选择* where table.ANYCOLOUMNHERE like'％'|| anyvaluehere ||'％'
  

我有 Map< String，String> 其中key是列名，并为其值赋值）。 （Entry< String，String> filter：filters.entrySet（））{
crit.add（限制条件）。/ p>

  ilike（filter.getKey（），filter.getValue（），MatchMode.ANYWHERE））; 
} 
  

但是当字段类型不是字符串时，它会引用 java.lang.ClassCastException 。

  [com.nsn.util.LoggerUtilerror]（http-localhost -127.0.0.1-8080-1）获取警报时出错：java.lang.ClassCastException：无法在org.hibernate.type.descriptor.java.LongTypeDescriptor中将java.lang.String转换为java.lang.Long 
 .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] 
在org.hibernate.type.AbstractStandardBasicType.nullSafeSet（AbstractStandardBasicType.java:305）[hibernate-core-4.1.1.Final.jar ：4.1.1.Final] 
在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] 
在org.hibernate.loader.Loader.bindParameterValues（Loader.java:1862）[hibernate-core-4.1.1.Final.jar：4.1.1 .Final] 
在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] 
在org.hibernate.loader.Loader.doList（Loader.java:2447）[hibernate-core-4.1 .1.Final.jar：4.1.1.Final] 
在org.hibernate.loader.Loader.doList（Loader.java:2433）[hibernate-core-4.1.1.Final.jar：4.1.1 .Final] 
在org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2263）[hibernate-core-4.1 .1.Final.jar：4.1.1.Final] 
在org.hibernate.loader.Loader.list（Loader.java:2258）[hibernate-core-4.1.1.Final.jar：4.1.1 .Final] 
在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] 
在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：] $ b $ org.primefaces.component.datatable.DataTable Renderer.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：] 
，位于javax.faces.webapp.FacesServlet。服务（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）[jbos sweb-7.0.13.Final.jar：] 
 at org.apache.catalina.core.ApplicationFilterChain.doFilter（ApplicationFilterChain.java:248）[jbossweb-7.0.13.Final.jar：] 
 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] 
在org.apache.catalina.core.StandardHostValve.invoke（StandardHostValve.java:155）[jbossweb-7.0.13.Final.jar：] 
在org.apache.catalina.valves.ErrorReportValve。调用（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] 
  

是否可以解决这个问题？

Query对象而不是 Criteria对象，在这种情况下。

我不记得Criteria是否像Query对象那样将不理解的东西直接传递给数据库。基本上这意味着如果你使用了一个特定于数据库的函数，而Hibernate解析器不理解，那么它会按照原样传递给数据库。

<例如：

  Query queryObject = session.createQuery（from ClassName where VARIABLENAME like'％'|| anyvaluehere ||' ％'order by VARIABLENAME）; 
列表< YourClass> resultList = queryObject.list（）; 
  

欲了解更多信息，请参阅 here

I want to implement some universal filter with hibernate Criteria. It should work like like command is sql:

Select * from table where table.ANYCOLOUMNHERE like '%'||anyvaluehere||'%'

I have Map<String, String> where key is column name, and value 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 couse 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 it way to resolve this?

解决方案

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

这篇关于在条件中实现sql'like'。过冬的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！