Spring Data : Named queries, Paging and Sorting

In the previous post we saw about deried queries in spring data. However sometimes, these are not feasiable for our requirements when we have some complex queries. Here we will ee how to execute JPQL (Java Persistence Query Language) queries with spring data. We will also look into paging and sorting techniques.

Before moving forward. Lets increase our rows in DB as we will be seeing paging,sorting etc.


INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (1, 'John', 'Doe', '1990-07-03 00:00:00', 123123, 'M');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (2, 'Jane', 'Doe', '1991-02-03 00:00:00', 130000, 'F');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (3, 'Nathan', 'Drake', '1985-02-03 00:00:00', 150000, 'M');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (4, 'John', 'Snow', '1987-04-03 00:00:00', 170000, 'M');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (5, 'Sansa', 'Stark', '1988-11-12 00:00:00', 140000, 'F');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (6, 'Daenerys', 'Targaryen', '1988-10-12 00:00:00', 170000, 'F');
INSERT INTO `employee` (`id`, `first_name`, `last_name`, `dob`, `salary`, `gender`) VALUES (7, 'Tyrion', 'Lannister', '1980-02-10 00:00:00', 150000, 'M');

Lets start by writing our own queries. Adding in EmployeeRepository.java


package com.mynotes.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import com.mynotes.entity.Employee;

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

@Query("select e from Employee e where e.salary > ?1")
public List<Employee> queryUsingPositionalParameter(double amount);

@Query("from Employee e where e.salary > :amt")
public List<Employee> queryUsingNamedParameter(@Param("amt") double amount);

}

As you can see we used @Query annotations over methods (that are in our repository) to define JPQL queries. In queryUsingPositionalParameter method we used a filter on salary that will be supplied by our method. We are using the ordinal value 1 to map it with the method arguments. Its recommended not to use positional parameters and instead use named parameters. In queryUsingNamedParameter method we used a @Param to name our method arguments that could be used in out query. Also Notice that we can skip ‘select e’ here, its added implicitly if not there. Lets run these. Application.java


ApplicationContext context = SpringApplication.run(Application.class, args);
EmployeeRepository repository = context.getBean(EmployeeRepository.class);
List<Employee> empList=repository.queryUsingPositionalParameter(150000.00);
empList.forEach(emp->System.out.println("findTopByOrderBySalaryDesc==>"+emp));

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

spring-data-named-queries1

Normally we define these named queries on our Entity which they corresponds to. Lets remove all the annotation from our method in EmployeeRepository.java


package com.mynotes.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.mynotes.entity.Employee;

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

public List<Employee> queryUsingPositionalParameter(double amount);

public List<Employee> queryUsingNamedParameter(@Param("amt") double amount);

}

Adding Named queries in Employee.java


package com.mynotes.entity;

import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name = "employee")
@NamedQueries({
@NamedQuery(name = "Employee.queryUsingPositionalParameter", query = "select e from Employee e where e.salary > ?1"),
@NamedQuery(name = "Employee.queryUsingNamedParameter", query = "from Employee e where e.salary > :amt")
})
public class Employee {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
public int id;

@Column(name = "first_name")
public String fname;

@Column(name = "last_name")
public String lname;

@Column(name = "dob")
public Calendar dob;

@Column(name = "salary")
public Double salary;

@Column(name = "gender")
public String gender;
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public Calendar getDob() {
return dob;
}

public void setDob(Calendar dob) {
this.dob = dob;
}

public Double getSalary() {
return salary;
}

public void setSalary(Double salary) {
this.salary = salary;
}

public String getFname() {
return fname;
}

public void setFname(String fname) {
this.fname = fname;
}

public String getLname() {
return lname;
}

public void setLname(String lname) {
this.lname = lname;
}

public String getGender() {
return gender;
}

public void setGender(String gender) {
this.gender = gender;
}

@Override
public String toString() {
return "Employee [id=" + id + ", fname=" + fname + ", lname=" + lname + ", dob=" + dob + ", salary=" + salary
+ ", gender=" + gender + "]";
}
}

Make sure you use javax.persistence. classes and not the hibernate ones. NamedQuery has 2 elemets that we need to define. The first one is the name and the second one if the query. For ‘name‘ we must follow the spring data standards. That standards say you must lead with the entity name (Employee), followed by the ‘.’ operator and then the name of the method in the repository (EmployeeRepository method queryUsingPositionalParameter). Lets test by running the same thing again in Application.java. the result will be same.

Lets Page our resultset using spring data. Paging is used when we have a very large result and we want to return this in small chunks of data. This is mainly done from performance perspective. Since we are extending JpaRepository which extends the Paging and Sorting interface, we get spring data paging support. Lets use it in findAll method in Application.java:


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

Iterable<Employee> empList= repository.findAll(new PageRequest(0, 3));
empList.forEach((emp) -> System.out.println("First Page==>"+emp));

empList= repository.findAll(new PageRequest(1, 3));
empList.forEach((emp) -> System.out.println("Second Page==>"+emp));

empList= repository.findAll(new PageRequest(2, 3));
empList.forEach((emp) -> System.out.println("Third Page==>"+emp));



As you can see we used <strong>PageRequest</strong> argument i the findAll method. PageRequest <strong>requires 2 arguments</strong> - <strong>first</strong> the <strong>page</strong> you would like to see and the <strong>second</strong> the <strong>number of records</strong> on it. Paging starts with 0, so in the first call we use 0 and we want to see 3 results on it. Similary we use 2,3 in the next calls. OutPut:

<a href="https://djcodes.files.wordpress.com/2016/11/spring-data-named-queries2.png"><img class="alignnone size-full wp-image-1570" src="https://djcodes.files.wordpress.com/2016/11/spring-data-named-queries2.png" alt="spring-data-named-queries2" width="500" height="126" /></a>

Lets look into another way to acheive this pagination. In Our EmployeeRepository.java



public List<Employee> findBySalaryGreaterThan(double salary, Pageable pageable);

The second argument here will tell spring about what page i want to see. Lets use it in Application.java


List<Employee> empList= repository.findBySalaryGreaterThan(125000.00, new PageRequest(0, 3));
empList.forEach((emp) -> System.out.println(emp));

spring-data-named-queries3

Spring provides other return types as well so as to make paging easy. We have been using List till now. We can also use Page and Slice return types that provides us additional method that might help us in making better decisions in our code. Lets take a brief look into them.


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

Page<Employee> page= repository.findAll(new PageRequest(0, 4));
System.out.println("page.getTotalPages()==>"+page.getTotalPages());
System.out.println("page.getNumberOfElements()==>"+page.getNumberOfElements());

Slice<Employee> slice= repository.findAll(new PageRequest(0, 4));
System.out.println("slice.getSize()==>"+slice.getSize());
System.out.println("slice.getNumber()==>"+slice.getNumber());
System.out.println("sslice.hasNext()==>"+slice.hasNext());

Output:

spring-data-named-queries8

Lets see how can we SORT our resultset.


List<Employee> empList= repository.findAll(new Sort("fname"));
empList.forEach((emp) -> System.out.println(emp));

Oe of the argument in the findAll method is the Sort parameter. We can tell spring that element we need to sort on by giving the element name. By default all sort are in ascending order.

spring-data-named-queries4

If we want to change to descending order we need to provide the Direction parameter in the Sort constructor.


List<Employee> empList=repository.findAll(new Sort(Sort.Direction.DESC,"fname"));
empList.forEach((emp) -> System.out.println(emp));

Now we will get result in descending order.

We can give multiple parameters to sort on.


List<Employee> empList=repository.findAll(new Sort(Sort.Direction.ASC,"fname","salary"));
empList.forEach((emp) -> System.out.println(emp));

Above will first sort result in ascending order of fname and then if the fname are same it will sort in ascending order of salary. Output:

spring-data-named-queries5

What if we want to sort first ascending on first parameter and then descending on the second parameter. We can do this by adding .and


List<Employee> empList=repository.findAll(new Sort(Sort.Direction.ASC,"fname").and(new Sort(Sort.Direction.DESC,"salary")));
empList.forEach((emp) -> System.out.println(emp));

spring-data-named-queries6

Another way to do sort is to provide a Sort parameter in our repository. Adding in EmployeeRepository.java


public List<Employee> findBySalaryGreaterThan(double salary, Sort sort);

Testing the method in Application.java


List<Employee> empList=repository.findBySalaryGreaterThan(125000.00,new Sort(Sort.Direction.DESC,"salary"));
empList.forEach((emp) -> System.out.println(emp));

spring-data-named-queries7

 

Advertisements
%d bloggers like this: