CODEWITHSUNDEEP
javaspringhibernatespring-data-jpaspring-data
Heatmanofurioso
Heatmanofurioso

Reputation: 1028

Spring Data JPA Query By Example with related entities

I seem to be unable to add QueryByExample probes that match related entities.

@Entity
@Data
public class ArtistEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String description;
    @ManyToMany(fetch = FetchType.EAGER)
    private Set<GenreEntity> genreList = new HashSet<>();
    @Version
    private Long version;
}

@Entity
@Data
public class GenreEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @Version
    private Long version;
}

@Repository
public interface ArtistRepository extends JpaRepository<ArtistEntity, Long> {
}

When I try the following query, according to Hibernate's logs, my probe isn't running any conditions against the Genre

GenreEntity genreEntity = new GenreEntity();
genreEntity.setName("Heavy Metal");

ArtistEntity artistEntity = new ArtistEntity();

Set<GenreEntity> genreEntitySet = new HashSet<>();
genreEntitySet.add(genreEntity);

artistEntity.setGenreList(genreEntitySet);

Example<ArtistEntity> example = Example.of(artistEntity);
Pageable pagination = PageRequest.of(0, 10);
artistRepository.findAll(example, pagination);

I also tried looking on the Spring Data JPA documentation regarding QBE, but I didn't find anything specifically mentioning this limitation, which brought me to assume it's an unexpected behaviour.

Upvotes: 3

Views: 4338

Answers (2)

İsmail Y.
İsmail Y.

Reputation: 3945

Currently, you cannot do this with Query By Example.

The spring document states that this only works with SingularAttribute.

Currently, only SingularAttribute properties can be used for property matching. https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example.running

You want to search by a property that is a Set<GenreEntity> (genreList), which is a PluralAttribute. It is not possible to search by this field.

It will be ignored when building a query, as can be seen here: https://github.com/spring-projects/spring-data-jpa/blob/master/src/main/java/org/springframework/data/jpa/convert/QueryByExamplePredicateBuilder.java#L127

You can use Specification.

Advanced Spring Data JPA - Specifications and Querydsl. https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

For this you need to extend from interface JpaSpecificationExecutor:

public interface ArtistRepository extends 
    JpaRepository<ArtistEntity>, JpaSpecificationExecutor<ArtistEntity> {}
  • And you also need to implement your custom Specification<T>.
  • And then you can use findAll(Specification<T> spec, Pageable pageable).

Upvotes: 7

torshid
torshid

Reputation: 167

You may just use this library which supports nested fields and much more: https://github.com/turkraft/spring-filter

It will let you run search queries such as:

/search?filter= average(ratings) > 4.5 and brand.name in ('audi', 'land rover') and (year > 2018 or km < 50000) and color : 'white' and accidents is empty

Upvotes: 0

Related Questions