JPA CriterialBuilder.concat强制使用concat函数 [英] JPA CriterialBuilder.concat force to use concat function

问题描述

我正在使用CriteriaBuilder.concat连接2个字符串，并使用以下代码:

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

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

但是生成的SQL类似于:

But the generated SQL is something like:

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?

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)

更新:

从错误中我们可以看到问题更多是在Hibernate(v3.6.10.Final)方面，这就是为什么使MySQL接受||进行连接无济于事，而且更新到较新版本也不是问题.我的选择.

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.

谢谢

推荐答案

我实际上找到了一种解决方法.通过使用 @Formula (来自Hibernate)而不是CriteriaBuilder来完成相同的任务，就像这样:

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;

  //...
}

这样，我可以将concated字段用于CriteriaBuilder.countDistinct:

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();

我希望JPA将(或希望已经)支持多列的countDistinct，这样就可以避免所有这些混乱(请参阅:

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).

这篇关于JPA CriterialBuilder.concat强制使用concat函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！