Spring Boot 入门 - 基础篇（11）- 数据源配置

（1）单一数据源

默认Spring Boot会在classpath中查找H2, HSQL, Derby等内存数据库的jar包自动配置一个内存数据库的DataSource。

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>

但如果在application.properties中设置了spring.datasource.*相关的信息，Spring Boot会自定使用该设置自动配置DataSource。

引用

spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

（2）多个数据源

数据源不局限于数据库：

• 多种数据库，比如：MySQL里的DB1、PostgreSQL的DB2、Oracle的DB3
• 一种数据库里的多个库，比如：MySQL里的DB1、DB2、DB3

引用

# MySQL database
spring.ds_mysql.url=jdbc:mysql://localhost:3306/rensanning
spring.ds_mysql.username=root
spring.ds_mysql.password=root
spring.ds_mysql.driverClassName=com.mysql.jdbc.Driver

# PostgreSQL database
spring.ds_pg.url=jdbc:postgresql://localhost:5432/rensanning
spring.ds_pg.username=postgres
spring.ds_pg.password=postgres
spring.ds_pg.driverClassName=org.postgresql.Driver

# Oracle database
spring.ds_oracle.url=jdbc:oracle:thin:@localhost:1521:rensanning
spring.ds_oracle.username=scott
spring.ds_oracle.password=tiger
spring.ds_oracle.driverClassName=oracle.jdbc.driver.OracleDriver

@Configuration
public class MultipleDBConfig {

	@Bean(name = "mysqlDB")
	@Primary
	@ConfigurationProperties(prefix = "spring.ds_mysql")
	public DataSource mysqlDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "postgresDB")
	@ConfigurationProperties(prefix = "spring.ds_pg")
	public DataSource postgresDataSource() {
		return  DataSourceBuilder.create().build();
	}

	@Bean(name = "oracleDB")
	@ConfigurationProperties(prefix = "ds_oracle")
	public DataSource oracleDataSource() {
		return  DataSourceBuilder.create().build();
	}

}

使用@Autowired注入时会首先使用被标记为@Primary的Bean。

@Autowired
private DataSource mysqlDataSource;

@Autowired @Qualifier("postgresDB") 
private DataSource postgresDataSource;

@Autowired @Qualifier("oracleDB")
private DataSource oracleDataSource;

（3）动态数据源
Spring提供org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource来支持DataSource路由配置，具体可以参考官网Blog：https://spring.io/blog/2007/01/23/dynamic-datasource-routing/

a - 定义配置

public enum SchemaType { 
    DEFAULT, MASTER, SLAVE
}

@Component
@Configuration
public class DatasourceConfig {

    public DataSource defaultDatasource(){
        DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/rensanning01");
        ds.setUsername("user01");
        ds.setPassword("pass01");
        return ds;
    }

    public DataSource masterDatasource(){
        DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/rensanning02");
        ds.setUsername("user02");
        ds.setPassword("pass02");
        return ds;
    }

    public DataSource slaveDatasource(){
        DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/rensanning03");
        ds.setUsername("user03");
        ds.setPassword("pass03");
        return ds;
    }

    @Bean
    public DynamicRoutingDataSourceResolver dataSource() {
        DynamicRoutingDataSourceResolver resolver = new DynamicRoutingDataSourceResolver();

        Map<Object, Object> dataSources = new HashMap<Object,Object>();
        dataSources.put("default", defaultDatasource());
        dataSources.put("master", masterDatasource());
        dataSources.put("slave", slaveDatasource());

        resolver.setTargetDataSources(dataSources);

        // default datasource
        resolver.setDefaultTargetDataSource(masterDatasource());

        return resolver;
    }
}

public class DynamicRoutingDataSourceResolver extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        if (SchemaContextHolder.getSchemaType() == null) {
            return "default";
        }

        if(SchemaContextHolder.getSchemaType() == SchemaType.MASTER) {
            return "master";
        }

        if(SchemaContextHolder.getSchemaType() == SchemaType.SLAVE) {
            return "slave";
        }

        return "default";
    }

}

public class SchemaContextHolder {
    private static ThreadLocal<SchemaType> contextHolder = new ThreadLocal<SchemaType>();

    public static void setSchemaType(SchemaType datasourcename) {
        contextHolder.set(datasourcename);
    }

    public static SchemaType getSchemaType() {
        return contextHolder.get();
    }

    public static void clear() {
        contextHolder.remove();
    }
}

b - 切换数据源

需要切换数据源的时候SchemaContextHolder.setSchemaType(SchemaType.MASTER);完成后调用clear()即可。比如：

    @GetMapping("findall")
    public List<User> findall(@RequestParam("ds") String ds) {
        if(ds.equals("master")) {
            SchemaContextHolder.setSchemaType(SchemaType.MASTER);
        } else {
            SchemaContextHolder.setSchemaType(SchemaType.SLAVE);
        }
        return userService.findAll();
    }

通过Interceptor实现切换数据源。

public class DatasourceInterceptor extends HandlerInterceptorAdapter {

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        if(!StringUtils.isEmpty(request.getSession().getAttribute("datasource"))){
            SchemaContextHolder.setSchemaType((SchemaType)request.getSession().getAttribute("datasource"));
        }
        return true;
    }

}

@Component
@Configuration
public class DatasourceConfig {
    // ...
    @Bean
    public HandlerInterceptor datasourceInterceptor(){
        return new DatasourceInterceptor();
    }

    @Bean
    public MappedInterceptor interceptorMapping(){
        return new MappedInterceptor(new String[]{"/**"}, datasourceInterceptor());
    }
    // ...
}

通过修改Session个中的值即可切换数据库： request.getSession().setAttribute("datasource", type);

（4）基于package指定数据源
上边已经可以获取到多个Datasource，那么就可以通过DataSource生成不同的SqlSessionFactoryBean，通过@MapperScan为不同的package指定不同的SqlSessionFactoryBean。

https://github.com/mybatis/spring-boot-starter/issues/78

@MapperScan(basePackages = "com.rensanning.springboot.mappers.cmn", sqlSessionFactoryRef = CmnDatasourceConfig.SQL_SESSION_FACTORY_NAME)

@MapperScan(basePackages = "com.rensanning.springboot.mappers.user", sqlSessionFactoryRef = UsrDatasourceConfig.SQL_SESSION_FACTORY_NAME)

（5）通过数据库管理数据源
上边场景基本都是所有数据库信息都已知，可在代码(或application.properties)中固定配置!还有一种很常见的场景是用户登录时使用一个数据源，但是登录后的数据源需要通过从DB中取得，需要能实时更新DataSource路由。可以通过自己实现AbstractDataSource来自己对数据源的创建和获取等管理操作。
具体参考附件代码：点击下载

参考：
http://qiita.com/syukai/items/0d4bf27f82fef9965cdd
http://qiita.com/kazuki43zoo/items/9d8aec0ecab117a4d5c1