Spring JPA Query problem using 'in' operator

Question

In Spring Intreface I have following code:

@Query("select gl from GroupEntity gl where ?1 in gl.contacts ")
List<GroupEntity> findGroupsByContact(ContactEntity contactEntity);

and want to find all Group with contains a Contact, but IntelliJ idea shows error after where condition ( ?1 in gl.contacts) and shows this error:

'(' or <input parameter> expected, got 'gl'

what is the solution?

related parts of Entities are:

@Entity
@Table(name = "contactGroup")
public class GroupEntity {
...
    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<ContactEntity> contacts;
...
}

but in ContactEntity did not use @ManyToMany for GroupEntity(Is it the root of the problem?)

@Entity
@Table(name = "contact")
public class ContactEntity {
...
}

please show entity and Repository why create query when repository methods are ez and available? — silentsudo
– silentsudo, Commented Oct 19, 2020 at 12:04

Julien · Accepted Answer · 2020-10-19 13:31:59Z

3

You can't use the in JPQL operator when the entity attribute is the right operant.

You should use member of instead. "Member of" should be used when you provide a value to the query in order to check if it belongs to some collection in your entity. "In" should be used when you provide a list of values to the query in order to check if a property in your entity belong to this list.

Examples:

@Entity
public class EntityA {

@Id
private String id;

@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<EntityB> entities;

}

JPQL:

SELECT a FROM EntityA a WHERE a.id IN :ids

SELECT a FROM EntityA a WHERE :b MEMBER OF a.entities

answered Oct 19, 2020 at 13:31

Julien

2,2363 gold badges27 silver badges31 bronze badges

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

Comments

Sreehari Puliyakkot · Accepted Answer · 2020-10-20 05:08:21Z

2

If the relationships are established properly then you can query Groups from Contact repository.

Group.java

  @OneToMany(fetch=FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "group", orphanRemoval = true)
    List<Contact> contacts = new ArrayList<Contact>();

Contact.java

@ManyToOne
Group group;

edited Oct 20, 2020 at 5:08

answered Oct 19, 2020 at 12:07

Sreehari Puliyakkot

7361 gold badge7 silver badges21 bronze badges

4 Comments

Eklavya Over a year ago

OP wants to is a single contactEntity is in the list contact of group. Your answer is not relevant with what OP wants.

Sreehari Puliyakkot Over a year ago

Answers need not be direct to the point. It's about sharing the right approahc and what works in projects that implement JPA in production.

Eklavya Over a year ago

First of all your solution is wrong, did you test it ? And the second OP don't want to find a list of ids, OP wants groups those having a specific contactEntity. Please read the post again.

Sreehari Puliyakkot Over a year ago

That's answered in the second part of my answer. Why do you think that's wrong? If ManyToMany relationship defined then what's the confusion in querying groups? Anyway for clarity I'm editing and removing my first part.

Collectives™ on Stack Overflow

Spring JPA Query problem using 'in' operator

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related