Need help in creating CriteriaQuery

Question

First of all, I would like to know if it is possible to do?

Below is my query and I am trying to build using criteria.

SELECT CONCAT('record-', rl.record_id) AS tempId, 
        'sloka' AS type, 
         rl.record_id AS recordId,
         rl.title AS title, 
         rl.locale as locale,
         rl.intro AS intro, 
         rl.title AS localetitle, 
         NULL AS audioUrl,
         lp.name AS byName,
         lp.person_id AS byId,
         lp.name AS onName,
         lp.person_id AS onId

    FROM record_locale rl 
        LEFT JOIN record r ON rl.record_id = r.record_id 
        LEFT JOIN locale_person lp ON r.written_on = lp.person_id 
    WHERE rl.title LIKE :title 
        AND rl.locale = :locale 
        AND lp.locale = :locale 

UNION 

SELECT CONCAT('lyric-', s.song_id) AS tempId, 
        'bhajan' AS type,
        s.song_id AS recordId,
        s.title, 
        l.locale as locale,
        NULL AS intro,
        l.title AS localetitle,
        s.audio_url AS audioUrl,
        lpb.name AS byName,
        lpb.person_id AS byId,
        lpo.name AS onName,
        lpo.person_id AS onId 

    FROM song s 
        LEFT JOIN locale_person lpb 
    ON (s.written_by = lpb.person_id AND lpb.locale = :locale) 
        LEFT JOIN locale_person lpo
    ON (s.written_on = lpo.person_id AND lpo.locale = lpb.locale)
        INNER JOIN lyric l 
    ON (l.locale = lpb.locale AND l.song_id = s.song_id) 

    WHERE s.title LIKE :title AND s.approved_by IS NOT NULL 

    ORDER BY localeTitle ASC

// END

Based on few conditions, I might need to have union of both queries or just individual query without union.

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

Converting the SQL to JPQL is usually a good first step, as we can't quite tell what these tables map to, or what you are expecting to get back. If it is possible to do in JPQL, it should be possible with a criteria query. Except in this case: JPA/JPQL does not have the union operator so it won't work in straight JPA, but some providers such as EclipseLink have support. See:

UNION to JPA Query and http://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/j_union.htm

edited May 23 '17 at 12:04

Community

1
1

answered Dec 16 '15 at 14:40

Chris

20,138
2
29
43

Hi Chris, thanks for helping. I am using hibernate as well. What exactly I am trying is, I have two tables song and record_locale, based on few parameters, I would wish to add where clause. Also based on few conditions I would like to query just the song table/record_locale table. – Vijayendra Mudigal Dec 16 '15 at 15:43

Need help in creating CriteriaQuery

1 Answers1