一、前言

JdbcTemplate配置Druid多数据源的核心在于创建JdbcTemplate时候为其分配不同的数据源,然后在需要访问不同数据库的时候使用对应的JdbcTemplate即可。这里介绍在Spring Boot中基于Oracle和Mysql配置Druid多数据源。

二、引入依赖

先根据 《Spring Boot项目创建》 应用,然后引入如下依赖:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
        <dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>6.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>

三、多数据源配置

接着在Spring Boot配置文件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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源
mysql:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true&useSSL=false
username: spring
password: spring#123
oracle:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@125.72.228.21:1521:qhfwbzdb
username: wno704
password: wno704db312
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
# 配置连接在池中的最小生存时间
min-evictable-idle-time-millis: 300000
validation-query: select '1' from dual
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-open-prepared-statements: 20
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
filters: stat,wall
# Spring监控AOP切入点,如x.y.z.service.*,配置多个英文逗号分隔
aop-patterns: com.wno704.boot.service.*


# WebStatFilter配置
web-stat-filter:
enabled: true
# 添加过滤规则
url-pattern: /*
# 忽略过滤的格式
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'

# StatViewServlet配置
stat-view-servlet:
enabled: true
# 访问路径为/druid时,跳转到StatViewServlet
url-pattern: /druid/*
# 是否能够重置数据
reset-enable: false
# 需要账号密码才能访问控制台
login-username: druid
login-password: druid123
# IP白名单
# allow: 127.0.0.1
# IP黑名单(共同存在时,deny优先于allow)
# deny: 192.168.1.218

# 配置StatFilter
filter:
stat:
log-slow-sql: true

然后创建一个多数据源配置类,根据application.yml分别配置一个Mysql和Oracle的数据源,并且将这两个数据源注入到两个不同的JdbcTemplate中:

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
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "mysqldatasource")
@ConfigurationProperties("spring.datasource.druid.mysql")
public DataSource dataSourceOne(){
return DruidDataSourceBuilder.create().build();
}

@Bean(name = "oracledatasource")
@ConfigurationProperties("spring.datasource.druid.oracle")
public DataSource dataSourceTwo(){
return DruidDataSourceBuilder.create().build();
}

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

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

上述代码根据application.yml创建了mysqldatasource和oracledatasource数据源,其中mysqldatasource用@Primary标注为主数据源,接着根据这两个数据源创建了mysqlJdbcTemplate和oracleJdbcTemplate。

@Primary标志这个Bean如果在多个同类Bean候选时,该Bean优先被考虑。多数据源配置的时候,必须要有一个主数据源,用@Primary标志该Bean。

数据源创建完毕,接下来开始进行测试代码编写。

四、编码

首先往Mysql和Oracle中创建测试表,并插入一些测试数据:

Mysql:

1
2
3
4
5
6
7
8
9
10
11
drop table if exists `student`;
create table `student` (
`sno` int(11) not null auto_increment comment '学号',
`sname` varchar(50) character set utf8 collate utf8_general_ci not null comment '姓名',
`ssex` varchar(2) character set utf8 collate utf8_general_ci not null comment '性别',
primary key (`sno`) using btree
) engine = innodb auto_increment = 1 character set = utf8 collate = utf8_general_ci row_format = dynamic;

insert into `student` values (1, 'KangKang', 'M');
insert into `student` values (2, 'Mike', 'M');
insert into `student` values (3, 'Jane', 'F');

Oracle:

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
    CREATE TABLE "WNO704"."STUDENT" (
"SNO" NUMBER NOT NULL,
"SNAME" NVARCHAR2(50),
"SSEX" NVARCHAR2 (2),
CONSTRAINT "PK_STUDENT_SNO" PRIMARY KEY ("SNO")
)
tablespace WNO704
PCTFREE 10
initrans 1
maxtrans 255 storage (
INITIAL 768K
next 8K
minextents 1
MAXEXTENTS unlimited t 8K
minextents 1
MAXEXTENTS unlimited
);

COMMENT ON COLUMN "WNO704"."STUDENT"."SNO" IS '学号';
COMMENT ON COLUMN "WNO704"."STUDENT"."SNAME" IS '姓名';
COMMENT ON COLUMN "WNO704"."STUDENT"."SSEX" IS '性别';

INSERT INTO "STUDENT" VALUES (1, 'KangKang', 'M');
INSERT INTO "STUDENT" VALUES (2, 'Mike', 'M');
INSERT INTO "STUDENT" VALUES (3, 'Jane', 'F');

MysqlStudentDao接口:

1
2
3
public interface MysqlStudentDao {
List<Map<String, Object>> getAllStudents();
}

MysqlStudentDao实现;

1
2
3
4
5
6
7
8
9
10
11
@Repository
public class MysqlStudentDaoImpl implements MysqlStudentDao {
@Autowired
@Qualifier("mysqlJdbcTemplate")
private JdbcTemplate jdbcTemplate;

@Override
public List<Map<String, Object>> getAllStudents() {
return this.jdbcTemplate.queryForList("select * from student");
}
}

可看到,在MysqlStudentDaoImp中注入的是mysqlJdbcTemplate。

OracleStudentDao接口:

1
2
3
public interface OracleStudentDao {
List<Map<String, Object>> getAllStudents();
}

OracleStudentDao实现:

1
2
3
4
5
6
7
8
9
10
11
@Repository
public class OracleStudentDaoImpl implements OracleStudentDao {
@Autowired
@Qualifier("oracleJdbcTemplate")
private JdbcTemplate jdbcTemplate;

@Override
public List<Map<String, Object>> getAllStudents() {
return this.jdbcTemplate.queryForList("select * from student");
}
}

在OracleStudentDaoImp中注入的是oracleJdbcTemplate。

随后编写Service层:

StudentService接口:

1
2
3
4
public interface StudentService {
List<Map<String, Object>> getAllStudentsWithMysql();
List<Map<String, Object>> getAllStudentsWithOracle();
}

StudentService实现:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Service("studentService")
public class StudentServiceImpl implements StudentService {

@Autowired
private OracleStudentDao oracleStudentDao;

@Autowired
private MysqlStudentDao mysqlStudentDao;

@Override
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.mysqlStudentDao.getAllStudents();
}

@Override
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.oracleStudentDao.getAllStudents();
}
}

五、测试

最后编写TestController:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@RestController
public class TestController {
@Autowired
private StudentService studentService;

@RequestMapping( value = "/getAllStudentsWithMysql", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithMysql() {
return this.studentService.getAllStudentsWithMysql();
}

@RequestMapping( value = "/getAllStudentsWithOracle", method = RequestMethod.GET)
public List<Map<String, Object>> getAllStudentsWithOracle() {
return this.studentService.getAllStudentsWithOracle();
}
}

最终项目目录如下图所示:

启动项目,访问: http://localhost:8080/getAllStudentsWithMysql

http://localhost:8080/getAllStudentsWithOracle