想做个多条件查询，输入“集中器地址、数据类型”等条件都可以查询成功，但是一输入日期范围查询后台就报错，哪位朋友帮忙解答解答？
0
悬赏园豆：50
[已解决问题]
解决于 2017-02-04 14:17
我的查询是这样拼接查询条件的
@Override public PageUtil<MessageEntity> query(PageUtil<MessageEntity> page) { String concentratorAddress = (String) page.getParameter("concentratorAddress"); String type = (String) page.getParameter("type"); String analytical = (String) page.getParameter("analytical"); String date = (String) page.getParameter("date"); String date1 = (String) page.getParameter("date1"); String sWhere = "signal = 1"; if (StringUtils.isNotEmpty(concentratorAddress)) { sWhere = "concentratorAddress = " + concentratorAddress; } if (StringUtils.isNotEmpty(type)) { sWhere += " AND type = " + type; } if (StringUtils.isNotEmpty(analytical)) { sWhere += " AND analytical like '%" + analytical + "%' "; } if(StringUtils.isNotEmpty(date)){ sWhere += " AND datetime >= " + date ; } if(StringUtils.isNotEmpty(date1)){ sWhere += " AND datetime <= " + date1 ; } System.out.println("sWhere:" + sWhere); int count = messageDao.count(sWhere); page.setTotalSize(count); if (count != 0) { List<MessageEntity> messageList = messageDao.queryByPage(page .getBegin() - 1, page.getPageSize(), sWhere, " id "); page.setResultSet(messageList); } return page; }
查询集中器地址后台显示如下（这是正常的情况）：
dispatchMethod:query sWhere:concentratorAddress = 22 Hibernate: select count(*) as col_0_0_ from T_BI_LOG messageent0_ where messageent0_.CONCENTRATOR_ADDRESS=22 Hibernate: select * from ( select messageent0_.DATA_ID as DATA1_44_, messageent0_.CONCENTRATOR_ADDRESS as CONCENTR2_44_, messageent0_.DATA_TYPE as DATA3_44_, messageent0_.DATA_DI as DATA4_44_, messageent0_.DATA_CONTENT as DATA5_44_, messageent0_.DATE_TIME as DATE6_44_, messageent0_.DATA_ANALYTICAL as DATA7_44_, messageent0_.DATA_UPDATETIME as DATA8_44_, messageent0_.DATA_STATE as DATA9_44_ from T_BI_LOG messageent0_ where messageent0_.CONCENTRATOR_ADDRESS=22 order by messageent0_.DATA_ID ) where rownum <= ?
查询日期范围后台报错如下：
dispatchMethod:query sWhere:signal = 1 AND datetime >= 2017-02-04 10:02:36 AND datetime <= 2017-02-04 10:02:38 testone org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: 10 near line 1, column 109 [ SELECT count(*) FROM com.hd.imrs.maintain.entity.MessageEntity WHERE signal = 1 AND datetime >= 2017-02-04 10:02:36 AND datetime <= 2017-02-04 10:02:38]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 10 near line 1, column 109 [ SELECT count(*) FROM com.hd.imrs.maintain.entity.MessageEntity WHERE signal = 1 AND datetime >= 2017-02-04 10:02:36 AND datetime <= 2017-02-04 10:02:38] at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:657) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921) at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:913) at com.hd.imrs.dao.HibernateDaoImpl.count(HibernateDaoImpl.java:166) at com.hd.imrs.maintain.service.impl.MessageServiceImpl.query(MessageServiceImpl.java:96) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy442.query(Unknown Source) at com.hd.imrs.maintain.action.ManipulLogAction.query(ManipulLogAction.java:44) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270) at com.hd.imrs.action.BaseAction.dispatchMethod(BaseAction.java:93) at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187) at com.hd.imrs.action.BaseAction.execute(BaseAction.java:48) at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:110) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.hd.imrs.filter.SecurityFilter.doFilter(SecurityFilter.java:95) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.hd.imrs.filter.LogFilter.doFilter(LogFilter.java:67) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.hd.imrs.filter.CharsetEncodingFilter.doFilter(CharsetEncodingFilter.java:33) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 10 near line 1, column 109 [ SELECT count(*) FROM com.hd.imrs.maintain.entity.MessageEntity WHERE signal = 1 AND datetime >= 2017-02-04 10:02:36 AND datetime <= 2017-02-04 10:02:38] at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31) at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24) at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72) at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133) at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112) at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623) at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:923) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419) ... 59 more
最佳答案
1
你数据库的日期类型也是string吗？
收获园豆：50
1.看是否忘记将hibernate的映射文件添加到Hibernate.cfg.xml（使用Hibernate时）或者applicationContext.xml中
2.检查表中的字段和映射文件中的字段是否一一对应
3.检查字段名是否使用了数据库中的关键字
4.HQL语句是否正确
我数据库里面的是Date类型
@李序锴: 那你把date date1转成Date类型，试试
@让我发会呆: 我比较了一下其他查询条件跟日期范围查询的语句差别，确实有点出入，你看看有啥问题没
select count(*) as col_0_0_ from T_BI_LOG messageent0_ where signal=1 and (messageent0_.DATA_ANALYTICAL like '%8%')
SELECT count(*) FROM com.hd.imrs.maintain.entity.MessageEntity WHERE signal = 1 AND datetime >= 2016-08-10 09:31:40 AND datetime <= 2016-11-01 09:31:51
@李序锴:
hql不能直接用字符串拼接特殊类型的字段 比如日期什么的，要用占位符才行
Query q = XXX.createQuery("from AMDCoupons tc where tc.endDate > :endate order by tc.couponsId desc ") q.setDate("endate",这里是传入的时间类型);网上说的
@让我发会呆: 能否发下链接，我想看看说明
@李序锴: http://www.oschina.net/question/1589424_163486?sort=time
直接找度娘。
@让我发会呆: 多谢，我把代码改成如下就可以正常运行了
@Override public PageUtil<MessageEntity> query(PageUtil<MessageEntity> page) { String concentratorAddress = (String) page.getParameter("concentratorAddress"); String type = (String) page.getParameter("type"); String analytical = (String) page.getParameter("analytical"); String formatbgString = (String) page.getParameter("date"); String formatendString = (String) page.getParameter("date1"); // Date formatbgString = null; // Date formatendString = null; // if(date != null || date1 != null){ // formatbgString = StrToDate(date); // formatendString = StrToDate(date1); // } // String sWhere = "signal = 1"; StringBuilder where = new StringBuilder("signal = 1"); if (concentratorAddress != null && !"".equals(concentratorAddress)) { where.append(" AND concentratorAddress=" + concentratorAddress ); } if (type != null && !"".equals(type)) { where.append(" AND type=" + type ); } if (analytical != null && !"".equals(analytical)) { where.append(" AND analytical like '%" + analytical + "%' " ); } // String formatbgString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date); // String formatendString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date1); if (formatbgString != null && !"".equals(formatbgString)) { where.append("and datetime > to_date('" + formatbgString + "', 'yyyy-MM-dd HH24:mi:ss') "); } if (formatendString != null && !"".equals(formatendString)) { where.append("and datetime < to_date('" + formatendString + "', 'yyyy-MM-dd HH24:mi:ss') "); } // if (date1 != null && !"".equals(date1)) { // where.append("and to_number(to_char(applyTime,'yyyy-mm-dd hh24:mi:ss')) <= ?" + date1); // } // if (StringUtils.isNotEmpty(concentratorAddress)) { // sWhere = "concentratorAddress = " + concentratorAddress; // } // if (StringUtils.isNotEmpty(type)) { // sWhere += " AND type = " + type; // } // if (StringUtils.isNotEmpty(analytical)) { // sWhere += " AND analytical like '%" + analytical + "%' "; // } // System.out.println("dateMin:" + dateMin); // System.out.println("dateMax:" + dateMax); // if(StringUtils.isNotEmpty(date)){ // sWhere += " AND datetime >= " + date ; // } // if(StringUtils.isNotEmpty(date1)){ // sWhere += " AND datetime <= " + date1 ; // } // System.out.println(date); // System.out.println(date1); // sWhere += " AND datetime >= Tue Nov 01 17:22:24 CST 2016"; // sWhere += " AND datetime <= Mon Jan 23 17:22:37 CST 2017"; // sWhere += " AND datetime >= 2016-11-23 " ; // sWhere += " AND datetime <= 2017-01-01 " ; // if (StringUtils.isNotEmpty(date) && StringUtils.isNotEmpty(date1) ) { // sWhere += " AND datetime <='" + date1 + "' AND '" + date + "'<= datetime"; // } // System.out.println("sWhere:" + sWhere); String HQL = where.toString(); int count = messageDao.count(HQL); page.setTotalSize(count); if (count != 0) { List<MessageEntity> messageList = messageDao.queryByPage(page .getBegin() - 1, page.getPageSize(), HQL, " id "); page.setResultSet(messageList); } return page; }
其他回答(1)
0
语法报错，日期加个单引号试试。
