cgval cgval - 2 months ago 18
MySQL Question

Spring JPA using Specifications and CriteriaQuery on Joint Tables

I am using JPA specifications and CriteriaQuery in order to add where clauses to my entities..

Domain Class:

public class Domain {

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "account_id")
private Long accountId;

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "domain_id")
private List<Notification> notification;
}


Notification Class:

public class Notification {

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "domain_id")
private Long domainId;

@Column(name = "contact")
private String contact;
}


Then I have the following class which I use to create Specifications:

public class DomainSpecification implements Specification<Domain> {

final private SearchCriteria criteria;

public DomainSpecification(SearchCriteria searchCriteria) {
this.criteria = searchCriteria;
}

@Override
public Predicate toPredicate(
Root<Domain> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

switch (criteria.getOperation()) {
case ":":
if (root.get(criteria.getKey()).getJavaType() == String.class) {
return builder.like(
root.<String>get(criteria.getKey()),
"%" + criteria.getValue() + "%");
} else {
return builder.equal(root.get(criteria.getKey()),
criteria.getValue());
}
case "=":
return builder.equal(root.get(criteria.getKey()),
criteria.getValue());
default:
return null;
}
}

}


And the SearchCriteria Object:

public class SearchCriteria {

private final String key;
private final String operation;
private final Object value;

public SearchCriteria(String key, String operation, Object value) {
this.key = key;
this.operation = operation;
this.value = value;
}

public String getKey() {
return key;
}

public String getOperation() {
return operation;
}

public Object getValue() {
return value;
}

}


Then, whenever I want to create a where clause, I do the following:

DomainSpecification idSpecification
= new DomainSpecification(
new SearchCriteria("id", "=", domainId));

Specifications<Domain> specifications = Specifications.where(idSpecification);

final Domain domain = this.domainRepository.findOne(specifications);


This would search by the field called
id
in the Domain Entity..

Now, how can I also filter by the entity that I am joining with ? For example, I want to filter where Domain.id = 10 and Notification.contact = "abc"?

Thanks for your help

Answer

You could wrap your Specifications definitions into helper class:

public class DelegationSpecificationsHelper {

    public static Specification<Domain> notificationContactSpec(String contact) {
        return (root, query, cb) -> cb.equal(root.join("notification").get("contact"), contact);
    }

    public static Specification<Domain> idSpec(SearchCriteria searchCriteria) {
        switch (criteria.getOperation()) {
          case ":":
            if (root.get(criteria.getKey()).getJavaType() == String.class) {
              return builder.like(
                      root.<String>get(criteria.getKey()),
                      "%" + criteria.getValue() + "%");
            } else {
              return builder.equal(root.get(criteria.getKey()),
                      criteria.getValue());
            }
          case "=":
            return builder.equal(root.get(criteria.getKey()),
                    criteria.getValue());
          default:
            return null;
        }
    }
}

And then you could use it like this:

Specifications<Domain> specifications = Specifications.where(DelegationSpecificationsHelper.idSpec(new SearchCriteria("id", "=", domainId))
                                                      .and(DelegationSpecificationsHelper.notificationContactSpec("someSearchString"));

After static imports and some refactoring:

SearchCriteria idCriteria = new SearchCriteria("id", "=", domainId)
Specifications<Domain> specifications = 
                 Specifications.where(idSpec(idCriteria)
                               .and(notificationContactSpec("someSearchString"));

Of course you should get rid of hardcoded values from here: cb.equal(root.join("notification").get("contact"), contact); and use some DTO object or generated JPA meta model instead.

After adding metamodel it could look like this:

 public static Specification<Domain> notificationContactSpec(String contactValue) {
        return (root, query, cb) -> cb.equal(root.join(Domain_.notification).get(Notification_.contact), contactValue);
 }

More about metamodel generation: https://docs.jboss.org/hibernate/orm/5.0/topical/html/metamodelgen/MetamodelGenerator.html