I cant use group_concat in queries

Hibernate ORM
1

I need to execute a SELECT with GROUP_CONCAT but I recieved this error all the time.

This is the query:

“select bk.categoria,GROUP_CONCAT(bk.nombre),GROUP_CONCAT(bk.id) from modelos.Productos as bk where bk.code=‘25416854168746541’ group by bk.categoria ORDER BY bk.categoria,bk.nombre ASC”

And this is the error:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
±[METHOD_CALL] MethodNode: ‘(’
| ±[METHOD_NAME] IdentNode: ‘GROUP_CONCAT’ {originalText=GROUP_CONCAT}
| -[EXPR_LIST] SqlNode: ‘exprList’
| -[DOT] DotNode: ‘productos0_.Nombre’ {propertyName=nombre,dereferenceType=ALL,propertyPath=nombre,path=bk.nombre,tableAlias=productos0_,className=modelos.Productos,classAlias=bk}
| ±[ALIAS_REF] IdentNode: ‘productos0_.ID’ {alias=bk, className=modelos.Productos, tableAlias=productos0_}
| -[IDENT] IdentNode: ‘nombre’ {originalText=nombre}

Is possible use GROUP_CONCAT on hibernate queries?

2

You need to register the function so that Hibernate knows about it. Check out the MySQLDialect for an example of how you can register a function which you can do by extending the Dialect or via the bootstrapping mechanism.

3

Thanks for your answer vlad :slight_smile:

This is the first time that I hear abour “functions”. I searched a tutorial on internet, but I dont find anything. The only thing that I find is something complicated with a class that extends from dialect, etc

This is the only option? Dont exist an easiest option?

4

You can create a custom Integrator as explained in this article.

And, in the integrate method you register the SQL function:

@Override
public void integrate(
        Metadata metadata,
        SessionFactoryImplementor sessionFactory,
        SessionFactoryServiceRegistry serviceRegistry) {

    metadata.getSqlFunctionMap().put("group_concat", new StandardSQLFunction( "group_concat", StandardBasicTypes.STRING)); 

}
1 Like
5

Thank you so much vlad :slight_smile:

6

Hi again!

I copied that method like I saw in your website but I recieve that error.

“The method getSqlFunctionMap() is undefined for the type Metadata”. And I cant import “org.hibernate.boot.model.relational.Database”.

¿Need I an extra librarie or something?

7

What version of Hibernate are you using?

8

I have the version 4.1 of hibernate core.

9

That’s no longer supported. My answer is for 5.2 which is the latest version. For older versions, you need to check the 4.x source code and see whether you can do the same.

10

I have Java 8 and JPA 2.1 (the minimun requisites for 5.2), if I change the 4.1 core for the 5.2 it should work?

Sorry for ask a lot, but it is a litlle complicated to me.

11

If you have an existing application, you should read the migration guide from 4.x to 5.x since there have been some backward incompatible changes.

12

okey, I will do that, thanks vlad :slight_smile:

13

Hi Vlad :slight_smile:
I updated hibernate but I cant reach this constructor:

When I check in the documentation I cant find “Metadata”:in the constructors.I only can implement the method with MetadataImplementor.

https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/integrator/spi/Integrator.html

Should I add something?

14

I finally solve that adding a configuration on the sessionfactoryutil:

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


// ...

 private static SessionFactory sessionFactory;
    
    static {
Configuration conf= new Configuration();
        	conf.configure();

conf.addSqlFunction("group_concat", new StandardSQLFunction("group_concat", new StringType()));



 sessionFactory = conf.buildSessionFactory();

}