Advertisement
Google Ad Slot: content-top
Spring Boot JPQL
JPQL (Java Persistence Query Language) is a Hibernate-based query language used to retrieve data from the database in Spring Data JPA. It is similar to SQL but operates on entity objects instead of database tables.
Example Entity Class
Let's assume we have a User entity with fields: id, name, email and age.
User.java
import jakarta.persistence.*;
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String email;
private int age;
// Getters and Setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
Table name : user
| id | name | age | |
|---|---|---|---|
| 1 | John | john@gmail.com | 25 |
| 2 | Mike | mike@gmail.com | 35 |
| 3 | Watson | watson@gmail.com | 40 |
| 4 | Kenny | kenny@gmail.com | 45 |
Create JPA Repositories
Example
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface UserRepository extends JpaRepository<User, Long> {
// ✅ Find All Users
@Query("SELECT u FROM User u") // Optional Jpa write for you
List<User> findAllUsers();
// ✅ Find User by Name
@Query("SELECT u FROM User u WHERE u.name = :name") // Optional Jpa write for you
User findByName(@Param("name") String name);
// ✅ Find Users by Age Greater Than
@Query("SELECT u FROM User u WHERE u.age > :age") // Optional Jpa write for you
List<User> findUsersOlderThan(@Param("age") int age);
// ✅ Find User by Email (Native Query)
@Query(value = "SELECT * FROM user WHERE email = :email", nativeQuery = true) // Native query
User findByEmail(@Param("email") String email);
// ✅ Update User Email by ID
@Modifying
@Transactional
@Query(value = "UPDATE user SET email = :email WHERE id = :id", nativeQuery = true) // Native query
int updateUserEmail(@Param("id") Long id, @Param("email") String email);
// ✅ Delete User by ID
@Modifying
@Transactional
@Query(value = "DELETE FROM user WHERE id = :id", nativeQuery = true) // Native query
int deleteUserById(@Param("id") Long id);
}
findAllUsers , findByName , findUsersOlderThan optional queries you don't want to write Jpa write query for you
Create Services
UserService.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
// ✅ Get All Users
public List<User> getAllUsers() {
return userRepository.findAllUsers();
}
// ✅ Get User by Name
public User getUserByName(String name) {
return userRepository.findByName(name);
}
// ✅ Get Users Older Than Age
public List<User> getUsersOlderThan(int age) {
return userRepository.findUsersOlderThan(age);
}
// ✅ Update User Email
public int updateUserEmail(Long id, String email) {
return userRepository.updateUserEmail(id, email);
}
// ✅ Delete User by ID
public int deleteUser(Long id) {
return userRepository.deleteUserById(id);
}
}
Create Controllers
UserController.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/users")
public class UserController {
@Autowired
private UserService userService;
// ✅ Get All Users
@GetMapping
public ResponseEntity<List<User>> getAllUsers() {
return ResponseEntity.ok(userService.getAllUsers());
}
// ✅ Get User by Name
@GetMapping("/{name}")
public ResponseEntity<User> getUserByName(@PathVariable String name) {
return ResponseEntity.ok(userService.getUserByName(name));
}
// ✅ Get Users Older Than Age
@GetMapping("/older/{age}")
public ResponseEntity<List<User>> getUsersOlderThan(@PathVariable int age) {
return ResponseEntity.ok(userService.getUsersOlderThan(age));
}
// ✅ Update Email
@PutMapping("/{id}/email")
public ResponseEntity<String> updateUserEmail(@PathVariable Long id, @RequestBody String email) {
int rowsUpdated = userService.updateUserEmail(id, email);
return ResponseEntity.ok(rowsUpdated + " row(s) updated.");
}
// ✅ Delete User
@DeleteMapping("/{id}")
public ResponseEntity<String> deleteUser(@PathVariable Long id) {
int rowsDeleted = userService.deleteUser(id);
return ResponseEntity.ok(rowsDeleted + " row(s) Deleted.");
}
}
Advanced JPQL Queries
JPQL with Sorting
@Query("SELECT u FROM User u ORDER BY u.age DESC")
List<User> findAllUsersSortedByAge();
JPQL with LIKE (Search by Name)
@Query("SELECT u FROM User u WHERE u.name LIKE %:keyword%")
List<User> searchUsersByName(@Param("keyword") String keyword);
JPQL with COUNT
@Query("SELECT COUNT(u) FROM User u WHERE u.age > :age")
Long countUsersOlderThan(@Param("age") int age);
Testing the REST API with Postman:
Once the application is running, test the endpoints:
Get All Users GET:http://localhost:8080/users
Get User by Name GET:http://localhost:8080/users/{userName}
Get Users Older Than 30 GET:http://localhost:8080/users/older/{age}
Update Email PUT:http://localhost:8080/users/{userID}/email
Delete User DELETE:http://localhost:8080/users/{userID}
