0

I have two entities with a one-to-many relationship. I want to get all entities that are tied to a set of the other entity. This are my classes:

public class Instance {

    @Id
    @GeneratedValue
    private long id;

    @OneToMany(mappedBy = "instance")
    private Set<Action> actions = new HashSet<>();

}

public class Action {

    @Id
    @GeneratedValue
    private long id;

    @ManyToOne
    @JoinColumn(name = "instance_id")
    private Instance instance;

}

Furthermore I have the following repository:

public interface InstanceRepository extends JpaRepository<Instance, Long> {

    List<Instance> findByActions(Set<Action> actions);

}

When I call the method with empty or single element sets, I get no errors. But if the set contains more elements I get an exception. MySQL says Operand should contain 1 column(s). The generated SQL for empty or single element sets is

select instance0_.id as id1_3_
from instance instance0_
left outer join action actions1_
on instance0_.id=actions1_.instance_id
where actions1_.id=?

and for other sets

select instance0_.id as id1_3_
from instance instance0_
left outer join action actions1_
on instance0_.id=actions1_.instance_id
where actions1_.id=(?, ?, ?, ...)

This is obviously wrong and it should be something like

select instance0_.id as id1_3_
from instance instance0_
left outer join action actions1_
on instance0_.id=actions1_.instance_id
where actions1_.id in (?, ?, ?, ...)

Why does Hibernate generate this SQL and how do I fix it?

4
  • are you 100% certain Spring Data JPA is supposed to support collections as a parameter, and that behavior is actually defined? Commented Jun 11, 2015 at 11:04
  • Did you try to defining the query yourself using a @Query("select ...") Tag? docs.spring.io/spring-data/jpa/docs/current/reference/html/… Commented Jun 11, 2015 at 11:12
  • @Ajan already provided the correct answer. I was missing the In at the end of the query method. Commented Jun 11, 2015 at 11:14
  • You need to put the suffix In for every property for which you plan to pass a collection of values. See @Ajan's answer below. Commented Jun 11, 2015 at 11:15

1 Answer 1

4

According to the Spring Data spec you have to define this method as:

List<Instance> findByActionsIn(Collection<Action> actions);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.