How to Modify Data in Spring Boot JPA Using Custom Queries

 

Introduction

Updating table with custom queries has always been a need in java. There will be scenarios when not everything works with the available default queries from JPA, we need some specific handling to be done. In such cases, we can make use of  @Query annotation, combined with @Modifying and @Transactional for update operations.

---

Updating a Single Column Using JPQL

The recommended way to update a single column in JPA is using JPQL (Java Persistence Query Language). Here’s how:

Repository Method

java
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.repository.query.Param;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    @Modifying
    @Transactional
    @Query("UPDATE Employee e SET e.salary = :salary WHERE e.id = :id")
    int updateSalaryById(@Param("id") Long id, @Param("salary") Double salary);
}

Explanation:

• @Modifying → Required to indicate an update operation.
• @Transactional → Ensures the update is executed within a transaction.
• Return Type (int) → The number of rows updated.

Service Layer Usage:

java
int updatedRows = employeeRepository.updateSalaryById(1L, 75000.0);
System.out.println("Rows Updated: " + updatedRows);

---

Updating a Single Column Using Native SQL

If your application requires database-specific queries, you can use native SQL inside @Query.

Repository Method

java
@Modifying
@Transactional
@Query(value = "UPDATE employee SET salary = :salary WHERE id = :id", nativeQuery = true)
int updateSalaryByIdNative(@Param("id") Long id, @Param("salary") Double salary);

Key Differences:

• JPQL (UPDATE Employee e...`) → Works with entity names and is database-independent.
• Native SQL (UPDATE employee...`) → Works with table names and is database-dependent.

---

Updating Multiple Fields in a Single Query

You can update multiple columns at once using JPQL:

Repository Method

java
@Modifying
@Transactional
@Query("UPDATE Employee e SET e.salary = :salary, e.department = :department WHERE e.id = :id")
int updateEmployeeDetails(@Param("id") Long id, @Param("salary") Double salary, @Param("department") String department);

✔ This method updates both salary and department for a given employee ID.

---

When to Use @Query for Updates?

Scenario	Approach
Updating a single column	Use @Query("UPDATE Employee e SET e.salary = :salary WHERE e.id = :id")
Updating multiple columns	Use @Query("UPDATE Employee e SET e.salary = :salary, e.department = :department WHERE e.id = :id")
Need database-specific SQL?	Use nativeQuery = true in @Query
High-performance batch updates?	Consider bulk updates with Spring Batch

---

Common Pitfalls & Best Practices

• Always use @Modifying for update operations.
• Mark the method as @Transactional to avoid transaction issues.
• JPQL is recommended unless you need database-specific features.
• Ensure indexes are optimized for WHERE conditions to improve query performance.
• Avoid unnecessary updates—only update fields when changes are required.

---

Conclusion

Spring Data JPA makes updating records easy with @Query and @Modifying. Whether you use JPQL or native queries, following best practices ensures efficiency and reliability.

Would you like to explore bulk updates or optimizations for large datasets? Let me know in the comments! 🚀

---

Key Takeaways:

• Use JPQL for database-independent updates.
• Use native SQL if you need database-specific queries.
• Always use @Modifying and @Transactional for update queries.
• Batch updates can improve performance for large datasets.