How to filter certain records for all database operations in Spring Boot

Viewed 363

I'm trying to create an API to retrieve records from a database with Spring Boot Data JDBC.

Say I have the following entity:

record Customer(@Id Long id, String name) {}

and the following repository:

public interface CustomerRepository extends CrudRepository<Customer, Long> { }

Then customerRepository.findAll() neatly returns a List<Customer> containing all customers, and repository.findById(23) successfully finds the customer with id #23, provided that such customer actually exists.

Now the underlying database table has an extra column isDisabled, which contains either 0 or 1. I want my API to exclude all records where the customer is disabled.

I could create queries where I manually exclude those records, like this:

public interface CustomerRepository extends CrudRepository<Customer, Long> {

    @Query("""
        SELECT id, name
        FROM Customers
        WHERE id = :id
        AND isDisabled = 0
    """)
    @Override
    public Optional<Customer> findById(@Param("id") String id);
}

This works, but the problem is that I have to do this for all database retrieval queries, including findAll, findByName et cetera.

Is there a way to somehow filter these records for all retrieval queries at once?

For example, suppose we have the following table:

id name isDisabled
1 Alice 0
2 Brittany 1
3 Caroline 0
4 Dakota 0

then findAll() would return

[
    Customer(1, Alice),
    Customer(3, Caroline),
    Customer(4, Dakota)
]

Likewise, findById(2) would return an empty Optional.

1 Answers

It seems like you are trying to implement a 'soft delete' kind of functionality. You can add the following annotation to your entity which will do what you are asking for:

@Where(clause = "isDisabled=0")

See this link for more information on soft delete.

Related