Criteria API and JPQL API with GROUP BY and GROUP_CONCAT with DISTINCT / ORDER BY / SEPERATOR Support?

Question

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 -

1. I am not able to figure out how to use the SEPERATOR, i want to use a separator other than the default ,(comma).
2. I also want to use DISTINCT, ORDER BY features of group_concat.
   How do i pass these through criteria api.

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.

Answer 1

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')

• 1: name of the column for aggregation
• 2: true\false use DISTINCT or not
• 3: the separator for concatenation
• 4: column name for ORDER BY
• 5: sorting type ASC/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"]]

Answer 2

You can use CriteriaBuilder.function to call arbitrary SQL functions.

I don't see an easy way to mimic the SEPARATOR ',' syntax. What you could do instead would be to append the separator to the field before invoking group_concat. You'd need to strip away the last ",".

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 mentions that you need to register the function when using it in the select clause.

How to do that is explained in https://stackoverflow.com/a/52725042/66686. It might even allow to create custom SQL which could be used to render the SEPERATOR clause.