spring-data-jpa 条件API和JPQL API是否支持GROUP BY和GROUP_CONCAT(支持DISTINCT / ORDER BY / SEPERATOR)？

3qpi33ja 于 2022-11-10 发布在 Spring

关注(0)|答案(2)|浏览(458)

使用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            |
+------+-----------------+

Criteria Query / JPQL是否有group_concat的等价物，或者是否有其他方法可以实现上述最终输出？
我已经检查并测试了这两个API，它们似乎都只提供concat函数，这与SQL group_concat不同。

编辑-
**我知道如何注册一个db函数-**我可以使用Criteria 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
                )
        );
    }
}

，然后在application.properties中将该类通知给 Boot 。
spring.jpa.properties.hibernate.dialect = com.mypackage.CustomMySQLDialect
虽然它的工作，但与问题-
1.我不知道如何使用SEPERATOR，我想使用一个分隔符，而不是默认的,（逗号）。
1.我还想使用group_concat的DISTINCT、ORDER BY特性。
我如何通过标准api传递这些。

现状-。

目前我的条件查询的group_concat代码部分如下所示 * -

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

并且生成的sql部分是 * -GROUP_CONCAT(packages4_.package_name) as col_3_0_,。
输出为 * -Package-1,Package-1,Package-2,Package-2
SOF建议的情况-
就像@jens-schauder建议的那样（谢谢jens）-如果我使用

函数（“group_concat”，字符串.类，cb.concat（根.get（“名称”），cb.literal（“，”））

即代码为 *

cb.function("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,

此方法中的问题是-cb.literal(",")中的,与列值串联在一起。这种情况不应发生，应予以解决。*
**所需/所需的情况-**我要生成的SQL是-

GROUP_CONCAT(DISTINCT packages4_.package_name ORDER BY packages4_.package_name DESC SEPARATOR ' # ') as col_3_0_, .

所需输出为 *

Package-2 # Package-1

我还应该添加什么到条件查询。任何答案都将非常感谢....这对我来说是相当关键的。

spring-data-jpa

来源：https://stackoverflow.com/questions/73129333/criteria-api-and-jpql-api-with-group-by-and-group-concat-with-distinct-order-b

2条答案

按热度按时间

yqkkidmi1#

解决方案之一是创建一个自定义的GROUP_CONCAT HQL函数，该函数将被转换为SQL。
创意是创造功能：group_concat(name, true, ' # ', name, 'DESC')个

1：用于聚合的列的名称
2：true\false是否使用DISTINCT
3：连接的分隔符
4：ORDER BY的列名
5：排序类型ASC/DESC

它们正在翻译：GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR ' # ' )

**请注意：**实现并不处理GROUP_CONCAT函数的所有可能用例，例如，不处理限制参数和用于排序的多个列。但它可以扩展。当前实现完全解决了所描述的问题。
1.使用处理DISTINCT/ ORDER BY / SEPARATOR参数的逻辑扩展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);
    }
}

用法示例：
前提条件

第一个

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"]]

赞(0）回复(0）举报 2022-11-10

am46iovg2#

可以使用CriteriaBuilder.function调用任意SQL函数。
我看不出有什么简单的方法来模仿SEPARATOR ','语法。你可以做的是在调用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(",")
        )
    )
)

This article提到在select子句中使用函数时需要注册该函数。
https://stackoverflow.com/a/52725042/66686中解释了如何执行此操作。它甚至可以允许创建可用于呈现SEPERATOR子句的自定义SQL。

赞(0）回复(0）举报 2022-11-10

我来回答

spring-data-jpa 条件API和JPQL API是否支持GROUP BY和GROUP_CONCAT(支持DISTINCT / ORDER BY / SEPERATOR)？

2条答案

相关问题

热门标签

最新问答