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

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