JPA CriteriaBuilder.concat 强制使用 concat 函数。

Question

我正在使用 CriteriaBuilder.concat 来连接两个字符串，使用以下代码：

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

但是生成的SQL语句类似于：

select distinct col_1 || col_2

这会导致org.hibernate.hql.ast.QuerySyntaxException的发生:

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

我想知道如何强制生成以下使用concat()函数而不是||运算符的SQL语句？

select distinct concat(col_1, col_2)

更新：

从错误信息中可以看出，问题主要在于Hibernate (v3.6.10.Final)方面，因此让MySQL接受||进行连接并没有帮助。对我来说，升级到新版本也不是一个选项。

谢谢。

- ryenus

---

Hibernate应该使用适合您的数据库的适当方言将标准翻译成SQL。也许您已经配置它使用Oracle方言而不是MySQL方言。请发布您的JPA / Hibernate配置。 - JB Nizet

我检查了配置，发现我正在使用 play1，它默认使用 driver specific dialect。对于MySQL，该方言是 org.hibernate.dialect.MySQLInnoDBDialect 的子类。 - ryenus

更新上面评论中的链接以获取正确的分支/版本：特定于驱动程序的方言，它扩展了org.hibernate.dialect.MySQLInnoDBDialect。 - ryenus

Answer 1

我实际上找到了一个解决方法。使用Hibernate的@Formula而不是CriteriaBuilder来完成同样的任务，就像这样：

@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;

  //...
}

这样我就可以使用concated字段来进行CriteriaBuilder.countDistinct操作：

//...

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

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

我希望JPA能够支持使用多个列进行countDistinct，这样就可以避免所有这些麻烦了（参见：如何在多个列上进行countDistinct，答案是不支持）。

- ryenus

Answer 2

我曾经遇到过concat函数的类似问题。我在selectCase中使用了concat函数，结果也返回了同样的QuerySyntaxException。

我的解决方法是通过criteria builder函数来使用concat函数：

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版本4.3.11.Final。

- J3ernhard

Answer 3

我也遇到了这个问题。JPA/HQL生成的sql查询使用管道符作为连接符（即||）。

• 我正在使用Mariadb 10，Springboot-data-jpa2.0.6（带有Hibernate 5.2.17）

问题示例

• 给定HQL：select x from Xxx x where concat(x.field1, x.field2) = $1
• 生成的SQL：select ..... where (x.field1 || x.field2) = ?

原因：

• 使用||已经被弃用，自mysql 8.0开始，除非启用PIPES_AS_CONCAT SQL模式：https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
• 对于Mariadb：https://mariadb.com/kb/en/or/

解决方法：

1. （推荐）与使用concat函数相比，有一个类似的函数concat_ws：https://mariadb.com/kb/en/concat_ws/
2. 使用JPA本地查询@Query(nativeQuery = true, value ="select * from ....")
3. set global sql_mode=<包含PIPES_AS_CONCAT模式列表> （我没有尝试过这个方法）

- leo

---

使用本地查询确实是一个可行的解决方案，谢谢！ - ryenus