@Query Annotation - Spring Data JPA

Mastering Spring Data JPA's @Query Annotation: A Comprehensive Guide with Code Examples

Introduction:

Spring Data JPA simplifies the development of data access layers in Java applications by providing a powerful set of features and abstractions. One of the key features offered by Spring Data JPA is the `@Query` annotation, which allows developers to define custom SQL or JPQL (Java Persistence Query Language) queries directly within repository methods. In this blog post, we'll explore the internal workings of the `@Query` annotation, along with 10 different code examples demonstrating its usage in various scenarios.

@Query Annotation - Spring Data JPA

Understanding the @Query Annotation:

The `@Query` annotation is used to declare custom queries directly within Spring Data JPA repository interfaces. This annotation provides a flexible and expressive way to execute SQL or JPQL queries, enabling developers to perform complex data retrieval and manipulation operations.

Internal Working of @Query Annotation:

When a method in a Spring Data JPA repository interface is annotated with `@Query`, Spring Data JPA generates a concrete implementation for that method at runtime. This implementation dynamically constructs the appropriate SQL or JPQL query based on the query string provided in the annotation and executes it against the underlying database.

Code Examples:

Now, let's explore 10 different code examples showcasing the usage of the `@Query` annotation in various scenarios:

1. Basic Select Query:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.username = ?1")

    User findByUsername(String username);

}

2. Named Parameters:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.username = :username")

    User findByUsername(@Param("username") String username);

}

3. Using Native SQL Query:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM users WHERE username = ?1", nativeQuery = true)

    User findByUsername(String username);

}

4. Join Query:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u JOIN u.roles r WHERE r.name = ?1")

    List<User> findByRoleName(String roleName);

}

5. Aggregate Function:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT COUNT(u) FROM User u")

    Long countUsers();

}

6. Update Query:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying

    @Query("UPDATE User u SET u.enabled = true WHERE u.id = ?1")

    int enableUserById(Long userId);

}

7. Delete Query:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying

    @Query("DELETE FROM User u WHERE u.lastLoginDate < ?1")

    int deleteInactiveUsers(Date thresholdDate);

}

8. Paging and Sorting:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.enabled = true")

    Page<User> findAllEnabledUsers(Pageable pageable);

}

9. Projection:

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u.username, u.email FROM User u")

    List<Object[]> findAllUserDetails();

}

10. Using SpEL (Spring Expression Language):

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.username LIKE %:keyword% OR u.email LIKE %:keyword%")

    List<User> searchUsers(@Param("keyword") String keyword);

}

Conclusion:

The `@Query` annotation in Spring Data JPA provides a powerful mechanism for defining custom queries directly within repository interfaces. By leveraging this annotation, developers can execute SQL or JPQL queries tailored to their specific requirements, ranging from basic selects to complex joins and updates. With the flexibility and expressiveness offered by the `@Query` annotation, Spring Data JPA empowers developers to build robust and efficient data access layers for their applications.