Geek Answers Handbook

Hibernate CriteriaBuilder combines multiple lines into one

I am currently getting duplicate results using hibernate using CriteriaBuilder.

I have a table that stores an event identifier and a connection table that stores several values ​​that have an identifier in another table. For instance,

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 lead to double results. For instance.

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

Is it possible that inside hibernate CriteriaBuilder get this result:

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

I saw several solutions in the SQL server itself, but I can not find it using hibernate CriteriaBuilder.

+6
source share
2 answers

I don't know any solution using only CriteriaBuilder. Two options that may work. Tables look like many, many relationships between an Event entity and a TAG.

  • Add two fields with @Formula annotation in the Event entity. @Formula annotations allow you to use several sql statements in it and can use parameters from the current object. Inside the formula, you can use some kind of SQL approach

  • Of course, you can do this in Java if you have a collection of the Tag object on the Event object. Perhaps already reviewed by you also

+6
source

When using Oracle, there is a LISTAGG function. Joining "::" here:

eg.

 (SELECT LISTAGG(b.DESCRIPTION, '::') WITHIN GROUP ( ORDER BY b.id) FROM TABLE_T)

This will be a native SQL query.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

0
source

More articles:


All Articles