Hibernate CriteriaBuilder concatenate multiple rows into one

Question

I am currently getting double results with hibernate using the CriteriaBuilder.

I have a table which stores event ID's and a join table which stores multiple values that have a ID to another table. Eg;

event

ID ....
---------
1123
1124
1125

join table

ID  event_id  tag_id
----------------------
1  1124      2
2  1124      3
3  1123      6
4  1123      7

tag

ID tag
---------
1  Dance
2  Hiphop
...

This will obviously result in double results.. Eg.

eventID ... tag_id   tag
--------------------
1124        2      Hiphop
1124        3      Dance

Is it possible within hibernate's CriteriaBuilder to get a result set like so;

eventID ... tag_id   tag
--------------------
1124        2,3      Hiphop, Dance

I've seen multiple solutions within SQL server itself but I cannot find one using hibernate's CriteriaBuilder.

Answer 1

I do not know any solution using CriteriaBuilder alone. Two options that could work. The tables seems like a many to many relation between an Event entity and a TAG one.

1. Add two fields with a @Formula annotation in Event entity. The @Formula annotation allows you to use some sql statements in it and can use parameters from the current entity. Inside a formula you can use some SQL proprietary approach

2. Of course you can do this in Java if you have a collection of Tag object on a Event object. Probably already considered by you also