使用SpringBoot集成JPA(Hibernate)、QueryDSL时设置默认查询条件

应用框架升级到SpringBoot3以后,持久层框架沿用了JPA技术路线,在升级过程中持久化框架进行数据查询时,需要带上默认的逻辑删除标识以及租户Id。
查阅了一翻资料,JPA的实现方案比较多,但是比较杂乱。QueryDSL框架的实现方法几乎没有。
这里把实现过程简单记录下来,供后来者参考。

JPA设置默认查询条件

  1. Entity的父类中增加@FilterDef@Filter注解
@MappedSuperclass
@FilterDef(name = "tenantAndLogicFilter", parameters = {@ParamDef( name = "deleted", type = String.class), @ParamDef( name = "tenantId", type = String.class)})
@Filter(name = "tenantAndLogicFilter", condition = "deleted = :deleted and tenant_id = :tenantId")
public class BaseEntity implements Serializable
  1. 自定义JpaTransactionManager,在createEntityManagerForTransaction方法返回EntityManager启用过滤器并设置查询条件
@Override
protected EntityManager createEntityManagerForTransaction() {
    EntityManager em = super.createEntityManagerForTransaction();
    em.unwrap(Session.class).enableFilter("tenantAndLogicFilter")
            .setParameter("tenantId", CurrentSessionHolder.getTenantId())
            .setParameter("deleted", "N");
    return em;
}
  1. 测试用例
@Test
@DisplayName("Example查询测试")
public void testQueryByExample() {
    User user = new User();
    user.setAge(18);
    userRepository.findAll(Example.of(user));
}
  1. 执行SQL如下
select
    u1_0.ID,
    u1_0.AGE,
    u1_0.CARD,
    u1_0.CJR_ID,
    u1_0.CJSJ,
    u1_0.NAME,
    u1_0.DELETED,
    u1_0.XGR_ID,
    u1_0.XGSJ,
    u1_0.TENANT_ID 
from
    T_USER u1_0 
where
    u1_0.deleted = ? 
    and u1_0.tenant_id = ? 
    and u1_0.AGE=?

QueryDSL设置默认查询条件

这里说个题外话,QueryDSL官方版本全部有严重的安全漏洞,而且很久没有维护了,这里使用OpenFeign维护的QueryDSL平替,GitHub传送

  1. 自定义Repository
public interface CustomRepository<T extends BaseEntity, ID> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T>, QuerydslJpaRepository<T, ID> {
}
  1. 自定义Repository实现类
public class CustomRepositoryImpl<T extends BaseEntity, ID> extends SimpleJpaRepository<T, ID> implements CustomRepository<T, ID> {

    private final JpaEntityInformation<T, ?> entityInformation;

    private final EntityManager entityManager;

    private final JPAQueryFactory dslQueryFactory;

    protected final static String LOGIC_DELETE_VARIABLE = "deleted";

    protected final static String LOGIC_DELETED_TAG = "Y";

    protected final static String LOGIC_UNDELETED_TAG = "N";

    protected final static String TENANT_VARIABLE = "tenantId";

    public CustomRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityInformation = entityInformation;
        this.entityManager = entityManager;
        this.dslQueryFactory = new JPAQueryFactory(this.entityManager);
    }

    protected Path<?> getPath(Expression<?> expression) {
        if (expression instanceof EntityPath entityPath) {
            return entityPath;
        } else if (expression instanceof Path propertyPath) {
            return propertyPath.getRoot();
        }
        throw new UnsupportedOperationException("expression require EntityPath(Table) or StringPath(field)");
    }

    @Override
    public <U> JPQLQuery<U> select(Expression<U> expression) {
        Path path = getPath(expression);
        return dslQueryFactory
                .select(expression)
                .where(
                        new CustomStringPath(path, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(path, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public JPQLQuery<Tuple> select(Expression<?>... expressions) {
        Path path = getPath(expressions[0]);
        return dslQueryFactory
                .select(expressions)
                .where(
                        new CustomStringPath(path, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(path, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public <U> JPQLQuery<U> selectDistinct(Expression<U> expression) {
        Path path = getPath(expression);
        return dslQueryFactory
                .selectDistinct(expression)
                .where(
                        new CustomStringPath(path, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(path, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public JPQLQuery<Tuple> selectDistinct(Expression<?>... expressions) {
        Path path = getPath(expressions[0]);
        return dslQueryFactory
                .selectDistinct(expressions)
                .where(
                        new CustomStringPath(path, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(path, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public JPQLQuery<Integer> selectOne() {
        return dslQueryFactory
                .selectOne()
                .where(
                        new CustomStringPath(PathMetadataFactory.forVariable(LOGIC_DELETE_VARIABLE)).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(PathMetadataFactory.forVariable(TENANT_VARIABLE)).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public JPQLQuery<Integer> selectZero() {
        return dslQueryFactory
                .selectZero()
                .where(
                        new CustomStringPath(PathMetadataFactory.forVariable(LOGIC_DELETE_VARIABLE)).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(PathMetadataFactory.forVariable(TENANT_VARIABLE)).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public <U> JPQLQuery<U> selectFrom(EntityPath<U> entityPath) {
        return dslQueryFactory
                .selectFrom(entityPath)
                .where(
                        new CustomStringPath(entityPath, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(entityPath, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public UpdateClause<?> update(EntityPath<T> entityPath) {
        return dslQueryFactory
                .update(entityPath)
                .where(
                        new CustomStringPath(entityPath, LOGIC_DELETE_VARIABLE).eq(LOGIC_UNDELETED_TAG),
                        new CustomStringPath(entityPath, TENANT_VARIABLE).eq(CurrentSessionHolder.getTenantId())
                );
    }

    @Override
    public InsertClause<?> insert(EntityPath<T> entityPath) {
        return dslQueryFactory.insert(entityPath);
    }
}

  1. 注册自定义Repository替换默认Repository,可以在@EnableJpaRepositories注解中指定repositoryBaseClass属性为CustomRepositoryImpl.class,也有其它方案大家自行实现。
  2. 单元测试
public interface UserRepository extends CustomRepository<User, String> {

    default List<User> findByDSL() {
        return select(QUser.user).from(QUser.user).where(QUser.user.age.eq(18)).fetch();
    }

}
  1. 执行SQL如下
    select
        u1_0.ID,
        u1_0.AGE,
        u1_0.CARD,
        u1_0.CJR_ID,
        u1_0.CJSJ,
        u1_0.NAME,
        u1_0.DELETED,
        u1_0.XGR_ID,
        u1_0.XGSJ,
        u1_0.TENANT_ID 
    from
        T_USER u1_0 
    where
        u1_0.DELETED=? 
        and u1_0.TENANT_ID=? 
        and u1_0.AGE=?
posted @   codest  阅读(18)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~
点击右上角即可分享
微信分享提示