Hibernate Union Alternatives

Hibernate Union alternatives

Use VIEW. The same classes can be mapped to different tables/views using entity name, so you won't even have much of a duplication. Being there, done that, works OK.

Plain JDBC has another hidden problem: it's unaware of Hibernate session cache, so if something got cached till the end of the transaction and not flushed from Hibernate session, JDBC query won't find it. Could be very puzzling sometimes.

How to do a Union SQL statement in HQL?

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

Is there an alternative to using UNION in this SQL statement, using Hibernate 3?

In the past I have side stepped this issues completely by creating a database view. You could create a database view based on the SQL you have in your question, then simply map that database view to "view" object.

The drawback is if you need to manipulate and save the data retrieved in this manner you would have to read the manipulable objects based on the original database table in order to modify and save them through hibernate.

Is Union supported in Hibernate?

Not yet, but I am working on it for Hibernate 6.0. In the meantime you could use Blaze-Persistence, a query builder library that works on top of JPA, which has support for this. See the documentation for more info and examples.

How to execute query with UNION in hibernate

your query is fine at sql level, but in case of Hibernate you will face this exception

Caused by: java.lang.IllegalArgumentException: node to traverse cannot be null!

so convert this query

@Query("(select category from Category category where category.isDelete=false and category.status='A' AND " +
    "category.id in (select cat.id from Category cat where cat.isDelete=false and cat.status='A' and cat.parentCategory IS NOT NULL))" +
    "UNION" +
    "(select category from Category category where category.isDelete=false and category.status='A' and category.parentCategory IS NOT NULL)")

into two queries

@Query("select category from Category category where category.isDelete=false and category.status='A' AND " +
    "category.id in (select cat.id from Category cat where cat.isDelete=false and cat.status='A' and cat.parentCategory IS NOT NULL)")

@Query("select category from Category category where category.isDelete=false and category.status='A' and category.parentCategory IS NOT NULL")

and call them by different methods.

How to execute query with union in hibernate?

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!

---

Related Topics