如何在HQL中执行Union SQL语句？

Question

我正在尝试使用HQL (Hibernate查询语言)在两个表之间创建一个Union。这个SQL脚本在我的SQL服务器上运行得很好：

SELECT COUNT(DISTINCT linkedin_id) as test, school_name
FROM
(SELECT * FROM alum_education 
 UNION
 SELECT * FROM alum_connection_educations) AS UNIONS where school_name='some string'

问题是，当我尝试在grails中运行它时，如下所示：

     def countOfEdu = AlumEducation.executeQuery("select count (distinct linkedinId ) as countOfEdu, schoolName as SchoolName from (SELECT * FROM alumEducation UNION SELECT * FROM alumConnectionEducations) AS UNIONS where schoolName='some string'" )

我得到了这个错误：

  org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 83 [select count(distinct linkedinId )  as countOfEdu, schoolName as SchoolName  from (SELECT * FROM alumEducation UNION SELECT * FROM alumConnectionEducations) AS UNIONS where schoolName='Duquesne University']

如何在grails中运行上述SQL语句？

谢谢，杰森

Answer 1

HQL不支持联合。Hibernate的JIRA中有一个issue，自2005年以来一直是开放的。

Answer 2

我想分享我找到的一种避免这种情况的方法。这里唯一的规则是具有相同的类型，在本例中是一个字符串，对应于返回列表的类型，您可以添加任意多个表：

public List<String> findByCPForCNPJ(String query){
TypedQuery<String> ccpf = manager.createQuery("select cpf from PessoaFisica where cpf like :pCpf", String.class);
ccpf.setParameter("pCpf", "%" + query + "%");
List<String> lista1 = ccpf.getResultList();

TypedQuery<String> ccnpj = manager.createQuery("select cnpj from PessoaJuridica where cnpj like :pCnpj", String.class);
ccnpj.setParameter("pCnpj", "%" + query + "%");

lista1.addAll(ccnpj.getResultList());
return lista1;
}

我在这个解决方案中使用了JAVA中的一个方法。我希望我能做出一点贡献，祝大家好运……

Answer 3

在您的例子中，真正的问题是EJBQL中的FROM子句不支持子查询。