SpringBoot + Durid | Hikari 多数据源 JDBCTemplate

发表于 | 更新于 | 分类于 | 阅读次数：

SpringBoot + Durid 多数据源 JDBCTemplate
SpringBoot + Hikari 多数据源 JDBCTemplate

SpringBoot 配置多数据源(Druid | Hikari)

注释:

Druid：阿里系数据连接池
Hikari：SpringBoot 2.0开始推HikariCP，将默认的数据库连接池tomcat jdbc pool改为了hikari

SpringBoot + Druid 配置

Druid-Jdbctemplate 连接配置

jdbctemplate连接

  1. 增加pom依赖 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    <!-- 本地连接oracle的文件 -->
    <dependency>
    	<groupId>com.oracle.ojdbc</groupId>
    	<artifactId>ojdbc8</artifactId>
    	<version>${ojdbc.version}</version>
    </dependency>

    <!-- 数据库连接池 -->
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
    <dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid-spring-boot-starter</artifactId>
    	<version>${druid.version}</version>
    </dependency>

    <!-- jdbc -->
    <dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

  2. 设置application.yml文件配置 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    spring:
    	autoconfigure:
    		## 多数据源下必须排除掉 DataSourceAutoConfiguration,否则会导致循环依赖报错
    		exclude:
    		- org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
    	datasource:
    		type: com.alibaba.druid.pool.DruidDataSource
    		druid:
    			## 以`spring.datasources`和`spring.datasource.druid`开头的属性会作为公共配置,注入到每一个数据源
    			initial-size: 5
    			min-idle: 5 
    			max-active: 20
    			stat-view-servlet:
    				login-username: admin
    				login-password: admin
    			max-wait: 60000
    			time-between-eviction-runs-millis: 60000 ## 配置间隔多久才进行一次检测,检测需要关闭的空闲连接.单位是毫秒
    			min-evictable-idle-time-millis: 300000 ## 配置一个连接池中最小生存的时间,单位是毫秒      
    			## 配置监控统计拦截的filters,去掉后监控界面SQL无法进行统计,`wall`用于防火墙(https://blog.csdn.net/garyond/article/details/80189939)
    			filters: config,stat,wall,log4j
    			web-stat-filter:
    				exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
    			## 多数据源的标识,若该属性存在则为多数据源环境,不存在则为但数据源环境
    			data-sources: 
    				primary:
    					url: jdbc:oracle:thin:@//xx:1521/xx
    					username: xx
    					password: xx
    					driverClassName: oracle.jdbc.driver.OracleDriver
    				secondary:
    					url: jdbc:oracle:thin:@//xx:1521/xx
    					username: xx
    					password: xx
    					driverClassName: oracle.jdbc.driver.OracleDriver

  3. Druid 连接池配置类 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    import javax.sql.DataSource;

    import org.springframework.beans.factory.annotation.Qualifier;
    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;

    import com.alibaba.druid.pool.DruidDataSource;

    /**
    * 
    * @ClassName: DruidDataScouConfig
    * @Description: 多数据源,集成Druid
    * @author time
    * @date 2018/10/29
    */
    @Configuration
    public class DruidDataScouConfig {

    	@Primary//必需注解，缺少该注解将启动异常.可自定义某个数据源为主数据源
    	@Bean(name = "primaryDataSource")
    	@Qualifier(value = "primaryDataSource")
    	@ConfigurationProperties(prefix = "spring.datasource.druid.data-sources.primary")
    	public DataSource primaryDataSource() {
    		return new DruidDataSource();
    	}

    	@Bean(name = "secondaryDataSource")
    	@Qualifier(value = "secondaryDataSource")
    	@ConfigurationProperties(prefix = "spring.datasource.druid.data-sources.secondary")
    	public DataSource secondaryDataSource() {
    		return new DruidDataSource();
    	}

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

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

  4. 测试数据源是否可用 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    package com.sanss.config;

    import java.util.List;
    import java.util.Map;

    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.test.context.junit4.SpringRunner;

    import com.alibaba.fastjson.JSON;

    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class DruidDataSourceTest {

    	@Autowired
    	// @Qualifier("primaryJdbcTemplate")
    	private JdbcTemplate primaryJdbcTemplate;

    	@Autowired
    	// @Qualifier("secondaryJdbcTemplate")//注解可省略
    	private JdbcTemplate secondaryJdbcTemplate;

    	@Test
    	public void TestPrimaryDataSourceConnect() {
    		System.err.println("primary data source connection start:");
    		String sql = "select 1 from dual";
    		List<Map<String, Object>> result = primaryJdbcTemplate.queryForList(sql);
    		System.out.println(JSON.toJSONString(result));
    	}

    	@Test
    	public void TestSecondaryDataSourceConnect() {
    		System.err.println("secondary data source connection start:");
    		String sql = "select 1 from dual";
    		List<Map<String, Object>> result = secondaryJdbcTemplate.queryForList(sql);
    		System.out.println(JSON.toJSONString(result));
    	}

    }

Hikari-jdbctemplate 连接配置

  1. 添加pom.xml依赖 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <!-- 本地连接oracle的文件 -->
    <dependency>
    	<groupId>com.oracle.ojdbc</groupId>
    	<artifactId>ojdbc8</artifactId>
    	<version>${ojdbc.version}</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
    <dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

  2. 设置application.yml多环境配置文件 

1
2
3
4
5
6
7
8
9
10
11
12
spring:
  datasource:
    primary: 
      jdbc-url: jdbc:oracle:thin:@//xx:1521/xx
      username: xx
      password: xx
      driver-class-name: oracle.jdbc.driver.OracleDriver
    secondary: 
      jdbc-url: jdbc:oracle:thin:@//xx:1521/xx
      username: xx
      password: xx
      driver-class-name: oracle.jdbc.driver.OracleDriver
  1. Hikari 数据源配置 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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 com.zaxxer.hikari.HikariDataSource;

/**
 * 
 * @ClassName: HikariDataSourceConfig
 * @Description: Hikari 多数据源配置
 * @author time
 * @date 2018/10/29
 */
@Configuration
public class HikariDataSourceConfig {

	@Primary
	@Bean(name = "primaryDataSource")
	@Qualifier(value = "primaryDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.primary")
	public DataSource primaryDataSource() {
		return DataSourceBuilder.create().type(HikariDataSource.class).build();
	}

	@Bean(name = "secondaryDataSource")
	@Qualifier(value = "secondaryDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.secondary")
	public DataSource secondaryDataSource() {
		return DataSourceBuilder.create().type(HikariDataSource.class).build();
	}

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

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

}
  1. 测试数据源是否可用 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

import com.alibaba.fastjson.JSON;

@RunWith(SpringRunner.class)
@SpringBootTest
public class HikariDataSourceTest {

	@Autowired
	private JdbcTemplate primaryJdbcTemplate;

	@Autowired
	private JdbcTemplate secondaryJdbcTemplate;

	@Test
	public void TestPrimaryDataSourceConnect() {
		System.err.println("primary data source connection start:");
		String sql = "select 1 from dual";
		List<Map<String, Object>> result = primaryJdbcTemplate.queryForList(sql);
		System.out.println("primary data source :\t"+JSON.toJSONString(result));
	}

	@Test
	public void TestSecondaryDataSourceConnect() {
		System.err.println("secondary data source connection start:");
		String sql = "select 1 from dual";
		List<Map<String, Object>> result = secondaryJdbcTemplate.queryForList(sql);
		System.out.println("secondary data source :\t"+JSON.toJSONString(result));
	}

}

配置 NamedParameterJdbcTemplate

  1. pom.xml依赖、applicaiton.yml数据源配置文件内容与jdbctemplate配置一致

  2. 需要修改配置类返回的实例为NamedParameterJdbcTemplate

1
2
3
4
5
6
7
8
9
10
11
12
@Bean(name = "secondaryDataSource")
@Qualifier(value = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
	return DataSourceBuilder.create().type(HikariDataSource.class).build();
}

@Bean(name = "secondaryJdbcTemplate")
public NamedParameterJdbcTemplate secondaryJdbcTemplate(
		@Qualifier("secondaryDataSource") DataSource dataSource) {
	return new NamedParameterJdbcTemplate(dataSource);
}
  1. 测试类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@RunWith(SpringRunner.class)
@SpringBootTest
public class HikariDataSourceConfigTest {

	@Autowired
	private NamedParameterJdbcTemplate secondaryJdbcTemplate;

	@Test
	public void TestNamedParameterJdbcTemplate() {
		System.err.println("namedParameterJdbcTemplate data source connection start:");
		String sql = "select 1 from dual";

		int result = secondaryJdbcTemplate.queryForObject(sql, new HashMap<>(), Integer.class);
		System.out.println("TestNamedParameterJdbcTemplate data source:\t" + result);
	}
}