Hibernate: how to use CONCAT and GROUP_CONCAT

Question

How can I use CONCAT() and GROUP_CONCAT() in HQL queries?

Answer 1

About concat: it works exactly the same way as it does in MySQL (it concatenates strings, it is not an aggregate function).

You can add group_concat as an sql function to your configuration. This way you assume that the underlaying DB knows this function, and you tie your program to MySQL.

import org.hibernate.cfg.Configuration; 
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StringType;

// ...
myConf.addSqlFunction("group_concat", new StandardSQLFunction("group_concat", new StringType()));

You also indicate that the output of the function is a string. Without this when you group_concat numeric fields Hibernate will assume the result also to be numeric and crash.

Answer 2

subclass the dialect

and

registerFunction("group_concat", new StandardSQLFunction("group_concat", Hibernate.STRING));

or use SQLFunctionTemplate

Answer 3

If you are using createSQLQuery, use addScalar to that column as String.

SQLQuery query = sessionObj.createSQLQuery("select group_concat(column1,column2) as mycolumn from some table group by someThing");
query.addScalar("mycolumn ", Hibernate.STRING);