首页 新闻 会员 周边 捐助

想实现一个查询指定时间段HQL查询语句（包括年月日时分秒的时间段查询），下面是我拼接的查询语句，总是报错，帮忙看看怎么改？

0
悬赏园豆：30 [已关闭问题] 关闭于 2017-02-07 10:47
复制代码
   @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;
    }
复制代码
HQL
李序锴的主页 李序锴 | 初学一级 | 园豆：67
提问于：2017-02-04 11:57
< >
分享
所有回答(2)
0

日期是不是少了单引号？

调试一下，把完整的SQL语句贴上来看下。

chengeng | 园豆：294 (菜鸟二级) | 2017-02-04 14:11
0

已解决，得出结论是hql不能直接用字符串拼接特殊类型的字段，比如日期类型，修改拼接代码如下：

@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 + "%' " );
        }
        
        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:27
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。