使用SpringBoot集成JPA(Hibernate)、QueryDSL时设置默认查询条件
应用框架升级到SpringBoot3以后,持久层框架沿用了JPA技术路线,在升级过程中持久化框架进行数据查询时,需要带上默认的逻辑删除标识以及租户Id。
查阅了一翻资料,JPA的实现方案比较多,但是比较杂乱。QueryDSL框架的实现方法几乎没有。
这里把实现过程简单记录下来,供后来者参考。
JPA设置默认查询条件
- 在
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
- 自定义
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;
}
- 测试用例
@Test
@DisplayName("Example查询测试")
public void testQueryByExample() {
User user = new User();
user.setAge(18);
userRepository.findAll(Example.of(user));
}
- 执行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传送。
- 自定义Repository
public interface CustomRepository<T extends BaseEntity, ID> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T>, QuerydslJpaRepository<T, ID> {
}
- 自定义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);
}
}
- 注册自定义Repository替换默认Repository,可以在
@EnableJpaRepositories
注解中指定repositoryBaseClass
属性为CustomRepositoryImpl.class
,也有其它方案大家自行实现。 - 单元测试
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();
}
}
- 执行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=?
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~