如何在Spring Boot Jdbc中为MySQL数据库指定默认模式

We are connecting to multiple databases from Spring boot.

我们从Spring启动连接到多个数据库。

application.properties:

datasource.target.url=jdbc:mysql://localhost:3306/db_test_1?useSSL=false
datasource.target.driver-class-name=com.mysql.jdbc.Driver

datasource.origin.url=jdbc:mysql://localhost:3306/db_test_2?useSSL=false
datasource.origin.driver-class-name=com.mysql.jdbc.Driver

Database Config:

@Configuration
public class DatabaseConfig {

    @Bean(name = "originJdbcTemplate")
    public JdbcTemplate originJdbcTemplate() {
        return new JdbcTemplate(originDataSource());
    }

    @Bean(name = "targetJdbcTemplate")
    public JdbcTemplate targetJdbcTemplate() {
        return new JdbcTemplate(targetDataSource());
    }

    @Bean
    @Primary
    @ConfigurationProperties("datasource.origin")
    public DataSourceProperties originDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("datasource.origin")
    public DataSource originDataSource() {
        return originDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    @ConfigurationProperties("datasource.target")
    public DataSourceProperties targetDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("datasource.target")
    public DataSource targetDataSource() {
        return targetDataSourceProperties().initializeDataSourceBuilder().build();
    }

}

We are using Spring Jdbc template and we have to explicitly name the schema in each query even though we have defined the schema name in url above in application properties. e.g: The query without schema name doesn't work

我们正在使用Spring Jdbc模板,我们必须在每个查询中明确命名模式,即使我们已在应用程序属性中的url中定义了模式名称。例如:没有模式名称的查询不起作用

select * from db_test_1.user //works with schema name
select * from user //doesn't work

As per this SO Answer I should use Connection.setCatalog(), how do I do it in Spring configuration?

根据这个SO答案,我应该使用Connection.setCatalog(),我如何在Spring配置中做到这一点?

1 个解决方案

#1

By using Delegating Datasource

通过使用委派数据源

class DefaultSchemaDelegatingDS extends DelegatingDataSource {
    private final String catalogName;

    public DefaultSchemaDelegatingDS(final String catalogName, final DataSource dataSource) {
        super(dataSource);
        this.catalogName = catalogName;
    }

    @Override
    public Connection getConnection() throws SQLException {
        final Connection connection = super.getConnection();
        connection.setCatalog(this.catalogName);
        return connection;
    }
}

and then in the DatabaseConfig

然后在DatabaseConfig中

@Configuration
public class DatabaseConfig {

    @Value("${datasource.target.default_schema}")
    private String defaultTargetSchema;

    @Value("${datasource.origin.default_schema}")
    private String defaultOriginSchema;

    @Bean(name = "originJdbcTemplate")
    public JdbcTemplate originJdbcTemplate() {
        return new JdbcTemplate(originDataSource());
    }

    @Bean(name = "targetJdbcTemplate")
    public JdbcTemplate targetJdbcTemplate() {
        return new JdbcTemplate(targetDataSource());
    }

    @Bean
    @Primary
    @ConfigurationProperties("datasource.origin")
    public DataSourceProperties originDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("datasource.origin")
    public DataSource originDataSource() {
        final DataSource ds = originDataSourceProperties().initializeDataSourceBuilder().build();
        return new DefaultSchemaDelegatingDS(defaultOriginSchema, ds);
    }

    @Bean
    @ConfigurationProperties("datasource.target")
    public DataSourceProperties targetDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("datasource.target")
    public DataSource targetDataSource() {
        final DataSource ds = targetDataSourceProperties().initializeDataSourceBuilder().build();
        return new DefaultSchemaDelegatingDS(defaultTargetSchema, ds);
    }
}

The other way without using the DelegatingDatasource was to simply add

不使用DelegatingDatasource的另一种方法是简单地添加

jdbcTemplate.getDataSource().getConnection().setCatalog(catalogName);

e.g.,

@Bean(name = "originJdbcTemplate")
public JdbcTemplate originJdbcTemplate() throws SQLException {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(originDataSource());
    jdbcTemplate.getDataSource().getConnection().setCatalog(defaultOriginSchema);
    return jdbcTemplate;
}

#1