H2用户定义的聚合函数ListAgg不能在第一个参数上使用DISTINCT或TRIM()

问题描述

因此，我有一个DB2生产数据库，需要在其中使用可用的功能ListAgg.我希望使用H2的单元测试能够正确测试此功能.不幸的是，H2不直接支持ListAgg.但是，我可以创建用户定义的聚合函数...

So I have a DB2 production database in which I need to use the available function ListAgg. I would like my unit test, which is using H2, to correctly test this functionality. Unfortunately H2 does not support ListAgg directly. I can, however, create a user defined aggregate function...

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.h2.api.AggregateFunction;

import com.google.common.base.Joiner;

public class ListAgg implements AggregateFunction
{

private List<String> values = new ArrayList<String>();
private String delimiter = ",";

@Override
public void init(Connection conn) throws SQLException
{
}

@Override
public int getType(int[] inputTypes) throws SQLException
{
    if (inputTypes.length != 2) {
        throw new java.sql.SQLException("The aggregate function ListAgg must have 2 arguments.");
    }
    return java.sql.Types.VARCHAR;
}

@Override
public void add(Object sqlValues) throws SQLException
{
    Object[] objects = (Object[]) sqlValues;
    this.delimiter = (String) objects[1];
    String value = (String) objects[0];
    values.add(value);
}

@Override
public Object getResult() throws SQLException
{
    return Joiner.on(delimiter).join(values);
}

}

我做到了.

ListAgg(columnName, ',')

但是失败了

ListAgg(DISTINCT TRIM(columnName), ',')

我想念什么?

我收到以下错误消息:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar [select ..., LISTAGG(DISTINCT TRIM(columnName), ',') as
columnName_LIST, from ... group by ...]; nested exception is
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT ... ";
expected "NOT, EXISTS, INTERSECTS, SELECT, FROM"; SQL statement:
select ... from ... group by ... [42001-174]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

COUNT()是否也实现了AggregateFunction还是在内部使用了其他东西?因为H2能够处理 COUNT(DISTINCT columnName)

Does COUNT() also implement AggregateFunction or is it using something else internally? Because H2 is able to handle COUNT(DISTINCT columnName)

推荐答案

我不认为H2允许您在自定义聚合函数上使用 DISTINCT 关键字.但是您可以轻松定义 ListAgg 的不同"版本:

I don't think H2 allows you to use the DISTINCT keyword on your custom aggregate functions. But you can easily define your own "distinct" version of ListAgg:

public class DistinctListAgg implements AggregateFunction {
    private Set<String> values = new LinkedHashSet<String>();
    // The rest is the same
}

这篇关于H2用户定义的聚合函数ListAgg不能在第一个参数上使用DISTINCT或TRIM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！