JPA 使用@Where 注解实现全局过滤

需求

在互联网项目中，通常删除都不是物理删除，而是逻辑删除。那么在展示数据的时候需要过滤掉已删除的数据。而@Where 注解可以说就是为此而设计的。

/**
 * Where clause to add to the element Entity or target entity of a collection.  The clause is written in SQL.
 * A common use case here is for soft-deletes.
 *
 * @author Emmanuel Bernard
 */
@Target({TYPE, METHOD, FIELD})
@Retention(RUNTIME)
public @interface Where {
    /**
     * The where-clause predicate.
     */
    String clause();
}

大致意思为通常添加在集合或实体类上作为sql 的where条件使用，常见的使用方式是软删除。因为是where 子句的条件，所以写的是数据库字段的名称与实际结果。

使用

实体类

@Entity
@Table(name = "tb_abc")
@Data
@Where(clause = "del_flag=0")
public class ABC {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private String name;
    private boolean delFlag;
}

单元测试

    @Test
    public void whereTest(){
        Optional<ABC> one = abcRepository.findById(1);
        System.out.println(one.isPresent());
        ABC a3 = abcRepository.findByName("a3");
        System.out.println(a3);
        ABC byDelFlagIsTrue = abcRepository.findByDelFlagIsTrue();
        System.out.println(byDelFlagIsTrue);
        List<ABC> all = abcRepository.findAll();
        System.out.println(all.toString());
    }

select abc0_.id as id1_0_0_, abc0_.del_flag as del_flag2_0_0_, abc0_.name as name3_0_0_ from abc abc0_ where abc0_.id=? and ( abc0_.del_flag=0)
select abc0_.id as id1_0_, abc0_.del_flag as del_flag2_0_, abc0_.name as name3_0_ from abc abc0_ where ( abc0_.del_flag=0) and abc0_.name=?
select abc0_.id as id1_0_, abc0_.del_flag as del_flag2_0_, abc0_.name as name3_0_ from abc abc0_ where ( abc0_.del_flag=0) and abc0_.del_flag=1
select abc0_.id as id1_0_, abc0_.del_flag as del_flag2_0_, abc0_.name as name3_0_ from abc abc0_ where ( abc0_.del_flag=0)

那万一想查已被删除的数据

先试一下@Query 的方式

    @Query("select a from ABC a where  a.name=?1")
    ABC findByName1(String name);

结果

select abc0_.id as id1_0_, abc0_.del_flag as del_flag2_0_, abc0_.name as name3_0_ from tb_abc abc0_ where ( abc0_.del_flag=0) and abc0_.name=?
null

再试下原生的方式

    @Query(nativeQuery = true, value = "select * from tb_abc where name = ?1")
    ABC findByName2(String name);

select * from tb_abc where name = ?
ABC(id=2, name=a1, delFlag=true)

总结

相比于要查已删除的数据，情况远比查未删除的少得多。所以在实体类上加上@Where注解来筛选那些通常不变的条件是省时省力的做法。