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!

score 7 · Answer 1 · answered Jul 09 '17 at 13:46

7

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

answered Jul 09 '17 at 13:46

natros

710
4
10

query() comes from AbstractBaseClass and returns TestQuery. So, what do I replace query() with? – Nathan Jun 17 '22 at 14:34
var queryFactory = new SQLQueryFactory(...); queryFactory.query().union(SQLExpressions.select(...).from(...).where(...), SQLExpressions.select(...).from(...).where(....)).fetch() – natros Jun 17 '22 at 20:23
I was trying to use JPAQueryFactory. I didn't realize it was SQLQueryFactory. What are the Maven coordinates for SQLQueryFactory? – Nathan Jun 19 '22 at 01:20
The original question was about querydsl-sql and not JPA. You can find SQLQueryFactore in "com.querydsl:querydsl-sql:5.0.0". – natros Jun 19 '22 at 08:09

Emmanuel Touzery · Answer 2 · 2019-06-23T06:23:53.373

7

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.

edited Jun 23 '19 at 06:23

answered Jun 21 '19 at 06:38

Emmanuel Touzery

9,008
3
65
81

How to write Union All query in Query DSL

2 Answers2