How can I write spring data query with IN statement?

Question

I need to get Entity1's by entity2's ids

There is params for query - List numbers - ids of entity2 . And two entities with @OneToMany bound

@Entity
class Entity1 {
private Integer id;

private Entity2 entity2;

---------
@OneToMany(cascade = ALL, fetch =FetchType.EAGER)
@Column
public Entity2 getEntity2(){
return entity2;

}

and

@Entity
public class Entity2{

private Integer id;

@ManyToOne(FetchType.LAZY)
private Entity1 entity1;
}

i have working code

@Repository
public interface Entity1Repository extends JpaRepository<Entity1, Integer> {

@Query("SELECT c" +
            " FROM  Entity1 c" +
            " inner JOIN  c.entity2 a" +
            "  WHERE  a.id IN(?1)")
    Set<Entity1> findByEntity2Ids(List<Integer> pEntity2_Ids);
}

i want beautify query by using Spring Data

and write something like

Set<Entity1> findAllByEntity2In(List<Integer> pChannels);

but it doesnt work now

I catch an Enxception :

\Caused by: java.lang.IllegalArgumentException: Parameter value element [2] did not match expected type [com.test.Entity2 (n/a)]

Who knows how to solve?

Thanks !

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

UPD

I found out the query for Spring Data :

  @Repository
    public interface Entity1Repository extends JpaRepository<Entity1, Integer> {
 
    Set<Enity1> findByEntity2In(List<Integer> pEntities2Ids);

}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 24, 2018 at 8:06

lazarevsky

831 gold badge1 silver badge7 bronze badges

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

Comments

araknoid · Accepted Answer · 2017-10-06 15:53:27Z

0

Remove the brackets from the query. It should look like this:

@Query("SELECT c" +
            " FROM  Entity1 c" +
            " inner JOIN  c.entity2 a" +
            "  WHERE  a.id IN ?1")

For a better readable query, you could choose to use a named parameter instead of a positional one. Said so, your query could be like this:

@Query("SELECT c" +
            " FROM  Entity1 c" +
            " inner JOIN  c.entity2 a" +
            "  WHERE  a.id IN :ids")
    Set<Entity1> findByEntity2Ids(@Param("ids") List<Integer> pEntity2_Ids);

answered Oct 6, 2017 at 15:53

araknoid

3,1235 gold badges36 silver badges37 bronze badges

Comments

Yogi · Accepted Answer · 2017-10-06 16:26:05Z

0

You can do search by List or set of Entity2 like

Set<Entity1> findByEntity2In(Set<Entity2> entity2);

For finding by Entity2 Id, you need to write Query like

@Query("SELECT ent1 FROM  Entity1 ent1 INNER JOIN  c.entity2 ent2 WHERE  ent2.id IN :ids")
Set<Entity1> findByEntity2Ids(@Param("ids") List<Integer> pEntity2_Ids);

answered Oct 6, 2017 at 16:26

Yogi

1,92515 silver badges24 bronze badges

Collectives™ on Stack Overflow

How can I write spring data query with IN statement?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related