java - 是 hibernate bug吗？

Question

我写

    String sql = "select candidate_skill.candidate_id from candidate_skill " +
            "inner join skill on  skill.id = candidate_skill.skill_id  " +
            "where skill_id in (:skillIdList) group by candidate_skill.candidate_id " +
            "Having count(candidate_skill.candidate_id) = (select count(*) from skill where skill.id in (:skillIdList) )";
    sql = sql.replace(":skillIdList", generateSkillIdList(skills));
    Query query = session.createSQLQuery(sql);
    List<Candidate> candidates = query.list();

效果不错

第二种情况:

    String sql = "select candidate_skill.candidate_id from candidate_skill " +
            "inner join skill on  skill.id = candidate_skill.skill_id  " +
            "where skill_id in :skillIdList group by candidate_skill.candidate_id " +
            "Having count(candidate_skill.candidate_id) = (select count(*) from skill where skill.id in :skillIdList )";
    Query query = session.createSQLQuery(sql).setParameterList("skillIdList", skills);
    List<Candidate> candidates = query.list()

日志:

Hibernate: select candidate_skill.candidate_id from candidate_skill inner join skill on  skill.id = candidate_skill.skill_id  where skill_id in (?, ?) group by candidate_skill.candidate_id Having count(candidate_skill.candidate_id) = (select count(*) from skill where skill.id in ?, ? )

这不起作用

第三个:

String sql = "select candidate_skill.candidate_id from candidate_skill " +
                "inner join skill on  skill.id = candidate_skill.skill_id  " +
                "where skill_id in :skillIdList group by candidate_skill.candidate_id " +
                "Having count(candidate_skill.candidate_id) = (select count(*) from skill where skill.id in (:skillIdList) )";
        Query query = session.createSQLQuery(sql).setParameterList("skillIdList", skills);
        List<Candidate> candidates = query.list();

日志:

Hibernate: select candidate_skill.candidate_id from candidate_skill inner join skill on  skill.id = candidate_skill.skill_id  where skill_id in (?, ?) group by candidate_skill.candidate_id Having count(candidate_skill.candidate_id) = (select count(*) from skill where skill.id in (?, ?) )

效果很好

附注注意 :skillIdList 周围的括号

如果我在查询中使用 setParameterList("argument",value) 和参数两次，那么第一次 hibernate 会替换括号，第二次则不会替换括号

Answer 1

IN 的语法需要括号。

至于为什么第三个示例有效，有两个猜测:

• hibernate 有一个功能可以自动附加缺失的括号，但不知何故这并不能扩展到子查询
• sql server 首先执行子查询，然后另一个查询由于您传递的参数而变得多余，因此不会执行。