How to do a Union SQL statement in HQL?
Solution 1
Unions are not supported by HQL. There is an issue in Hibernate's JIRA that is open since 2005.
Solution 2
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...
jason
Results-oriented project manager and innovative software developer experienced with multi-cultural international aid organizations, government offices, and NGO’s. Innovative data management specialist able to see the business and technical sides of a problem with an eye for streamlining work, reducing cost, and maintaining data integrity. Proven leadership, with a strong emphasis on negotiation and problem resolution abilities. Demonstrated ability to acquire technical knowledge and skills rapidly.
Updated on September 01, 2020Comments
-
jason over 3 years
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
-
Andrey Adamovich over 12 yearsPossible duplicate: stackoverflow.com/questions/201023/hibernate-union-alternatives
-
-
Eduardo Cuomo over 6 yearsBut is duplicated by "HHH-3971 Using UNION ALL , INTERSECTION functionality in hibernate", and it was closed