Tuesday 27 August 2019

Spring Data JPA: Query by Example

'QueryByExampleExecutor' interface provide methods to execute queries by example (For example, you can ask for give me all the employee details where age is 20 by passing an Employee entity by setting age to 20).

Below table summarizes the methods provided by 'QueryByExampleExecutor' interface.

Method
Description
<S extends T> Optional<S> findOne(Example<S> example)
Returns a single entity matching the given Example or null if none was found.
<S extends T> Iterable<S> findAll(Example<S> example)
Returns all entities matching the given Example. In case no match could be found an empty Iterable is returned.
<S extends T> Iterable<S> findAll(Example<S> example, Sort sort)
Returns all entities matching the given Example applying the given Sort. In case no match could be found an empty Iterable is returned.
<S extends T> Page<S> findAll(Example<S> example, Pageable pageable)
Returns a Page of entities matching the given Example. In case no match could be found, an empty Page is returned.
<S extends T> long count(Example<S> example)
Returns the number of instances matching the given Example.
<S extends T> boolean exists(Example<S> example)
Checks whether the data store contains elements that match the given Example. Return true if the data store contains elements that match the given Example.

How to create an Example?
Example interface provides 'of' methods to create an Example object.

Example
Employee exampleEmployee =new Employee();
exampleEmployee.setAge(32);
Example<Employee> age32 = Example.of(exampleEmployee);

Above statement internally creates below kind of query.

SELECT * FROM my_employee emp WHERE emp.age=32

You can even use 'ExampleMatcher' to specify matching criteria for query by example.

Example
ExampleMatcher nameMatcher = ExampleMatcher.matching().withIgnoreCase().withStringMatcher(StringMatcher.ENDING);
Employee employeeEndsWitha = new Employee();
employeeEndsWitha.setFirstName("a");
Example<Employee> nameEndsWithA = Example.of(employeeEndsWitha, nameMatcher);

Above snippet creates below kind of query.
SELECT * FROM my_employee emp WHERE LOWER(emp.first_name) LIKE '%a';

Find the below working application.

Employee.java
package com.sample.app.model;

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 = "my_employee")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

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

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

    @Column(name = "age")
    private Integer age;

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

    public Integer getId() {
        return id;
    }

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

    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 Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Double getSalary() {
        return salary;
    }

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

    public static EmployeeBuilder builder() {
        return new EmployeeBuilder();
    }

    public static class EmployeeBuilder {
        private Employee emp;

        public EmployeeBuilder() {
            emp = new Employee();
        }

        public EmployeeBuilder firstName(String firstName) {
            emp.setFirstName(firstName);
            return this;
        }

        public EmployeeBuilder lastName(String lastName) {
            emp.setLastName(lastName);
            return this;
        }

        public EmployeeBuilder age(int age) {
            emp.setAge(age);
            return this;
        }

        public EmployeeBuilder salary(double salary) {
            emp.setSalary(salary);
            return this;
        }

        public Employee build() {
            return emp;
        }
    }

    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Employee [id=").append(id).append(", firstName=").append(firstName).append(", lastName=")
                .append(lastName).append(", age=").append(age).append(", salary=").append(salary).append("]");
        return builder.toString();
    }

}

EmployeeRepository.java
package com.sample.app.repository;

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

import com.sample.app.model.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    
}

App.java
package com.sample.app;

import java.util.List;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.StringMatcher;

import com.sample.app.model.Employee;
import com.sample.app.repository.EmployeeRepository;

@SpringBootApplication
public class App {

    public static void main(String args[]) {
        SpringApplication.run(App.class, args);
    }

    @Bean
    public CommandLineRunner demo(EmployeeRepository employeeRepository) {
        return (args) -> {
            Employee emp1 = Employee.builder().firstName("Ram").lastName("Gurram").age(32).salary(100000.23).build();
            Employee emp2 = Employee.builder().firstName("Joel").lastName("Chelli").age(43).salary(60000).build();
            Employee emp3 = Employee.builder().firstName("Gopi").lastName("Battu").age(32).salary(1000000).build();
            Employee emp4 = Employee.builder().firstName("Bomma").lastName("Srikanth").age(39).salary(60000).build();
            Employee emp5 = Employee.builder().firstName("Surendra").lastName("Sami").age(32).salary(100000.23).build();
            Employee emp6 = Employee.builder().firstName("Bhadri").lastName("Venakata RamaRao").age(32)
                    .salary(100000.23).build();
            Employee emp7 = Employee.builder().firstName("Sushmithaa").lastName("Kulakarni").age(39).salary(100000.23)
                    .build();

            employeeRepository.save(emp1);
            employeeRepository.save(emp2);
            employeeRepository.save(emp3);
            employeeRepository.save(emp4);
            employeeRepository.save(emp5);
            employeeRepository.save(emp6);
            employeeRepository.save(emp7);

            System.out.println("\nEmployees with age 32");
            Employee exampleEmployee = new Employee();
            exampleEmployee.setAge(32);
            Example<Employee> age32 = Example.of(exampleEmployee);

            List<Employee> emps = employeeRepository.findAll(age32);

            for (Employee emp : emps) {
                System.out.println(emp);
            }

            System.out.println("\nEmployees with first name ends with 'a'");
            ExampleMatcher nameMatcher = ExampleMatcher.matching().withIgnoreCase()
                    .withStringMatcher(StringMatcher.ENDING);
            Employee employeeEndsWitha = new Employee();
            employeeEndsWitha.setFirstName("a");
            Example<Employee> nameEndsWithA = Example.of(employeeEndsWitha, nameMatcher);

            emps = employeeRepository.findAll(nameEndsWithA);

            for (Employee emp : emps) {
                System.out.println(emp);
            }

        };
    }

}

Create 'application.properties' file under 'src/main/resources' folder.

application.properties    
logging.level.root=WARN
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## JPA specific properties
# Creates the schema, destroying previous data.
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

#spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

pom.xml
<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>springJPA</groupId>
    <artifactId>springJPA</artifactId>
    <version>1</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>



    <dependencies>


        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>


    </dependencies>

</project>

Total project structure looks like below.


Run App.java, you can able to see below messages in the console.
  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.6.RELEASE)


Employees with age 32
Employee [id=1, firstName=Ram, lastName=Gurram, age=32, salary=100000.23]
Employee [id=3, firstName=Gopi, lastName=Battu, age=32, salary=1000000.0]
Employee [id=5, firstName=Surendra, lastName=Sami, age=32, salary=100000.23]
Employee [id=6, firstName=Bhadri, lastName=Venakata RamaRao, age=32, salary=100000.23]

Employees with first name ends with 'a'
Employee [id=4, firstName=Bomma, lastName=Srikanth, age=39, salary=60000.0]
Employee [id=5, firstName=Surendra, lastName=Sami, age=32, salary=100000.23]
Employee [id=7, firstName=Sushmithaa, lastName=Kulakarni, age=39, salary=100000.23]

You can download the complete working application from this link.
    


Previous                                                    Next                                                    Home

No comments:

Post a Comment