避免多模式数据库上的代码重复 [英] Avoiding code repetition on multischema database

问题描述

我有一个带有数据库的旧版应用程序，该数据库将数据拆分为同一物理数据库上的多个架构.架构的结构相同.

I have a legacy application with a database that splits up the data into multiple schemas on the same physical database. The schemas are identical in structure.

我使用使用Spring Boot Data JPA的微服务来在单个架构上工作.然后，为避免代码重复，我创建了一个路由器服务，该服务将请求转发到单个模式微服务副本，每个副本具有不同的数据库连接.但是我发现有点矫kill过正(但是有效)

I use a microservice using Spring Boot Data JPA to do work on a single schema. Then to avoid code repetition, I created a router service that forwards the request to the single schema microservice replica each with a different database connection. But I found that a bit overkill (but works)

我正在尝试将其缩减为单个微服务.我还没有成功，但是我使用schema属性设置了表.

I am trying to reduce it back down to a single microservice. I haven't been successful yet, but I set up the tables with the schema property.

@Table(
    name = "alerts",
    schema = "ca"
)

但是，当我尝试进行继承和@MappedSuperclass来减少代码重复时，它会感到困惑.

However, it gets confused when I try to do inheritance and @MappedSuperclass to reduce the code duplication.

此外，@OneToMany破裂是因为继承得到诸如X references an unknown entity: Y

In addition the @OneToMany breaks apart because of the inheritance getting errors like X references an unknown entity: Y

基本上，有一种在JPA上使用继承的方法，该方法使用相同的表结构，不同之处只是没有复制和粘贴太多代码的架构.理想情况下，我只想向DAO传递一个"schema"参数，它以某种方式对我有用.

Basically is there a way of using inheritance on JPA that uses the same table structure with the difference being just the schema without copy and pasting too much code. Ideally I'd like to just pass a "schema" parameter to a DAO and it somehow does it for me.

推荐答案

最后，我们只需要一个根据情况路由的数据源.为此，使用扩展了AbstractRoutingDataSource的@Component和ThreadLocal来存储请求上下文.

In the end, we just need a data source that would route according to the situation. To do this a @Component that extends AbstractRoutingDataSource is used and a ThreadLocal to store the request context.

ThreadLocal应该是这样的(示例使用的是Lombok)

The ThreadLocal would be something like this (examples are using Lombok)

@AllArgsConstructor
public class UserContext {
    private static final ThreadLocal<UserContext> context =
        new ThreadLocal<>();
    private final String schema;

    public static String getSchema() {
        return context.get().schema;
    }

    public static void setFromXXX(...) {
        context.set(new UserContext(
            ...
        ));
    }
}

需要数据源:

@Configuration
public class DataSources {

    @Bean
    public DataSource schema1() {
        return build("schema1");
    }

    @Bean
    public DataSource schema2() {
        return build("schema2");
    }

    private DataSource buildDataSource(String schema) {
        ...
        return new DriverManagerDataSource(url, username, password);
    }
}

最后是标记为@Primary数据源的路由器，以确保它是供JPA使用的路由器.

And finally the router which is marked as the @Primary data source to make sure it is the one that gets used by JPA.

@Component
@Primary
public class RoutingDatasource extends AbstractRoutingDataSource {
    @Autowired
    @Qualifier("schema1")
    private DataSource schema1;

    @Autowired
    @Qualifier("schema2")
    private DataSource schema2;

    @Override
    public void afterPropertiesSet() {
        setTargetDataSources(
            Map.of(
                "schema1", schema1,
                "schema2", schema2
            )
        );
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return UserContext.getSchema();
    }
}

当所有不同的是模式甚至是数据源时，这避免了代码重复.

This avoids the code duplication when all that is different is a schema or even a data source.

这篇关于避免多模式数据库上的代码重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！