首页 新闻 搜索 专区 学院

想做个多条件查询,输入“集中器地址、数据类型”等条件都可以查询成功,但是一输入日期范围查询后台就报错,哪位朋友帮忙解答解答?

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
复制代码
李序锴的主页 李序锴 | 初学一级 | 园豆:67
提问于:2017-02-04 10:15
< >
分享
最佳答案
1

你数据库的日期类型也是string吗?

收获园豆:50
让我发会呆 | 老鸟四级 |园豆:2642 | 2017-02-04 10:25

1.看是否忘记将hibernate的映射文件添加到Hibernate.cfg.xml(使用Hibernate时)或者applicationContext.xml中

2.检查表中的字段和映射文件中的字段是否一一对应

3.检查字段名是否使用了数据库中的关键字

4.HQL语句是否正确

让我发会呆 | 园豆:2642 (老鸟四级) | 2017-02-04 10:27

我数据库里面的是Date类型

李序锴 | 园豆:67 (初学一级) | 2017-02-04 10:27

@李序锴:  那你把date date1转成Date类型,试试

让我发会呆 | 园豆:2642 (老鸟四级) | 2017-02-04 10:28

@让我发会呆: 我比较了一下其他查询条件跟日期范围查询的语句差别,确实有点出入,你看看有啥问题没

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
李序锴 | 园豆:67 (初学一级) | 2017-02-04 11:03

@李序锴: 

hql不能直接用字符串拼接特殊类型的字段 比如日期什么的,要用占位符才行

Query q = XXX.createQuery("from AMDCoupons tc where tc.endDate > :endate order by tc.couponsId desc ") q.setDate("endate",这里是传入的时间类型);网上说的

让我发会呆 | 园豆:2642 (老鸟四级) | 2017-02-04 11:06

@让我发会呆: 能否发下链接,我想看看说明

李序锴 | 园豆:67 (初学一级) | 2017-02-04 11:35

@李序锴: http://www.oschina.net/question/1589424_163486?sort=time

直接找度娘。

让我发会呆 | 园豆:2642 (老鸟四级) | 2017-02-04 11:38

@让我发会呆: 多谢,我把代码改成如下就可以正常运行了

    @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;
    }
李序锴 | 园豆:67 (初学一级) | 2017-02-04 14:16
其他回答(1)
0

语法报错,日期加个单引号试试。

chengeng | 园豆:294 (菜鸟二级) | 2017-02-04 14:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册