一、前言 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