java - 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

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!