mysql - 如何在带有Gorm的Grails中使用GROUP_CONCAT

我需要在Grails中使用GROUP_CONCAT聚合函数，最好使用HQL，但也可以使用标准。

我有这个查询:

ClickTracking.executeQuery("SELECT pageId, containerId, GROUP_CONCAT(clicks) as click" +
                                            "FROM  ClickTracking " +
                                            "WHERE pageId =  ? " +
                                            "GROUP BY containerId ", [pageId])

这是行不通的，因为HQL不了解GROUP_CONCAT，因为它是特定于数据库的。我可以将我的项目与MySQL绑定(bind)，所以我尝试在BootStrap.groovy中添加它:

Configuration conf = grailsApplication.getMainContext().getBean("&sessionFactory").configuration;
conf.addSqlFunction("GROUP_CONCAT", new StandardSQLFunction("GROUP_CONCAT", new StringType()));

没运气。

然后，我尝试对方言进行子类化并使用它:

import org.hibernate.dialect.MySQL5InnoDBDialect
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.Hibernate

class ExtendedMySqlDialect extends MySQL5InnoDBDialect {

public ExtendedMySqlDialect() {
    super();
    registerFunction("GROUP_CONCAT", new StandardSQLFunction("GROUP_CONCAT", Hibernate.STRING));

}

}

和在DataSource.groovy中

dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
dialect = "ExtendedMySqlDialect"
logSql = true

}

仍然没有运气。我得到:

No data type for node: org.hibernate.hql.ast.tree.MethodNode

-[METHOD_CALL] MethodNode:'('
+-[METHOD_NAME] IdentNode:“GROUP_CONCAT” {originalText = GROUP_CONCAT}
-[EXPR_LIST] SqlNode:“exprList”
-[DOT] DotNode:'clicktrack0_.clicks'{propertyName = clicks，dereferenceType = ALL，propertyPath = clicks，path = {synthetic-alias} .clicks，tableAlias = clicktrack0_，className = com.ui.gorm.ClickTracking，classAlias =空值}
+-[IDENT] IdentNode:'{synthetic-alias}'{originalText = {synthetic-alias}}
-[IDENT] IdentNode:“点击” {originalText = clicks}
。 Stacktrace如下:
消息:节点没有数据类型:org.hibernate.hql.ast.tree.MethodNode
-[METHOD_CALL] MethodNode:'('
+-[METHOD_NAME] IdentNode:“GROUP_CONCAT” {originalText = GROUP_CONCAT}
-[EXPR_LIST] SqlNode:“exprList”
-[DOT] DotNode:'clicktrack0_.clicks'{propertyName = clicks，dereferenceType = ALL，propertyPath = clicks，path = {synthetic-alias} .clicks，tableAlias = clicktrack0_，className = com.ui.gorm.ClickTracking，classAlias =空值}
+-[IDENT] IdentNode:'{synthetic-alias}'{originalText = {synthetic-alias}}
-[IDENT] IdentNode:“点击” {originalText = clicks}

Line | Method

->> 156 | org.hibernate.hql.ast.tree.SelectClause中的initializeExplicitSelectClause

如果设置断点，然后查看grailsApplication.getMainContext()。getBean(“＆sessionFactory”)。configuration，则可以在其中找到一个名为sqlFuncions的属性和GROUP_COCNAT。

我进行了一些调试，最终通过以下代码进入SelectExpressionList.java:

public SelectExpression[] collectSelectExpressions() {
    // Get the first child to be considered.  Sub-classes may do this differently in order to skip nodes that
    // are not select expressions (e.g. DISTINCT).
    AST firstChild = getFirstSelectExpression();
    AST parent = this;
    ArrayList list = new ArrayList( parent.getNumberOfChildren() );
    for ( AST n = firstChild; n != null; n = n.getNextSibling() ) {
        if ( n instanceof SelectExpression ) {
            list.add( n );
        }
        else {
            throw new IllegalStateException( "Unexpected AST: " + n.getClass().getName() + " " + new ASTPrinter( SqlTokenTypes.class ).showAsString( n, "" ) );
        }
    }
    return ( SelectExpression[] ) list.toArray( new SelectExpression[list.size()] );
}

似乎n = n.getNextSibbling()以某种方式与group_concat混为一谈，但这很奇怪，因为它来自antlr包。

无论如何，我被困住了，我很好奇如何使用group_concat(或带有gorm的grails中的任何其他数据库特定的函数)。我正在使用grails 2.0.4

最佳答案

尝试这样的事情:

Yourdomain.withSession{ session ->
   session.createSQLQuery(yourQueryWithGroup_concat).setLong(yourParameterName,yourParameterValue).list()
}

关于mysql - 如何在带有Gorm的Grails中使用GROUP_CONCAT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18316199/