Spring data jpa: Query Methods

In my previous posts, I explained about the methods provided by CrudRepository interface. These methods are quite helpful in performing simple CRUD operations. But in real life applications, these doesn't help us much.

For example,

a.   You may want to get the nth employee whose is earning more salary

b.   You want to get the address of all employees whose name is 'krishna' and age is > 25

One way to solve above problem is, to write native SQL queries and execute them.

Other way is to write Query methods.

What exactly query methods are?

Just like any other method in Java, Query methods are also methods that are defined in Repository interface. Query methods must follow below naming conventions.

a.   A query method can return one or more results.

b.   A Query method must starts with 'findBy' clause followed by entity attribute name (Use camel case here)

c.    You can optionally chain subattribute names (Ex: findByAddressCityName)

d.   Query method parameters type must match with the data types of entity attributes.

e.   If a query method returning more than one result, then result type can be List or Stream

For example, I defined 3 query methods in EmployeeRepository interface.

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

    //SELECT * FROM employee WHERE salary=X
    public List<Employee> findBySalary(double salary);
    
    //SELECT * FROM employee WHERE lastName=X
    public List<Employee> findByLastName(String lastName);
    
    
    //SELECT * FROM employee WHERE age=X AND salary=Y
    public List<Employee> findByAgeAndSalary(int age, double salary);
    
}

When you declare a query method, Spring internally implement the methods.

For example, below method

public List<Employee> findBySalary(double salary);

is equivalent to

SELECT * FROM employee WHERE salary=X

Other Examples

//SELECT * FROM employee WHERE salary=X

public List<Employee> findBySalary(double salary);

// SELECT * FROM employee WHERE lastName=X

public List<Employee> findByLastName(String lastName);

// SELECT * FROM employee WHERE age>X

public List<Employee> findByAgeGreaterThan(int age);

// SELECT * FROM employee WHERE age=X AND salary=Y

public List<Employee> findByAgeAndSalary(int age, double salary);

Find the below working application.

Prerequisite: Create new maven project

Step 1: Create a package 'com.sample.app.model' and define Employee.java like below.

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 = "employee")
public class Employee {
 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 @Column(name = "employee_id")
 private int id;

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

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

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

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

 public int getId() {
  return id;
 }

 public void setId(int 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 int getAge() {
  return age;
 }

 public void setAge(int 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();
 }

}

Step 2: Create a package 'com.sample.app.repository' and define EmployeeRepository interface like below.

EmployeeRepository.java

package com.sample.app.repository;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

import com.sample.app.model.Employee;

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

 // SELECT * FROM employee WHERE salary=X
 public List<Employee> findBySalary(double salary);

 // SELECT * FROM employee WHERE lastName=X
 public List<Employee> findByLastName(String lastName);

 // SELECT * FROM employee WHERE age>X
 public List<Employee> findByAgeGreaterThan(int age);

 // SELECT * FROM employee WHERE age=X AND salary=Y
 public List<Employee> findByAgeAndSalary(int age, double salary);

}

Step 3: Define App.java in com.sample.app package.

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 com.sample.app.model.Employee;
import com.sample.app.repository.EmployeeRepository;

@SpringBootApplication
public class App {

 private static void printEmployee(Employee emp) {
  System.out.println("------------------------------");
  System.out.println("Id : " + emp.getId());
  System.out.println("firstName : " + emp.getFirstName());
  System.out.println("lastName : " + emp.getLastName());
  System.out.println("------------------------------");
 }

 private static void printEmployees(String message, List<Employee> emps) {
  System.out.println("------------------------------");
  System.out.println(message);
  for (Employee emp : emps) {
   System.out.println(emp);
  }
  System.out.println("------------------------------");
 }

 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(45).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();

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

   List<Employee> emps = employeeRepository.findBySalary(60000);
   printEmployees("SELECT * FROM employee WHERE salary=600000", emps);
   
   emps = employeeRepository.findByLastName("Battu");
   printEmployees("SELECT * FROM employee WHERE last_name='Battu'", emps);
   
   emps = employeeRepository.findByAgeGreaterThan(35);
   printEmployees("SELECT * FROM employee WHERE age>35", emps);
   
   emps = employeeRepository.findByAgeAndSalary(32, 100000.23);
   printEmployees("SELECT * FROM employee WHERE age=32 AND salary=100000.23", emps);

  };
 }

}

Step 4: 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

Total project looks like below.

Run App.java, you can able to see below messages in console.

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.6.RELEASE)

------------------------------
SELECT * FROM employee WHERE salary=600000
Employee [id=2, firstName=Joel, lastName=Chelli, age=43, salary=60000.0]
Employee [id=4, firstName=Bomma, lastName=Srikanth, age=39, salary=60000.0]
------------------------------
------------------------------
SELECT * FROM employee WHERE last_name='Battu'
Employee [id=3, firstName=Gopi, lastName=Battu, age=45, salary=1000000.0]
------------------------------
------------------------------
SELECT * FROM employee WHERE age>35
Employee [id=2, firstName=Joel, lastName=Chelli, age=43, salary=60000.0]
Employee [id=3, firstName=Gopi, lastName=Battu, age=45, salary=1000000.0]
Employee [id=4, firstName=Bomma, lastName=Srikanth, age=39, salary=60000.0]
------------------------------
------------------------------
SELECT * FROM employee WHERE age=32 AND salary=100000.23
Employee [id=1, firstName=Ram, lastName=Gurram, age=32, salary=100000.23]
Employee [id=5, firstName=Surendra, lastName=Sami, age=32, salary=100000.23]
------------------------------

You can download complete working application from this link.

Reference

https://docs.spring.io/spring-data/jpa/docs/2.0.0.M1/reference/html/#repository-query-keywords    

Previous                                                    Next                                                    Home