Query Annotations

Question 1

What is

Answer 1

JPQL (Java Persistence Query Language) is similar to SQL but works on the entity objects rather than directly on database tables.

Question 2

@Query - Positional Parameter

Answer 2

@Query(“SELECT u FROM User u WHERE u.email = ?1”)
Optional<User> findByEmail(String email);</User>

User is an entity class.

?1 is a positional parameter representing the

first method parameter (email).

The query fetches a User object where the email matches the provided parameter.

Question 3

@Query - Named Parameter

Answer 3

@Query(“SELECT u FROM User u WHERE u.age > :age”)
List<User> findUsersOlderThan(@Param("age") int age);</User>

:age is a named parameter, and you pass its value using the @Param annotation.

This method returns a list of users older than the specified age.

Question 4

@Query - Native Query

Answer 4

A native query is a SQL statement that is specific to a particular database like MySQL. It varies a little from JPQL (Java Persistence Query Language) which is used by Spring Data JPA by default. Below is an example of a native SQL query
SELECT * FROM Student ORDER BY age
It is similar to the standard SQL query. But if you want to use this native query in the Spring Boot project then we have to take the help of @Query Annotation and we have to set an attribute nativeQuery=true in Query annotation to mark the query as native.

@Query(nativeQuery = true, value = “SELECT * FROM Student ORDER BY age”)
Optional<Student> findSortedStudentByAge();</Student>

Here, nativeQuery = true tells JPA that the query is a native SQL query rather than a JPQL (Java Persistence Query Language) query.

You should specify column names and table names as they exist in the database.
Complex Queries: For more complex queries, such as multi-table joins or aggregation functions, which may be cumbersome or less efficient in JPQL.

Question 5

Native Query Complex Example

Answer 5

@Query(
value = “SELECT c.customer_id, c.name, COUNT(o.order_id) AS total_orders “ +
“FROM customers c “ +
“JOIN orders o ON c.customer_id = o.customer_id “ +
“WHERE o.status = ‘COMPLETED’ AND c.city = :city “ +
“GROUP BY c.customer_id, c.name “ +
“ORDER BY total_orders DESC”,
nativeQuery = true
)
List<Object[]> findCompletedOrderCountByCustomerInCity(@Param(“city”) String city);

The return type List<Object[]> is used when the query returns multiple columns but doesn’t map directly to a single entity class. Each row in the list will contain an array of objects, where:

Object[0] is the customer_id,
Object[1] is the name,
Object[2] is the total_orders.

Usage

public void printCompletedOrderCountByCity(String city) {
List<Object[]> results = orderRepository.findCompletedOrderCountByCustomerInCity(city);

for (Object[] row : results) {
    Long customerId = (Long) row[0];
    String customerName = (String) row[1];
    Long totalOrders = (Long) row[2];
    
    System.out.printf("Customer ID: %d, Name: %s, Total Completed Orders: %d%n", customerId, customerName, totalOrders);
} }

Question 6

Pagination and Sorting

Answer 6

You can also use @Query with Pageable and Sort parameters to handle pagination and sorting
@Query(“SELECT u FROM User u WHERE u.status = :status”)
Page<User> findUsersByStatus(@Param("status") String status, Pageable pageable);</User>

Question 7

@Query - Updates with @Modifying

Answer 7

@Transactional
@Modifying
@Query(“UPDATE User u SET u.status = :status WHERE u.id = :id”)
int updateUserStatus(@Param(“id”) Long id, @Param(“status”) String status);
The @Transactional annotation is commonly used with @Modifying in Spring Data JPA to manage database transactions properly when you are executing update, delete, or insert operations.

Question 8

Handling Nulls

Answer 8

If the query result might be null, consider using Optional to avoid NullPointerException.
@Query(“SELECT u FROM User u WHERE u.username = :username”)
Optional<User> findByUsername(@Param("username") String username);</User>