Spring Data JPA findBy Column Name Example

Author:

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 = ?1

Where ?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=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.

3. Create JPA Entity - Employee

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: 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);
}
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

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());
    }
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 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
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();
    }
}

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.

Related Spring Data JPA Query Method Tutorials

Comments

Post a Comment

Leave Comment