Using JPA Criteria API, I want to group by a column and join the values of another column.
For example, the below is the sql approach and I am looking for the equivalent criteria query (and jpql query) approach.
mysql> select *from GroupConcatenateDemo;
+------+-------+
| Id | Name |
+------+-------+
| 10 | Larry |
| 11 | Mike |
| 12 | John |
| 10 | Elon |
| 10 | Bob |
| 11 | Sam |
+------+-------+
GROUP BY USING 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 |
+------+-----------------+
Does Criteria Query / JPQL have equivalent of group_concat or is there any other approach i can follow to achieve the above final output.
I have checked and tested both apis, they both seem to provide only concat function which is not same as the SQL group_concat.
Edit -
I Figured out how to register a db function -
I could use the GROUP_CONCAT function from Criteria API. For this I had to add a Custom Dialect Class and inform spring(boot) about this class.
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
)
);
}
}
And then inform spring boot about this class, in application.properties-
spring.jpa.properties.hibernate.dialect = com.mypackage.CustomMySQLDialect
Its working though but with issues -
SEPERATOR, i want to use a separator other than the default ,(comma).DISTINCT, ORDER BY features of group_concat.Current Situation -.
Currently my group_concat code part of criteria query is something like below -
some other selects... , cb.function("GROUP_CONCAT", String.class, packagesJoin.get("packageName")), some other selects
and the generated sql part is - GROUP_CONCAT(packages4_.package_name) as col_3_0_,.
And the output is - Package-1,Package-1,Package-2,Package-2
SOF Suggested situation -
like suggested by @jens-schauder (thanks jens) - if i use
cb.function( "group_concat", String.class, cb.concat( root.get("name"), cb.literal(",") )
i.e the code is
cb.function("GROUP_CONCAT", String.class, packagesJoin.get("packageName"), cb.literal(",")),
the generated sql is -
GROUP_CONCAT(packages4_.package_name,
',') as col_3_0_,
the output is:
Package-1,,Package-1,,Package-2,,Package-2,
Problem in this approach is - the , in cb.literal(",") is concatenated with the column value. This should not happen and be resolved.
Wanted/Desired Situation -
The SQL I want to be generated is -
GROUP_CONCAT(DISTINCT packages4_.package_name ORDER BY packages4_.package_name DESC SEPARATOR ' # ') as col_3_0_,.
And desired output is
Package-2 # Package-1
What more should i add to the criteria query. Any answers will be very much appreciated.... this is quite critical for me.
One of the solutions is to create a custom GROUP_CONCAT HQL function that is translated to SQL.
Idea is to create function: group_concat(name, true, ' # ', name, 'DESC')
Which are translating: GROUP_CONCAT(DISTINCT name ORDER BY name DESC SEPARATOR ' # ' )
PLEASE NOTE: implementation does not handle all possible use cases of the GROUP_CONCAT function, for example not handled limit parameter and several columns for sorting. But it can be extended. Current implementation fully resolves described problem.
1. Extend StandardSQLFunction with logic of handling DISTINCT/ ORDER BY / SEPARATOR parameters
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. Register GROUP_CONCAT function
public class CustomMetadataBuilderContributor implements MetadataBuilderContributor {
@Override
public void contribute(MetadataBuilder metadataBuilder) {
metadataBuilder.applySqlFunction(GroupConcatFunction.INSTANCE.getName(), GroupConcatFunction.INSTANCE);
}
}
Example of usage:
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 query
public interface GroupConcatenateDemoRepository extends JpaRepository<GroupConcatenateDemo, Long> {
@Query("SELECT recid, group_concat(name, true, ' # ', name, 'DESC') FROM GroupConcatenateDemo GROUP BY recid")
List<Object[]> findGroup();
}
Generated 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
Criteria 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();
}
Generated 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
Output:
[[10,"Larry # Elon # Bob"],[11,"Sam # Mike"],[12,"John"]]