如何向 Spring Data JPA 默认和派生查询添加租户条件

问题描述

我有一个 Springboot 应用程序，其中包含 Spring Data JPA 查询(如 findOne、findAll 以及 findByID 或 findByName 等派生查询)的存储库.

I have a Springboot Application with Repositories having Spring Data JPA Queries like findOne, findAll and also derived ones like findByID or findByName etc.

我想要实现的是多租户.所有实体都有一个包含租户的account_id"列.

What I want to achieve is multitenancy. All entities have an "account_id" column which holds the tenant.

如何在不使用包含诸如 findIdAndAccountid(将是 findone)之类的名称的派生查询的情况下向上面提到的所有查询添加类似account_id"的过滤器

How do I add a filter like "account_id" to all the queries metioned above without using derived queries that contains those name slike findIdAndAccountid (which would be findone)

@Repository
public interface CategoryRepository extends JpaRepository<Category, Long> {

    Category findByName(String name);

}

这里是对应的实体

@Entity
@Table(name = "unit")
@Data
public class Unit {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String name;

    @Column(name = "account_id")
    private Long account_id;
}

我知道大多数人使用模式作为租户分离，但这对我来说是不可能的.有没有办法(我没有找到)在不编写 NamedQueries 或使用 DerivedQueries 的情况下在这些查询上添加这样的租户过滤条件.一个优雅的解决方案，比如注释存储库或实体，或者所有查询都应该添加附加过滤器account_id"的查询?

I know most people use schemas as tenant separation but that's impossible for me. Is there a way (I didn't find one) to add such a tenant filter condition on those queries without writing NamedQueries or using DerivedQueries. An elegeant solution like annotate the repository or entity or maybe the queries that all queries should add the additional filter "account_id"?

推荐答案

更新和解决方案1. 创建一个过滤器像这样对实体进行 FilterDef

Update and Solution1. Create a Filter & FilterDef on the entity like so

@FilterDef(name="accountFilter", parameters=@ParamDef( name="accountId", type="long" ) )
@Filters( {
        @Filter(name="accountFilter", condition=":accountId = account_id")
} )
public class Category {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String name;

    @Column(name = "account_id")
    private Long account_id;
}

1. 通过自动装配实体管理器在控制器中启用过滤器，编写一个方法来启用过滤器并在@ModelAttribute 中为每个请求激活过滤器

@RestController
@RequestMapping(path = "/categories",produces = MediaType.APPLICATION_JSON_VALUE )
public class CategoryController {
    private final CategoryRepository repository;

    @Autowired
    private EntityManager entityManager;

    CategoryController(CategoryRepository repository) {
        this.repository = repository;
    }

    private void activateFilter() {
        Session session = entityManager.unwrap(Session.class);

        Filter filter = session.enableFilter("accountFilter");
        filter.setParameter("accountId", Long.valueOf(TenantContext.getCurrentTenant()));


    }
    @ModelAttribute
    public void initFilter() {
        activateFilter();
    }

    ... your rest methods here

}

这篇关于如何向 Spring Data JPA 默认和派生查询添加租户条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！