How to do a Union SQL statement in HQL?

Question

I'm trying to create a Union between two tables, using HQL (Hibernate Query Language). This SQL script works fine on my SQL server:

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'

the problem is, when i try to run it in grails like this:

     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'" )

i get this error:

  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']

How can I run the above SQL statement in grails?

thanks jason

Possible duplicate: http://stackoverflow.com/questions/201023/hibernate-union-alternatives — Andrey Adamovich, Aug 24 '11 at 21:24

score 41 · Accepted Answer · edited Sep 01 '16 at 07:46

41

Unions are not supported by HQL. There is an issue in Hibernate's JIRA that is open since 2005.

edited Sep 01 '16 at 07:46

Jacob van Lingen

8,989
7
48
78

answered Aug 24 '11 at 21:30

Andrey Adamovich

20,285
14
94
132

But is duplicated by "**[HHH-3971](https://hibernate.atlassian.net/browse/HHH-3971) Using UNION ALL , INTERSECTION functionality in hibernate**", and it was closed – Eduardo Cuomo Sep 04 '17 at 12:41
1

The issue HHH-1050 is now closed and says « Support for set operations (UNION, INTERSECT, EXCEPT) was added in Hibernate 6.0. » – Pascal Pixel Rigaux Aug 30 '22 at 21:12

score 1 · Answer 2 · edited Dec 19 '14 at 16:57

I would like to share a way I have found to avoids this situation. The only rule here is to have the same type, in this case a String, corresponding to the type of the return list, you could add as many tables as you want:

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

I used a method in JAVA for this solution. I hope I have contributed a bit, good luck to all...

score 0 · Answer 3 · answered Oct 28 '14 at 19:04

0

In your case, the real problem is that subqueries are not supported in FROM clause in EJBQL.

answered Oct 28 '14 at 19:04

Babou

1

score 0 · Answer 4 · answered Aug 05 '22 at 12:19

0

Support for set operations (UNION, INTERSECT, EXCEPT) was added in Hibernate 6.0.

See the issue linked by Andrey Adamovich

answered Aug 05 '22 at 12:19

SackCastellon

1
2
1

score 0 · Answer 5 · answered Sep 05 '22 at 07:09

0

You can always use native query to get result set.

answered Sep 05 '22 at 07:09

Rohit Maurya

57
2
8

How to do a Union SQL statement in HQL?

5 Answers5

Linked