时间戳

首页 > 解决方案 > 如何使用 DBUtil. ​​查找 SQL fetchlist 中的错误？

java - 如何使用 DBUtil. ​​查找 SQL fetchlist 中的错误？

问题描述

我的 DBUtil 中有 fetchList 方法，如下所示。

public static List fetchList(String cols, String tables, String conditions) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    List lst = null;
    try {
        if (s != null) {
            log.debug("session is " + s.toString());
        }
        else {
            log.debug("session is null");
        }
        Query q = s.createQuery(setColumn(cols) + " from " + tables + setConditions(conditions));
        log.info("Prepared Query: "+q.toString());
        q.setCacheable(true);
        lst = q.list();
    } catch (HibernateException e) {
        log.error("DBUtil fetchlist1 error:", e);
    } finally {
        s.close();
    }
    return lst;
}

我正在传递以下参数进行查询。

List lst = DBUtil.fetchList("new map( COUNT(distinct username) as DistinctUser,\r\n" + 
            " COUNT(IF(searchParam='Account Sync',1,NULL)) AS 'AccountSync',\r\n" + 
            " COUNT(IF(searchParam='Account Info',1,NULL)) AS 'AccountInfo',\r\n" + 
            " COUNT(IF(searchParam='spectrum.net',1,NULL)) AS 'spectrum.net',\r\n" + 
            " COUNT(IF(searchParam='STVA',1,NULL)) AS 'STVA',\r\n" + 
            " COUNT(IF(searchParam like 'TVE%',1,NULL)) AS 'TVE',\r\n" + 
            " COUNT(IF(searchParam='sbnet',1,NULL)) AS 'sbnet',\r\n" + 
            " COUNT(IF(searchParam='OOH Info',1,NULL)) AS 'OOHInfo')", "CareToolAuditBO" , "date(accesseddate)='"+reportDate+"' AND username<>'Scheduled'");

但它给出了如下的mysql错误。

       at com.caretool.controller.UsageEmailController.getEmailReport(UsageEmailController.java:35)
        at com.caretool.scheduler.UsageReportMailTask.run(UsageReportMailTask.java:15)
        at java.util.TimerThread.mainLoop(Timer.java:555)
        at java.util.TimerThread.run(Timer.java:505)
2019-08-12 20:32:00 ERROR DBUtil:193 - DBUtil fetchlist1 error:
org.hibernate.hql.internal.ast.QuerySyntaxException: **expecting CLOSE, found '(' near line 2, column 10** [select new map( COUNT(distinct username) as DistinctUser,
 COUNT(IF(searchParam='Account Sync',1,NULL)) AS 'AccountSync',
 COUNT(IF(searchParam='Account Info',1,NULL)) AS 'AccountInfo',
 COUNT(IF(searchParam='spectrum.net',1,NULL)) AS 'spectrum.net',
 COUNT(IF(searchParam='STVA',1,NULL)) AS 'STVA',
 COUNT(IF(searchParam like 'TVE%',1,NULL)) AS 'TVE',
 COUNT(IF(searchParam='sbnet',1,NULL)) AS 'sbnet',
 COUNT(IF(searchParam='OOH Info',1,NULL)) AS 'OOHInfo') from com.caretool.bean.dbBean.CareToolAuditBO where date(accesseddate)='2019-08-12' AND username<>'Scheduled']
        at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
        at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)

我没有看到任何语法错误。但仍然显示错误。请帮忙。

标签： javamysqlarraysapachemaven

解决方案

Remove new map and check it   

 List lst = DBUtil.fetchList(" COUNT(distinct username) as DistinctUser,\r\n" + 
                " COUNT(IF(searchParam='Account Sync',1,NULL)) AS 'AccountSync',\r\n" + 
                " COUNT(IF(searchParam='Account Info',1,NULL)) AS 'AccountInfo',\r\n" + 
                " COUNT(IF(searchParam='spectrum.net',1,NULL)) AS 'spectrum.net',\r\n" + 
                " COUNT(IF(searchParam='STVA',1,NULL)) AS 'STVA',\r\n" + 
                " COUNT(IF(searchParam like 'TVE%',1,NULL)) AS 'TVE',\r\n" + 
                " COUNT(IF(searchParam='sbnet',1,NULL)) AS 'sbnet',\r\n" + 
                " COUNT(IF(searchParam='OOH Info',1,NULL)) AS 'OOHInfo'", "CareToolAuditBO" , "date(accesseddate)='"+reportDate+"' AND username<>'Scheduled'");

推荐阅读