Previously we discovered the advantages of repositories compared to classic DAO layer. We saw that repositories supported both native SQL and JPQL queries. But do they support other features of JPA, as Criteria API ? Yes, and we'll see it in this article.
In the first part of this article we'll the main components used to defined a Criteria API query inside Spring Data JPA repositories. We don't explain again the idea of Criteria API. Some article were written about and you can start by read the JPA Criteria queries. At the second time we'll write our own implementation of dynamic queries inside one repository.
How Criteria API query inside Spring JPA repository works ?
Key elements of customized Criteria API queries inside repositories are org.springframework.data.jpa.repository.JpaSpecificationExecutor<T> and org.springframework.data.jpa.domain.Specification<T>. JpaSpecificationExecutor enables the possibility of using Specification instances as queries criteria. JpaSpecificationExecutor is a simple interface with 5 defined methods: findOne (gets one row), findAll, findAll pageable, findAll sortable (all found two or more rows corresponding to criteria), count (count the numbers of corresponding rows).
Specification is also an interface. By implementing it, we have to define a toPredicate method. It returns javax.persistence.criteria.Predicate, used as a condition of WHERE clause in Criteria JPA queries. Both interfaces must be typed to the entity concerned by generated query.
To resume these first points, our repository must extends JpaSpecificationExecutor interface and the WHERE conditions must be placed to Specification's toPredicate method. Now, how these two points are transplanted into Spring Data ecosystem ?
As we've already seen, Spring "translates" all CrudRepository interfaces into SimpleJpaRepository class. This class contains the same methods, with the same signatures, as JpaSpecificationExecutor. More, SimpleJpaRepository implements JpaSpecificationExecutor interface. The queries for the 5 methods saw at the begin of this part are implemented through the call of given methods:
protected TypedQuery<T> getQuery(Specification<T> spec, Sort sort) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<T> query = builder.createQuery(getDomainClass());
Root<T> root = applySpecificationToCriteria(spec, query);
query.select(root);
if (sort != null) {
query.orderBy(toOrders(sort, root, builder));
}
return applyRepositoryMethodMetadata(em.createQuery(query));
}
private <S> Root<T> applySpecificationToCriteria(Specification<T> spec, CriteriaQuery<S> query) {
Assert.notNull(query);
Root<T> root = query.from(getDomainClass());
if (spec == null) {
return root;
}
CriteriaBuilder builder = em.getCriteriaBuilder();
Predicate predicate = spec.toPredicate(root, query, builder);
if (predicate != null) {
query.where(predicate);
}
return root;
}
Does it look familiar ? Yes, internally all Specification objects are translated to Predicate ones thanks to implemented toPredicate method. After this translation, all predicates are used as "WHERE" conditions in initially constructed SELECT query. At the end, the query is constructed through createQuery method and the results are extracted with getResultList or getSingleResult of EntityManager.
How to implement Criteria API query inside Spring Data JPA repository ?
It's now our turn to implement Criteria API inside Spring Data JPA repository. Let's begin by show entity with its metamodel, and corresponding repository:
// Product.java
@Entity
@Table(name="products")
public class Product {
private Integer id;
private String name;
private String color;
private double price;
private Date addedDate;
public void setId(Integer id) {
this.id = id;
}
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name="id")
public Integer getId() {
return this.id;
}
public void setName(String name) {
this.name = name;
}
@Column(name="name")
@NotEmpty
public String getName() {
return this.name;
}
@Column(name="color")
public String getColor() {
return this.color;
}
@Column(name="price")
public double getPrice() {
return this.price;
}
@Column(name="added_date")
@Temporal(TIMESTAMP)
public Date getAddedDate() {
return this.addedDate;
}
public void setColor(String color) {
this.color = color;
}
public void setPrice(double price) {
this.price = price;
}
public void setAddedDate(Date addedDate) {
this.addedDate = addedDate;
}
@Override
public String toString() {
return "Product {"+this.name+"}";
}
}
// Product_.java
@StaticMetamodel(Product.class)
public class Product_ {
public static volatile SingularAttribute<Product, Integer> id;
public static volatile SingularAttribute<Product, String> name;
public static volatile SingularAttribute<Product, String> color;
public static volatile SingularAttribute<Product, Double> price;
public static volatile SingularAttribute<Product, Date> addedDate;
}
// ProductRepository.java
public interface ProductRepository extends CrudRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}
Nothing complex so far. Just an entity with its static metamodel. Repository remains as simplest as possible too. A little bit more code is needed to implement the Specification object:
public class ProductSpecifications {
/**
* Specification used to construct dynamic query based on JPA Criteria API.
*
* @param price Demanded price (must be bigger than 0 to be taken for the query)
* @param name Product's name (must be not null to be taken for the query)
* @return Specification to use with JpaSpecificationExecutor
*/
public static Specification<Product> withDynamicQuery(final int price, final String name) {
return new Specification<Product>() {
@Override
public Predicate toPredicate(Root<Product> product, CriteriaQuery<?> query, CriteriaBuilder builder) {
if (price == 0 && name == null) {
throw new IllegalStateException("At least one parameter should be provided to construct complex query");
}
List<Predicate> predicates = new ArrayList<Predicate>();
if (price > 0) {
predicates.add(builder.and(builder.gt(product.get(Product_.price), price)));
}
if (name != null) {
predicates.add(builder.and(builder.equal(product.get(Product_.name), name.toLowerCase())));
}
Predicate[] predicatesArray = new Predicate[predicates.size()];
return builder.and(predicates.toArray(predicatesArray));
}
};
}
}
In our dynamic query, we want to retrieve the products by theirs name (only if provided) and by theirs price (only when bigger than 0). The code uses Criteria API to construct a chain of Predicate objects. Only returned Specification is a part of Spring Data JPA project. After that, we need to write JUnit test case to see if dynamic query works well:
public class JpaRepositoryTest {
@Autowired
private ProductService productService;
@Test
public void complexQuery() {
List<Product> teas = productService.getByDynamicQuery("tea", 35);
assertTrue("Unexpected number of expensive teas was found ("+teas.size()+", instead of 1)",
teas.size() == 1);
List<Product> allTeas = productService.getByDynamicQuery("tea", 0);
assertTrue("Some teas were not found (expected 2, but only "+allTeas.size() + " were found)",
allTeas.size() == 2);
List<Product> expensiveProducts = productService.getByDynamicQuery(null, 40);
assertTrue("coffee and tea should be present in expensive product's query, but they weren't (result:"+expensiveProducts+")",
expensiveProducts.toString().contains("coffee") && expensiveProducts.toString().contains("tea"));
List<Product> gold = productService.getByDynamicQuery("golden", 0);
assertTrue("They're no gold in the database, but some products corresponding to the query were found",
gold.size() == 0);
}
}
Below, you can find needed rows to pass successfully these tests:
CREATE TABLE IF NOT EXISTS `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `price` double NOT NULL, `color` varchar(15) NOT NULL, `added_date` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `products` (`id`, `name`, `price`, `color`, `added_date`) VALUES (11197, 'tea', 31.5, 'white', '2014-05-16 14:08:13'), (11219, 'coffee', 40.5, 'black', '2014-05-16 14:15:21'), (11220, 'tea', 45.5, 'brown', '2014-05-16 14:15:21'), (11221, 'milk', 31.5, 'white', '2014-05-16 14:15:21');
Thanks to this data, all tests should pass without problems.
We can see that even if Spring Data JPA repositories are considered as a static stuff (interfaces with String-based queries or dynamic findBy ones), we can introduce there some of dynamism. Thanks to special JpaSpecificationExecutor interface, the repository will be able to treat JPA's Predicate clauses generated within Specification objects. This feature can be useful for all kind of dynamically generated queries. In our case, instead of have 3 defined queries, we have only one that takes dynamic parameters in signature.
