8 ways to write queries in Spring Data
Intro
Spring Data is a great way to simplify your code accessing the database. It also gives a lot of flexibility in terms of available abstractions. Programmers can choose from high-level constructs requiring minimum amount of code or dive deep into nitty-gritty implementation details, while accepting a more verbose solution.
Kickstarting a project with Spring Data is as simple as adding this dependency:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>Assuming we have such entity:
@Entity
public class Employee {
@Id
@GeneratedValue
private Long id;
private String name;
private BigDecimal salary;
public Employee(Long id, String name, BigDecimal salary) {
this.id = id;
this.name = name;
this.salary = salary;
}
// getters and setters
}…stored in the following table:
CREATE TABLE employee (
id BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(255),
salary DECIMAL(19,2)
);Let’s add the following test data:
INSERT INTO employee (id, name, salary) VALUES (1, 'Bob', 5000.00);
INSERT INTO employee (id, name, salary) VALUES (2, 'Trent', 7500.00);
INSERT INTO employee (id, name, salary) VALUES (3, 'Alice', 10000.00);1) Filtering based on method names
By carefully crafting a repository method name we can avoid writing even a single line of SQL code.
For example this method signature:
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
List<Employee> findByNameContainingIgnoreCaseAndSalaryGreaterThanOrderBySalaryDesc(
String name, BigDecimal salary);
}…will cause Hibernate to spit out something along the lines of:
select employee0_.id as id1_0_,
employee0_.name as name2_0_,
employee0_.salary as salary3_0_
from employee employee0_
where (upper(employee0_.name) like upper(?) escape ?)
and employee0_.salary > ?
order by employee0_.salary descWe can easily select employees whose name contains a given string of characters and whose salary is above a threshold. No SQL, no JPQL, no HQL, just Java code.
Now, with the test data inserted earlier, when we call this:
repository.findByNameContainingIgnoreCaseAndSalaryGreaterThanOrderBySalaryDesc(
"LIC", new BigDecimal("8000"));…we should get a one-element list containing only Alice:
[Employee{id=3,name='Alice',salary=10000.00}]2) Using @Query annotation
The whole process of experimentation and trying to come up with the right method name is fun and exciting, but sometimes the end result is not that satisfying.
findByNameContainingIgnoreCaseAndSalaryGreaterThanOrderBySalaryDesc is quite long and looks clunky. Try to say it in one breath. It feels like we have traded readability for a quicker result.
It may be worth to replace this long method name with a shorter equivalent, where we can decide how to call the method, but the implementation in JPQL has to be provided in return (there is no free lunch I am afraid):
@Query(
"SELECT e FROM Employee e " +
"WHERE lower(e.name) LIKE lower(concat('%', :name, '%')) " +
"AND e.salary > :salary " +
"ORDER BY e.salary DESC"
)
List<Employee> findByNameLikeAndSalaryAbove(
@Param("name") String name, @Param("salary") BigDecimal salary);Now the method name looks friendlier:
repository.findByNameLikeAndSalaryAbove("LIC", new BigDecimal("8000"));3) Native @Query
We have sorted out the problem of having too long method names, but in the process we have introduced rather unpleasant implementation of case-insensitive LIKE condition:
WHERE lower(e.name) LIKE lower(concat('%', :name, '%'))In some databases there is a nicer ILIKE keyword that can be used to make case-insensitive matching with less effort.
The code could then be refactored to this (please note nativeQuery attribute set to true):
@Query(
value = "SELECT * FROM employee e " +
"WHERE e.name ILIKE %:name% " +
"AND e.salary > :salary " +
"ORDER BY e.salary DESC",
nativeQuery = true
)
List<Employee> findByNameLikeAndSalaryAbove(
@Param("name") String name, @Param("salary") BigDecimal salary);The caveat here is that we expose ourselves to a possible vendor lock-in so usually it is better to stay on the safe side and use nativeQuery sparingly.
Here nativeQuery just makes the code nicer to look at and it might be up for debate whether it is the best solution in this scenario. There are, however, situations when you absolutely need it. It is up to you to make the call when it should be used and when not. The decision is not always easy, but programming is the art of trade-offs after all.
4) Query by example
Another interesting alternative is querying by providing an example record:
Employee employee = new Employee(null, "LIC", null);
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("name", contains().ignoreCase());
List<Employee> result = repository.findAll(Example.of(employee, matcher));
// result=[Employee{id=3,name='Alice',salary=10000.00}]Unfortunately, this is mostly limited to strings as stated in the docs which boils down to number expressions (e.g. greater/lower than) not being supported:
5) Specifications
A more advanced option are specifications, which were built on top of criteria API in JPA.
Here is how the previous conditions can be implemented as specifications:
public class EmployeeSpecs {
public static Specification<Employee> hasNameLike(String name) {
return (Specification<Employee>) (employee, query, builder) ->
builder.like(
builder.lower(employee.get("name")),
"%" + name.toLowerCase() + "%");
}
public static Specification<Employee> hasSalaryAbove(BigDecimal salary) {
return (Specification<Employee>) (employee, query, builder) ->
builder.greaterThan(employee.get("salary"), salary);
}
}After adding JpaSpecificationExecutor to the list of interfaces implemented by the repository:
@Repository
public interface EmployeeRepository extends JpaSpecificationExecutor<Employee> {
...
}…you can select elements based on specifications by calling an overloaded findAll method:
public interface JpaSpecificationExecutor<T> {
/**
* Returns all entities matching the given {@link Specification}.
* @param spec can be {@literal null}.
* @return never {@literal null}.
*/
List<T> findAll(@Nullable Specification<T> spec);
}The best part is that specifications can be mixed together allowing for better customizability and reusability:
repository.findAll(
hasNameLike("LIC").and(hasSalaryAbove(new BigDecimal("8000"))));6) Querydsl
One downside of specifications is that they are not strictly typed, meaning we can run into issues when misstyping a property name or if we assume incorrect property type.
To enforce a stricter name and type checking Querydsl can be used:
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>4.1.3</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>4.1.3</version>
</dependency>
</dependencies>It works by generating special classes prepended with “Q” and based on the entity classes. In our case Querydsl will generate QEmployee class from the corresponding Employee entity.
Below you can find Maven configuration for generating Q* classes during compilation:
<build>
<plugins>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>After setting it up you can write code like this:
QEmployee employee = QEmployee.employee;
repository.findAll(
employee.name.likeIgnoreCase("%LIC%")
.and(employee.salary.gt(new BigDecimal("8000"))));What’s great about Querydsl is we get autocompletion:
Custom repositories
It is very likely, that at one point you will reach a situation where all the above solutions do not work. For such scenarios Spring Data provides a way to strip all the layers of abstractions and get down to writing a more specific implementation.
The assumption is that you will rarely fallback to writing low-level custom code and most of the time you will stick to Spring Data repositories cherishing the features described so far.
In order to extend an existing Spring Data repository you need to create a dedicated repository interface:
public interface EmployeeRepositoryCustom {
List<Employee> findByNameLikeAndSalaryCustom(String name, BigDecimal salary);
}…that is then extended by your original repository:
public interface EmployeeRepository extends EmployeeRepositoryCustom {
...
}Finally, you should put your code inside an implementation of the custom interface:
public class EmployeeRepositoryCustomImpl implements EmployeeRepositoryCustom {
@Override
public List<Employee> findByNameLikeAndSalaryCustom(
String name, BigDecimal salary) {
// <custom code goes here>
}
}Spring Data will do all the plumbing for you so that when findByNameLikeAndSalaryCustom is called on the interface the call is then correctly propagated to the implementation inside EmployeeRepositoryCustomImpl:
@Autowired
private EmployeeRepository repository;
...
repository.findByNameLikeAndSalaryCustom("LIC", new BigDecimal("8000"));7) Custom repository with Criteria API
Going back to our initial problem of having to overcome difficulties with previous solutions here is how JPA criteria API can play together with custom repositories to implement dynamic queries:
public class EmployeeRepositoryCustomImpl implements EmployeeRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<Employee> findByNameLikeAndSalaryCustom(String name, BigDecimal salary) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> query = builder.createQuery(Employee.class);
Root<Employee> employee = query.from(Employee.class);
Predicate criteria = builder.conjunction();
if (name != null) {
Predicate nameLike = builder.like(
builder.lower(employee.get("name")),
"%" + name.toLowerCase() + "%");
criteria = builder.and(criteria, nameLike);
}
if (salary != null) {
Predicate salaryAbove = builder.greaterThan(employee.get("salary"), salary);
criteria = builder.and(criteria, salaryAbove);
}
query.select(employee).where(criteria);
return entityManager.createQuery(query).getResultList();
}
}What immediately stands out is the verbosity of this solution, but it works quite well when you need to implement a search capability where criteria changes frequently.
8) Old school approach using StringBuilder
You should be able to solve 99% of problems using one of the previous 7 different alternatives.
However, so far most of the solutions relied on JPA (and JPQL) which, like any other abstraction, has its own shortcomings. You may want to have in your toolkit something highly-customizable that can circumvent JPA.
If you mix the ol’ school approach of building the SQL query yourself together with custom repositories you might end up with something like this:
public class EmployeeRepositoryCustomImpl implements EmployeeRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<Employee> findByNameLikeAndSalaryCustom(String name, BigDecimal salary) {
Map<String, Object> params = new HashMap<>();
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM employee e WHERE 1=1 ");
if (name != null) {
sql.append("AND e.name ILIKE :name ");
params.put("name", "%" + name + "%");
}
if (salary != null) {
sql.append("AND e.salary > :salary ");
params.put("salary", salary);
}
sql.append("ORDER BY e.salary DESC");
Query query = entityManager.createNativeQuery(sql.toString(), Employee.class);
for (Entry<String, Object> param : params.entrySet()) {
query.setParameter(param.getKey(), param.getValue());
}
return query.getResultList();
}
}Such code has many disadvantages: it is incredibly prone to typos and it is also quite verbose. But this is the price to be paid for having the flexibility of accessing the database in a very direct way.
Still a good rule of thumb is to use it as a last resort. If it is unavoidable then you have to do what you have to do, but considering the alternatives is advisable.