definition

Logical deletion: the database record will not be deleted directly, but the record to be deleted will be identified. It will not be found in each query to achieve the effect of deletion.

Usually, the company may not allow us to delete data at will, so it will require us to operate the database by logical deletion.

jpa in this article refers to spring data jpa.

Logical deletion of Mybatis

Mybatis comes with such a plug-in.

The operation is as follows:

1. Let's configure this plug-in first.

You can use application Configuration in YML

global-config:
    db-config: #Configure logical deletion
      logic-delete-value: 1 # Logical deleted value (default = 1)
      logic-not-delete-value: 0 # Logical undeleted value (default is 0)

You can also use @ Bean to register plug-ins.

Note: @ MapperScan: generate the implementation classes of all interfaces under this address.

2. Mark @ TableLogic on an attribute of the entity class. The internal structure is as follows

@TableLogic Annotation parameters
value = "" Undeleted value. The default value is 0
delval = "" After deletion, the default value is 1
@TableLogic(value="Original value",delval="Change value")

If the attribute is defined by us, delete it

@TableLogic
private boolean delete;

The logical deletion of mybatis is so simple.

Logical deletion of JPA (key)

Because JPA does not support logical deletion, we need to implement it ourselves.

1. Flexibly use @ Delete and @ Where

@SQLDelete(sql = "update demo set deleted = 1 where id = ?")
@Where(clause = "deleted = 0")

This makes it easy to complete our task.

But after all, the demand is changeable. If people only want to delete physically, we can simply delete these two sentences. However, if the two products want the coexistence of logical deletion and physical deletion, we can't do it.

2. Solve the problem of 1.

Wouldn't it be good to add a physically deleted sql directly to the repository?

But it's too troublesome to add it to every table.

It's better not to write the code.

3. The ultimate solution.

In fact, by rewriting BaseDao / jpareportory and adding the method of physical deletion, our code will not be so complex.

@NoRepositoryBean: This is the interface that rewrites basedao. If it is not rewritten, the default one is used.

Note here that jdk8 and later, we can write the implementation method in the interface method.

@NoRepositoryBean
public interface BaseDao<T extends BaseEntry, ID extends Serializable> extends PagingAndSortingRepository<T, ID> {
 
    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.deleted = false")
    List<T> findAll();
 
    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.id in ?1 and e.deleted = false")
    Iterable<T> findAll(Iterable<ID> ids);
 
    @Override
    @Transactional(readOnly = true)
    @Query("select e from #{#entityName} e where e.id = ?1 and e.deleted = false")
    T findOne(ID id);
 
    @Override
    @Transactional(readOnly = true)
    @Query("select count(e) from #{#entityName} e where e.deleted = false")
    long count();
 
    @Override
    @Transactional(readOnly = true)
    default boolean exists(ID id) {
        return findOne(id) != null;
    }
 
    @Query("update #{#entityName} e set e.deleted = true where e.id = ?1")
    @Transactional
    @Modifying
    void logicDelete(ID id);
 
    @Transactional
    default void logicDelete(T entity) {
        logicDelete((ID) entity.getId());
    }
 
    @Transactional
    default void logicDelete(Iterable<? extends T> entities) {
        entities.forEach(entity -> logicDelete((ID) entity.getId()));
    }
 
    @Query("update #{#entityName} e set e.deleted = true ")
    @Transactional
    @Modifying
    void logicDeleteAll();
}

BaseEntry is as follows

@Data
@MappedSuperclass
public class BaseEntry implements Serializable {
 
    private static final long serialVersionUID = 5966306766659220492L;
 
    @Id
    protected String id;
 
    @Temporal(TemporalType.TIMESTAMP)
    protected Date createdDate;
 
    protected String createdBy;
 
    @Temporal(TemporalType.TIMESTAMP)
    protected Date updatedDate;
 
    protected String updatedBy;
 
    protected Boolean deleted = false;
}

So we can solve it.