WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

Spring Boot Derived Query Methods

Previous Next

Spring Data JPA provides Derived Query Methods, which allow you to create queries automatically based on method names without writing SQL or JPQL.

How Derived Query Methods Work?

  • Spring Data JPA parses the method name and generates the corresponding SQL query.
  • It follows a specific naming convention to understand what to query.
  • No need to use @Query unless you need custom queries.

Example Entity Class

Let's assume we have a User entity with fields: id, name, email, status, and age.

import jakarta.persistence.*;

@Entity
@Table(name = "users") // Table name in MySQL
public class User {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY) // Auto-increment ID
private Long id;

@Column(nullable = false)
private String name;

@Column(nullable = false)
private Integer age;

@Column(unique = true, nullable = false)
private String email;

private String status;

// Constructors
public User() {}

public User(String name, String email,Integer age,String status) {
this.name = name;
this.email = email;
this.age = age;
this.status = status;
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getStatus() {
return status;
}

public void setStatus(String status) {
this.status = status;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Integer getAge() {
return age;
}

public void setAge(Integer age) {
this.age = age;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}
}

Creating Derived Query Methods

Now, let's define a Spring Data JPA Repository.

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {

// Find by exact match
List<User> findByName(String name);

// Find by "LIKE" match
List<User> findByNameContaining(String name);

// Find users where age is greater than a certain value
List<User> findByAgeGreaterThan(int age);

// Find by multiple conditions
List<User> findByNameAndStatus(String name, String status);

// Find users where status is either 'Active' or 'Inactive'
List<User> findByStatusIn(List<String> statuses);

// Find users where email is not null
List<User> findByEmailIsNotNull();
}

Spring Data JPA Naming Keywords

Spring understands these keywords and converts them into queries:

Keyword

Description

Example

findBy

Selects records based on a field

findByName(String name)

And

Combines two conditions

findByNameAndStatus(String name, String status)

Or

Finds records where at least one condition matches

findByNameOrEmail(String name, String email)

Between

Finds values within a range

findByAgeBetween(int start, int end)

LessThan

Finds values less than a specific value

findByAgeLessThan(int age)

GreaterThan

Finds values greater than a specific value

findByAgeGreaterThan(int age)

Like

Finds partial matches

findByNameLike(String name)

Containing

Similar to LIKE (%keyword%)

findByNameContaining(String name)

StartingWith

Similar to LIKE (keyword%)

findByNameStartingWith(String prefix)

EndingWith

Similar to LIKE (%keyword)

findByNameEndingWith(String suffix)

OrderBy

Sort results

findByNameOrderByAgeAsc(String name)

Not

Negates a condition

findByStatusNot(String status)

In

Matches against a list of values

findByStatusIn(List<String> statuses)

Using Derived Queries in a Service Class

import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserService {

private final UserRepository userRepository;

// Load users
public List<User> loadUsers() {
List<User> users =
new ArrayList<>(List.of(
new User( "John","john@gmail.com",32,"Inactive"),
new User("Kenny","kenny@gmail.com",30,"Inactive")
));
return userRepository.saveAll(users);
}

public List<User> getUsersByName(String name) {
return userRepository.findByName(name);
}

public List<User> findByAgeGreaterThan(Integer age) {
return userRepository.findByAgeGreaterThan(age);
}
}

Using Derived Queries in a Controller

import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/users")
public class UserController {

private final UserService userService;

@GetMapping("loadUser")
public List<User> loadUsers() {
return userService.loadUsers();
}

@GetMapping("/search")
public List<User> searchUsers(@RequestParam String name) {
return userService.getUsersByName(name);
}

@GetMapping("/ageGreaterThan")
public List<User> searchUsers(@RequestParam Integer age) {
return userService.findByAgeGreaterThan(age);
}
}

Testing the REST API with Postman:

Once the application is running, test the endpoints:


Load Users http://localhost:8080/users/loadUser

Find Users by Name  http://localhost:8080/users/search?name=John

Find Users Older Than 30  http://localhost:8080/users/ageGreaterThan?age=30


Previous Next