一、前言
最近公司来了一个项目，在理需求和编写接口文档的时候有一个比较有意思的需求：项目启动时不加载数据库，然后管理页面需要提供一个接口，用来配置数据库，配置成功后才连接数据库。于是我做了以下的尝试。
二、技术预研
1.我首先想到的是：不配置url
2019-11-22 16:16:27.268 ERROR 90476 --- [ main] o.s.b.d.LoggingFailureAnalysisReporter : *************************** APPLICATION FAILED TO START *************************** Description: Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured. Reason: Failed to determine a suitable driver class
这里表示如果需要spring容器管理dataSource就必须在配置文件中配置一个数据源，但是与我的需求不符合，失败。
2.然后我就想如果我排除spring自动配置数据源
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class, DataSourceAutoConfiguration.class})
但是这里也存在一个问题，通过自动装配实现的mapper无法被识别和实例化，需要自己全部接管所有的sqlBeanFactory，这简直是一个灾难，所以这个宣告失败。
3.使用sqlite数据库当做默认数据库，让springboot帮我自动装配，然后调用接口的时候把这个bean从容器中移除
sqlite是一个文件，可以放在项目中，不需要开启服务，连接就能用，很方便，这个宣告成功，实现代码如下。
三、实现思路
- 在启动方法处获取spring上下文
- 注入到SpringContextUtil中
- 通过@Bean注入MyDynamicDataSource配置到容器中，并设置默认的数据源
- 重写AbstractRoutingDataSource类的determineCurrentLookupKey方法，实现数据源的切换
- ThreadLocal 保证当前线程安全的前提下设置当前线程的数据源
四、代码实现
- 配置文件配置
server.port=8081 server.tomcat.uri-encoding=utf-8 #指定连接地址和数据库 spring.datasource.url=jdbc:sqlite:classpath:static/defaultDataBase/default.db spring.datasource.driver-class-name=org.sqlite.JDBC #指定数据源为druid spring.datasource.type=com.alibaba.druid.pool.DruidDataSource # 如果没有进行第三步3的配置，下面的属性是不会生效的 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 spring.datasource.maxWait=6000 spring.datasource.timeBetweenEvictionRunsMillis=6000 spring.datasource.minEvictableIdleTimeMillis=30000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false spring.datasource.poolPreparedStatements=true # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 #filters=stat,wall,log4j spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 spring.datasource.useGlobalDataSourceStat=true spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 # 指定全局配置文件的位置 mybatis.config-location=classpath:mybatis-config.xml # 指定sql映射文件的位置 mybatis.mapper-locations=classpath:mappers/*.xml
2.pom引入
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.infosec</groupId> <artifactId>sra</artifactId> <version>0.0.1-SNAPSHOT</version> <name>SRA</name> <description>Demo project for Spring Boot</description> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.9.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.51</version> </dependency> <!--aop--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <!-- swagger --> <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 --> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.7.0</version> </dependency> <dependency> <groupId>com.gizwits</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.7.0</version> </dependency> <!-- 阿里连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!--mappers--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <!--mysql驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--ojdbc7驱动 --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2.2</version> <scope>system</scope> <systemPath>${pom.basedir}/src/main/resources/static/lib/ojdbc7-12.1.0.2.jar </systemPath> </dependency> <!--sqlite驱动--> <dependency> <groupId>org.xerial</groupId> <artifactId>sqlite-jdbc</artifactId> <version>3.23.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>8</source> <target>8</target> </configuration> </plugin> </plugins> </build> </project>
这里有一个问题如果druid版本太低会出现以下的异常，解决办法是提高版本
Caused by: java.lang.NoClassDefFoundError: org/springframework/boot/autoconfigure/jdbc/metadata/DataSourcePoolMetadataProvider at java.lang.Class.getDeclaredMethods0(Native Method) ~[na:1.8.0_201] at java.lang.Class.privateGetDeclaredMethods(Class.java:2701) ~[na:1.8.0_201] at java.lang.Class.getDeclaredMethods(Class.java:1975) ~[na:1.8.0_201]
- druid配置类
package com.infosec.ra.configuration; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import com.infosec.ra.common.MyDynamicDataSource; import com.infosec.ra.filter.SrmFilter; import lombok.extern.slf4j.Slf4j; import org.mybatis.spring.SqlSessionFactoryBean; import org.springframework.boot.context.properties.ConfigurationProperties; 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; import org.springframework.context.annotation.Primary; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Slf4j @Configuration public class DruidConfig { /** * @return * @Bean 防止数据监控报错，无法查看数据源 * @ConfigurationProperties 会把配置文件的参数自动赋值到dataSource里。 * @Primary 用于标识默认使用的 DataSource Bean */ @Bean(name = "masterDataSource") @ConfigurationProperties(prefix = "spring.datasource") @Primary public DataSource masterDataSource() { log.info("创建masterDataSource"); //DruidDataSource druidDataSource = new DruidDataSource(); DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build(); return druidDataSource; } @Bean(name = "dynamicDataSource") public DataSource dynamicDataSource() { MyDynamicDataSource myDynamicDataSource = new MyDynamicDataSource(); // 配置多数据源 Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put("master", masterDataSource()); myDynamicDataSource.setTargetDataSources(targetDataSources); return myDynamicDataSource; } /** * 配置 SqlSessionFactoryBean */ @Bean(value = "sqlSessionFactoryBeanTest") @ConfigurationProperties(prefix = "mappers") public SqlSessionFactoryBean sqlSessionFactoryBean() { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); // 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource 作为数据源则不能实现切换 sqlSessionFactoryBean.setDataSource(dynamicDataSource()); return sqlSessionFactoryBean; } /** * 注入 DataSourceTransactionManager 用于事务管理 */ @Bean public PlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(dynamicDataSource()); } @Bean public ServletRegistrationBean statViewServlet() { //创建servlet注册实体 ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); //设置ip白名单 servletRegistrationBean.addInitParameter("allow", ""); //设置ip黑名单 servletRegistrationBean.addInitParameter("deny", ""); //设置控制台管理用户__登录用户名和密码 servletRegistrationBean.addInitParameter("loginUsername", "druid"); servletRegistrationBean.addInitParameter("loginPassword", "123456"); //是否可以重置数据 servletRegistrationBean.addInitParameter("resetEnable", "false"); return servletRegistrationBean; } /** * druid过滤器 * * @return */ @Bean public FilterRegistrationBean druidFilter() { //创建过滤器 FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(); filterRegistrationBean.setFilter(new WebStatFilter()); //设置过滤器过滤路径 filterRegistrationBean.addUrlPatterns("/*"); //忽略过滤的形式 filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; } }
- 启动类
我这里是在windows开发，然后正式环境是在linux上的，所以为了避免频繁修改配置文件，我通过System类获取操作系统类型，判断是开发还是正式环境，自动切换配置文件。
package com.infosec.ra; import com.infosec.ra.util.DataSourceUtil; import com.infosec.ra.util.SpringContextUtil; import lombok.extern.slf4j.Slf4j; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.builder.SpringApplicationBuilder; import org.springframework.boot.web.servlet.support.SpringBootServletInitializer; import org.springframework.context.ConfigurableApplicationContext; import java.util.Map; @Slf4j @SpringBootApplication public class SraApplication extends SpringBootServletInitializer { //环境变量OS对应操作系统 private static final String OS = "OS"; //项目所处操作系统环境为windows private static final String WINDOWS = "Windows"; //项目为开发环境 private static final String DEVELOP = "develop"; //项目为测试环境 private static final String PRODUCT = "product"; @Override protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) { return builder.sources(SraApplication.class).profiles(getProfile()); } private static String getProfile(){ //获取系统环境变量 Map<String, String> env = System.getenv(); //获取操作系统类型，支持windows和linux String profile = (env != null && env.get(OS).contains(WINDOWS) ? DEVELOP : PRODUCT); return profile; } public static void main(String[] args) { ConfigurableApplicationContext applicationContext = new SpringApplicationBuilder().sources(SraApplication.class).profiles(getProfile()).run(args); log.info("SpringBoot启动成功"); SpringContextUtil.setApplicationContext(applicationContext); DataSourceUtil.initDataSource(); } }
5.数据源工具类
package com.infosec.ra.util; import com.alibaba.druid.pool.DruidDataSource; import com.infosec.ra.common.MyDynamicDataSource; import lombok.extern.slf4j.Slf4j; import org.slf4j.LoggerFactory; import java.util.HashMap; import java.util.Map; /** * 初始化和添加数据源 */ @Slf4j public class DataSourceUtil { public static final Map<Object, Object> dataSourceMap = new HashMap<>(); public static void initDataSource() { //获取masterDataSource DruidDataSource masterDataSource = (DruidDataSource) SpringContextUtil.getBean("masterDataSource"); addDataSource("master", masterDataSource); //初始化其它数据源 //initOthersDataSource(); //刷新数据源 flushDataSource(); } public static void flushDataSource() { //获取spring管理的dynamicDataSource MyDynamicDataSource myDynamicDataSource = (MyDynamicDataSource) SpringContextUtil.getBean("dynamicDataSource"); //将数据源设置到 targetDataSources myDynamicDataSource.setTargetDataSources(dataSourceMap); //将 targetDataSources 中的连接信息放入 resolvedDataSources 管理 myDynamicDataSource.afterPropertiesSet(); } public static void addDataSource(String key, DruidDataSource masterDataSource) { dataSourceMap.put(key, masterDataSource); } private static void initOthersDataSource() { //在此处可以查询出所有的数据源（例如，配置文件，数据库）然后添加 String key = "slave"; DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUsername("root"); druidDataSource.setPassword("123456"); druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); druidDataSource.setUrl("jdbc:mysql://10.100.61.160:3306/db1"); //添加数据源到map addDataSource(key, druidDataSource); } }
- spring上下文工具类
package com.infosec.ra.util; import org.springframework.context.ApplicationContext; public class SpringContextUtil { private static ApplicationContext applicationContext; //获取上下文 public static ApplicationContext getApplicationContext() { return applicationContext; } //设置上下文 public static void setApplicationContext(ApplicationContext applicationContext) { SpringContextUtil.applicationContext = applicationContext; } //通过名字获取上下文中的bean public static Object getBean(String name) { return applicationContext.getBean(name); } //通过类型获取上下文中的bean public static Object getBean(Class<?> requiredType) { return applicationContext.getBean(requiredType); } }
- 动态数据源MyDynamicDataSource
package com.infosec.ra.common; import lombok.extern.slf4j.Slf4j; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import org.springframework.util.StringUtils; @Slf4j public class MyDynamicDataSource extends AbstractRoutingDataSource { @Override public Object determineCurrentLookupKey() { //获取当前线程的数据源，如果不存在使用master数据源 String datasource = DBContextHolder.getDataSource(); if (StringUtils.isEmpty(datasource)) { datasource = "master"; } logger.info("datasource=" + datasource); return datasource; } }
8.数据源切换类
package com.infosec.ra.common; import com.infosec.ra.util.DataSourceUtil; public class DBContextHolder { // 对当前线程的操作-线程安全的 private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>(); // 调用此方法，切换数据源 public static void setDataSource(String dataSource) { if (DataSourceUtil.dataSourceMap.containsKey(dataSource)) { contextHolder.set(dataSource); } else { throw new RuntimeException("数据源:" + dataSource + "不存在"); } } // 获取数据源 public static String getDataSource() { return contextHolder.get(); } // 删除数据源 public static void clearDataSource() { contextHolder.remove(); } }
9.mapper类
package com.infosec.ra.mapper; import com.infosec.ra.entity.Role; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Select; import java.util.List; @Mapper public interface RoleMapper { @Select("select * from ROLE") List<Role> getRoles(); }
10.controller类
package com.infosec.ra.controller; import com.alibaba.druid.pool.DruidDataSource; import com.infosec.ra.common.DBContextHolder; import com.infosec.ra.entity.Role; import com.infosec.ra.mapper.RoleMapper; import com.infosec.ra.util.DataSourceUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RestController; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; @RestController public class DataSourceController { @Autowired private RoleMapper roleMapper; @GetMapping("/role") public List<Role> getRoles(){ //double random = Math.random(); /*if((random*10)%2==0){ DBContextHolder.setDataSource("master"); }else{ DBContextHolder.setDataSource("slave2"); }*/ DBContextHolder.setDataSource("master"); return roleMapper.getRoles(); } @GetMapping("/roles") public List<Role> getRoless(){ DBContextHolder.setDataSource("slave2"); return roleMapper.getRoles(); } @PostMapping("/test") public String addDataSource(String token,String userName,String password,String driver,String url,String key){ Map<String, String> map = new HashMap<>(); DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUsername(userName); druidDataSource.setPassword(password); druidDataSource.setDriverClassName(driver); druidDataSource.setUrl(url); //添加数据源到map DataSourceUtil.addDataSource(key, druidDataSource); // 刷新 DataSourceUtil.flushDataSource(); map.put("msg", "数据源数量:" + DataSourceUtil.dataSourceMap.size()); return "添加成功："+DataSourceUtil.dataSourceMap.size(); } }
五、结果展示
- 刚启动，前端没有配置数据源，所以看不到数据源
在这里插入图片描述
2.通过swagger调用接口，配置数据源
再加一个mybatits数据库
userName: root pwd: 123456 driver: com.mysql.jdbc.Driver url: jdbc:mysql://10.100.61.160:3306/db1 key: master
- 再次查看数据源,可以在数据源中找到两个，一个是mysql，一个是oracle
在这里插入图片描述
- 查询结果
#mysql [ { "id": 1, "name": "test" } ] #oracle [ { "id": 1, "name": "安全管理员" }, { "id": 2, "name": "系统管理员" }, { "id": 3, "name": "日志管理员" } ]
这里需要先执行查询方法，然后在数据源中才能看到刚刚添加的数据源，如果碰到问题，欢迎提问！
