CODEWITHSUNDEEP
javahibernatejpaspring-data-jpaspring-data
adamine
adamine

Reputation: 143

How to query @ElementCollection with Specifications

I have an entity with different fields including an @ElementCollections list:

@Entity
public class Property {

//different attributes

@ElementCollection
@Enumerated(EnumType.STRING)
protected List<PropertyCriteria> criterias = new ArrayList<>();

}

where PropertyCriteria is a simple enum class.

I would like to implement a search method which receive multiple values, and return the correspondent listings.

@GetMapping(value = "/search")
public Page<Property> search(
  @RequestParam Map<String, String> filters,
  @RequestParam(value = "criterias") List<PropertyCriteria> criterias, 
  Pageable pageable) {
    return service.search(filters, criterias, pageable);
}

In order to do that, it seems to me that specifications is the best way to do it, since I will have others attributes to filter. The criterias parameters is a list containing all the values needed in the property(ElementCollections). And the filters Map is a key value parameter with all the others attributes I receive. I tried something like this, but it doesn't work:

public Page<Property> search(Map<String, String> filters, List<PropertyCriteria> criterias,
  Pageable pageable) {
  Specification<Property> specification = Specification
  .where(criteriasFilter(criterias)
  //.and(others specifications on other attributes)
  ;
  return repository.findAll(specification, pageable);
}

public static Specification<Property> criteriasFilter(List<PropertyCriteria> criterias) {
    Specification<Property> propertySpecification = (root, query, builder) -> {
      query.distinct(true);
      Predicate where = builder.conjunction();
      return builder.and(where, root.joinList(Property_.CRITERIAS).in(
          criterias));
    };
    return propertySpecification;
  }

Upvotes: 0

Views: 1161

Answers (2)

Roberto Petrilli
Roberto Petrilli

Reputation: 857

In my case this code worked

        Specification<Preparazione> newSpec = (root, query, qb) -> {
            return qb.isMember(idIndicazione, root.get("indicazioniTer"));
        };

where my entity is like this

    @Entity
    public class Preparazione {
        @Id
        public Long idPreparazione;
        
        @ElementCollection(targetClass = Long.class)
        @Column(name = "id_indicazione")
        @CollectionTable(
                name = "preparazioni_indicazione",
                joinColumns = @JoinColumn(name = "id_preparazione")
        )
        public List<Long> indicazioniTer = new ArrayList<>();
    }

and idIndicazione is the variable containing the value to search

Upvotes: 0

Christian Beikov
Christian Beikov

Reputation: 16400

If you need all criterias to match it's best to create a subquery with a count and check if the count is expected. Something like this:

public static Specification<Property> criteriasFilter(List<PropertyCriteria> criterias) {
    Specification<Property> propertySpecification = (root, query, builder) -> {
      Subquery<Long> subquery = query.subquery(Long.class);
      Root<Property> r = subquery.correlate(root);
      subquery.where(r.joinList(Property_.CRITERIAS).in(
          criterias));
      subquery.select(builder.count(builder.literal(1)));
      return builder.equal(subquery, (Long) criterias.size());
    };
    return propertySpecification;
}

Upvotes: 2

Related Questions