JCH
Java Community | Help. Code. Learn.•6mo ago
gosdom

Using Spring Data Criteria Builder to create a dynamic query that includes a UNION

I am currently having a problem where I need to dynamically build a query in Spring based on parameters being passed for a multicriteria search. The problem comes from the fact that I can't seem to find any way to do a union and include pagination in the select. What I want is something along the lines of the following SQL 
WITH UNITED AS (
SELECT
    TABLEA.*
FROM
    TABLEA
JOIN TABLEB ON
    TABLEB.idA = TABLEA.idA
JOIN TABLEC ON
    TABLEC.idB = TABLEB.idB
    AND TABLEC.cellphone = '960000000'
UNION ALL
SELECT
    A.*
FROM
    TABLEA A
JOIN TABLEB B ON
    A.idA = B.idA
    AND B.cellphone = '960000000') 
SELECT
    *
FROM
    UNITED
LIMIT 10 ;
WITH UNITED AS (
SELECT
    TABLEA.*
FROM
    TABLEA
JOIN TABLEB ON
    TABLEB.idA = TABLEA.idA
JOIN TABLEC ON
    TABLEC.idB = TABLEB.idB
    AND TABLEC.cellphone = '960000000'
UNION ALL
SELECT
    A.*
FROM
    TABLEA A
JOIN TABLEB B ON
    A.idA = B.idA
    AND B.cellphone = '960000000') 
SELECT
    *
FROM
    UNITED
LIMIT 10 ;
1 Reply
JavaBot
JavaBot•6mo ago
⌛ This post has been reserved for your question.
Hey @gosdom! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.
TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here. 💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Did you find this page helpful?