This article's development environment: spring-boot:2.0.3.RELEASE + java1.8
WHY TO DO
Soft deletion: that is, no real deletion operation is performed. Due to the existence of binding (foreign keys) between our entities, deleting some data will lead to incomplete other data. For example, the teacher in Computer Class 1801 is Zhang San. At this time, if we delete Zhang San, then when we query Computer Class 1801, since Zhang San no longer exists, we will report the EntityNotFound error. Of course, in a database with foreign key constraints, if Zhang San is a teacher in Class 1801, then we will directly delete Zhang San and report a binding exception. In other words, the behavior of directly deleting Zhang San cannot be executed.
But sometimes, we do have the need to delete it. For example, an employee leaves and then we want to delete the employee in employee management. However: The employee has a history in the data table. For example, we recorded that the data structure of the second semester of 2017 was Zhang Sanjiao. Then, due to the existence of constraints, a binding error will be reported when deleting Zhang San. In other words: the embarrassment that it should be deleted but cannot be deleted.
This uses the soft deletion mentioned in this article. The so-called soft deletion means that I do not really delete the data in the data table, but instead add a mark to the record whether to delete it.
spring jpa supports soft deletion, we can find more articles with good quality to solve this problem. The general steps are: 1. Add @SqlDelete("update xxxx set deleted = 1 where id = ?")。2.加入@Where(clause = "deleted = false") . But this solution is not perfect. Specifically manifested in:
Let’s take Zhang San as a teacher in Class 1801 as an example.
After adding the annotation, we can indeed successfully delete Zhang San. After the deletion operation, we checked the data table and Zhang San's record is indeed still there. But at this time, if we do all or page query, we will get a 500 EntiyNotFound error. This is because during all query, jpa automatically adds the query parameters in @Where . Due to deleted = true of the associated data, an exception that the associated entity is not found.
But the fact is: although the entity is deleted, it is still there, and we want to apply it to the association query. It is not expected that it will have a 500 EntiyNotFound exception.
The solution of this article implements:
Solution
Implementation
initialization
Create three new entities: ClazzTest, Clazz, Teacher , and create a new BaseEntity abstract class entity. Where ClazzTest is used to demonstrate the exception that occurs when using @Where(clause = "deleted = false") .
package com.mengyunzhi.springbootsamplecode.softdelete.entity;import javax.persistence.MappedSuperclass;@MappedSuperclasspublic abstract class BaseEntity { private Boolean deleted = false; // setter and getter} package com.mengyunzhi.springbootsamplecode.softdelete.entity;import org.hibernate.annotations.SQLDelete;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;/** * Class*/@Entity@SQLDelete(sql = "update `klass` set deleted = 1 where id = ?")public class Klass extends BaseEntity { @Id @GeneratedValue private Long id; private String name; // setter and getter} @Entity@SQLDelete(sql = "update `klass_test` set deleted = 1 where id = ?")@Where(clause = "deleted = false")public class KlassTest extends BaseEntity { @Id @GeneratedValue private Long id; private String name;} Rewrite CrudRepository
package com.mengyunzhi.springbootsamplecode.softdelete.core;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.CrudRepository;import org.springframework.data.repository.NoRepositoryBean;import javax.transaction.Transactional;import java.util.Optional;/** * Apply soft delete* Default @Where(clause = "deleted = 0") will cause an exception of ObjectNotFound when an associated query is performed inside hibernate* Redefine the interface here* Reference: https://stackoverflow.com/questions/19323557/handling-soft-deletes-with-spring-jpa/22202469 * @author Mengyunzhi Software Development Team of Hebei University of Technology*/@NoRepositoryBeanpublic interface SoftDeleteCrudRepository<T, ID> extends CrudRepository<T, ID> { @Override @Transactional @Query("select e from #{#entityName} e where e.id = ?1 and e.deleted = false") Optional<T> findById(ID id); @Override @Transactional default boolean existsById(ID id) { return findById(id).isPresent(); } @Override @Transactional @Query("select e from #{#entityName} e where e.deleted = false") Iterable<T> findAll(); @Override @Transactional @Query("select e from #{#entityName} e where e.id in ?1 and e.deleted = false") Iterable<T> findAllById(Iterable<ID> ids); @Override @Transactional @Query("select count(e) from #{#entityName} e where e.deleted = false") long count();} Create a new warehouse class
Inheriting spring's CrudRepository.
/** * Class* @author panjie */public interface KlassRepository extends SoftDeleteCrudRepository<Klass, Long>{} public interface KlassTestRepository extends SoftDeleteCrudRepository<KlassTest, Long> {} public interface TeacherRepository extends CrudRepository<Teacher, Long> {} test
package com.mengyunzhi.springbootsamplecode.softdelete.repository;import com.mengyunzhi.springbootsamplecode.softdelete.entity.Klass;import com.mengyunzhi.springbootsamplecode.softdelete.entity.KlassTest;import com.mengyunzhi.springbootsamplecode.softdelete.entity.Teacher;import org.assertj.core.api.Assertions;import org.junit.Test;import org.junit.runner.RunWith;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.orm.jpa.JpaObjectRetrievalFailureException;import org.springframework.test.context.junit4.SpringRunner;import java.util.List;import java.util.Optional;/** * @author panjie */@SpringBootTest@RunWith(SpringRunner.class)public class TeacherRepositoryTest { private final static Logger logger = LoggerFactory.getLogger(TeacherRepositoryTest.class); @Autowired KlassRepository klassRepository; @Autowired KlassTestRepository klassTestRepository; @Autowired TeacherRepository teacherRepository; @Test public void findById() { logger.info("Create a new teacher with Klass and KlassTest"); Klass klass = new Klass(); klassRepository.save(klass); KlassTest klassTest = new KlassTest(); klassTestRepository.save(klassTest); Teacher teacher = new Teacher(); teacher.setKlass(klass); teacher.setKlassTest(klassTest); teacherRepository.save(teacher); logger.info("Look for the teacher, assert that the entity was found, and no exception occurred"); Optional<Teacher> teacherOptional = teacherRepository.findById(teacher.getId()); Assertions.assertThat(teacherOptional.get()).isNotNull(); logger.info("Delete the associated Klass, then look up the teacher entity, assert that the entity was found, no exception occurred. Assert that there is still a deleted Klass entity in the teacher entity"); klassRepository.deleteById(klass.getId()); teacherOptional = teacherRepository.findById(teacher.getId()); Assertions.assertThat(teacherOptional.get()).isNotNull(); Assertions.assertThat(teacherOptional.get().getKlass().getId()).isEqualTo(klass.getId()); logger.info("Look for teacher list, no exception occurred. Assert that there is a deleted Klass entity record in the teacher entity"); List<Teacher> teacherList = (List<Teacher>) teacherRepository.findAll(); for (Teacher teacher1 : teacherList) { Assertions.assertThat(teacher1.getKlass().getId()).isEqualTo(klass.getId()); } logger.info("Delete the associated KlassTest, then look up the teacher entity, assert that the deleted klassTest was found"); klassTestRepository.deleteById(klassTest.getId()); teacherOptional = teacherRepository.findById(teacher.getId()); Assertions.assertThat(teacherOptional.get()).isNotNull(); Assertions.assertThat(teacherOptional.get().getKlassTest().getId()).isEqualTo(klassTest.getId()); logger.info("Look up the teacher list again, assert that a JpaObjectRetrievalFailureException will occur(EntityNotFound exception is caught, encapsulated and thrown) exception"); Boolean catchException = false; try { teacherRepository.findAll(); } catch (JpaObjectRetrievalFailureException e) { catchException = true; } Assertions.assertThat(catchException).isTrue(); }} Summarize
When using the default @SqlDelete and @Where annotation, jpa data can handle the findById() method well, but it fails to handle the findAll() method well. Here, we have implemented the method of rewriting the CrunRepository method. When we will conduct basic queries, we use our customized method to add deleted = true. When jpa conducts an association query, since we do not set the @Where annotation, all data will be queryed, thus avoiding the exception that occurs sometimes when the foundAll() query is performed.
In this article, we only give some example code.
If you need the complete code, please click: https://github.com/mengyunzhi/springBootSampleCode/tree/master/softDelete.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.