How to set group_concat_max_len with MySQL and Hibernate

Question

while using group concat in query I am not able to get all the event group name due to default length of group concat is 1024 so how I can set max_length of group concat in existing code.

I have a code here were I am using group concat and set max len

==========================================================================
        DATA_QUERY="set group_concat_max_len=10024;
 select group_concat(eg.name) from event_groups eg left join theatres t ON t.theatre_id = eg.theatre_id group by t.theatre_id order by t.application_name"

        Session session = getFacadeLookup().getPersistenceFacade().getHibernateSession();
        Query query = session.createSQLQuery(DATA_QUERY) and execute 

        List<Object[]> lstResult = query.list();
============================================================================

error set group_concat_max_len not support here

I don't think you can set the session-attributes that way. What not split the result in more than one query to get around the limit? — Benvorth, Apr 21 '15 at 14:01
http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — user3741598, Apr 21 '15 at 14:41
i am using hibernate session object for creating sqlQuery so how i can set **SET SESSION group_concat_max_len = 1000000;** — Ravi Bhalsod, Apr 22 '15 at 05:35

Vlad Mihalcea · Accepted Answer · 2016-12-02T11:37:39.990

6

Try setting the group_concat_max_len first:

session.doWork(connection -> {
    try(Statement statement = connection.createStatement()) {
        statement.execute("SET GLOBAL group_concat_max_len=10024");
    }
});

or pre-Java 8 syntax:

session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.execute("SET GLOBAL group_concat_max_len=10024");
        }
    }
});

And only then execute your query:

Query query = session.createSQLQuery(
    "select group_concat(eg.name) " +
    "from event_groups eg " +
    "left join theatres t ON t.theatre_id = eg.theatre_id " +
    "group by t.theatre_id order by t.application_name");
List<Object[]> lstResult = query.list();

edited Dec 02 '16 at 11:37

answered Apr 22 '15 at 06:06

Vlad Mihalcea

142,745
71
566
911

is it possible in jre 1.7 version session.doWork(connection -> lamda sign not supported by jre 1.7 version – Ravi Bhalsod Apr 22 '15 at 07:08
Yes of course. You need to use the `new Work () {}` anonymous class syntax then. – Vlad Mihalcea Apr 22 '15 at 07:19
session.doWork(new Work() { @Override public void execute(Connection connection) throws SQLException { try (Statement statement = connection.createStatement()) { statement .execute("SET GLOBAL group_concat_max_len=10024"); statement.close(); } }}); it works fine thank you :) – Ravi Bhalsod Apr 22 '15 at 08:47

score 0 · Answer 2 · answered Apr 09 '21 at 20:00

if you use Hikari connection pool, you can set all session configuration values when the session is started in the in connection-init-sql like below,

spring.datasource.hikari.connection-init-sql=SET sql_require_primary_key = off;SET SESSION group_concat_max_len = 1000000;

How to set group_concat_max_len with MySQL and Hibernate

2 Answers2