标准API和JPQL与组BY和GROUP_CONCAT具有不同的/ ORDER / SEPERATOR支持？

Question

使用JPA标准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是否与group_concat相当，或者是否有其他方法可以实现上述最终输出。

我检查并测试了这两个apis，它们似乎都只提供了与SQL不同的concat函数。

编辑-

我想出了如何注册db函数- -，我可以使用标准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引导这个类，在应用中。属性-

spring.jpa.properties.hibernate.dialect = com.mypackage.CustomMySQLDialect

虽然很有效，但有问题-

1. 我无法理解如何使用SEPERATOR，我希望使用默认的,(逗号)以外的分隔符。
2. 我还想使用DISTINCT，ORDER BY的group_concat特性。 如何通过条件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建议的那样(谢谢jens) -如果我使用

cb.function( "group_concat"，String.class，cb.concat( root.get("name ")，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(",")中与列值连接在一起。这种情况不应发生，而应予以解决。

想要/想要的情况-我想要生成的是-

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

所期望的输出是

Package-2 # Package-1

我还应该在标准查询中添加哪些内容。任何答案都将不胜感激.这对我来说很关键。

Answer 1

解决方案之一是创建一个自定义的GROUP_CONCAT HQL函数，该函数被转换为SQL。

思想是创建函数：group_concat(name, true, ' # ', name, 'DESC')

• 1:聚合用列的名称
• 2:真\假使用不同与否
• 3:串联用分离器
• 4:列名，以供订购
• 5: ASC/DESC分类类型

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

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

1.用处理不同/顺序/分隔符参数的逻辑扩展 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);
    }
}

用法示例：

Preconditions

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

    private Long recid;

    private String name;
}

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

Answer 2

您可以使用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(",")
        )
    )
)

这篇文章提到在select子句中使用函数时需要注册它。

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