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 and SQL Generation from Method Names
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.
1. Set up a Spring Boot project
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.
2. Configure the MySQL database
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=updateMake 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.
3. Create JPA Entity - Employee
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;
}@Getter: Generates getter methods for the fields of the class.
@Setter: Generates setter methods for the fields of the class.
3. Create Spring Data JPA Repository - EmployeeRepository
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);
}List<Employee> findByDepartment(String department) - this query method retrieve a list of employees by department
How Spring Data JPA generates the SQL:
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 = ?4. Testing - EmployeeRepository Query Methods
Now, let's write a JUnit test case to test EmployeeRepository query methods:
Test findByName() Query Method
@Test
void findByNameTest(){
Employee employee = employeeRepository.findByName("Ramesh");
System.out.println(employee.getId());
System.out.println(employee.getName());
System.out.println(employee.getDepartment());
}Hibernate:
select
e1_0.id,
e1_0.department,
e1_0.name
from
employees e1_0
where
e1_0.name=?
15
Ramesh
ITTest findByDepartment() Query Method
@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());
});
}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
ITHere 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();
}
}Conclusion
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.
Comments
Post a Comment
Leave Comment