Scribd
Upload
5 views2 pages

Custom Queries in Spring Data JPA

In Spring Boot with Spring Data JPA, custom repository methods can be defined using derived query methods, the @Query annotation, and @Modifying for DML operations. Derived query methods automatically generate queries based on method names, while @Query allows for custom SQL or JPQL definitions. Additionally, pagination and sorting can be implemented using Pageable and Sort parameters.

Uploaded by

3future1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views2 pages

Custom Queries in Spring Data JPA

In Spring Boot with Spring Data JPA, custom repository methods can be defined using derived query methods, the @Query annotation, and @Modifying for DML operations. Derived query methods automatically generate queries based on method names, while @Query allows for custom SQL or JPQL definitions. Additionally, pagination and sorting can be implemented using Pageable and Sort parameters.

Uploaded by

3future1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

In Spring Boot with Spring Data JPA, you can define custom repository methods to

perform operations beyond the default CRUD operations like save(), findById(),
findAll(), etc. These custom methods are defined using:

✅ 1. Derived Query Methods (Method Naming Conventions)


Spring Data JPA can automatically generate queries based on method names.

🔸 Syntax:
java
Copy
Edit
List<User> findByName(String name);
User findByEmail(String email);
List<User> findByAgeGreaterThan(int age);
🔸 Examples:
Method Name Query Generated
findByUsername(String username) SELECT * FROM user WHERE username = ?
findByAgeGreaterThan(int age) SELECT * FROM user WHERE age > ?
findByNameAndCity(String name, String city) SELECT * FROM user WHERE name = ?
AND city = ?

📘 Keywords: And, Or, Between, Like, In, Not, IsNull, IsNotNull, OrderBy, Top,
First, etc.

✅ 2. @Query Annotation (JPQL or Native SQL)


Use @Query to define custom SQL or JPQL.

🔹 JPQL Example:
java
Copy
Edit
@Query("SELECT u FROM User u WHERE [Link] = ?1")
List<User> getUsersByCity(String city);
🔹 Native SQL Example:
java
Copy
Edit
@Query(value = "SELECT * FROM users WHERE city = ?1", nativeQuery = true)
List<User> getUsersByCityNative(String city);
🔹 Named Parameters:
java
Copy
Edit
@Query("SELECT u FROM User u WHERE [Link] = :name")
List<User> findByName(@Param("name") String name);
✅ 3. @Modifying for DML Operations (UPDATE, DELETE)
Spring Data JPA does not support UPDATE/DELETE in repository method names, so use
@Modifying.

🔹 Update Example:
java
Copy
Edit
@Transactional
@Modifying
@Query("UPDATE User u SET [Link] = :name WHERE [Link] = :id")
int updateUserName(@Param("id") Long id, @Param("name") String name);
🔹 Delete Example:
java
Copy
Edit
@Transactional
@Modifying
@Query("DELETE FROM User u WHERE [Link] = :city")
int deleteUsersByCity(@Param("city") String city);
🔐 Must be used inside @Transactional methods.

✅ 4. Pagination & Sorting


🔹 Pageable
java
Copy
Edit
Page<User> findByCity(String city, Pageable pageable);
🔹 Sort
java
Copy
Edit
List<User> findByCity(String city, Sort sort);

You might also like