标准 API 和 JPQL API 与分组依据和GROUP_CONCAT与不同 / 排序依据 / 分离器支持？

Question

使用JPA Criteria API，我想按列分组，并连接另一列的值。

例如，下面是sql方法，我正在寻找等价的标准查询(和jpql查询)方法。

mysql> select *from GroupConcatenateDemo;
+------+-------+
| Id   | Name  |
+------+-------+
|   10 | Larry |
|   11 | Mike  |
|   12 | John  |
|   10 | Elon  |
|   10 | Bob   |
|   11 | Sam   |
+------+-------+

使用 SQL 进行分组

mysql> select Id,group_concat(Name SEPARATOR ',') as GroupConcatDemo from GroupConcatenateDemo group by Id;

+------+-----------------+
| Id   | GroupConcatDemo |
+------+-----------------+
|   10 | Larry,Elon,Bob  |
|   11 | Mike,Sam        |
|   12 | John            |
+------+-----------------+

标准/ JPQL查询有相当于<鳕鱼

我已经检查并测试了这两个API，它们似乎都只提供了＜code＞concat＜/code＞函数，这与SQL＜code＞group_concat

编辑-

我想出了如何注册数据库函数 - 我可以使用标准API中的GROUP_CONCAT函数。为此，我必须添加一个自定义方言类，并通知spring（boot）有关该类的信息。

package com.mypackage;

import org.hibernate.dialect.MySQL8Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomMySQLDialect extends MySQL8Dialect {
    public CustomMySQLDialect() {
        super();

        registerFunction(
                "GROUP_CONCAT",
                new StandardSQLFunction(
                        "GROUP_CONCAT",
                        StandardBasicTypes.STRING
                )
        );
    }
} 

然后在应用程序中通知spring boot这个类。属性-
spring.jpa.properties.hibernate。方言=com.mypackage.custommysqldialent

虽然它在工作，但有一些问题-

1. 我不知道如何使用分隔符，我想使用默认的、以外的分隔符（逗号）
2. 我还想使用group_concat的＜code＞DISTINCT＜/code＞、＜code＞ORDER BY＜/code＞特性
   我如何通过标准api传递这些信息

目前的情况。< br >目前我的标准查询的< code>group_concat代码部分如下所示

some other selects... , cb.function("GROUP_CONCAT", String.class, packagesJoin.get("packageName")), some other selects   

生成的 sql 部分是 - GROUP_CONCAT（packages4_.package_name） 作为 col_3_0_，.

输出是- 包-1，包-1，包-2，包-2

SOF建议的情况 -
就像@jens-schauder建议的那样（感谢jens） - 如果我使用

cb.function( "group_concat "，String.class，cb.concat( root.get("name ")，cb.literal("，")

i、 e代码为
cb。函数（“GROUP_CONCAT”，String.class，packagesJoin.get（“packageName”），cb.literal（“，”），

生成的sql -

GROUP_CONCAT(packages4_.package_name,
        ',') as col_3_0_,

输出为：

Package-1,,Package-1,,Package-2,,Package-2,

这种方法的问题是中的，。文字（“，”与列值连接。这不应该发生，也不应该解决。

想要/想要的情况-我想要生成的SQL是-＜br＞＜code＞GROUP_CONCAT（不同的packages4_ package_name ORDER BY packages 4_。

和期望输出值

Package-2 # Package-1

我还应该向条件查询添加什么。任何答案都将非常感谢……这对我来说非常关键。

Answer 1

您可以使用<代码> CriteriaBuilder.function > < /代码

我没有看到一种模仿SEATCH '，'语法的简单方法。相反，您可以做的是在调用group_concat之前将分隔符附加到字段。您需要剥离最后一个“，”。

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root root = cq.from(Demo.class);
cq.select(
    cb.function(
        "group_concat", 
        String.class, 
        cb.concat( 
            root.get("name"), 
            cb.literal(",")
        )
    )
)

本文提到，在 select 子句中使用函数时，需要注册该函数。

如何做到这一点在 https://stackoverflow.com/a/52725042/66686 中进行了解释。它甚至可能允许创建自定义 SQL，这些 SQL 可用于呈现 SEPERATOR 子句。

Answer 2

其中一个解决方案是创建一个自定义<代码> G

• 1： 聚合列的名称。
• 2： true\false：使用不同或不使用
• 3： 用于连接的分隔符
• 4： 订购人的列名
• 5： 排序类型ASC/DESC

正在翻译:< code>GROUP_CONCAT(按名称DESC分隔符' # '排序的不同名称)

请注意:实现并不处理< code>GROUP_CONCAT函数的所有可能用例，例如未处理的限制参数和用于排序的几个列。但是可以延长。当前的实现完全解决了所描述的问题。

1.使用处理不同/ ORDER BY /分隔符参数的逻辑扩展StandardSQLFunction

public class GroupConcatFunction extends StandardSQLFunction {

    public static GroupConcatFunction INSTANCE = new GroupConcatFunction();

    public GroupConcatFunction() {
        super("GROUP_CONCAT", StandardBasicTypes.STRING);
    }

    @Override
    public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory) throws QueryException {
        return render(arguments);
    }

    @SuppressWarnings("UnusedParameters")
    protected String render(List<Object> arguments) {
        String column;
        String separator = null;
        Boolean distinct = Boolean.FALSE;
        String orderBy = null;

        if (arguments.size() > 0) {
            column = arguments.get(0).toString();
        } else {
            throw new IllegalArgumentException("GROUP_CONCAT should have at least one Column Name parameter!");
        }

        if (arguments.size() > 1) {
            distinct = Boolean.valueOf(arguments.get(1).toString());
        }

        if (arguments.size() > 2) {
            separator = arguments.get(2).toString();
        }

        if (arguments.size() > 4) {
            orderBy = String.format("%s %s", arguments.get(3).toString(), arguments.get(4).toString().replace("'", ""));
        }
        return render(column, separator, distinct, orderBy);
    }

    protected String render(String column, String separator, Boolean distinct, String orderBy) {
        StringBuilder groupConcatFunction = new StringBuilder();
        groupConcatFunction.append("GROUP_CONCAT(");
        if (distinct) {
            groupConcatFunction.append("DISTINCT");
        }
        groupConcatFunction.append(" ").append(column);
        if (orderBy != null) {
            groupConcatFunction.append(" ORDER BY ").append(orderBy);
        }
        if (separator != null) {
            groupConcatFunction.append(" SEPARATOR ").append(separator);
        }
        groupConcatFunction.append(" )");
        return groupConcatFunction.toString();
    }
}

2. 注册GROUP_CONCAT功能

public class CustomMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(GroupConcatFunction.INSTANCE.getName(), GroupConcatFunction.INSTANCE);
    }
}

用法示例：
先决条件

@Entity
@NoArgsConstructor
@Data
@Table(name = "Group_Concatenate_Demo")
public class GroupConcatenateDemo {
    @Id
    private Long id;

    private Long recid;

    private String name;
}

sql prettyprint-override">INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(1, 10, 'Larry')
INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(2, 11, 'Mike')
INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(3, 12, 'John')
INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(4, 10, 'Elon')
INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(5, 10, 'Bob')
INSERT INTO Group_Concatenate_Demo (ID, RECID, NAME) VALUES(6, 11, 'Sam')

JPQL查询

public interface GroupConcatenateDemoRepository extends JpaRepository<GroupConcatenateDemo, Long> {
    @Query("SELECT recid, group_concat(name, true, ' # ', name, 'DESC') FROM GroupConcatenateDemo GROUP BY recid")
    List<Object[]> findGroup();
}

生成的sql

    select
        groupconca0_.recid as col_0_0_,
        GROUP_CONCAT(DISTINCT groupconca0_.name 
    ORDER BY
        groupconca0_.name ASC SEPARATOR ' # ' ) as col_1_0_ 
    from
        group_concatenate_demo groupconca0_ 
    group by
        groupconca0_.recid

标准API

    public List<Object[]> groupCriteria() {
        final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
        Root<GroupConcatenateDemo> groupConcatenateDemoRoot = criteriaQuery.from(GroupConcatenateDemo.class);

        criteriaQuery.multiselect(groupConcatenateDemoRoot.get("recid").alias("recid"),
                                  criteriaBuilder.function("group_concat", String.class,
                                  groupConcatenateDemoRoot.get("name"),
                                          criteriaBuilder.literal(true),
                                          criteriaBuilder.literal(" # "),
                                          groupConcatenateDemoRoot.get("name"),
                                          criteriaBuilder.literal("DESC")).alias("name"));

        criteriaQuery.where().groupBy(groupConcatenateDemoRoot.get("recid"));

        return entityManager.createQuery(criteriaQuery).getResultList();
    }

生成的sql

    select
        groupconca0_.recid as col_0_0_,
        GROUP_CONCAT(DISTINCT groupconca0_.name 
    ORDER BY
        groupconca0_.name DESC SEPARATOR ' # ' ) as col_1_0_ 
    from
        group_concatenate_demo groupconca0_ 
    where
        1=1 
    group by
        groupconca0_.recid

输出：

[[10,"Larry # Elon # Bob"],[11,"Sam # Mike"],[12,"John"]]