CODEWITHSUNDEEP
javajpaspring-bootspring-data
DevDio
DevDio

Reputation: 1564

How to search with JpaRepository and nested list of objects?

Description

There is a PersonRepository and Person entity, Person class contains List<Qualification>. Qualification class has 3 simple fields.

I have tried to add @Query annotation on custom method and use JPQL to get the results, but Qualification class fields were not available for manipulation in JPQL as it repository itself contains List<Qualification> instead of just a simple field of Qualification.

How can I search by these Qualification's nested fields?

Query

Now I need to find list of person entity where qualification's experienceInMonths is greater than 3 and less than 9 AND qualification's name field = 'java'.

Code

Person.java

@Data
@Entity
public class Person {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private String id;

@NotEmpty
@Size(min = 2)
private String name;

@NotEmpty
@Size(min = 2)
private String surname;

@ElementCollection(targetClass = java.util.ArrayList.class, fetch = FetchType.EAGER)
private List<Qualification> qualifications = new ArrayList<>();

}

PersonRepository.java

@Repository
public interface PersonRepository extends JpaRepository<Person, String> {
}

Qualification.java

@Data
@AllArgsConstructor
public class Qualification implements Serializable {

    @Id @GeneratedValue
    private String id;
    private String name;
    private String experienceInMonths;
}

EDIT: not duplicate of this post, as here is the collection of nested objects. Not just single reference.

Upvotes: 16

Views: 31326

Answers (2)

KayV
KayV

Reputation: 13835

W can use ‘_’ (Underscore) character inside the method name to define where JPA should try to split.

In this case our method name will be

List<Person> findByQualification_name(String name);

Upvotes: 1

Cepr0
Cepr0

Reputation: 30279

First, change experienceInMonths from String to int (otherwise you can not compare the string with the number). Then you can try to use this 'sausage':

List<Person> findByQualifications_experienceInMonthsGreaterThanAndQualifications_experienceInMonthsLessThanAndName(int experienceGreater, int experienceLess, String name);

Or you can try to use this pretty nice method:

@Query("select p from Person p left join p.qualifications q where q.experienceInMonths > ?1 and q.experienceInMonths < ?2 and q.name = ?3")
List<Person> findByQualification(int experienceGreater, int experienceLess, String name);

Upvotes: 20

Related Questions