Tutorial

Sign Up

log In

Spring Boot
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 ❯
Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.
© Copyright 2024 www.whereisstuff.com. All rights reserved. Developed by whereisstuff Tech.