Spring Data JPA Delete Native Query

In this tutorial, we will learn how to use native SQL query to perform delete operations using Spring Data JPA.

Create JPA Entity

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDate;

@Setter
@Getter
@Entity
@Table(name = "persons")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String city;
    private LocalDate birthdate;  // Assuming you have this field
}

Create Spring Data JPA Repository - PersonRepository

Let's create a PersonRepository interface that extends the JpaRepository interface from Spring Data JPA:

import com.springdatajpa.springboot.entity.Person;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PersonRepository extends JpaRepository<Person, Long> {
}

Native Query Deletion in Repository

Spring Data JPA's @Query annotation is a gateway to native SQL operations when its nativeQuery attribute is set to true.

Delete a Person by City

@Modifying
@Query(value = "DELETE FROM Person WHERE city = ?1", nativeQuery = true)
void deleteByCity(String city);

@Modifying: Informs Spring Data JPA that this query modifies data.

nativeQuery=true: Indicates that the provided query is a native SQL statement.

Delete by a Date Range (Assuming a birthdate column)

@Modifying
@Query(value = "DELETE FROM Person WHERE birthdate BETWEEN ?1 AND ?2", nativeQuery = true)
void deleteByBirthdateBetween(LocalDate startDate, LocalDate endDate);

Delete by Name Pattern (Using SQL's LIKE)

@Modifying
@Query(value = "DELETE FROM Person WHERE name LIKE ?1", nativeQuery = true)
void deleteByNamePattern(String namePattern);

Delete by ID Less Than a Given Value

@Modifying
@Query(value = "DELETE FROM Person WHERE id < ?1", nativeQuery = true)
void deleteByIdLessThan(Long id);

Delete All Records

@Modifying
@Query(value = "DELETE FROM Person WHERE id < ?1", nativeQuery = true)
void deleteByIdLessThan(Long id);

Note: Native queries skip the ORM layer, so they're generally faster but lack the safety checks that come with using the ORM. Always ensure you understand the implications of your native queries, especially deletions, and thoroughly test them before deploying to a production environment.

Service Layer

Let's see how to use one of the deleteByCity query method in the service layer:

@Service
public class PersonService {

    @Autowired
    private PersonRepository personRepository;

    @Transactional
    public void removePersonsByCity(String city) {
        personRepository.deleteByCity(city);
    }
}

The @Transactional annotation is crucial, ensuring atomic operations. If the deletion process encounters an error, the transaction will be automatically rolled back.

Testing the Deletion Logic

Spring Boot's integrated testing support makes it convenient to validate our implementation:

@SpringBootTest
public class PersonServiceTest {

    @Autowired
    private PersonService personService;
    @Autowired
    private PersonRepository personRepository;

    @Test
    public void testDeleteByCity() {
        // Given: Initial data setup
        personRepository.save(new Person("John", "New York"));
        personRepository.save(new Person("Jane", "Los Angeles"));

        // When: Deleting persons by city
        personService.removePersonsByCity("New York");

        // Then: Assert that only persons from New York are deleted
        List<Person> remainingPersons = personRepository.findAll();
        assertTrue(remainingPersons.stream().noneMatch(person -> "New York".equals(person.getCity())));
    }
}

Conclusion

In this tutorial, we have learned how to use native SQL query to perform delete operations using Spring Data JPA. Remember, native queries skip the ORM layer, so they're generally faster but lack the safety checks that come with using the ORM. Always ensure you understand the implications of your native queries, especially deletions, and thoroughly test them before deploying to a production environment.