CODEWITHSUNDEEP
springspring-bootobjectnestedspring-data-jpa
Martin
Martin

Reputation: 87

Spring Data JPA Query by Example with access to nested Objects Attributes

I use Query by Example and want to know how I can find objects with certain properties in the nested objects.

A plan anyone?

Here is my example Code:

    ExampleMatcher matcher = ExampleMatcher.matching()
      .withMatcher("offer2product.id.productId", match -> match.exact()
              );

    Offer2ProductId id = new Offer2ProductId();
    id.setProductId(1337L);

    Offer2Product offer2Product = new Offer2Product();
    offer2Product.setId(id);

    Set<Offer2Product> offer2productSet = new HashSet<>();
    offer2productSet.add(offer2Product);

    Offer probe = new Offer();
    probe.setOffer2productSet(offer2productSet);

    Example<Offer> example = Example.of(probe, matcher);
    List<Offer> offerList = offerRepository.findAll(example);

Upvotes: 5

Views: 5102

Answers (2)

Kelvin Biserra
Kelvin Biserra

Reputation: 1

man actually can do it, follow the example below, where demand has a list of labels, I don't have time to explain everything but soon I'll update this post.

@Repository
@RequiredArgsConstructor
public class DemandaFilterRepository {

    private final EntityManager entityManager;

    public Page<Demanda> findAll(DemandaFilterDTO demandaFilter, Pageable pageable) {
        String query = "select d from Demanda d where 1=1";
        Map<String, Object> parameters = new HashMap<>();
        if (demandaFilter.getId() != null) {
            query += " and d.id = :id";
            parameters.put("id", demandaFilter.getId());
        }
        if (!StringUtils.isEmpty(demandaFilter.getTenant())) {
            query += " and upper(d.tenant) = upper(:tenant)";
            parameters.put("tenant", demandaFilter.getTenant());
        }
        if (!StringUtils.isEmpty(demandaFilter.getAssunto())) {
            query += " and upper(d.assunto) like  upper(:assunto)";
            parameters.put("assunto", "%" + demandaFilter.getAssunto() + "%");
        }
        if (!StringUtils.isEmpty(demandaFilter.getDescricao())) {
            query += " and upper(d.descricao) like upper(:descricao)";
            parameters.put("descricao", "%" + demandaFilter.getDescricao() + "%");
        }
        if (!StringUtils.isEmpty(demandaFilter.getEtiqueta())) {
            query = query.replace("Demanda d", "Demanda d inner join d.etiquetas etiqueta");
            query += " and upper(etiqueta.descricao) = upper(:etiqueta)";
            parameters.put("etiqueta", demandaFilter.getEtiqueta());
        }
        if (!StringUtils.isEmpty(demandaFilter.getNomeDemandante())) {
            query += " and upper(d.demandante.nome) like upper(:nomeDemandante)";
            parameters.put("nomeDemandante", "%" + demandaFilter.getNomeDemandante() + "%" );
        }
        if (!StringUtils.isEmpty(demandaFilter.getDtInclusao())) {
            query += " d.dtInclusao like :dtInclusao";
            parameters.put("dtInclusao", "%" + demandaFilter.getDtInclusao() + "%");
        }
        query += "  ORDER BY d.id DESC ";
        TypedQuery<Demanda> typedQuery = entityManager.createQuery(query, Demanda.class)
                .setMaxResults(pageable.getPageSize())
                .setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        parameters.forEach(typedQuery::setParameter);
        return new PageImpl<>(typedQuery.getResultList());
    }
}

this is a class for one of a small project that I'm working on ...

Upvotes: 0

gosia
gosia

Reputation: 61

Quoting Spring data documentation: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

Currently, only SingularAttribute properties can be used for property matching.

In your example, you want to search by a property that is a Set<> (offer2productSet), 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#L112

Upvotes: 1

Related Questions