Stack Exchange
how to perform union clause query with hibernate criteria api
SELECT 
    supplier_id  FROM suppliers   UNION ALL 

SELECT     supplier_id  FROM orders;

i just creating two criteria above "UNION ALL" clause of query and below "UNION ALL" clause of query.
but my question is how i perform UNION ALL clause in criteria?
Thanks in Advance.

with criteria I think hibernate does not support UNION ALL but you can use two criteria queries to get the expected result:
Criteria cr1 = session.createCriteria(Suppliers.class);
 cr1.setProjection(Projections.projectionList()
    .add( Projections.property("supplier_id"), "supplier_id" )
 );
List results1 = cr1.list();

Criteria cr2 = session.createCriteria(Orders.class);
 cr2.setProjection(Projections.projectionList()
    .add( Projections.property("supplier_id"), "supplier_id" )
 );
List results2 = cr2.list();

results1.add(results2); 

List unionAllList =  results1; //this is the expected result.


Pity it doesn't solve the ORDER BY problem, cause you can use ORDER BY with UNION (applies to all results from union), but not with this criteria trick.

@Googie, question is not about ORDER BY, but even if it was then sorting a list is easy in java, the OP may use another collection like tree set or etc instead of a list just for sorting the result

yeah, I know, I just mentioned the ORDER BY, cause I had this problem and came here looking for answer. Just in case anyone else was looking for the same: I dealt with it by creating a view in database and creating Entity mapped to that view. The view is joining unrelated entities and returning IDs of both entities, so my "view entity" has those both entities as associations.

You did great, also for complicated queries, creating view and mapping an entity to it is really a good solution.
