SpringBoot配置数据源

2020-05-30| Spring

前言

因为工作项目的新需求涉及到在SpringBoot中配置多个MySQL数据库，为了理解如何配置，学习了SpringBoot自动配置数据源的原理，在此记录。这篇文章先介绍SpringBoot配置单个数据源的原理，再介绍多个数据源的配置方法。

SpringBoot配置单个数据源

未指定spring.datasource.type时，SpringBoot的默认数据源

以下面application.yml为例：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

server.port: 18081

mybatis: 
  mapper-locations: classpath:mapper/*.xml

执行Junit Test看下SpringBoot使用的默认数据源是什么：

@RunWith(SpringRunner.class)
@SpringBootTest
public class MysqlDemoApplicationTests {
    @Autowired
    DataSource dataSource;
    
	@Test
	public void contextLoads() throws SQLException {
        System.out.println("dataSource: " + dataSource.getClass());
	}
}

在未指定spring.datasource.type的情况下，SpringBoot会使用默认数据源，在1.X版本中，默认数据源是org.apache.tomcat.jdbc.pool.DataSource；在2.X版本中，默认数据源是com.zaxxer.hikari.HikariDataSource。

以下源码版本都是SpringBoot 2.3.0

SpringBoot是怎么指定数据源的？道理在DataSourceConfiguration这个类，源码如下：


abstract class DataSourceConfiguration {

	@SuppressWarnings("unchecked")
	protected static <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) {
		return (T) properties.initializeDataSourceBuilder().type(type).build();
	}

	/**
	 * Tomcat Pool DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource",
			matchIfMissing = true)
	static class Tomcat {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.tomcat")
		org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) {
			org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(properties,
					org.apache.tomcat.jdbc.pool.DataSource.class);
			DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
			String validationQuery = databaseDriver.getValidationQuery();
			if (validationQuery != null) {
				dataSource.setTestOnBorrow(true);
				dataSource.setValidationQuery(validationQuery);
			}
			return dataSource;
		}

	}

	/**
	 * Hikari DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(HikariDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource",
			matchIfMissing = true)
	static class Hikari {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")
		HikariDataSource dataSource(DataSourceProperties properties) {
			HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class);
			if (StringUtils.hasText(properties.getName())) {
				dataSource.setPoolName(properties.getName());
			}
			return dataSource;
		}

	}

	/**
	 * DBCP DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource",
			matchIfMissing = true)
	static class Dbcp2 {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.dbcp2")
		org.apache.commons.dbcp2.BasicDataSource dataSource(DataSourceProperties properties) {
			return createDataSource(properties, org.apache.commons.dbcp2.BasicDataSource.class);
		}

	}

	/**
	 * Generic DataSource configuration.
	 */
	@Configuration(proxyBeanMethods = false)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type")
	static class Generic {

		@Bean
		DataSource dataSource(DataSourceProperties properties) {
			return properties.initializeDataSourceBuilder().build();
		}

	}

}

在前三个内部类Tomcat、Hikari、Dbcp2上的注解：

@Configuration(proxyBeanMethods = false)：会装配Bean的配置类
@ConditionalOnClass(XXX.class)：给定的XXX.class在系统中存在时，就实例化当前Bean
@ConditionalOnMissingBean(DataSource.class)：若IOC容器中还不存在DataSource这个Bean，就在IOC容器中实例化DataSource Bean
@ConditionalOnProperty(name = “spring.datasource.type”, havingValue = “XXX”,matchIfMissing = true)：配置中，是否有spring.datasource.type=XXX的配置。matchIfMissing = true表示，即使没有指定配置，该配置类也生效

@ConditionalOnProperty 注解中若没有配置 havingValue 属性，则处理方式为：若指定配置项值=false，则当前注解修饰的配置类不生效，否则生效。若没有配置 matchIfMissing 属性，则默认 matchIfMissing=true。

因此，以Tomcat类为例，这些注解表示，若系统存在org.apache.tomcat.jdbc.pool.DataSource.class，即使没有配置spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource，也是使用org.apache.tomcat.jdbc.pool.DataSource作为数据源。

当我们指定了spring.datasource.type时，就使最后一个类Generic生效，它会初始化spring.datasource.type配置项指定的数据源。

PS：为什么1.X和2.X两个版本默认的数据源不同？
因为在spring-boot-start-jdbc 1.X中，默认引入依赖tomcat-jdbc；而在spring-boot-start-jdbc 2.X中，默认引入依赖HikariCP

指定spring.datasource.type，使用Druid数据源

application.yml更新为：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

server.port: 18081

mybatis: 
  mapper-locations: classpath:mapper/*.xml

再执行上面的Junit Test，可以看到dataSource换成了com.alibaba.druid.pool.DruidDataSource。这个就是由上面的DataSourceConfiguration类下的Generic初始化的。

配置Druid数据库连接池参数并使其生效

application.yml更新为：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20 

server.port: 18081

mybatis: 
  mapper-locations: classpath:mapper/*.xml

但是，通过运行下面的Junit Test，我们可以看到，数据库连接池的属性如maxActive并没有生效。

@RunWith(SpringRunner.class)
@SpringBootTest
public class MysqlDemoApplicationTests {
	
	@Autowired
	DataSource dataSource;
    
	@Test
	public void contextLoads() throws SQLException {
		System.out.println("dataSource: " + dataSource.getClass());
		Connection connection = dataSource.getConnection();
		System.out.println("connection: " + connection);
		if(dataSource instanceof DruidDataSource) {
			DruidDataSource dDataSource = (DruidDataSource) dataSource;
			System.out.println("maxActive: " + dDataSource.getMaxActive());
			System.out.println("minIdle: " + dDataSource.getMinIdle());
		}
		connection.close();
	}

}

我们配置的是maxActive=20，minIdle=1，打印出来的是maxActive=8，minIdle=0。
什么原因呢？我们先看上面那个DataSourceConfiguration类下的Generic的方法；

@Bean
DataSource dataSource(DataSourceProperties properties) {
	return properties.initializeDataSourceBuilder().build();
}

查看DataSourceProperties类的源码，它用注解@ConfigurationProperties(prefix = "spring.datasource")把”spring.datasource”下的属性映射到自己的同名属性中了，但在这个类中，和我们配置项同名的只有url、username、password、driver-class-name、type这5个属性，并没有maxActive这样的数据库连接池属性。

@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {

	/**
	 * Fully qualified name of the connection pool implementation to use. By default, it
	 * is auto-detected from the classpath.
	 */
	private Class<? extends DataSource> type;

	/**
	 * Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
	 */
	private String driverClassName;

	/**
	 * JDBC URL of the database.
	 */
	private String url;

	/**
	 * Login username of the database.
	 */
	private String username;

	/**
	 * Login password of the database.
	 */
	private String password;

	/*省略其他属性*/

	/**
	 * Initialize a {@link DataSourceBuilder} with the state of this instance.
	 * @return a {@link DataSourceBuilder} initialized with the customizations defined on
	 * this instance
	 */
	public DataSourceBuilder<?> initializeDataSourceBuilder() {
		return DataSourceBuilder.create(getClassLoader()).type(getType()).driverClassName(determineDriverClassName())
				.url(determineUrl()).username(determineUsername()).password(determinePassword());
	}
	
	/*省略其他方法*/
}

@ConfigurationProperties(prefix=”spring.datasource”)注解表示把”spring.datasource”下的属性映射到实体类的同名属性上。它的用法有：标注在类声明上，然后用其他注解如 @Component 或 @Bean 注入这个 Bean 即可生效；另一个是不标注在类声明上，而是用 @Bean 注入这个类的 Bean 时，标注在 @Bean 修饰的方法上也可生效。

再查看DataSourceBuilder类build()方法源码，可以看到这里只是反射调用DruidDataSource()构造函数，建立DruidDataSource对象，没有给这个对象属性赋值。DruidDataSource中的maxActive属性是有默认值的，这个默认值就是我们打印出的值。

public T build() {
	Class<? extends DataSource> type = getType();
	DataSource result = BeanUtils.instantiateClass(type);
	maybeGetDriverClassName();
	bind(result);
	return (T) result;
}

那么怎样让我们的配置生效呢？SpringBoot的官方文档给了我们三种方法。
新建一个配置类：

@Configuration
public class DataSourceConfig {
	
//  第一种	
	@Bean
	@ConfigurationProperties(prefix="spring.datasource")
	public DataSource dataSource() {
	    return new DruidDataSource();
	}
	
//  第二种	
//	@Bean
//	@ConfigurationProperties("spring.datasource")
//	public DataSource dataSource() {
//	    return DataSourceBuilder.create().type(DruidDataSource.class).build();
//	}
	
//  第三种	
//	@Bean
//	@ConfigurationProperties(prefix = "spring.datasource")
//	public DataSource dataSource(
//			DataSourceProperties properties) {
//		return properties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
//	}	
	
}

三种方法都能使我们的配置生效。第二种和第三种方法的里面就是反射调用DruidDataSource()构造函数。因为@ConfigurationProperties注解的作用，我们的配置属性被映射到了DruidDataSource对象属性上，即使DruidDataSource是通过反射建立的也会生效。

关于第三种方法，若我们的url、username、password、driver-class-name、type五个配置和别的数据库连接池配置是分开的，例如：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    
druid: 
  filters: stat
  maxActive: 20
  initialSize: 1
  maxWait: 60000

第三种方法就应该这样写：

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

@Bean
@ConfigurationProperties("druid")
public DataSource dataSource(DataSourceProperties properties) {
    return properties.initializeDataSourceBuilder().type(DruidDataSource.class).build();
}

原因就是，”spring.datasource.*“属性只映射到DataSourceProperties对象，properties反射创建DruidDataSource对象后，”druid.*“属性才映射到DruidDataSource对象。

SpringBoot+Mybatis配置多个数据源

这里指的是配置多个Mysql数据源。
配置文件application.yml：

spring:
  datasource:
    db1: 
      url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      username: root
      password: 123456
    db2: 
      url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      username: root
      password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    filters: stat
    minIdle: 1
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000  
    minEvictableIdleTimeMillis: 3000000
    validationQuery: select 'x'  
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

理解了配置单个数据源的方法后，很容易写出多个数据源的配置类：

@Configuration
public class DataSourceConfig {
	
	@Bean
	@Primary
	@ConfigurationProperties("spring.datasource.db1")
	public DataSourceProperties ds1DataSourceProperties() {
	    return new DataSourceProperties();
	}

	@Bean
	@Primary
	@ConfigurationProperties("spring.datasource")
	public DataSource ds1DataSource() {
	    return ds1DataSourceProperties().initializeDataSourceBuilder().type(DruidDataSource.class).build();
	}

	@Bean
	@ConfigurationProperties("spring.datasource.db2")
	public DataSourceProperties ds2DataSourceProperties() {
	    return new DataSourceProperties();
	}

	@Bean
	@ConfigurationProperties("spring.datasource")
	public DataSource ds2DataSource() {
	    return ds2DataSourceProperties().initializeDataSourceBuilder().type(DruidDataSource.class).build();
	}
	
}

用Junit Test验证一下：

@RunWith(SpringRunner.class)
@SpringBootTest
public class MysqlMultipleDemoApplicationTests {
	@Test
	public void contextLoads() {
		Object ds1DataSource = ApplicationContextUtil.getBean("ds1DataSource");
		if(ds1DataSource instanceof DruidDataSource) {
			DruidDataSource ds = (DruidDataSource) ds1DataSource;
			System.out.println(ds.getUrl());
		}
		
		Object ds2DataSource = ApplicationContextUtil.getBean("ds2DataSource");
		if(ds2DataSource instanceof DruidDataSource) {
			DruidDataSource ds = (DruidDataSource) ds2DataSource;
			System.out.println(ds.getUrl());
		}
	}
}

打印的内容与我们的配置相对应。

如果是用JdbcTemplate，给不同的JdbcTemplate指定不同的数据源即可。但现在项目多是用Mybatis，有多个数据源存在时，需要把mapper.xml和mapper.java文件与数据源对应起来。
新建两个数据库的Mybatis配置类：
1.MybatisConfigDB1

@Configuration
// basePackages为要扫描的mapper.java所在包
@MapperScan(basePackages ="com.example.demo.mysql.multiple.dao.test", sqlSessionTemplateRef  = "ds1SqlSessionTemplate")
public class MybatisConfigDB1 {
    //主数据源 ds1数据源
    @Primary
    @Bean("ds1SqlSessionFactory")
    public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds1DataSource") DataSource dataSource) throws Exception {
    	SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                        getResources("classpath:mapper/test/*.xml"));// mapper.xml目录
        sqlSessionFactory.setTypeAliasesPackage("com.example.demo.mysql.multiple.domain.test");//实体类所在包，用于生成实体类别名
        return sqlSessionFactory.getObject();
    }

    @Primary
    @Bean(name = "ds1TransactionManager")
    public DataSourceTransactionManager ds1TransactionManager(@Qualifier("ds1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean(name = "ds1SqlSessionTemplate")
    public SqlSessionTemplate ds1SqlSessionTemplate(@Qualifier("ds1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2.MybatisConfigDB2

@Configuration
@MapperScan(basePackages ="com.example.demo.mysql.multiple.dao.test2", sqlSessionTemplateRef  = "ds2SqlSessionTemplate")
public class MybatisConfigDB2 {
    //ds2数据源
    @Bean("ds2SqlSessionFactory")
    public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds2DataSource") DataSource dataSource) throws Exception {
    	SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                        getResources("classpath:mapper/test2/*.xml"));  
        sqlSessionFactory.setTypeAliasesPackage("com.example.demo.mysql.multiple.domain.test2");
        return sqlSessionFactory.getObject();
    }

    @Bean(name = "ds2TransactionManager")
    public DataSourceTransactionManager ds1TransactionManager(@Qualifier("ds2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "ds2SqlSessionTemplate")
    public SqlSessionTemplate ds1SqlSessionTemplate(@Qualifier("ds2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

可以自己写个Junit Test验证，看每个数据源下的mapper.java和mapper.xml是否有效。