开始
项目开发中存在不止一个数据库的情况,需要用到多数据源配置,进行相关业务开发。
实现方式
使用方式与单个druid配置相同
pom
1 2 3 4 5
| <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency>
|
配置文件
application配置
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.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql:
# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
## 初始化连接池的连接数量 大小,最小,最大 spring.datasource.druid.initial-size=1 spring.datasource.druid.min-idle=1 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=300000 spring.datasource.druid.validation-query=SELECT 'x' spring.datasource.druid.test-on-borrow=false spring.datasource.druid.test-on-return=false spring.datasource.druid.filter.stat.log-slow-sql=true # 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开 spring.datasource.druid.pool-prepared-statements=false spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 spring.datasource.druid.filters=stat,wall spring.datasource.druid.test-while-idle=true #Spring监控,对内部各接口调用的监控 spring.datasource.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
# 第二个数据库配置 spring.datasource.felix.username=root spring.datasource.felix.password=root spring.datasource.felix.driver-class-name=com.mysql.jdbc.Driver spring.datasource.felix.url=jdbc:mysql: # 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid spring.datasource.felix.type=com.alibaba.druid.pool.DruidDataSource ## 初始化连接池的连接数量 大小,最小,最大 spring.datasource.felix.druid.initial-size=1 spring.datasource.felix.druid.min-idle=1 spring.datasource.felix.druid.max-active=20 ## 配置获取连接等待超时的时间 spring.datasource.felix.druid.max-wait=60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 spring.datasource.felix.druid.time-between-eviction-runs-millis=60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 spring.datasource.felix.druid.min-evictable-idle-time-millis=300000 spring.datasource.felix.druid.validation-query=SELECT 'x' spring.datasource.felix.druid.test-on-borrow=false spring.datasource.felix.druid.test-on-return=false spring.datasource.felix.druid.filter.stat.log-slow-sql=true # 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开 spring.datasource.felix.druid.pool-prepared-statements=false spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size=20 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 spring.datasource.felix.druid.filters=stat,wall spring.datasource.felix.druid.test-while-idle=true #Spring监控,对内部各接口调用的监控 spring.datasource.felix.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
mybatis.mapper-locations=classpath:mapper
|
yml 配置文件相类似
第一个config类
新建两个druid配置,采用手动配置的方式,避免druid不能监控到SQL
第一个配置文件
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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
| import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import tk.mybatis.spring.annotation.MapperScan;
import javax.sql.DataSource; import java.sql.SQLException;
@Configuration @MapperScan(basePackages ="com.felix.project.mapper", sqlSessionFactoryRef = "masterSqlSessionFactory") public class DruidConfig {
@Value("${spring.datasource.url}") private String url; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; @Value("${spring.datasource.driver-class-name}") private String driverClassName; @Value("${spring.datasource.druid.initial-size}") private int initialSize; @Value("${spring.datasource.druid.min-idle}") private int minIdle; @Value("${spring.datasource.druid.max-active}") private int maxActive; @Value("${spring.datasource.druid.max-wait}") private int maxWait; @Value("${spring.datasource.druid.time-between-eviction-runs-millis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.druid.min-evictable-idle-time-millis}") private int minEvictableIdleTimeMillis; @Value("${spring.datasource.druid.validation-query}") private String validationQuery; @Value("${spring.datasource.druid.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.druid.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.druid.test-on-return}") private boolean testOnReturn; @Value("${spring.datasource.druid.pool-prepared-statements}") private boolean poolPreparedStatements; @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}") private int maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.druid.filters}") private String filters; @Value("${spring.datasource.druid.aop-patterns}") private String[] aopPatterns;
@Bean(name = "masterDataSource") @Primary public DataSource masterDataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(url); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); datasource.setPoolPreparedStatements(poolPreparedStatements); try { datasource.setFilters(filters); } catch (SQLException e) { e.printStackTrace(); } return datasource; }
@Primary @Bean(name = "primaryTransactionManager") public DataSourceTransactionManager primaryTransactionManager() throws SQLException { return new DataSourceTransactionManager(masterDataSource()); }
@Primary @Bean(name = "masterSqlSessionFactory") public SqlSessionFactory primarySqlSessionFactory(@Qualifier("masterDataSource") DataSource primaryDataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(primaryDataSource); sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath:mapper/*.xml")); return sessionFactory.getObject(); } }
|
第二config类
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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
| package com.felix.project.config;
import com.alibaba.druid.pool.DruidDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import tk.mybatis.spring.annotation.MapperScan;
import javax.sql.DataSource; import java.sql.SQLException;
@Configuration @MapperScan(basePackages ="com.felix.project.mapper2",sqlSessionFactoryRef = "secondSqlSessionFactory") public class DruidFelicConfig {
@Value("${spring.datasource.felix.url}") private String url; @Value("${spring.datasource.felix.username}") private String username; @Value("${spring.datasource.felix.password}") private String password; @Value("${spring.datasource.felix.driver-class-name}") private String driverClassName; @Value("${spring.datasource.felix.druid.initial-size}") private int initialSize; @Value("${spring.datasource.felix.druid.min-idle}") private int minIdle; @Value("${spring.datasource.felix.druid.max-active}") private int maxActive; @Value("${spring.datasource.felix.druid.max-wait}") private int maxWait; @Value("${spring.datasource.felix.druid.time-between-eviction-runs-millis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.felix.druid.min-evictable-idle-time-millis}") private int minEvictableIdleTimeMillis; @Value("${spring.datasource.felix.druid.validation-query}") private String validationQuery; @Value("${spring.datasource.felix.druid.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.felix.druid.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.felix.druid.test-on-return}") private boolean testOnReturn; @Value("${spring.datasource.felix.druid.pool-prepared-statements}") private boolean poolPreparedStatements; @Value("${spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size}") private int maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.felix.druid.filters}") private String filters; @Value("${spring.datasource.felix.druid.aop-patterns}") private String[] aopPatterns;
@Bean(name = "secondDataSource") public DataSource secondDataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(url); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); datasource.setPoolPreparedStatements(poolPreparedStatements); try { datasource.setFilters(filters); } catch (SQLException e) { e.printStackTrace(); } return datasource; }
@Bean(name = "secondTransactionManager") public DataSourceTransactionManager secondTransactionManager() throws SQLException { return new DataSourceTransactionManager(secondDataSource()); }
@Bean(name = "secondSqlSessionFactory") public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource primaryDataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(primaryDataSource); sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath:mapper/*.xml")); return sessionFactory.getObject(); } }
|
两配置文件基本相同,主要区别在与主配置需要加上@Primary注解
监控
新建一个DruidMoniterConfig配置项用于开启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
| package com.felix.project.config;
import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;
@Configuration public class DruidMoniterConfig { @Bean @ConditionalOnMissingBean public ServletRegistrationBean druidServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); servletRegistrationBean.addInitParameter("loginUsername", "admin"); servletRegistrationBean.addInitParameter("loginPassword", "admin"); servletRegistrationBean.addInitParameter("resetEnable", "false"); return servletRegistrationBean; }
@Bean @ConditionalOnMissingBean public FilterRegistrationBean filterRegistrationBean() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; }
}
|
登录
![druid](https://www.felixm.xyz/2019/03/15/SpringBoot-druid%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/Snipaste_2019-03-15_17-01-48.jpg)
![druid](https://www.felixm.xyz/2019/03/15/SpringBoot-druid%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/Snipaste_2019-03-15_17-05-09.jpg)
![druid](https://www.felixm.xyz/2019/03/15/SpringBoot-druid%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/Snipaste_2019-03-15_17-05-47.jpg)
查询数据库验证
![druid](https://www.felixm.xyz/2019/03/15/SpringBoot-druid%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/Snipaste_2019-03-15_17-21-20.jpg)
参考
https://www.cnblogs.com/qdhxhz/p/10192041.html