最近在做一个项目,需要使用 SpringBoot+Mybatis+Druid 使用多数据源,前提条件是数据源的个数和名称不确定,是在 application.properties 文件中设定,在使用时根据条件动态切换。
这样就不能像 Druid 官网提到的,通过 ConfigurationProperties 注解创建多个 DruidDataSource,因为这样属于硬编码,添加一个数据源就要再添加代码,我考虑的是只使用一套构建 DataSource 的代码,添加或删除数据源只需要修改配置文件。
Spring 提供的 AbstractRoutingDataSource 提供了运行时动态切换 DataSource 的功能,但是 AbstractRoutingDataSource 对象中包含的 DataSourceBuilder 构建的仅仅是 Spring JDBC 的 DataSource,并不是我们使用的 DruidDataSource, 需要自行构建。
这篇文章介绍了如何使用 AbstractRoutingDataSource 构建多数据源,但是它有几点不足:
1) 构建的 TargetDataSources 中的 DataSource 仅包含 driverClassName,username,password,url 等基本属性,对于 DruidDataSource 这种复杂的 DataSource,仅赋这些属性是不够的。
2) 构建 AbstractingRoutingDataSource 使用 ImportBeanDefinitionRegistrar 进行注册,不够直观。
我的方案对这个解决方案做了一定的修改。
我在本地 MySQL 新建三个数据库 testdb_1,testdb_2,testdb_3, 每个数据库新建一张 student 表
建表语句如下
CREATE TABLE student (
ID int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
CLASS_NAME varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
CREATE_DATE timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
UPDATE_DATE timestamp(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (ID) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
分别创建三个用户 appuser_1,appuser_2,appuser_3,用于连接三个数据库 (需要赋予它们访问数据库的相应权限)
我们构建一个名为 SpringBootDruidMultiDB 的 SpringBoot 项目,导入 mybatis-spring-boot-starter 和
spring-boot-starter-web 以及 spring-boot-starter-test,为了使用 Druid 方便,项目还导入 druid-spring-boot-starter。由于使用 Log4j2 记录日志,在每个导入的 starter 中都要 exclude 掉 spring-boot-starter-logging(去掉自带的 slf4j),添加 log4j2 所需要的库,pom 文件的配置如下
- <parent>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-parent</artifactId>
- <version>1.5.9.RELEASE</version>
- <relativePath/>
- </parent>
- <dependencies>
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid-spring-boot-starter</artifactId>
- <version>1.1.6</version>
- <exclusions>
- <exclusion>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.mybatis.spring.boot</groupId>
- <artifactId>mybatis-spring-boot-starter</artifactId>
- <version>1.3.1</version>
- <exclusions>
- <exclusion>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-web</artifactId>
- <exclusions>
- <exclusion>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-test</artifactId>
- <scope>test</scope>
- <exclusions>
- <exclusion>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.apache.logging.log4j</groupId>
- <artifactId>log4j-api</artifactId>
- <version>2.10.0</version>
- </dependency>
- <dependency>
- <groupId>org.apache.logging.log4j</groupId>
- <artifactId>log4j-core</artifactId>
- <version>2.10.0</version>
- </dependency>
- <dependency>
- <groupId>com.lmax</groupId>
- <artifactId>disruptor</artifactId>
- <version>3.3.7</version>
- </dependency>
- <dependency>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- <version>1.2</version>
- </dependency>
- </dependencies>
修改 src/main/resources 文件,添加多个数据源信息
spring.custom.datasource.name=db1,db2,db3
spring.custom.datasource.db1.name=db1
spring.custom.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.custom.datasource.db1.url=jdbc:mysql://localhost:3306/testdb_1?characterEncoding=utf8&autoReconnect=true&useSSL=false&useAffectedRows=true
spring.custom.datasource.db1.username=appuser1
spring.custom.datasource.db1.password=admin
spring.custom.datasource.db2.name=db2
spring.custom.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.custom.datasource.db2.url=jdbc:mysql://localhost:3306/testdb_2?characterEncoding=utf8&autoReconnect=true&useSSL=false&useAffectedRows=true
spring.custom.datasource.db2.username=appuser2
spring.custom.datasource.db2.password=admin
spring.custom.datasource.db3.name=db3
spring.custom.datasource.db3.driver-class-name=com.mysql.jdbc.Driver
spring.custom.datasource.db3.url=jdbc:mysql://localhost:3306/testdb_3?characterEncoding=utf8&autoReconnect=true&useSSL=false&useAffectedRows=true
spring.custom.datasource.db3.username=appuser3
spring.custom.datasource.db3.password=admin
再添加 DruidDataSource 的属性
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.async-init=true
spring.datasource.druid.async-close-connection-enable=true
spring.datasource.druid.max-active=20
spring.datasource.druid.max-wait=60000
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=30000
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.filters=stat,wall,log4j2
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=.js,.gif,.jpg,.png,.CSS,.ico,/druid/*
spring.datasource.druid.web-stat-filter.session-stat-enable=true
spring.datasource.druid.web-stat-filter.profile-enable=true
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
spring.datasource.druid.filter.wall.config.alter-table-allow=false
spring.datasource.druid.filter.wall.config.truncate-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.wall.config.none-base-statement-allow=false
spring.datasource.druid.filter.wall.config.update-where-none-check=true
spring.datasource.druid.filter.wall.config.select-into-outfile-allow=false
spring.datasource.druid.filter.wall.config.metadata-allow=true
spring.datasource.druid.filter.wall.log-violation=true
spring.datasource.druid.filter.wall.throw-exception=true
spring.datasource.druid.filter.stat.log-slow-sql= true
spring.datasource.druid.filter.stat.slow-sql-millis=1000
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.log4j2.enabled=true
spring.datasource.druid.filter.log4j2.connection-log-enabled=true
spring.datasource.druid.filter.log4j2.connection-close-after-log-enabled=true
spring.datasource.druid.filter.log4j2.connection-commit-after-log-enabled=true
spring.datasource.druid.filter.log4j2.connection-connect-after-log-enabled=true
spring.datasource.druid.filter.log4j2.connection-connect-before-log-enabled=true
spring.datasource.druid.filter.log4j2.connection-log-error-enabled=true
spring.datasource.druid.filter.log4j2.data-source-log-enabled=true
spring.datasource.druid.filter.log4j2.result-set-log-enabled=true
spring.datasource.druid.filter.log4j2.statement-log-enabled=true
在 src/main/resources 目录下添加 log4j2.xml 文件
- <properties>
- <property name="logPath">
- ./logs/
- </property>
- </properties>
- <Appenders>
- <Console name="Console" target="SYSTEM_OUT" ignoreExceptions="false">
- <PatternLayout pattern="%d [%t] %-5p %c - %m%n" />
- <ThresholdFilter level="trace" onMatch="ACCEPT" onMismatch="DENY" />
- </Console>
- <RollingFile name="infoLog" fileName="${logPath}/multidb_info.log" filePattern="${logPath}/multidb_info-%d{yyyy-MM-dd}.log"
- append="true" immediateFlush="true">
- <PatternLayout pattern="%d [%t] %-5p %c - %m%n" />
- <TimeBasedTriggeringPolicy />
- <Policies>
- <SizeBasedTriggeringPolicy size="10 MB" />
- </Policies>
- <DefaultRolloverStrategy max="30" />
- <Filters>
- <ThresholdFilter level="error" onMatch="DENY" onMismatch="NEUTRAL" />
- <ThresholdFilter level="trace" onMatch="ACCEPT" onMismatch="DENY" />
- </Filters>
- </RollingFile>
- <RollingFile name="errorLog" fileName="${logPath}/multidb_error.log" filePattern="${logPath}/multidb_error-%d{yyyy-MM-dd}.log"
- append="true" immediateFlush="true">
- <PatternLayout pattern="%d [%t] %-5p %c - %m%n" />
- <TimeBasedTriggeringPolicy />
- <Policies>
- <SizeBasedTriggeringPolicy size="10 MB" />
- </Policies>
- <DefaultRolloverStrategy max="30" />
- <Filters>
- <ThresholdFilter level="error" onMatch="ACCEPT" onMismatch="DENY" />
- </Filters>
- </RollingFile>
- </Appenders>
- <Loggers>
- <AsyncLogger name="org.springframework.*" level="INFO" />
- <AsyncLogger name="com.rick" level="INFO" additivity="false">
- <AppenderRef ref="infoLog" />
- <AppenderRef ref="errorLog" />
- <AppenderRef ref="Console" />
- </AsyncLogger>
- <Root level="INFO">
- <AppenderRef ref="Console" />
- </Root>
- </Loggers>
一开始我参照单数据源的构建方式,想像下面的方式构建 DruidDataSource 数据源
public DataSource createDataSource(Environment environment,
- String prefix)
{
- return DruidDataSourceBuilder.create().build(environment, prefix);
}
如果你也想在 IT 行业拿高薪,可以参加我们的训练营课程,选择最适合自己的课程学习,技术大牛亲授,7 个月后,进入名企拿高薪。我们的课程内容有:Java 工程化、高性能及分布式、高性能、深入浅出。高架构。性能调优、Spring,MyBatis,Netty 源码分析和大数据等多个知识点。如果你想拿高薪的,想学习的,想就业前景好的,想跟别人竞争能取得优势的,想进阿里面试但担心面试不过的,你都可以来,群号为:575745314
来源: https://segmentfault.com/a/1190000012735921