Spring Data JPA makes it incredibly easy to handle database interactions without needing to write a lot of boilerplate code. One of the most magical features is the ability to generate SQL queries directly from method names.
In this blog post, we will explore how Spring Data JPA generates SQL using method names and how you can create a few query methods using just a single field in a JPA Repository, specifically for MySQL database.
Spring Data JPA analyzes repository method names and creates SQL queries behind the scenes. The logic follows specific conventions:
Start with a known prefix: The method starts with prefixes like find…By, read…By, query…By, get…By, etc.
Specify the property: Next, you provide the entity's property or field name, ensuring it begins with an uppercase character.
Add a condition (Optional): Finally, you can specify conditions on the property like …GreaterThan, …LessThan, …Between, …Like, etc.
For example, for an entity Employee with a field name, if you have a method
findByName(String name), Spring
Data JPA would generate a SQL similar to:
SELECT e FROM Employee e WHERE e.name = ?1Where ?1 refers to the first parameter passed to the method.
Let's launch Spring Initializr and fill up the following project details:
Project: Maven Project (or Gradle)
Language: Java
Packaging: Jar
Java version: 17
Dependencies: Spring Data JPA, MySQL Driver and Lombok
Download, extract the project, and import to your favorite IDE.
Let's open the src/main/resources/application.properties file and add the
MySQL configuration properties:
spring.datasource.url=jdbc:mysql://localhost:3306/demo
spring.datasource.username=root
spring.datasource.password=Mysql@123
spring.jpa.hibernate.ddl-auto=update
Make sure that you change the MySQL database username and password as per your MySQL installation on your machine.
The spring.jpa.hibernate.ddl-auto=update line ensures that tables and
columns get automatically created or
updated based on your JPA entities.
Let's create a simple Employee JPA entity that will map to database table -
employees:
import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String department;
}
We are using below Lombok annotations to reduce the boilerplate code such as getter/setter methods:
@Getter: getter methods for the fields of the class.
@Setter: Generates setter methods for the fields of the class.
Let's create an EmployeeRepository interface that extends the JpaRepository interface from Spring Data JPA:
import com.springdatajpa.springboot.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}
Next, let's define two query methods in EmployeeRepository interface:
import com.springdatajpa.springboot.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
Employee findByName(String name);
List<Employee> findByDepartment(String department);
}
Employee findByName(String name) - this query method retrieves an employee by name
List<Employee> findByDepartment(String department) - this query method retrieve a list of employees by department
For findByName(String name) : SQL generated would be something like:
SELECT * FROM employee WHERE name = ?In the SQL context, the placeholder ? will be replaced by the value provided
as the argument when invoking the findByName method.
For findByDepartment(String department) : The generated SQL would be:
SELECT * FROM employee WHERE department = ?Now, let's write a JUnit test case to test EmployeeRepository query methods:
@Test
void findByNameTest(){
Employee employee = employeeRepository.findByName("Ramesh");
System.out.println(employee.getId());
System.out.println(employee.getName());
System.out.println(employee.getDepartment());
}
Note that Spring Data JPA (uses Hibernate as a JPA provider) generated SQL statements in a console:
Hibernate:
select
e1_0.id,
e1_0.department,
e1_0.name
from
employees e1_0
where
e1_0.name=?
15
Ramesh
IT
@Test
void findByDepartmentTest(){
List employees = employeeRepository.findByDepartment("IT");
employees.forEach((employee) -> {
System.out.println(employee.getId());
System.out.println(employee.getName());
System.out.println(employee.getDepartment());
});
}
Run the above JUnit test case, Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console:
Hibernate:
select
e1_0.id,
e1_0.department,
e1_0.name
from
employees e1_0
where
e1_0.department=?
17
Ramesh
IT
18
Meena
IT
Here is the complete code for your reference:
import com.springdatajpa.springboot.entity.Employee;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class EmployeeRepositoryTests {
@Autowired
private EmployeeRepository employeeRepository;
@BeforeEach
public void setUp() {
Employee e1 = new Employee();
e1.setName("Ramesh");
e1.setDepartment("IT");
employeeRepository.save(e1);
Employee e2 = new Employee();
e2.setName("Meena");
e2.setDepartment("IT");
employeeRepository.save(e2);
}
@Test
void findByNameTest(){
Employee employee = employeeRepository.findByName("Ramesh");
System.out.println(employee.getId());
System.out.println(employee.getName());
System.out.println(employee.getDepartment());
}
@Test
void findByDepartmentTest(){
List<Employee> employees = employeeRepository.findByDepartment("IT");
employees.forEach((employee) -> {
System.out.println(employee.getId());
System.out.println(employee.getName());
System.out.println(employee.getDepartment());
});
}
@AfterEach
public void clean(){
employeeRepository.deleteAll();
}
}
In this blog post, we explored how Spring Data JPA generates SQL using method names and how you can create a few query methods using just a single field in a JPA Repository, specifically for MySQL database.