How to concat or sum the fields of objects collection referenced as @OneToMany in JPA Entity with CriteriaBuilder?

Question

I have two JPA Entities:

@Entity
@Table(name = "registry_group")
public class RegistryGroupEntity {
    @Id
    @SequenceGenerator(name="registry_groups_gen", sequenceName="registry_groups_id_seq", allocationSize = 1)
    @GeneratedValue(generator="registry_groups_gen")
    private Long id;
    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "registryGroup")
    private Collection<ServiceEntity> services;

}

and

@Entity
@Table(name = "services")
public class ServiceEntity {
    @Id
    @SequenceGenerator(name="service_gen", sequenceName="services_id_seq", allocationSize = 1)
    @GeneratedValue(generator="service_gen")
    private Integer id;
    @Column(nullable = false, length = 100)
    private String name;    
}

I'd like to represent the selection as RegistryGroupRow class object:

public class RegistryGroupRow {
    private Long id;
    private String name;    
    private String serviceNames;

    public RegistryGroupRow(Long id, String name, List<String> serviceNames) {
        this.id = id;
        this.name = name;
        this.serviceNames = serviceNames;
    }
}

I have a function like that:

public List<RegistryGroupRow> getRegistryGroupRows(RegistryGroupFilter filter, Integer offset, Integer limit) {

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<RegistryGroupRow> c = cb.createQuery(RegistryGroupRow.class);
    Root<RegistryGroupEntity> registryGroup = c.from(RegistryGroupEntity.class);


    c.multiselect(registryGroup.get(RegistryGroupEntity_.id),
            registryGroup.get(RegistryGroupEntity_.name),
            registryGroup.get(RegistryGroupEntity_.services) //problem is here
    );

It wouldn't work, as RegistryGroupEntity_.services is a collection of ServiceEntity, but I need a concatenation on ServiceEntity._name here. How I can call something like cb.concat(registryGroup.get(RegistryGroupEntity_.services).get(ServiceEntity._name)) for the last parameter of the RegistryRowGroup constructor? How to make CriteriaBuilder to run through the collection's objects specific field and collect/concat/sum its values?

For concat I'd like to concat with separator to String. For collect I'd like to collect into List<String>.

Answer 1

Approach

One way to solve this issue is to use a native sql function to do the aggregation of the service names.

E.g. in H2 database, there is a function named LISTAGG which takes 2 parameters, first the field to aggregate and second the delimiter. I am certain whichever database you are using have something similar to aggregate data; e.g. in postgres there is string_agg(expression, delimiter).

Prerequisites

The function from the database needs to be registered and mapped to a name in hibernate. A custom dialect can be created or a MetadataBuilderContributor can used to achieve the same. The MetadataBuilderContributor is neaty so here we are..

// Registering the function LISTAGG under the name 'listagg'
public class H2SqlFunctionContributor implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
                "listagg",
                new StandardSQLFunction("LISTAGG", StandardBasicTypes.STRING)
        );
    }
} 

Update the spring properties file to include the MetadataBuilderContributor usig the fully qualified name of the class as such;

spring.jpa.properties.hibernate.metadata_builder_contributor = com.stackoverflow.q75989598.H2SqlFunctionContributor

Update the class RegistryGroupRow to use this constructor;

public RegistryGroupRow(Long id, String name, String serviceNames) {
    this.id = id;
    this.name = name;
    this.serviceNames = serviceNames;
}

Querying part

Assuming RegistryGroupEntity looks as such:

public class RegistryGroupEntity {

    @Id
    private long id;

    // using 'groupName' to makes it distinctive in the queries to come
    @Column
    private String groupName;

    @OneToMany(mappedBy = "registryGroupEntity")
    List<ServiceEntity> serviceEntities;
}

And ServiceEntity looks as such:

public class ServiceEntity {

    @Id
    private long id;

    @Column
    private String name;

    @JoinColumn(name = "registry_group_id")
    @ManyToOne
    private RegistryGroupEntity registryGroupEntity;
}

The quering will be as follows;

// the usual stuffs
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<RegistryGroupRow> criteriaQuery = criteriaBuilder.createQuery(RegistryGroupRow.class);
Root<RegistryGroupEntity> registryGroup = criteriaQuery.from(RegistryGroupEntity.class);

// Create a join on the entity ServiceEntity using the defined relationship of 'serviceEntities'
Join<RegistryGroupEntity, ServiceEntity> services = registryGroup.join("serviceEntities");

criteriaQuery.multiselect(
        registryGroup.get("id"),
        registryGroup.get("groupName"),
        // use the criteria builder to call the function named 'listagg' and pass parameters in it
        criteriaBuilder.function("listagg", String.class, services.get("name"), criteriaBuilder.literal(",")).alias("serviceNames")
);

// using an aggregate function usually requires a group by to be done
criteriaQuery.groupBy(registryGroup.get("id"), registryGroup.get("groupName"));

RegistryGroupRow registryGroupRow = entityManager.createQuery(criteriaQuery).getSingleResult();

Result Query generated by hibernate:

select
    registrygr0_.id as col_0_0_,
    registrygr0_.group_name as col_1_0_,
    LISTAGG(serviceent1_.name, ',') as col_2_0_
from
    registry_group registrygr0_
inner join services serviceent1_ on
    registrygr0_.id = serviceent1_.registry_group_id
group by
    registrygr0_.id ,
    registrygr0_.group_name

Result of @ToString for the RegistryGroupRow retrieved.

RegistryGroupRow(id=1, name=RegistryGroup1, serviceNames=ServiceName1,ServiceName2)