JPA Specification FIND_IN_SET 以及子查询用法示例

Eclipse 官方的Wiki:
https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Criteria

数据库及需求说明

说明

抽题系统,有两种题目:题干还有题目
每个题干对应多个题目
题干条目以ID1,ID2,ID3...的形式存储子题目
目标是拿到某题干的所有子题的ID列表

目标SQL语句

SELECT id
FROM t_question
WHERE FIND_IN_SET(id, (
    SELECT superquestion
    FROM t_question
    WHERE qtype = '题干'
        AND id = ${id}
))

文件开头

/// 会自动注入EntityManager
@PersistenceContext
private EntityManager entityManager;

查询方法

    /**
     * 获取大题的所有小题ID列表
     *
     * @return 小题列表
     */
    List<Long> getSubquestionIdFromSuperquestion(Long superQuestionId) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // -=-=-=-=-=-=-= 子查询语句 =-=-=-=-=-=-=-
        CriteriaQuery<Object> sq = cb.createQuery();
        // from t_question
        Root<Question> sqfrom = sq.from(Question.class);
        // select superquestion
        sq.select(sqfrom.get("superquestion"));
        // qtype = "题干"
        Predicate sqqtype = cb.equal(sqfrom.get("qtype"), "题干");
        // id = ${id}
        Predicate sqid = cb.equal(sqfrom.get("id"), superQuestionId);
        // where qtype = "题干" and id = ${id}
        sq.where(cb.and(sqqtype, sqid));
        // 先查询子查询的结果
        Query sqquery = entityManager.createQuery(sq);
        // 查询到结果
        String sqresult = (String) sqquery.getSingleResult();
        // -=-=-=-=-=-=-= 主查询语句 =-=-=-=-=-=-=-
        CriteriaQuery<Object> q = cb.createQuery();
        // from t_question
        Root<Question> qfrom = q.from(Question.class);
        // select id
        q.select(qfrom.get("id"));
        // FIND_IN_SET
        Expression<Integer> qfunc = cb.function("FIND_IN_SET", Integer.class, qfrom.get("id"), cb.literal(sqresult));
        // where FIND_IN_SET > 0
        q.where(cb.gt(qfunc, 0));
        // 查询结果
        Query qquery = entityManager.createQuery(q);
        // 查询到结果
        List<Long> qresult = (List<Long>) qquery.getResultList();

        return qresult;
    }

执行的语句

实际没啥用,可以用来理解语句

abc- 2022-04-09 16:21:50.463 [http-nio-8000-exec-1] INFO  jdbc.sqlonly - select question0_.superquestion as col_0_0_ from t_question question0_ where question0_.qtype='题干' 
and question0_.id=3056 

abc- 2022-04-09 16:21:50.478 [http-nio-8000-exec-1] INFO  jdbc.sqlonly - select question0_.id as col_0_0_ from t_question question0_ where FIND_IN_SET(question0_.id, 
'3057,3058,3059,3060')>0 
暂无评论

发送评论 编辑评论


|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇