在创建CriteriaQuery时需要帮助

Question

首先，我想知道是否有可能这样做？

下面是我的查询，我正在尝试使用标准进行构建。

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

根据几个条件，我可能需要两个查询的联合，或者只有单个查询而不联合。

Answer 1

将SQL转换为JPQL通常是很好的第一步，因为我们不太清楚这些表映射到什么，或者您希望得到什么。如果在JPQL中可以做到这一点，那么条件查询也应该是可能的。除了这种情况: JPA/JPQL没有联合运算符，所以它不能直接在JPA中工作，但一些提供者，如EclipseLink，有支持。请参见：

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