Spring data – Derived queries

Till now we have looked into simple queries. Spring data has also support for custom finder methods, which means you can simply type in a method that follows a particular fomat and spring data will create the query for you. We would be building on the code we already wrote in the previous posts. Lets see this in action now. Adding new methods in EmployeeRepository.java


package com.mynotes.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.mynotes.entity.Employee;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee,Integer>{

public Employee findByFname(String fname);

public Iterable<Employee> findByLname(String lname);

}

So we created created a method ‘findByFname‘ that takes in a string argument. Spring data will inspect this method signature. The method name has the ‘findBy‘ prefix.  Spring data will strip off this prefix and then inspect the next portion of the method name which is ‘Fname‘. If we look our Employee entity we see that there is a ‘fname‘ field. Using this information spring data is able to generate a query that we can use to return entities by Fname. Similary for findByLname method. Lets use this in our Application.java :


ApplicationContext context = SpringApplication.run(Application.class, args);
EmployeeRepository repository = context.getBean(EmployeeRepository.class);

Employee aEmployee=repository.findByFname("John");
System.out.println(aEmployee);
Iterable<Employee> empList=repository.findByLname("Doe");
empList.forEach(emp->System.out.println(emp));

Output:

spring-data-derived-queries1

If you notice the hibernate query ,you will see the where caluse which has our clauses.

Lets look into other keywords thats could be added to this to support string comparisions. Lets add following in our EmployeeRepository.java:


public List<Employee> findByFnameLike(String fname);

public List<Employee> findByFnameContains(String fname);

In the first method we used the ‘Like‘ keyword which will be read by spring data which will use ‘like’ clause in our query. The argument here usually a wildcard. The second method we used ‘Contains‘ keyword. Sping data here will add wildcards on both side of our arguments. Lets test these in Application.java:


ApplicationContext context = SpringApplication.run(Application.class, args);
EmployeeRepository repository = context.getBean(EmployeeRepository.class);
List<Employee> empList=repository.findByFnameLike("%oh%");
empList.forEach(emp->System.out.println(emp));
empList=repository.findByFnameContains("an");
empList.forEach(emp->System.out.println(emp));

Output:

spring-data-derived-queries2

Similary you can add ‘StartingWith‘, ‘EndingWith‘, ‘IgnoreCase‘ etc, YOu can check the docs for all. Note that some database like Mysql are case insensitive (oracle is case sensitive by default) unless you do a binary comparison.

Lets see some examples of relational operators. Adding in EmployeeRepository.java:


public List<Employee> findBySalaryEquals(double amount);

public List<Employee> findBySalaryGreaterThan(double amount);

In the first method we used the ‘Equals‘ keyword that will match wilth the supplied argument. The second method has the ‘GreaterThan‘ keyword that will give all the employeess that have the slaary greater than the supplied argument. Testing this  in Application.java

List<Employee> empList=repository.findBySalaryEquals(150000.00);
empList.forEach(emp->System.out.println(emp));
empList=repository.findBySalaryGreaterThan(125000.00);
empList.forEach(emp->System.out.println(emp));

spring-data-derived-queries3

Similary we have ‘LessThan‘,’GreaterThanEqual‘,’LessThanEqual‘,’Between‘ keywords that we can use.

Lets see how to do Date Comparisions in our derived queries. Adding in EmployeeRepository.java:


public List<Employee> findByDobAfter(Calendar cal);

public List<Employee> findByDobBetween(Calendar cal1,Calendar cal2);

So we wan to compare using our Calendar attribute dob. We prefix it by ‘findBy‘ followed by the attibte ‘Dob’ and the the ‘After‘ keyword. This method takes in an argument of Calendar. This method will search for all the Employees having dob after the supplied date. In the second method we used ‘Between‘ keyword to find within a range. Similary we can also use ‘Before‘ keyword. Using these in Application.java


List<Employee> empList=repository.findByDobAfter(new GregorianCalendar(1990, Calendar.JANUARY, 01));
empList.forEach(emp->System.out.println("findByDobAfter==>"+emp));

empList=repository.findByDobBetween(new GregorianCalendar(1989, Calendar.JANUARY, 01),
new GregorianCalendar(1991, Calendar.JANUARY, 01));
empList.forEach(emp->System.out.println("findByDobBetween==>"+emp));

spring-data-derived-queries4

Lets see how can we combine varios clauses using logical operators. Adding inEmployeeRepository.java:


public List<Employee> findBySalaryGreaterThanOrDobAfter(double amount,Calendar cal);

public List<Employee> findBySalaryGreaterThanAndDobAfter(double amount,Calendar cal);

Above we combined 2 comparisions using ‘Or‘ and ‘And‘. Similary we can use ‘Not‘.


List<Employee> empList=repository.findBySalaryGreaterThanOrDobAfter(125000.00,
new GregorianCalendar(1991, Calendar.JANUARY, 01));
empList.forEach(emp->System.out.println("findBySalaryGreaterThanOrDobAfter==>"+emp));

empList=repository.findBySalaryGreaterThanAndDobAfter(125000.00,
new GregorianCalendar(1991, Calendar.JANUARY, 01));
empList.forEach(emp->System.out.println("findBySalaryGreaterThanAndDobAfter==>"+emp));

spring-data-derived-queries5

We can also use “OrderBy‘ keyword to sort on a particular field. We have to add ‘Asc‘ and ‘Desc‘ at the last to tell spring which order


public List<Employee> findBySalaryGreaterThanOrderByFnameAsc(double amount);

public List<Employee> findBySalaryGreaterThanOrderBySalaryDesc(double amount);

Calling these in Application.java


List<Employee> empList=repository.findBySalaryGreaterThanOrderByFnameAsc(125000.00);
empList.forEach(emp->System.out.println("findBySalaryGreaterThanOrderByFnameAsc==>"+emp));

empList=repository.findBySalaryGreaterThanOrderBySalaryDesc(125000.00);
empList.forEach(emp->System.out.println("findBySalaryGreaterThanOrderBySalaryDesc==>"+emp));

spring-data-derived-queries6

Now lets see if we want to limit the number of result returned by our query. We can use ‘Top‘, ‘First‘ for the same. An optional numeric value can be appended to top/first to specify the maximum result size to be returned. If the number is left out, a result size of 1 is assumed.


public List<Employee> findTopByOrderBySalaryDesc();

public List<Employee> findFirstByOrderByFnameAsc();

public List<Employee> findTop2BySalaryGreaterThanOrderBySalaryDesc(double amount);

Tesing in Application.java


List<Employee> empList=repository.findTopByOrderBySalaryDesc();
empList.forEach(emp->System.out.println("findTopByOrderBySalaryDesc==>"+emp));

empList=repository.findFirstByOrderByFnameAsc();
empList.forEach(emp->System.out.println("findFirstByOrderByFnameAsc==>"+emp));

empList=repository.findTop2BySalaryGreaterThanOrderBySalaryDesc(100000.00);
empList.forEach(emp->System.out.println("findTop2BySalaryGreaterThanOrderBySalaryDesc==>"+emp));

spring-data-derived-queries7

You can see other details in spring data docs. – http://docs.spring.io/spring-data/jpa/docs/current/reference/html/

All the above code is in my spring-data github repo. Next we will see Named queries.

Advertisements
%d bloggers like this: