Spring Data and Custom Queries: Best Practices

Spring Data provides a powerful way to interact with your database with minimum effort. Occasionally though, you need to write custom queries to fetch or manipulate data in a more granular way. Below are some best practices you should consider when writing custom queries in a Spring Data repository.

Examples in this brief tutorial are implemented in Kotlin for conciseness and clarity. The project is available on GitHub. Looking for clarity on managing persistence with Hibernate? Check this post for details and examples. Struggling with LazyInitializationException? Have a look at my other article that provides useful suggestions and explanations.

Table of Contents

• Define Custom Queries with Care
• Managing Transactions
• Transaction Propagation
• Rollback Behaviour
• Optimize for Bulk Operations
• Summary

Define Custom Queries with Care

When defining custom queries in a Spring Data repository, you have the option to use JPQL (Java Persistence Query Language) or native SQL. JPQL is platform-independent and can be a safer choice. Native SQL, on the other hand, might be necessary for performance reasons or when using features specific to your database.

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository
import javax.persistence.Entity
import javax.persistence.Id

@Entity
class Person {
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long? = null,
    var name: String = "",
    var age: Int = 0
}

interface PersonRepository : JpaRepository<Person, Long> {
    // Custom SELECT query using JPQL
    @Query("SELECT p FROM Person p WHERE p.name = :name")
    fun findByName(name: String): List<Person>
    
    // Custom UPDATE query using JPQL
    @Modifying
    @Query("UPDATE Person p SET p.name = :newName WHERE p.name = :oldName")
    fun updateName(oldName: String, newName: String): Int // Returns the number of entities updated
    
    // Custom DELETE query using JPQL
    @Modifying
    @Query("DELETE FROM Person p WHERE p.name = :name")
    fun deleteByName(name: String): Int // Returns the number of entities deleted
}

Managing Transactions

Transactional methods in Spring Data repositories ensure that an entire block of operations is treated as a single atomic unit. In case of any failure during one of these operations, the transaction is rolled back, leaving your data in a consistent state.

It is safe and recommended to mark custom update and delete repository methods with @Transactional. This ensures that changes are only committed if the entire transaction is successful. For read-only operations, transactions are not strictly necessary but you can use them to specify a read-only transaction context, which can optimize performance in some cases.

import org.springframework.transaction.annotation.Transactional

interface PersonRepository : JpaRepository<PersonRepository, Long> {
    ...
    
    @Modifying
    @Transactional
    @Query("UPDATE ExampleEntity e SET e.name = :newName WHERE e.name = :oldName")
    fun updateName(oldName: String, newName: String): Int 
    
    @Modifying
    @Transactional
    @Query("DELETE FROM ExampleEntity e WHERE e.name = :name")
    fun deleteByName(name: String): Int
}

 

The @Modifying annotation is specific to Spring Data JPA. You’d typically use it in conjunction with query methods in the repository interface. Use this annotation when you want to perform write operations using repository methods that execute INSERT, UPDATE, DELETE, or even DDL queries through JPQL or native SQL.

Spring takes @Modifying as a signal to treat the annotated method as a modifying operation and not a standard query. This differentiation is crucial because modifying operations can potentially have side effects that affect the current persistence context.

Transaction Propagation

When dealing with transactions and rollback behavior in Spring, it is essential to understand how transaction management is typically applied when calling repository methods from the service layer. This is crucial because in a layered architecture, transactions are often controlled at the service level rather than by the repository.

Transactions can be started at the service layer using the @Transactional annotation on methods or at the class level. When a transactional service method is called, Spring checks if there is an existing transaction. If not, it starts a new one.

If there is an existing transaction (possibly started by an outer service method), Spring will either participate in it or create a new transaction, depending on the propagation setting specified in the @Transactional annotation. The default propagation setting is PROPAGATION_REQUIRED, meaning it will reuse the existing transaction if present, otherwise it will create a new one.

Rollback Behaviour

Rollback is a mechanism that defines when a transaction should be undone. By default, in Spring, a transaction will roll back on any runtime exception that is thrown from within the transactional boundary.

The @Transactional annotation provides options to customize this behaviour through its rollbackFor and noRollbackFor attributes that allow you to specify which exceptions should or shouldn’t cause a rollback. By default, a transaction will roll back on any RuntimeException and Error.

Let’s see an example through a service calling our previously defined repository:

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional

@Service
class PersonSerivce(private val personRepository: PersonRepository) {

    // Transaction is started here, and the readOnly attribute is set to true since it's just a query
    @Transactional(readOnly = true)
    fun findPersonByName(name: String): List<Person> {
        return personRepository.findByName(name)
    }
    
    // This method can potentially modify data, so readOnly is false (which is the default)
    @Transactional
    fun updatePersonName(oldName: String, newName: String) {
        val updatedCount = personRepository.updateName(oldName, newName)
        if (updatedCount == 0) {
            throw RuntimeException("Entity not found or could not be updated")
        }
        // If a RuntimeException is thrown, the transaction will be rolled back
    }
    
    // Example of explicitly controlling rollback behaviour
    @Transactional(rollbackFor = [CustomException::class], noRollbackFor = [CertainNotSoSeriousException::class])
    fun deletePerson(name: String) {
        try {
            personRepository.deleteByName(name)
        } catch (e: CertainNotSoSeriousException) {
            // This won't cause a rollback
        }
    }
}

class CustomException(message: String) : Exception(message)
class CertainNotSoSeriousException(message: String) : Exception(message)

The findPersonByName method is transactional, but since it performs a read operation, we set readOnly = true. This allows the database optimization to kick in, such as bypassing certain kinds of flush operations.

The updatePersonName method is wrapped in a transaction which is not marked as read-only (readOnly = false is the default). If the updateName call fails to find and update an entity, we throw a RuntimeException. Spring will then automatically roll back the transaction.

The deletePerson method demonstrates how to define custom rollback rules. Suppose a CertainNotSoSeriousException is thrown during the execution of deleteByName. In that case, the annotation instructs Spring not to roll back the transaction, while a CustomException will cause a rollback.

This service-layer-driven transaction management allows you to control transaction boundaries and behavior closer to the business logic, thus providing more flexibility and a better abstraction over the data access layer.

Please note: When you specify exceptions in the rollbackFor attribute, you are instructing the transaction management system to force a rollback if any of these specified exceptions are thrown during the execution of the method. It doesn’t mean that these are the only exceptions that will trigger a rollback but rather that these exceptions will definitely trigger a rollback.

Optimize for Bulk Operations

When performing bulk update or delete operations, it is recommended to use @Modifying(clearAutomatically = true) to automatically clear the underlying persistence context upon query execution. This helps prevent any out-of-sync issues between the database and the persistence context in memory.

import org.springframework.data.jpa.repository.Modifying

@Modifying(clearAutomatically = true)
@Transactional
@Query("DELETE FROM ExampleEntity e WHERE e.name = :name")
fun deleteByName(name: String): Int

Summary

Custom queries in Spring Data are a powerful tool when used correctly. Always define your custom queries with caution, using the appropriate query language for your use case. Make sure to demarcate transaction boundaries explicitly with @Transactional for update and delete operations to maintain data integrity, especially in the face of exceptions. And finally, optimize bulk operations with appropriate annotations to keep the persistence context in sync.

Thanks for reading. Source code is available on GitHub.