Spring Data JPA – Pagination and Sorting With Query Methods Example

This tutorial shows how we can create a query methods and paginate the query results with out writing any queries/DAO implementation code - using Spring Data JPA

1) Dependencies

This examples needs the following dependencies.

<project xmlns="http://maven.apache.org/POM/4.0.0" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
        http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelversion>4.0.0</modelversion>
	<groupid>com.kalliphant.samples.springdata.jpa</groupid>
	<artifactid>CRUDSample</artifactid>
	<version>0.0.1-SNAPSHOT</version>
	<name>crudSample</name>

	<dependencies>

		<!-- Spring Data JPA -->
		<dependency>
			<groupid>org.springframework.data</groupid>
			<artifactid>spring-data-jpa</artifactid>
			<version>1.10.0.RELEASE</version>
		</dependency>

		<!-- Using H2 Database for this example. If you are using different database, 
			add DB specific dependency here -->
		<dependency>
			<groupid>com.h2database</groupid>
			<artifactid>h2</artifactid>
			<version>1.4.196</version>
		</dependency>

		<!-- JPA Provider - Hibernate -->
		<dependency>
			<groupid>org.hibernate</groupid>
			<artifactid>hibernate-entitymanager</artifactid>
			<version>4.3.8.Final</version>
		</dependency>

	</dependencies>
</project>

2) springconfig.xml - Spring Bean XML Configuration file

This configures the data source, JPA vendor for creating entity manager and directories to scan the repositories.
It should be in your class path.


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/data/jpa
    http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

	<!-- Using H2 in memory database, if you are using some other database, 
		make sure to change this and add the database specific drivers as dependencies 
		to your pom.xml file -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.h2.Driver"></property>
		<property name="url"
			value="jdbc:h2:mem:kalliphant-app;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"></property>
		<property name="username" value="dummy_user"></property>
		<property name="password" value="dummy_password"></property>
	</bean>

	<bean id="jpaVendorAdapter"
		class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
		<property name="showSql" value="true"></property>
		<property name="generateDdl" value="true"></property>
		<property name="database" value="H2"></property>
	</bean>

	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
		<property name="jpaVendorAdapter" ref="jpaVendorAdapter"></property>
		<property name="packagesToScan" value="com.kalliphant.samples.springdata.jpa"></property>
	</bean>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"></bean>

	<jpa:repositories base-package="com.kalliphant.samples.springdata.jpa"></jpa:repositories>

</beans>

3) Entity class

Employee Entity class with JPA annotations

package com.kalliphant.samples.springdata.jpa;

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

@Entity
@Table(name = "employee")
public class Employee {

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

	@Column(name = "first_name")
	private String firstName;

	@Column(name = "last_name")
	private String lastName;

	@Column(name = "designation")
	private String designation;

	public Employee() {
	}

	public Employee(String firstName, String lastName, String designation) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.designation = designation;
	}

	public Long getEmpId() {
		return empId;
	}

	public void setEmpId(Long empId) {
		this.empId = empId;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getDesignation() {
		return designation;
	}

	public void setDesignation(String designation) {
		this.designation = designation;
	}

	@Override
	public String toString() {
		return "Employee [empId=" + empId + ", firstName=" + firstName + ", lastName=" + lastName + ", designation="
				+ designation + "]";
	}
}

4) EmployeeRepository class

Create an EmployeeRepository interface that extends from Spring Data CrudRepository interface and add the findByDesignation method that should take

  • Pageable as last parameter and
  • Should return Page<Employee>

findByDesignation : Spring Data framework, strip the findBy from the method and starts parsing the rest of it and creates a query with Designation field filter. As we added a Pageable parameter as the last field, it will also apply pagination of the results.

package com.kalliphant.samples.springdata.jpa;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee , Long> {
        //For pagination, it must have 'Pageable' as last parameter and return Page<>
	Page<Employee> findByDesignation(String latname, Pageable pageable);
}

5) PaginationExample 1

This example shows how we can retrieve the data using pagination.

package com.kalliphant.samples.springdata.jpa;

import java.util.Arrays;
import java.util.List;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;

public class PaginationExample1 {

	public static void main(String[] args) {
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("springconfig.xml");
		EmployeeRepository repository = context.getBean(EmployeeRepository.class);

		// CREATE multiple employees at once using List
		System.out.println("\nInseting multiple Employees");
		List<Employee> employees = Arrays.asList(
				new Employee("John", "Joe", "Manager"),
				new Employee("Dave", "Harris", "Programmer"), 
				new Employee("Nael", "Jean", "Programmer"),
				new Employee("Consuelo", "Muñoz", "Programmer"), 
				new Employee("Ilona", "Koski", "Manager"),
				new Employee("Katie", "Kennedy", "Programmer"), 
				new Employee("Lucile", "Dufour", "Manager"),
				new Employee("Tim", "White", "Programmer"),
				new Employee("Kalliphant", "Bloge", "Programmer"));
		repository.save(employees);

		// COUNT employees
		System.out.println("\nGet count of Employees");
		System.out.println("Number of employees inserted : " + repository.count());

		//Retrieve 4 rows from first Page
		System.out.println("\nGetting 4 rows from first Page");
		Pageable pageable = new PageRequest(0, 4);
		Page<Employee> findPage1 = repository.findByDesignation("Programmer", pageable);
		for (Employee emp: findPage1) {
			System.out.println(emp);
		}
		System.out.println("Total elements in entity : " + findPage1.getTotalElements());
		System.out.println("Total number of pages with 4 records/page : "  + findPage1.getTotalPages());

		//Retrieve next 4 rows from next page (from second Page)
		System.out.println("\nGetting next 4 rows - from second Page");
		pageable = new PageRequest(1, 4);
		Page<Employee> findPage2 = repository.findByDesignation("Programmer", pageable);
		for (Employee emp: findPage2) {
			System.out.println(emp);
		}

		context.close();
	}
}

Output

Run the application, you should get the below output.

       :
       :
Inseting multiple Employees
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)

Get count of Employees
Hibernate: select count(*) as col_0_0_ from employee employee0_
Number of employees inserted : 9

Getting 4 rows from first Page
Hibernate: select count(employee0_.emp_id) as col_0_0_ from employee employee0_ where employee0_.designation=?
Hibernate: select employee0_.emp_id as emp_id1_0_, employee0_.designation as designat2_0_, 
             employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ 
             from employee employee0_ where employee0_.designation=? limit ?
Employee [empId=2, firstName=Dave, lastName=Harris, designation=Programmer]
Employee [empId=3, firstName=Nael, lastName=Jean, designation=Programmer]
Employee [empId=4, firstName=Consuelo, lastName=Muñoz, designation=Programmer]
Employee [empId=6, firstName=Katie, lastName=Kennedy, designation=Programmer]
Total elements in entity : 6
Total number of pages with 4 records/page : 2

Getting next 4 rows - from second Page
Hibernate: select count(employee0_.emp_id) as col_0_0_ from employee employee0_ where employee0_.designation=?
Hibernate: select employee0_.emp_id as emp_id1_0_, employee0_.designation as designat2_0_, 
             employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ 
             from employee employee0_ where employee0_.designation=? limit ? offset ?
Employee [empId=8, firstName=Tim, lastName=White, designation=Programmer]
Employee [empId=9, firstName=Kalliphant, lastName=Bloge, designation=Programmer]

6) PaginationWithSortExample 2

This example shows how we can sort on one field (Employee firstName) and retrieve the data using pagination.

package com.kalliphant.samples.springdata.jpa;

import java.util.Arrays;
import java.util.List;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

public class PaginationWithSortExample2 {

	public static void main(String[] args) {
		AbstractApplicationContext context = new ClassPathXmlApplicationContext("springconfig.xml");
		EmployeeRepository repository = context.getBean(EmployeeRepository.class);

		// CREATE multiple employees at once using List
		System.out.println("\nInseting multiple Employees");
		List<Employee> employees = Arrays.asList(
				new Employee("John", "Joe", "Manager"),
				new Employee("Dave", "Harris", "Programmer"), 
				new Employee("Nael", "Jean", "Programmer"),
				new Employee("Consuelo", "Muñoz", "Programmer"), 
				new Employee("Ilona", "Koski", "Manager"),
				new Employee("Katie", "Kennedy", "Programmer"), 
				new Employee("Lucile", "Dufour", "Manager"),
				new Employee("Tim", "White", "Programmer"),
				new Employee("Kalliphant", "Bloge", "Programmer"));
		repository.save(employees);

		// COUNT employees
		System.out.println("\nGet count of Employees");
		System.out.println("Number of employees inserted : " + repository.count());

		//Sort on the firstName (in Ascending order) and get the first 4 rows
		System.out.println("\nSort on the firstName (in Ascending order) and get the first 4 rows");
		
		Sort sortOnFirstName = new Sort(Sort.Direction.ASC, "firstName");
		Pageable pageable = new PageRequest(0, 4,sortOnFirstName);
		
		Page<Employee> findPage1 = repository.findByDesignation("Programmer", pageable);
		for (Employee emp: findPage1) {
			System.out.println(emp);
		}
		
		System.out.println("Total elements in entity : " + findPage1.getTotalElements());
		System.out.println("Total number of pages with 4 records/page : "  + findPage1.getTotalPages());

		//Sort on the firstName (in Ascending order) and get the next the next 4 rows
		System.out.println("\nGetting next 4 rows - from second Page");
		pageable = new PageRequest(1, 4, sortOnFirstName);
		Page<Employee> findPage2 = repository.findByDesignation("Programmer", pageable);
		for (Employee emp: findPage2) {
			System.out.println(emp);
		}

		context.close();
	}
}

Output

Run the application, you should get the below output.

       :
       :
Inseting multiple Employees
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)
Hibernate: insert into employee (emp_id, designation, first_name, last_name) values (null, ?, ?, ?)

Get count of Employees
Hibernate: select count(*) as col_0_0_ from employee employee0_
Number of employees inserted : 9

Sort on the firstName (in Ascending order) and get the first 4 rows
Hibernate: select count(employee0_.emp_id) as col_0_0_ from employee employee0_ where employee0_.designation=?
Hibernate: select employee0_.emp_id as emp_id1_0_, employee0_.designation as designat2_0_, 
            employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ 
            from employee employee0_ where employee0_.designation=? order by employee0_.first_name asc limit ?
Employee [empId=4, firstName=Consuelo, lastName=Muñoz, designation=Programmer]
Employee [empId=2, firstName=Dave, lastName=Harris, designation=Programmer]
Employee [empId=9, firstName=Kalliphant, lastName=Bloge, designation=Programmer]
Employee [empId=6, firstName=Katie, lastName=Kennedy, designation=Programmer]
Total elements in entity : 6
Total number of pages with 4 records/page : 2

Getting next 4 rows - from second Page
Hibernate: select count(employee0_.emp_id) as col_0_0_ from employee employee0_ where employee0_.designation=?
Hibernate: select employee0_.emp_id as emp_id1_0_, employee0_.designation as designat2_0_, 
             employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ 
             from employee employee0_ where employee0_.designation=? order by employee0_.first_name asc limit ? offset ?
Employee [empId=3, firstName=Nael, lastName=Jean, designation=Programmer]
Employee [empId=8, firstName=Tim, lastName=White, designation=Programmer]