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

Question

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);

Have you looked into QueryDsl?

Konstantinos
– Konstantinos

2018-11-22 10:30:52 +00:00
Commented Nov 22, 2018 at 10:30 — Konstantinos
– Konstantinos, Commented Nov 22, 2018 at 10:30

gosia · Accepted Answer · 2018-11-18 19:14:12Z

1

+50

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

answered Nov 18, 2018 at 19:14

gosia

612 bronze badges

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

Comments

Kelvin Biserra · Accepted Answer · 2020-06-24 12:26:32Z

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 ...

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related