Complex select on EAV model Spring data jpa

Question

I have a Product Entity like below (It's simple version)

@Entity
@Table(name = "product")
public class Product {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private long id;

   @OneToMany(mappedBy = "product")
   private List<ProductAtt> attributes;
}

Each Product could have one or more Attribute. Attribute look likes below

@Entity
@Table(name = "attribute")
public class Attribute {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private long id;

   private String name;
}

So I create a relation entity like below with extra value property

@Entity
@Table(name = "product_att")
public class ProductAtt implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne
    @JoinColumn
    private Product product;

    @ManyToOne
    @JoinColumn
    private Attribute attribute;

    private int value;
}

Now I want to find all products that have some attributes with custom values. For example all products that have attribute 1 with value 3 and attribute 2 with value 40 and ... .

What is the simplest and most efficient query to do that?

Answer 1

Since the number of attributes to query is not known at design time, one of the dynamic query mechanisms supported by Spring Data JPA will have to be used. The query can certainly be built using the JPA Specification or Criteria APIs.

If using QueryDSL support, subqueries with exists can be used. The following example shows how this can be done (assuming Java 8 and QueryDSL 4).

interface ProductRepository
          extends CrudRepository<Product, Long>
                  , QueryDslPredicateExecutor<Product> {
  default Iterable<Product> findAllByAttributes(final Map<String, String> attributes) {
    final QProduct root = QProduct.product;
    BooleanExpression query = root.isNotNull();

    for (final String attribute : attributes.keySet()) {
      final QProductAttribute branch = root.attributes.any();
      final BooleanExpression subquery = branch.attribute.name.equalsIgnoreCase(attribute)
                                                          .and(branch.value.equalsIgnoreCase(attributes.get(attribute)));

      query = query.and(JPAExpressions.selectFrom(QProductAttribute.productAttribute).where(subquery).exists());
    }

    return findAll(query);
  }
}

It should be noted that the database design is such that performance problems are bound to happen, because the same table (ProductAttr) is included as many times as there are attributes to search by. This is not a problem of QueryDSL, JPA, Hibernate, SQL or the database server but the data model itself (also known as the EAV model).