英文:
Set schema name at run time using REST API input in spring boot based application
问题
在 **postgres** 数据库中，**模式名称** 被设定为 **买家 ID**（因此，如果买家 ID 是 *buyer_2213*，那么模式名称将是 *buyer_2213*）。这些模式拥有多个表，而且这些表在所有模式中都有共同的结构。
现在，我正在使用 Spring Boot 编写 REST API，以从这些模式中获取买家数据，但由于 *模式名称* 取决于 *买家 ID*，我不得不编写手动查询，无法使用 JPA 的功能。
是否有任何方法可以使用 REST API 请求参数来设置实体的 **模式名称**。因此，在下面的实体中，是否可以通过在 BuyerController 中定义的 API 调用时使用传递的 *buyerId* 来设置 *schema*：
```java
@Entity
@Table(name="buyer_table", schema="使用_API输入设置")
public class BuyerTable{
...
}
@RestController
@RequestMapping("/buyer")
public class BuyerController{
@GetMapping("/{buyerId}")
public void getBuyerData(@PathVariable(required = true) String buyerId){
...
}
}
此外，买家 ID 与已登录用户不同（将此情况视为正在尝试获取买家详细信息的管理员用户），并且仅将其作为 API 请求参数（或任何其他方式作为 API 输入）提供。因此，我无法找到与 此链接 的相关性。
<details>
<summary>英文:</summary>
In a **postgres** database, the **schema name** is set as the **buyer id**(so, if buyer id is *buyer_2213* then schema name will be *buyer_2213*). These schemas have multiple tables and these tables have common structure for all schemas.
Now, I am writing REST API using spring boot to get buyer data from these schema, but since *schema name* is *buyer id* dependent, I have to write manual queries and could not use JPA features for that.
Is there any way to set **schema name** to entity using REST API request parameters. So, in below entity, can we set *schema* using *buyerId* passed at API call defined in BuyerController:
@Entity
@Table(name="buyer_table", schema="set_it_using_API_input")
public class BuyerTable{
...
}
@RestController
@RequestMapping("/buyer")
public class BuyerController{
@GetMapping("/{buyerId}")
public void getBuyerData(@PathVariable(required = true) String buyerId){
...
}
}
Also, the buyer Id is not same as logged in user(consider this situation as admin user who is trying to get buyer's details) and will be provided as API request param only(or any other way as API input). Hence I could not find relevance to [this][1]
[1]: https://stackoverflow.com/questions/39357367/change-database-schema-during-runtime-based-on-logged-in-user
</details>
# 答案1
**得分**: 1
我终于找到了一个可行的解决方案。此解决方案主要使用[这里][1]的配置，但更符合我的问题要求。<br/><br/>
显然的想法是使用[AbstractDataSource][2]，数据源配置与[这里][1]显示的几乎相同，只需使用可以从API逻辑内部调用的 *setter* 来设置模式名称。<br/>
首先，我们需要编写一个**AbstractDataSource**的实现，它将几乎看起来像这样：
```java
public class BuyerSchemaDataSource extends AbstractDataSource {
private LoadingCache<String, DataSource> dataSources = createCache();
public void setSchemaName(String schemaName){
this.schemaName = schemaName;
}
@Override public Connection getConnection() throws SQLException {
try {
return determineTargetDataSource().getConnection();
} catch (ExecutionException e) {
//打印异常
return null;
}
}
@Override public Connection getConnection(String username, String password)
throws SQLException {
try {
return determineTargetDataSource().getConnection(username,password);
} catch (ExecutionException e) {
//打印异常
return null;
}
}
private DataSource determineTargetDataSource() throws ExecutionException {
if(!utils.isNullOrEmpty(schemaName)){
return dataSources.get(schemaName);
}
return buildDataSourceFromSchema(null);
}
private LoadingCache<String, DataSource> createCache(){
return CacheBuilder.newBuilder()
.maximumSize(100)
.expireAfterWrite(10, TimeUnit.MINUTES)
.build(new CacheLoader<String, DataSource>() {
@Override public DataSource load(String key) throws Exception {
return buildDataSourceFromSchema(key);
}
});
}
private DataSource buildDataSourceForSchema(String schema) {
// 例如属性的示例: "jdbc:postgresql://localhost:5432/mydatabase?currentSchema="
String url = env.getRequiredProperty("spring.datasource.url") + schema;
return DataSourceBuilder.create()
.driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
[...]
.url(url)
.build();
}
}
<br/>
现在，就像任何其他数据源一样，这可以在Spring配置文件中使用，如下所示：<br/>
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "schemaSpecificEntityManagerFactory",
transactionManagerRef = "schemaSpecificTransactionManager")
public class SchemaSpecificConfig {
@Bean(name = "schemaSpecificDataSource")
public DataSource schemaSpecificDataSource(){
return new BuyerSchemaDataSource();
}
@Bean(name = "schemaSpecificEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean schemaSpecificEntityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("schemaSpecificDataSource") DataSource dataSource) {
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
return builder.dataSource(dataSource).properties(properties)
.persistenceUnit("SchemaSpecific").build();
}
@Bean(name = "schemaSpecificTransactionManager")
public PlatformTransactionManager schemaSpecificTransactionManager(
@Qualifier("schemaSpecificEntityManagerFactory") EntityManagerFactory schemaSpecificEntityManagerFactory) {
return new JpaTransactionManager(schemaSpecificEntityManagerFactory);
}
}
<br/>
现在，在控制器的API逻辑内部可以调用BuyerSchemaDataSource中定义的setSchema()方法。<br/><br/>
这看起来像是一个解决方法不太好的问题，但我没有找到比这更好的方法，欢迎所有建议/编辑。
英文:
I've finally found a working solution for this. This solution uses configurations mostly from here but is more specific to the my question requirements. <br/><br/>
The idea is obviously to use AbstractDataSource and data source configurations are pretty much similar to what shown in here with just the schema name will be set using a setter which can be called from inside API logic.<br/>
First, we need to write an implementation of AbstractDataSource which will pretty much look like this:
public class BuyerSchemaDataSource extends AbstractDataSource {
private LoadingCache<String, DataSource> dataSources = createCache();
public void setSchemaName(String schemaName){
this.schemaName = schemaName;
}
@Override public Connection getConnection() throws SQLException {
try {
return determineTargetDataSource().getConnection();
} catch (ExecutionException e) {
//print exception
return null;
}
}
@Override public Connection getConnection(String username, String password)
throws SQLException {
try {
return determineTargetDataSource().getConnection(username,password);
} catch (ExecutionException e) {
//print exception
return null;
}
}
private DataSource determineTargetDataSource() throws ExecutionException {
if(!utils.isNullOrEmpty(schemaName)){
return dataSources.get(schemaName);
}
return buildDataSourceFromSchema(null);
}
private LoadingCache<String, DataSource> createCache(){
return CacheBuilder.newBuilder()
.maximumSize(100)
.expireAfterWrite(10, TimeUnit.MINUTES)
.build(new CacheLoader<String, DataSource>() {
@Override public DataSource load(String key) throws Exception {
return buildDataSourceFromSchema(key);
}
});
}
private DataSource buildDataSourceForSchema(String schema) {
// e.g. of property: "jdbc:postgresql://localhost:5432/mydatabase?currentSchema="
String url = env.getRequiredProperty("spring.datasource.url") + schema;
return DataSourceBuilder.create()
.driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
[...]
.url(url)
.build();
}
}
<br/>
Now, just like any other data source this can be used in a spring configuration file like this:<br/>
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "schemaSpecificEntityManagerFactory",
transactionManagerRef = "schemaSpecificTransactionManager")
public class SchemaSpecificConfig {
@Bean(name = "schemaSpecificDataSource")
public DataSource schemaSpecificDataSource(){
return new BuyerSchemaDataSource();
}
@Bean(name = "schemaSpecificEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean schemaSpecificEntityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("schemaSpecificDataSource") DataSource dataSource) {
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
return builder.dataSource(dataSource).properties(properties)
.persistenceUnit("SchemaSpecific").build();
}
@Bean(name = "schemaSpecificTransactionManager")
public PlatformTransactionManager schemaSpecificTransactionManager(
@Qualifier("schemaSpecificEntityManagerFactory") EntityManagerFactory schemaSpecificEntityManagerFactory) {
return new JpaTransactionManager(schemaSpecificEntityManagerFactory);
}
}
<br/>
Now, the setSchema() method defined in BuyerSchemaDataSource can be called from inside API logic in controller.<br/><br/>
This looks like a bad way to solve it but I have not find anything better than this and all suggestions/edits are appreciated.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论