CODEWITHSUNDEEP
javahibernate
gstackoverflow
gstackoverflow

Reputation: 37034

Is it hibernate bug?

I write

    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();

It works good

second situation:

    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()

log:

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 ?, ? )

it doesn't works

and third:

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();

log:

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 (?, ?) )

it works good

P.S. Pay attention to the Brackets around :skillIdList

if I use setParameterList("argument",value) and argument in query 2 times, then first time hibernate substitutes brackets and in second - none

Upvotes: 1

Views: 504

Answers (1)

Bozho
Bozho

Reputation: 597106

The syntax of IN requires the brackets.

As of why the 3rd example is working, two guesses:

  • hibernate has a functionality to automatically append missing brackets, but somehow this doesn't extend to subqueries
  • the sql server executes the sub-select first, and then the other query becomes redundant with the parameters you've passed, and so is not executed.

Upvotes: 2

Related Questions