Blog

How to Implement Soft Delete with JPA and Hibernate

Tweet
Share
Share
Pin
0 Shares

In our previous tutorial, we added an extra column to an intermediary join table. In this tutorial, we are gonna implement soft delete functionality for the entities having many-to-many association with JPA and Hibernate.

Introduction

Soft delete performs an update operation to mark some data as deleted instead of physically deleting it from a table in the database. Basically, soft delete can be implemented by adding a field that will indicate whether the data has been deleted or not.

Implementing Soft Deletion

In order to implement soft delete functionality, we are gonna add a column called as ‘deleted‘ in both the User and User_Role tables as explained in our previous tutorial. Then, we need to add support for

  • Executing an update statement to set the deleted flag to true whenever the delete command of an entity is executed.
  • Filtering the records marked as deleted from all the read operations.

This is where the following hibernate annotations come into the picture:

  • @SQLDelete annotation provides support for defining a custom SQL statement for the delete of an entity/collection. This SQL statement will get executed whenever the delete command is executed. Basically, we will define an SQL update statement to set the deleted flag to true instead of deleting the record physically.
  • @Where annotation provides support for defining a SQL where clause to add to the element Entity or target entity of a collection. This where clause will be appended to the SQL select queries of all the read operations. Here, the where clause is deleted = false which filters the records that are marked as deleted.

Let’s add both @SQLDelete and @Where annotations for both User and UserRole entities.

User.java

@Entity
@NoArgsConstructor
@Getter
@Setter
@SQLDelete(sql = "UPDATE user SET deleted = true WHERE id = ?")
@Where(clause = "deleted = false")
public class User implements Serializable {

UserRole.java

@Entity
@Getter
@Setter
@NoArgsConstructor
@SQLDelete(sql = "UPDATE user_role SET deleted = true WHERE role_id = ? and user_id = ?")
@Where(clause = "deleted = false")
public class UserRole implements Serializable {

Pitfalls of Soft Deletion

  • When you are using the @Where annotation to filter the deleted records, then you may end up with org.hibernate.exception.ConstraintViolationException if you try to persist a record with a primary key that already exists in the table.
    • For example, in this tutorial, the email is the primary key in the User table. So, when you want to insert a new user, you will first check if a user with the same email id already exists in the table. If it exists and is marked as deleted, then your select query will filter out this record from the result. So, you will assume that it does not exist and try to insert this new user which will result in ConstraintViolationException. The same issue applies to UserRole table as well. Hence, in this scenario, it is better to avoid using the @Where annotation for filtering the deleted records.
  • When your domain model has many tables and they are associated with each other, then you should not try to implement soft delete for some specific tables alone. Either you should implement it for all or not at all. Otherwise, you may end up with FOREIGN KEY constraint exceptions.

Testing Time

Now, we are gonna define h2 in-memory database connection properties in src/test/resources/application.properties file for our Junit tests.

application.properties

#Test Properties
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MySQL;NON_KEYWORDS=USER
spring.datasource.username=sa
spring.datasource.password=sa
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

Note: We have specified NON_KEYWORDS=USER in the connection string. In h2 in-memory database, user is a keyword. But, we have mapped the user entity to the user table. Hence we need to specify this in the connection string in order to tell the h2 database not to interpret user as a keyword. if you don’t do this, then you need to change the table name to something like ‘users‘. otherwise you will end up with "Syntax error in SQL statement ... expected identifier"

src/test/resources/insert-scripts.sql

Here, we have added the insert statements for inserting roles into the tables required for the tests.

INSERT INTO `role` (`id`, `name`) VALUES (1, 'ROLE_USER');
INSERT INTO `role` (`id`, `name`) VALUES (2, 'ROLE_ADMIN');

UserRepositoryTest.java

This test class is responsible for testing the soft deletion of User & UserRole entities.

@DataJpaTest annotation is used for testing only the JPA components. Using this annotation will disable full auto-configuration and instead apply only configuration relevant to JPA tests. By default, tests annotated with @DataJpaTest are transactional and roll back at the end of each test. They also use an embedded in-memory database (replacing any explicit or usually auto-configured DataSource). The @AutoConfigureTestDatabase annotation can be used to override these settings.

@AutoConfigureTestDatabase annotation can be applied to a test class to configure a test database to use instead of the application-defined or auto-configured DataSource. In the case of multiple DataSource beans, only the @Primary DataSource is considered. Here, we have set the replace attribute to Replace.NONE since we had to customize the h2 database connection string to include NON_KEYWORDS=USER settings. Otherwise, we don’t need to use this annotation at all since @DataJpaTest will use the default connection string defined for the embedded h2 database.

@SQL annotation is used to annotate a test class or test method to configure SQL scripts and statements to be executed against a given database during integration tests.

package com.javachinna.repo;

import static org.junit.jupiter.api.Assertions.assertEquals;

import java.util.stream.Collectors;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase.Replace;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.jdbc.Sql;

import com.javachinna.model.Role;
import com.javachinna.model.User;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@DataJpaTest
@Sql(scripts = "classpath:insert-scripts.sql")
@AutoConfigureTestDatabase(replace = Replace.NONE)
class UserRepositoryTest {

	@Autowired
	private UserRepository userRepository;
	
	@Autowired
	private RoleRepository roleRepository;
	
	@Test
	void testSoftDelete() {
		// Add a new user
		User user = new User();
		user.setDisplayName("Chinna");
		user.setEmail("[email protected]");
		// Add a role to the user
		Role adminRole = roleRepository.findByName(Role.ROLE_ADMIN);
		user.addRole(roleRepository.findByName(Role.ROLE_USER));
		user.addRole(adminRole);
		user = userRepository.saveAndFlush(user);
		assertEquals(2, user.getRoles().size());
		// Remove a role from the user		
		user.removeRole(adminRole);
		userRepository.saveAndFlush(user);

		user = userRepository.findByEmail("[email protected]");
		
		assertEquals(1, user.getRoles().size());
		log.info("User Roles after removal: {}", user.getRoles().stream().map(r -> r.getRole().getName()).collect(Collectors.toList()));
		userRepository.delete(user);
		assertEquals(0, userRepository.findAll().size());
	}
}

Note: Here, I have called the JpaRepository.saveAndFlush() method instead of the CrudRepository.save() method. This is because, save method will persist only the user entity. But the associated user roles will be persisted when the changes are flushed. Hence, I wanted to save and flush the pending changes immediately in order to see the insert statements consecutively in the output.

Output
JPA Hibernate Soft delete
2022-08-06 03:08:01.370  INFO 29812 --- [           main] o.s.t.c.transaction.TransactionContext   : Began transaction (1) for test context [DefaultTestContext@46268f08 testClass = UserRepositoryTest, testInstance = com.javachinna.repo.UserRepositoryTest@1d3ac898, testMethod = testSoftDelete@UserRepositoryTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@2a76840c testClass = UserRepositoryTest, locations = '{}', classes = '{class com.javachinna.Application}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTestContextBootstrapper=true}', contextCustomizers = set[[ImportsContextCustomizer@71454b9d key = [org.springframework.boot.autoconfigure.cache.CacheAutoConfiguration, org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration, org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration, org.springframework.boot.autoconfigure.liquibase.LiquibaseAutoConfiguration, org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration, org.springframework.boot.autoconfigure.sql.init.SqlInitializationAutoConfiguration, org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration, org.springframework.boot.test.autoconfigure.jdbc.TestDatabaseAutoConfiguration, org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManagerAutoConfiguration]], org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@1bae316d, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@7cbd9d24, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.autoconfigure.OverrideAutoConfigurationContextCustomizerFactory$DisableAutoConfigurationContextCustomizer@21129f1f, org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@68d279ec, org.springframework.boot.test.autoconfigure.filter.TypeExcludeFiltersContextCustomizer@351584c0, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@50ae1eef, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@1115ec15, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@0], contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.event.ApplicationEventsTestExecutionListener.recordApplicationEvents' -> false]]; transaction manager [org.springframework.orm.jpa.JpaTransactionManager@53ddabc6]; rollback [true]
Hibernate: select role0_.id as id1_0_, role0_.name as name2_0_ from role role0_ where role0_.name=?
Hibernate: select role0_.id as id1_0_, role0_.name as name2_0_ from role role0_ where role0_.name=?
Hibernate: insert into user (id, created_date, deleted, display_name, email, enabled, modified_date, password, provider, provider_user_id, secret, using_2fa) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into user_role (deleted, role_id, user_id) values (?, ?, ?)
Hibernate: insert into user_role (deleted, role_id, user_id) values (?, ?, ?)
Hibernate: UPDATE user_role SET deleted = true WHERE role_id = ? and user_id = ?
Hibernate: select user0_.id as id1_1_, user0_.created_date as created_2_1_, user0_.deleted as deleted3_1_, user0_.display_name as display_4_1_, user0_.email as email5_1_, user0_.enabled as enabled6_1_, user0_.modified_date as modified7_1_, user0_.password as password8_1_, user0_.provider as provider9_1_, user0_.provider_user_id as provide10_1_, user0_.secret as secret11_1_, user0_.using_2fa as using_12_1_ from user user0_ where ( user0_.deleted = false) and user0_.email=?
2022-08-06 03:08:01.608  INFO 29812 --- [           main] com.javachinna.repo.UserRepositoryTest   : User Roles after removal: [ROLE_USER]
Hibernate: UPDATE user_role SET deleted = true WHERE role_id = ? and user_id = ?
Hibernate: UPDATE user SET deleted = true WHERE id = ?
Hibernate: select user0_.id as id1_1_, user0_.created_date as created_2_1_, user0_.deleted as deleted3_1_, user0_.display_name as display_4_1_, user0_.email as email5_1_, user0_.enabled as enabled6_1_, user0_.modified_date as modified7_1_, user0_.password as password8_1_, user0_.provider as provider9_1_, user0_.provider_user_id as provide10_1_, user0_.secret as secret11_1_, user0_.using_2fa as using_12_1_ from user user0_ where ( user0_.deleted = false)
2022-08-06 03:08:01.623  INFO 29812 --- [           main] o.s.t.c.transaction.TransactionContext   : Rolled back transaction for test: [DefaultTestContext@46268f08 testClass = UserRepositoryTest, testInstance = com.javachinna.repo.UserRepositoryTest@1d3ac898, testMethod = testSoftDelete@UserRepositoryTest, testException = [null], mergedContextConfiguration = [MergedContextConfiguration@2a76840c testClass = UserRepositoryTest, locations = '{}', classes = '{class com.javachinna.Application}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTestContextBootstrapper=true}', contextCustomizers = set[[ImportsContextCustomizer@71454b9d key = [org.springframework.boot.autoconfigure.cache.CacheAutoConfiguration, org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration, org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration, org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration, org.springframework.boot.autoconfigure.liquibase.LiquibaseAutoConfiguration, org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration, org.springframework.boot.autoconfigure.sql.init.SqlInitializationAutoConfiguration, org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration, org.springframework.boot.test.autoconfigure.jdbc.TestDatabaseAutoConfiguration, org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManagerAutoConfiguration]], org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@1bae316d, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@7cbd9d24, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.autoconfigure.OverrideAutoConfigurationContextCustomizerFactory$DisableAutoConfigurationContextCustomizer@21129f1f, org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@68d279ec, org.springframework.boot.test.autoconfigure.filter.TypeExcludeFiltersContextCustomizer@351584c0, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@50ae1eef, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@1115ec15, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@0], contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.event.ApplicationEventsTestExecutionListener.recordApplicationEvents' -> false]]

Source Code

https://github.com/JavaChinna/jpa-hibernate-soft-delete

Conclusion

That’s all folks. In this article, we have implemented the soft delete functionality for the user entity and written a Junit test to test if the records are soft deleted as expected.

Thank you for reading.

Tweet
Share
Share
Pin
0 Shares
Tags: , , , ,

Leave a Reply