4

I'm using CriteriaBuilder.concat to concatenate 2 Strings, using code below:

Expression<String> concat = criteriaBuilder.concat(expr1, expr2)

But the generated SQL is something like:

select distinct col_1 || col_2

which causes org.hibernate.hql.ast.QuerySyntaxException:

expecting CLOSE, found '||' near line 1, column 48 [
select count(distinct generatedAlias0.hostname || generatedAlias0.device) from ...
                                                ^(1,48)

I wonder how to force it to generate the following SQL which uses the concat() function, instead of the || operator?

select distinct concat(col_1, col_2)

Update:

From the error we can see that the problem is more on the Hibernate (v3.6.10.Final) side, which is why making MySQL to accept || for concatenation doesn't help, also updating to a newer version is not an option for me.

Thank you

J3ernhard
  • 252
  • 3
  • 12
ryenus
  • 15,711
  • 5
  • 56
  • 63
  • 2
    Hibernate should translate the criteria to SQL using the appropriate dialect for your database. Maybe you've configured it to use the Oracle dialect instead of the MySQL dialect. Post your JPA/Hibernate configuration(s). – JB Nizet Aug 24 '14 at 08:53
  • 1
    I checked the configuration, and I'm using [play1](https://github.com/playframework/play1), which use [driver specific dialect](https://github.com/playframework/play1/blob/a483edf09aedbe11cd087aa85e3fa91053f99516/framework/src/play/db/jpa/JPAPlugin.java#L306) by default, for MySQL, the dialect is a subclass of [org.hibernate.dialect.MySQLInnoDBDialect](https://github.com/playframework/play1/blob/44230c118db7527127316adb054c5ad178f7092d/framework/src/play/db/jpa/MySQLDialect.java#L8). – ryenus Aug 24 '14 at 12:52
  • ^ updating the links in above comment for the correct branch/version: [driver specific dialect](https://github.com/playframework/play1/blob/1.2.x/framework/src/play/db/jpa/JPAPlugin.java#L284), which extends [org.hibernate.dialect.MySQLInnoDBDialect](https://github.com/playframework/play1/blob/1.2.x/framework/src/play/db/jpa/MySQLDialect.java#L8). – ryenus Aug 25 '14 at 01:16

3 Answers3

3

I've actually found a workaround. by using @Formula (from Hibernate) instead of CriteriaBuilder for the same task, like this:

@Entity
public class MyEntity {

  @Column(name="col_a")
  private String colA;

  @Column(name="col_b")
  private String colB;

  @Formula("concat(col_a, col_b)")
  private String concated;

  //...
}

This way I can use the concated field for CriteriaBuilder.countDistinct:

//...

Expression<?> exp = criteriaBuilder.countDistinct(entity.get("concated"));
criteriaQuery.select(exp);

TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);
return query.getSingleResult();

I wish JPA would (or hopefully already) support countDistinct with multiple columns, then all these mess could have been avoided (see: How to countDistinct on multiple columns, the answer was NO).

Community
  • 1
  • 1
ryenus
  • 15,711
  • 5
  • 56
  • 63
2

I had a similar problem with the concat function. I have used the concat function in a selectCase and this also returns the same QuerySyntaxException.

My workaround is to use the concat function via criteria builder function:

cb().selectCase().when(cb().equal(root.get(Person_.flag), cb().literal("1")), 
        cb().function("CONCAT", String.class, root.get(Person_.something), cb().literal(" bla bla bla")))
    .otherwise(root.get(Person_.something)))

Hibernate Version 4.3.11.Final

J3ernhard
  • 252
  • 3
  • 12
1

I had this issue as well. The JPA/HQL generated sql query use pipes as concat (which is ||).

  • I am using Mariadb 10, Springboot-data-jpa2.0.6 (with Hibernate 5.2.17)

Issue example

  • Given HQL: select x from Xxx x where concat(x.field1, x.field2) = $1
  • Generated SQL: select ..... where (x.field1 || x.field2) = ?

Reason:

Work around:

  1. (preferred) ranther then use concat function, there is a similar one concat_ws: https://mariadb.com/kb/en/concat_ws/
  2. Use JPA native query @Query(nativeQuery = true, value ="select * from ....")
  3. set global sql_mode=<list of modes which contains PIPES_AS_CONCAT> (I didn't try this)
leo
  • 454
  • 5
  • 10