How do I use JPA 2.1's CriteriaBuilder.function with MySQL's "GROUP_CONCAT"?

Dave A

I’m using JPA 2.1, Hibernate 4.3.6.Final, and MySQL 5.5. I read JPA 2.1 supports invoking native DB functions, but I’m having trouble figuring out how to invoke MySQL's “GROUP_CONCAT.” I have the following code …

final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery criteria = StringUtils.equals(orderByCol, "organization") ? builder.createQuery(Tuple.class) : builder.createQuery(User.class);
final Root<User> user = criteria.from(User.class);
    …
final SetJoin<User, Organization> orgsJoin = orderByRoot.join(User_.organizations, JoinType.LEFT);
final Expression groupConcatExpr = builder.function("GROUP_CONCAT", String.class, orgsJoin.get(Organization_.name));
 criteria.select(builder.tuple(user, groupConcatExpr))
                    .groupBy(user);
…           
  orderByExpr = orgsJoin.get(Organization_.name);
  criteria.orderBy(orderByAscending == null || orderByAscending == true ? builder.asc(orderByExpr) : builder.desc(orderByExpr));

but I get the following exception …

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: 'function (GROUP_CONCAT)'
    +-[METHOD_NAME] IdentNode: 'GROUP_CONCAT' {originalText=GROUP_CONCAT}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[DOT] DotNode: 'organizati2_.NAME' {propertyName=name,dereferenceType=PRIMITIVE,getPropertyPath=name,path=generatedAlias1.name,tableAlias=organizati2_,className=org.mainco.subco.organization.domain.Organization,classAlias=generatedAlias1}
      +-[ALIAS_REF] IdentNode: 'organizati2_.id' {alias=generatedAlias1, className=org.mainco.subco.organization.domain.Organization, tableAlias=organizati2_}
      \-[IDENT] IdentNode: 'name' {originalText=name}

    at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:174)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:924)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:692)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:665)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:278)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:568)
    at org.hibernate.jpa.criteria.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:336)
    at org.hibernate.jpa.criteria.compile.CriteriaCompiler.compile(CriteriaCompiler.java:147)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:736)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
    at com.sun.proxy.$Proxy68.createQuery(Unknown Source)
    at org.mainco.subco.user.repo.UserDaoImpl.findUsers(UserDaoImpl.java:120)
    at org.mainco.subco.user.repo.UserDao2IT.testFindOrderByOrganizationAsc(UserDao2IT.java:624)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Dheeraj Arora

To user GROUP_CONCAT in JPA, below are the steps:

1.) Create a class "GroupConcatFunction" as below:

import java.util.List;
import org.hibernate.QueryException;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.engine.spi.Mapping;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

public class GroupConcatFunction implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping)
        throws QueryException {
        return StandardBasicTypes.STRING;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public String render(Type firstArgumentType, List arguments,
        SessionFactoryImplementor factory) throws QueryException {
        if (arguments.size() != 1) {
            throw new QueryException("group_concat should have only one argument");
        }
        return "group_concat(" + arguments.get(0) + ")";
    }
}

2.) We will create a custom dialect which will have the code to register our group_concat function in dialect:

import org.hibernate.dialect.MySQL5Dialect;

public class CustomMySql5Dialect extends MySQL5Dialect {

    public CustomMySql5Dialect() {
        super();
        registerFunction("group_concat", new GroupConcatFunction());
    }
}

3.) Replace the dialect definition in persistence.xml with your custom dialect:

<property name="hibernate.dialect"    value="<<pkg name>>.CustomMySql5Dialect" />

Now, you will be able to use this in your criteria query.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Spark SQL replacement for MySQL's GROUP_CONCAT aggregate function

From Dev

How do I write a group_concat function in sqlalchemy?

From Dev

MySQL's Group_Concat function miss the nulls. How can group the rows including NULLs.

From Dev

How to COUNT MySQL results when I use “GROUP_CONCAT” and “HAVING” in the query?

From Dev

How do I approach queries like this in MySQL, Group_Concat maybe?

From Dev

Invalid use of group function (group_concat and MySQL)

From Dev

Create PHP array from MySQL's GROUP_CONCAT

From Dev

Create PHP array from MySQL's GROUP_CONCAT

From Dev

how to use select query in a Group_concat sub query in mysql

From Dev

How can I use GROUP_CONCAT in Rails?

From Dev

How can I use group_concat on an entire subquery?

From Dev

Optimize MySQL query for group_concat function

From Dev

Aggregate function GROUP_CONCAT(expr) in MySQL

From Dev

Aggregate function GROUP_CONCAT(expr) in MySQL

From Dev

MySQL Use GROUP_CONCAT with Multiple JOINS

From Dev

MySQL - is it possible to use group_concat in an IN() statement?

From Dev

How to use group_concat in hibernate criteria?

From Dev

How to do a subquery in group_concat

From Dev

MySQL: update with join using GROUP_CONCAT: ERROR 1111 (HY000): Invalid use of group function

From Dev

How to use GROUP_CONCAT in mySQL when one of the fields contain comma seperated numbers?

From Dev

mysql query, how can i get top 5 downloads with GROUP_CONCAT

From Dev

mysql query, how can i get top 5 downloads with GROUP_CONCAT

From Dev

Mysql Group_concat where in again group concat how can i manage

From Dev

how do i group rows by an id row in group_concat into one row string?

From Dev

MySQL Group_Concat Not In

From Dev

Group_concat use?

From Dev

Group_concat use?

From Dev

MySQL use GROUP_CONCAT when INSERTing data

From Dev

Error when I used GROUP_CONCAT on mysql query

Related Related

  1. 1

    Spark SQL replacement for MySQL's GROUP_CONCAT aggregate function

  2. 2

    How do I write a group_concat function in sqlalchemy?

  3. 3

    MySQL's Group_Concat function miss the nulls. How can group the rows including NULLs.

  4. 4

    How to COUNT MySQL results when I use “GROUP_CONCAT” and “HAVING” in the query?

  5. 5

    How do I approach queries like this in MySQL, Group_Concat maybe?

  6. 6

    Invalid use of group function (group_concat and MySQL)

  7. 7

    Create PHP array from MySQL's GROUP_CONCAT

  8. 8

    Create PHP array from MySQL's GROUP_CONCAT

  9. 9

    how to use select query in a Group_concat sub query in mysql

  10. 10

    How can I use GROUP_CONCAT in Rails?

  11. 11

    How can I use group_concat on an entire subquery?

  12. 12

    Optimize MySQL query for group_concat function

  13. 13

    Aggregate function GROUP_CONCAT(expr) in MySQL

  14. 14

    Aggregate function GROUP_CONCAT(expr) in MySQL

  15. 15

    MySQL Use GROUP_CONCAT with Multiple JOINS

  16. 16

    MySQL - is it possible to use group_concat in an IN() statement?

  17. 17

    How to use group_concat in hibernate criteria?

  18. 18

    How to do a subquery in group_concat

  19. 19

    MySQL: update with join using GROUP_CONCAT: ERROR 1111 (HY000): Invalid use of group function

  20. 20

    How to use GROUP_CONCAT in mySQL when one of the fields contain comma seperated numbers?

  21. 21

    mysql query, how can i get top 5 downloads with GROUP_CONCAT

  22. 22

    mysql query, how can i get top 5 downloads with GROUP_CONCAT

  23. 23

    Mysql Group_concat where in again group concat how can i manage

  24. 24

    how do i group rows by an id row in group_concat into one row string?

  25. 25

    MySQL Group_Concat Not In

  26. 26

    Group_concat use?

  27. 27

    Group_concat use?

  28. 28

    MySQL use GROUP_CONCAT when INSERTing data

  29. 29

    Error when I used GROUP_CONCAT on mysql query

HotTag

Archive