Spring Data Query Creation by Method Names

Introduction

In this tutorial, we find out what Query Methods are and how we can use them in the Spring Data’s JPA Repository.

What do we create?

Spring Data is a Spring Framework project that simplifies data access and persistence in Java applications. It provides a consistent, high-level abstraction for working with various databases, including relational databases, NoSQL databases, and other data stores.

The main benefits of Spring Data are:

Reducing boilerplate code required for database interactions.
Using repository interfaces instead of writing queries for CRUD operations.
handling transactions and queries efficiently

In Spring Data JPA, you can define query methods in your repository interfaces just by following a naming convention. Spring Data JPA automatically generates the necessary SQL queries based on the method name.

Spring Data JPA interprets method names and creates queries automatically. The method name follows this pattern:

findBy[Attribute][Operation]

For instance:

List<Lemonade> findByName(String name);

This will generate the SQL equivalent of:

SELECT * FROM lemonades WHERE name = ?;

If we add the method in a new JPA Repository, the code would look like following example:

				
					public interface LemonadeRepository extends JpaRepository<Lemonade, Long> {

    List<Lemonade> findByName(String name);
    
}

That’s not all! We can be creative with our methods’ name. We can combine conditions using And or Or:

List<Lemonade> findByNameAndDescription(String name, String description);

We can use different operators: GreaterThan, LessThan, Between, After, Before

List<Lemonade> findByPriceGreaterThan(int price)

For partial matches, use Like, StartingWith, EndingWith, or Containing:

List<Lemonade> findByDescriptionLike(String description);

To sort results, we can use OrderBy:

List<Lemonade> findByPriceGreaterThanOrderByNameAsc(int price);

To get the first or top results:

Lemonade findFirstByOrderByPriceAsc();

Lemonade findTopByOrderByPriceDesc();

List<Lemonade> findTop3ByOrderByPriceDesc();

Spring Data supports various keywords to define queries:

Keyword	Method name	Generated Query
findBy	findByName(String name)	SELECT * FROM lemonades WHERE name = ?
countBy	countByName(String name)	SELECT COUNT(*) FROM lemonades WHERE name = ?
existsBy	existsByName(String name)	SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END FROM lemonades WHERE name = ?
deleteBy	deleteById(Long id)	DELETE FROM lemonades WHERE id = ?

You can check all the keywords and method name variations here.

In the next part of our tutorial we will create a new REST API using Spring Boot and SQLite database to see how query creation by method names works in a real-world project.

How do we create it?

You can use Spring Initializr to quickly generate a Spring Boot project with Maven or Gradle. We can use the following configurations:

Group: com.example
Artifact: explainJavaSpringData
Packing: jar
Java version: any
Dependencies: Spring Web (to create a REST API) and Spring Data JPA

You will receive a ready-to-use project. All the configurations are already written in the build.gradle file and the start ExplainjavaRestApiApplication class is already created.

Additionally, we also have an already created structure in the resources folder and a test package.

The next step is to add two additional dependencies in the build.gradle file for the SQLite database connection:

SQLite JDBC is a library for accessing and creating SQLite database files in Java
hibernate-community-dialects – help Hibernate generate database-specific SQL queries, ensuring compatibility with different database vendors

The build.gradle file should look like this:

				
					plugins {
	id 'java'
	id 'org.springframework.boot' version '3.4.1'
	id 'io.spring.dependency-management' version '1.1.7'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
	toolchain {
		languageVersion = JavaLanguageVersion.of(17)
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.hibernate.orm:hibernate-community-dialects:6.6.4.Final'
	runtimeOnly 'org.xerial:sqlite-jdbc'
}

After all the dependencies are ready to go, we need to tell Spring Data additional information about our database connection. For that, we need to add a application.properties file and add some basic configurations.

				
					spring.application.name=explainJavaSpringData

# Database configuration:
spring.datasource.url=jdbc:sqlite:database.db
spring.datasource.driverClassName=org.sqlite.JDBC
spring.datasource.username=
spring.datasource.password=

# Hibernate configuration:
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.show-sql=true

The next step is to create the main entity: Lemonade.

				
					@Entity
@Table(name = "lemonade")
public class Lemonade {
   
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
   
    private String description;

    // getters and setters   
}

Then, we can create the rest of the application using a multi-layer architecture: LemonadeRepository, LemondeService and LemonadeController.

Spring Data comes with an already created repository which can be used: JpaRepository. It is an interface in Spring Data JPA that provides CRUD (Create, Read, Update, Delete) operations and some other methods used for sorting and paging.

				
					public interface LemonadeRepository extends JpaRepository<Lemonade, Long> {

    List<Lemonade> findByName(String name);

    List<Lemonade> findByDescriptionContainingIgnoreCase(String description);

    Long countByName(String name);

}

The LemonadeRepository needs to be used in a new service class:

				
					@Service
public class LemonadeService {

    @Autowired
    private LemonadeRepository lemonadeRepository;

    public Lemonade saveLemonade(Lemonade lemonade) {
        return lemonadeRepository.save(lemonade);
    }

    public List<Lemonade> getLemonadesByName(String name) {
        return lemonadeRepository.findByName(name);
    }

    public List<Lemonade> searchLemonadesByDescription(String description) {
        return lemonadeRepository.findByDescriptionContainingIgnoreCase(description);
    }

    public Long countByName(String name) {
        return lemonadeRepository.countByName(name);
    }
}

And finally, we need to create the controller to define our endpoints:

				
					@RestController
@RequestMapping("/api/lemonades")
public class LemonadeController {

    @Autowired
    private LemonadeService lemonadeService;

    @PostMapping
    public Lemonade addLemonade(@RequestBody Lemonade lemonade) {
        return lemonadeService.saveLemonade(lemonade);
    }

    @GetMapping
    public List<Lemonade> getLemonadesByName(@RequestParam String name) {
        return lemonadeService.getLemonadesByName(name);
    }
    
    @GetMapping("/search")
    public List<Lemonade> searchLemonadesByDescription(@RequestParam String description) {
        return lemonadeService.searchLemonadesByDescription(description);
    }

    @GetMapping("/count")
    public Long countByName(@RequestParam String name) {
        return lemonadeService.countByName(name);
    }

}

That’s it. We can start the application using the command bootRun and we can test our application using Postman.

First, we can add some lemonades using POST method.