How to execute query with union in hibernate?

Question

Hibernate doesn't support union ,so i would like to run sql separately. but finally how to combine those values ?

String query ="select
dp.PRODUCTFAMILY,dp.PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCTFAMILY is null
union
select
dp.DIVNUMBER,dp.DIVDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCT is not null and dd.PRODUCTFAMILY is not null";

public List<PRODUCT> findmethod() {
        return findAllByQuery(query);
   }

Please advise how to execute two sql seperately and finally how to combine those values ?

Answer 1

would like to share, which in my case, I found a situation that skirted this situation. The only rule here is to have the same type, in this case String, corresponding to return the list, could add as many tables 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...

Answer 2

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

If this is true add alias to your query:

select
dp.PRODUCTFAMILY as PRODUCTFAMILY,dp.PRODUCTFAMILYDESCR as PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCTFAMILY is null
union
select
dp.DIVNUMBER as PRODUCTFAMILY,dp.DIVDESCR as PRODUCTFAMILYDESCR
from TABEL1 dd, TABEL2 DP
where dd.id = 00002
and dd.PRODUCT is not null and dd.PRODUCTFAMILY is not null

You can use SQLQuery and a AliasToBeanResultTransformer in this manner:

session.createSQLQuery(above sql with union).addScalar("PRODUCTFAMILY",StringType.INSTANCE).addScalar("PRODUCTFAMILYDESCR",StringType.INSTANCE).setResultTransformer(new AliasToBeanResultTransformer(PRODUCT.class))

PRODUCT must have an emtpy constructor and field accessors.

Else, if this union is intended to extract different fields with different types you have to run two queries separately the addAll() second result to first!