CODEWITHSUNDEEP
javahibernateplayframework
Georgi
Georgi

Reputation: 320

binding multiple values in hibernate using play framework

I am trying to build query in Play framework, I have

select * from Candidate c where (:schools member of c.schools)

After I bind :school with List with one element it returns result, but if I bind List with multiple elements nothing happens.

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: {vector} [select c from models.Candidate c where (:schools0_, :schools1_ member of c.schools)  group by c.id order by RAND()]

Actually I need something like 

select * from candidate where schools in (x,x,x,x,x);

Relation between candidate and school is in linked table.

Is there any way to bind multiple values?

Upvotes: 3

Views: 2404

Answers (2)

KLE
KLE

Reputation: 24169

With Hibernate, you can also use directly the list itself.

select c from Candidate c join c.schools as school where school.id in (:schools)

The type of the :schools parameter is typed according to your ids, for example List<Int> or List<Long>.

Upvotes: 1

Georgi
Georgi

Reputation: 320

Actually I found where the problem is - member of can be used only with single value and it works fine. When we need to use multiple values the best is to use standard sql in:

 select c from Candidate c inner join c.schools as school where school.id in (25980,25981)"

Joining the linked tables is required - we can't use c.schools.id, so we need to inner join c.schools with alias to specify the column.

I think all beginners should check http://www.javatx.cn/hibernate/reference/en/html/queryhql.html

Upvotes: 4

Related Questions