Spring Data JPA – Query By Example( (QBE) Tutorial

This tutorial shows how we can easily filter the data using dynamic queries with out writing any code/queries by using Spring Data JPA - Query By Example(QBE) .

Spring added this feature in 1.10, it works only with the Spring Data JPA 1.10 or beyond.

1) Dependencies

This examples needs the following dependencies. It uses H2 In-Memory Database to run this example.

<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

To use the Query By Example(QBE) we need to make our repositories extend from QueryByExampleExecutor interface.
In this example we will extend EmployeeRepository interface from JpaRepository interface.
JpaRepositoryextends from both CrudRepository and QueryByExampleExecutor interfaces.
Instead of extending from JpaRepository interface we could also extend from CrudRepository interface (to use CRUD methods) and QueryByExampleExecutor interface (to use Queue By Example methods) individually.

package com.kalliphant.samples.springdata.jpa;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee , Long> {
}

or

package com.kalliphant.samples.springdata.jpa;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long>, QueryByExampleExecutor <Employee> {
}

5) QueryByExampleApp class

This application shows following two examples by using Query By Filter
1) Fetch all employees with the designation - 'Programmer'
2) Fetch using ignore case and contains clause (using ExampleMatcher)

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.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.StringMatcher;

public class QueryByExampleApp {

	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("Tim", "Armeni", "Manager"),
				new Employee("Dave", "Harris", "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 ALL 'Programmer' employees - Using QueryByExample
		System.out.println("\nRetreving all 'Programmer' employees - Using QueryByExample(QBE)");
		
		//Filter Criteria
		Employee filterBy = new Employee();
		filterBy.setDesignation("Programmer"); 
		
		Example<Employee> example = Example.of(filterBy); 

		List<Employee> findProgrammers = repository.findAll(example);
		for (Employee programmer: findProgrammers) {
			System.out.println(programmer);
		}
		
		// RETRIEVE ALL employees who has 'ar' in their last name - Using QueryByExample
		System.out.println("\nRetreving all employees who has 'ar' in their last name - Using QueryByExample(QBE)");
		
		//Filter Criteria
		filterBy = new Employee();
		filterBy.setLastName("ar"); 

		//Filter - ignore case search and contains 
		ExampleMatcher matcher = ExampleMatcher.matching()     
				  .withStringMatcher(StringMatcher.CONTAINING)   // Match string containing pattern   
				  .withIgnoreCase();				 // ignore case sensitivity 
		
		example = Example.of(filterBy, matcher); 
		
		List<Employee> findEmployeesHasLnAr = repository.findAll(example);
		for (Employee empFiltered: findEmployeesHasLnAr) {
			System.out.println(empFiltered);
		}

		context.close();
	}
}

6) 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, ?, ?, ?)

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

Retreving all 'Programmer' employees - Using QueryByExample(QBE)
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=?
Employee [empId=3, firstName=Dave, lastName=Harris, designation=Programmer]
Employee [empId=4, firstName=Kalliphant, lastName=Bloge, designation=Programmer]

Retreving all employees who has 'ar' in their last name - Using QueryByExample(QBE)
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 lower(employee0_.last_name) like ?
Employee [empId=2, firstName=Tim, lastName=Armeni, designation=Manager]
Employee [empId=3, firstName=Dave, lastName=Harris, designation=Programmer]