How to write Union All query in Query DSL

Question

Need to implement sql query like:

SELECT A.class, A.section 
FROM 
STUDENT A 
INNER JOIN DEPARTMENT on A.student_id = B.id 
WHERE DEPT_NBR is not null
UNION ALL
SELECT A.class, A.section 
FROM 
TEACHER A 
INNER JOIN DEPARTMENT on A.teacher_id = B.id 
WHERE DEPT_NBR is not null

How can I write such statement with QueryDSL ? ( I am not using any JPA). Any help/hint is much appreciated!

Answer 1

so that others won't search for that too much... JPA doesn't support unions, hence querydsl when running on top of JPA doesn't support unions. It does support them when running on top of raw SQL though, see natros's answer for that.

Answer 2

There are many examples that can help you.

public void union_multiple_columns() throws SQLException {
        SubQueryExpression<Tuple> sq1 = query().from(employee).select(employee.firstname, employee.lastname);
        SubQueryExpression<Tuple> sq2 = query().from(employee).select(employee.lastname, employee.firstname);
        List<Tuple> list = query().union(sq1, sq2).fetch();
        assertFalse(list.isEmpty());
        for (Tuple row : list) {
            assertNotNull(row.get(0, Object.class));
            assertNotNull(row.get(1, Object.class));
        }
    }

This example was taken from the project itself: https://github.com/querydsl/querydsl/blob/8f96f416270d0353f90a6551547906f3c217833a/querydsl-sql/src/test/java/com/querydsl/sql/UnionBase.java#L73