Java Spring Hibernate : How to make MySql group_concat, concat_ws work in Hibernate

I have an application where I am using spring Hibernate and my applicationContext.xml files looks like below. I want to use group_concat and concat_ws in Hibernate queries.


 <bean id="sessionFactory"
  class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="configLocation">
   <value>classpath:hibernate.cfg.xml</value>
  </property>
  <property name="configurationClass">
   <value>org.hibernate.cfg.AnnotationConfiguration</value>
  </property>

  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">${jdbc.dialect}</prop>
    <prop key="hibernate.show_sql">true</prop>
   </props>
  </property>

 </bean>

Solution

Add a new java file somewhere in your solution. For example I am adding in the below package
com.myApp.util
Class Name : CustomDialect

package com.myApp.util;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StringType;
public CustomDialect(){
   registerFunction("group_concat", new StandardSQLFunction("group_concat", StringType.INSTANCE));
    registerFunction("concat_ws", new StandardSQLFunction("concat_ws", StringType.INSTANCE));
}

}

Update applicationConctext.xml like below

....
<props>
<prop key="hibernate.dialect">com.myApp.util.CustomDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
....





1 comment:

Manoj Singhal said...

This solution doesn't work for me.