String productTypeStr="1|2|3"; String hql="FROM UserExperience u WHERE u.productTypeStr regexp ('"+productTypeStr+"')"; List<UserExperience> userExperienceList=userExperienceDao.find(hql); 报异常:ERROR2013-08-16 11:06:15 ErrorCounter - line 1:62: unexpected token: regexp WARN2013-08-16 11:06:15 HqlParser - processEqualityExpression() : No expression to process! WARN2013-08-16 11:06:16 AbstractHandlerExceptionResolver - Handler execution resulted in exception org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')] at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912) at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904) at com.api.dao.impl.GenericDaoImpl.find(GenericDaoImpl.java:205) at com.api.dao.impl.GenericDaoImpl$$FastClassByCGLIB$$3c9f598c.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621) at com.api.dao.impl.GenericDaoImpl$$EnhancerByCGLIB$$49f70d2.find(<generated>) at com.api.service.impl.UserDemandExperienceServiceImpl.addUserDemandExperience(UserDemandExperienceServiceImpl.java:258) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy39.addUserDemandExperience(Unknown Source) at com.api.controller.UserDoDemandController.test(UserDoDemandController.java:83) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549) at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:163) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:556) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:401) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:267) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:245) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:260) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')] at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94) at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156) at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135) at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651) at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:914) at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406) ... 66 more
先在MYSQL本身的命令行工具中测试SQL语句是否正确,然后再到程序中检查。
测试了,可以查出数据:SELECT * FROM user_experience u WHERE u.`product_type_str` REGEXP ('38|1|2')貌似在hql中不能使用正则函数, 现在换了方式查询,解决了代码如下: String productTypeStr="38|1|2"; String sql="select * from user_experience u WHERE u.product_type_str regexp ('"+productTypeStr+"')"; List<UserExperience> userExperienceList=userExperienceDao.findbySql(sql, UserExperience.class); // 使用HSQL语句检索数据 @Override public List<T> findbySql(String sql,Class<T> clazz) { Session session = getHibernateTemplate().getSessionFactory().openSession(); SQLQuery s = session.createSQLQuery(sql); List<T> list=s.addEntity(clazz).list(); session.close(); return list; }
当table2表中的str2为1,2,3 ,则table1中id为1,2,3的数据都要查出来。
当table2表中的str2为2,3,4,则table1中id为1,2,5的数据都要查出来。
试过,不行,我的要求是,只要table1中str1字段里面“有一个”符合table2中str2中的值,就要查出来
REGEXP 正则的实现两个字符串组的匹配。
最近MySQL版块中类似问题出现得比较多。总结了一下。 由于某些原因,有时候我们没有按照范式的设计准则而把一些属性放到同一个字符串字段中。比如个人兴趣,有时候我们设计表为create table members (uid int primary key,uname varchar(20),hobby varchar(100));表中内容如下mysql> select * from ...
select * from table1 inner join table2 on instr(str1,str2)>0
第二句写错了,是查出1,2,5来.总之,我的意思是,table2中的str2作为查询条件,然后table1中的str1中的值只要有一个匹配到条件,则被查询出来。如:条件为1,2,3时table1中第一条、第二条和第三条数据的str1中都有1,则都满足查询条件。
String productTypeStr="1|2|3";
String hql="FROM UserExperience u WHERE u.productTypeStr regexp ('"+productTypeStr+"')";
List<UserExperience> userExperienceList=userExperienceDao.find(hql); 报异常:ERROR2013-08-16 11:06:15 ErrorCounter - line 1:62: unexpected token: regexp
WARN2013-08-16 11:06:15 HqlParser - processEqualityExpression() : No expression to process!
WARN2013-08-16 11:06:16 AbstractHandlerExceptionResolver - Handler execution resulted in exception
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')]
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904)
at com.api.dao.impl.GenericDaoImpl.find(GenericDaoImpl.java:205)
at com.api.dao.impl.GenericDaoImpl$$FastClassByCGLIB$$3c9f598c.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.api.dao.impl.GenericDaoImpl$$EnhancerByCGLIB$$49f70d2.find(<generated>)
at com.api.service.impl.UserDemandExperienceServiceImpl.addUserDemandExperience(UserDemandExperienceServiceImpl.java:258)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy39.addUserDemandExperience(Unknown Source)
at com.api.controller.UserDoDemandController.test(UserDoDemandController.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:163)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:556)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:401)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:267)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:245)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:260)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: regexp near line 1, column 62 [FROM com.api.entity.UserExperience u WHERE u.productTypeStr regexp ('38|1|2')]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:914)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
... 66 more
测试了,可以查出数据:SELECT * FROM user_experience u WHERE u.`product_type_str` REGEXP ('38|1|2')貌似在hql中不能使用正则函数,
现在换了方式查询,解决了代码如下: String productTypeStr="38|1|2";
String sql="select * from user_experience u WHERE u.product_type_str regexp ('"+productTypeStr+"')";
List<UserExperience> userExperienceList=userExperienceDao.findbySql(sql, UserExperience.class);
// 使用HSQL语句检索数据
@Override
public List<T> findbySql(String sql,Class<T> clazz) {
Session session = getHibernateTemplate().getSessionFactory().openSession();
SQLQuery s = session.createSQLQuery(sql);
List<T> list=s.addEntity(clazz).list();
session.close();
return list;
}