Criteria API和JPQL API是否支持以GROUP BY和GROUP_CONCAT结合DISTINCT / ORDER BY / SEPERATOR的方式进行查询？

10 浏览
0 Comments

Criteria API和JPQL API是否支持以GROUP BY和GROUP_CONCAT结合DISTINCT / ORDER BY / SEPERATOR的方式进行查询？

使用JPA Criteria API，我想按一列进行分组并连接另一列的值。例如，下面是SQL方法，我正在寻找等效的Criteria查询（和JPQL查询）方法。

使用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查询/JPQL是否有等效的group_concat函数，或者是否有其他方法可以实现上述最终输出。

我已经检查和测试了这两个API，它们似乎只提供了concat函数，而不是与SQL中的group_concat相同。

编辑-

我弄清楚如何注册一个数据库函数-

我可以使用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中告知spring boot关于这个类：

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

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

1. 我无法弄清楚如何使用SEPARATOR，我想使用一个不同于默认逗号（,）的分隔符。

2. 我还想使用group_concat的DISTINCT、ORDER BY功能。如何通过criteria api传递这些。

目前的情况-

目前，我的criteria查询中的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）-如果我使用

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(",")中的逗号与列值连接在一起。这不应该发生并且应该解决。

期望的情况-

我想要生成的SQL是：

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

期望的输出是：

Package-2 # Package-1

我应该在criteria查询中添加什么内容？非常感谢任何答案...这对我来说非常重要。

0

2 答案

0 Comments

问题的原因是想要在Criteria API和JPQL API中使用GROUP BY和GROUP_CONCAT功能，同时支持DISTINCT、ORDER BY和SEPERATOR。然而，目前没有直接支持SEPERATOR的语法。

解决方法是使用CriteriaBuilder.function方法来调用任意SQL函数，并使用cb.concat方法在调用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子句中调用该函数时进行函数注册。可以参考上述提到的文章和链接中的说明来完成注册。这种方法甚至可以创建自定义的SQL，用于渲染SEPERATOR子句。

感谢对建议和链接的提供。我会尝试并希望能够成功。我已经更新了问题，请您查看。

0
0 Comments

Criteria API与JPQL API不支持在GROUP BY子句中使用GROUP_CONCAT函数以及DISTINCT、ORDER BY和SEPARATOR参数。然而，可以通过创建自定义的GROUP_CONCAT HQL函数来解决这个问题。以下是解决方法的详细步骤：

1. 扩展StandardSQLFunction类，实现处理DISTINCT、ORDER BY和SEPARATOR参数的逻辑。

public class GroupConcatFunction extends StandardSQLFunction {
    public static GroupConcatFunction INSTANCE = new GroupConcatFunction();
    public GroupConcatFunction() {
        super("GROUP_CONCAT", StandardBasicTypes.STRING);
    }
    public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory) throws QueryException {
        return render(arguments);
    }
    protected String render(List arguments) {
        // 处理参数逻辑
    }
    protected String render(String column, String separator, Boolean distinct, String orderBy) {
        // 构建GROUP_CONCAT函数
    }
}




2. 注册GROUP_CONCAT函数。




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





通过以上步骤，我们可以在JPQL和Criteria API中使用GROUP_CONCAT函数，并且可以传入DISTINCT、ORDER BY和SEPARATOR参数。下面是使用示例：


在实体类GroupConcatenateDemo中，假设有id、recid和name三个属性。


JPQL查询示例：




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





Criteria API查询示例：




public List groupCriteria() {
    final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root 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();
}





通过以上方法，我们可以在JPQL和Criteria API中使用GROUP_CONCAT函数，并且传入DISTINCT、ORDER BY和SEPARATOR参数，从而解决了这个问题。

									
				

									

			
			
0