Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 视频教程 | 打赏(Donations) | About
知乎专栏多维度架构

5.19. 数据源配置

5.19.1. Master / Slave 主从数据库数据源配置

5.19.1.1. application.properties

			
spring.datasource.master.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.master.url=jdbc:mysql://192.168.1.240:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.master.username = root
spring.datasource.master.password = password

spring.datasource.slave.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.slave.url=jdbc:mysql://192.168.1.250:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.slave.username = root
spring.datasource.slave.password = password

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
			
			

5.19.1.2. 配置主从数据源

			
package cn.netkiller.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class MultiDataSourceConfig {

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

	@Bean("Master")
	@Primary
	@ConfigurationProperties("spring.datasource.master")
	public DataSource masterDataSource() {
		return masterDataSourceProperties().initializeDataSourceBuilder().build();
	}

	@Bean("masterJdbcTemplate")
	@Primary
	public JdbcTemplate masterJdbcTemplate(@Qualifier("Master") DataSource Master) {
		return new JdbcTemplate(Master);
	}

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

	@Bean(name = "Slave")
	@ConfigurationProperties("spring.datasource.slave")
	public DataSource slaveDataSource() {
		return slaveDataSourceProperties().initializeDataSourceBuilder().build();
	}

	@Bean("slaveJdbcTemplate")
	public JdbcTemplate slaveJdbcTemplate(@Qualifier("Slave") DataSource Master) {
		return new JdbcTemplate(Master);
	}

}
			
			

5.19.1.3. 选择数据源

			
	// 默认是 Master
	@Autowired
	private JdbcTemplate jdbcTemplate;

	// 或者这样写
	@Qualifier("masterJdbcTemplate")
	@Autowired
	private JdbcTemplate masterJdbcTemplate;
	
	// 下面是 Slave 数据源
	@Qualifier("slaveJdbcTemplate")
	@Autowired
	private JdbcTemplate slaveJdbcTemplate;
			
			

5.19.2. 多数据源配置

		
package cn.netkiller.project.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class DataSourceConfig {

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

	@Bean
	@Primary
	@ConfigurationProperties("spring.datasource")
	public DataSource defaultDataSource() {
		return defaultDataSourceProperties().initializeDataSourceBuilder().build();
	}

	@Bean("JdbcTemplate")
	@Primary
	public JdbcTemplate defaultJdbcTemplate(@Qualifier("defaultDataSource") DataSource Master) {
		return new JdbcTemplate(Master);
	}

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

	@Bean("Master")
	// @Primary
	@ConfigurationProperties("spring.datasource.master")
	public DataSource masterDataSource() {
		return masterDataSourceProperties().initializeDataSourceBuilder().build();
	}

	@Bean("masterJdbcTemplate")
	// @Primary
	public JdbcTemplate masterJdbcTemplate(@Qualifier("Master") DataSource Master) {
		return new JdbcTemplate(Master);
	}

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

	@Bean(name = "Slave")
	@ConfigurationProperties("spring.datasource.slave")
	public DataSource slaveDataSource() {
		return slaveDataSourceProperties().initializeDataSourceBuilder().build();
	}

	@Bean("slaveJdbcTemplate")
	public JdbcTemplate slaveJdbcTemplate(@Qualifier("Slave") DataSource Master) {
		return new JdbcTemplate(Master);
	}
	
	@Bean(name = "wwwDataSource")
	@Qualifier("wwwDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.www")
	public DataSource wwwDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean(name = "apiDataSource")
	@Qualifier("apiDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.api")
	public DataSource apiDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "cmsDataSource")
	@Qualifier("cmsDataSource")
	//@Primary
	@ConfigurationProperties(prefix = "spring.datasource.cms")
	public DataSource cmsDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean 
	PlatformTransactionManager transactionManager() {
		return new DataSourceTransactionManager(apiDataSource());
	}

	@Bean(name = "wwwJdbcTemplate")
	public JdbcTemplate wwwJdbcTemplate(@Qualifier("wwwDataSource") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}	
	
	@Bean(name = "appJdbcTemplate")
	public JdbcTemplate appJdbcTemplate(@Qualifier("apiDataSource") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}

	@Bean(name = "cmsJdbcTemplate")
	public JdbcTemplate cmsJdbcTemplate(@Qualifier("cmsDataSource") DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
}
		
		

对应 application.properties 的配置方法

		
spring.datasource.www.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.www.url=jdbc:mysql://localhost:3306/www?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.www.username=www
spring.datasource.www.password=passw0rd
spring.datasource.www.max-idle=10
spring.datasource.www.max-wait=10000
spring.datasource.www.min-idle=5
spring.datasource.www.initial-size=5
spring.datasource.www.validation-query=SELECT 1
spring.datasource.www.test-on-borrow=false
spring.datasource.www.test-while-idle=true
spring.datasource.www.time-between-eviction-runs-millis=18800
spring.datasource.www.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)

spring.datasource.api.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.api.url=jdbc:mysql://localhost:3306/api?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
spring.datasource.api.username=api
spring.datasource.api.password=passw0rd
spring.datasource.api.max-idle=10
spring.datasource.api.max-wait=10000
spring.datasource.api.min-idle=5
spring.datasource.api.initial-size=5
spring.datasource.api.validation-query=SELECT 1
spring.datasource.api.test-on-borrow=false
spring.datasource.api.test-while-idle=true
spring.datasource.api.time-between-eviction-runs-millis=18800
spring.datasource.api.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)		
		
		

选择数据库

		
	@Autowired
	@Qualifier("apiJdbcTemplate")
	JdbcTemplate jdbcTempalte;		
		
		

5.19.3. JPA 多数据源

多个 JPA 数据配置非常简单,请参考下面的例子。注意两点,第一点是设置Repository的位置:

		
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "cn.netkiller.repository.primary" }) //设置Repository所在位置
		
		

第二点是设置 Domain 位置,与 Repository 成套出现

		
	public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                .packages("cn.netkiller.domain.primary") //设置实体类所在包
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }		
		
		

首先配置第一组数据源。

		
package cn.netkiller.project.config;
		
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "cn.netkiller.repository.primary" }) //设置Repository所在位置
public class PrimaryConfig {

    @Autowired @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                .packages("cn.netkiller.domain.primary") //设置实体类所在包
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }

}		
		
		

设置第二组数据源

		
package cn.netkiller.project.config;
		
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "cn.netkiller.repository.secondary" }) //设置Repository所在位置
public class SecondaryConfig {

    @Autowired @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("cn.netkiller.repository.domain.secondary") //设置Domain实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }

}
		
		

5.19.4. Connection and Statement Pooling

注意:下面的实例仅限 Spring boot 2.0.2.RELEASE

5.19.4.1. org.apache.tomcat.jdbc.pool.DataSource

默认连接池,可以忽略配置

				spring.datasource.type = org.apache.tomcat.jdbc.pool.DataSource
			

5.19.4.2. druid

pom.xml

			
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.24</version>
		</dependency>
			
			

application.properties

			
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000 
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false 
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#spring.datasource.useGlobalDataSourceStat=true		

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.6.1:3306/test
spring.datasource.username=inf
spring.datasource.password=inf
spring.jpa.database=MYSQL
			
			

5.19.4.3. c3p0 - JDBC3 Connection and Statement Pooling

pom.xml

			
		<dependency>
		    <groupId>org.hibernate</groupId>
		    <artifactId>hibernate-c3p0</artifactId>
		    <version>4.3.6.Final</version>
		</dependency>
		<dependency>
		    <groupId>c3p0</groupId>
		    <artifactId>c3p0</artifactId>
		    <version>0.9.1.2</version>
		</dependency>
			
			

application.properties

			
spring.datasource.type=com.mchange.v2.c3p0.ComboPooledDataSource
			
			

5.19.4.4. dbcp2

			
spring.datasource.type = org.apache.commons.dbcp2.BasicDataSource			
			
			

5.19.4.5. bonecp

			
spring.datasource.type = com.jolbox.bonecp.BoneCPDataSource		
			
			

5.19.4.6. dbcp2

			
spring.datasource.type = org.apache.commons.dbcp2.BasicDataSource