Why does Spring Data JPA + Hibernate generate incorrect SQL?

Question

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?

are you 100% certain Spring Data JPA is supposed to support collections as a parameter, and that behavior is actually defined? — EpicPandaForce
– EpicPandaForce, 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/… — Nils
– Nils, 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. — stevecross
– stevecross, 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. — manish
– manish, Commented Jun 11, 2015 at 11:15

Arek · Accepted Answer · 2015-06-11 11:11:44Z

4

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

List<Instance> findByActionsIn(Collection<Action> actions);

edited Jun 11, 2015 at 11:11

answered Jun 11, 2015 at 11:06

Arek

3,1843 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does Spring Data JPA + Hibernate generate incorrect SQL?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related