5.26. Spring boot with Datasource

5.26. Spring boot with Datasource
上一頁	第 5 章 Spring Boot	下一頁

數據源配置

5.26.1. Master / Slave 主從資料庫數據源配置

5.26.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.26.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.26.1.3. 選擇數據源

			
	// 預設是 Master
	@Autowired
	private JdbcTemplate jdbcTemplate;

	// 或者這樣寫
	@Qualifier("masterJdbcTemplate")
	@Autowired
	private JdbcTemplate masterJdbcTemplate;
	
	// 下面是 Slave 數據源
	@Qualifier("slaveJdbcTemplate")
	@Autowired
	private JdbcTemplate slaveJdbcTemplate;

5.26.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.26.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.26.4. Connection and Statement Pooling

注意：下面的實例僅限 Spring boot 2.0.2.RELEASE

5.26.4.1. org.apache.tomcat.jdbc.pool.DataSource

預設連接池，可以忽略配置

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

5.26.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.26.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.26.4.4. dbcp2

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

5.26.4.5. bonecp

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

5.26.4.6. dbcp2

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

上一頁	上一級	下一頁
5.25. Spring boot with PostgreSQL	起始頁	5.27. Spring boot with Elasticsearch