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.

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)

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"
)

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

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

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.

Solution

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.

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(
            ...
        ));
    }
}

A source for the data sources would be needed:

@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);
    }
}

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.