今天补充一个其他的解决方案（原理差不多），这里是使用mysql+sqlserver作为数据源，可以供大家参考一下。
一、依赖引入
首先引入必要的jar包，有省略，自行补充
pom.xml
<!--连接池 --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.2.0</version> </dependency> <!--mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.31</version> </dependency> <!-- sqlserver--> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.0</version> </dependency>
二、代码实现
先创建 DatabaseType，定义两个数据源的名称
public enum DatabaseType { mysqlDataSource,sqlServerDataSource }
实现DatabaseContextHolder
public class DatabaseContextHolder { private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>(); public static void setDatabaseType(DatabaseType type) { contextHolder.set(type); } public static DatabaseType getDatabaseType() { return contextHolder.get(); } }
实现DynamicDataSource
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DatabaseContextHolder.getDatabaseType(); } }
实现MyBatisConfig
import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.env.Environment; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration // 该注解类似于spring配置文件 @MapperScan(basePackages = "com.xxx.dao") public class MyBatisConfig { @Autowired private Environment env; // 读取主数据源 @Bean public DataSource mysqlDataSource(Environment env) { Map<String, Object> dsMap = new HashMap<>(); dsMap.put("type", env.getProperty("spring.datasource.type")); dsMap.put("driver", env.getProperty("spring.datasource.driver-class-name")); dsMap.put("url", env.getProperty("spring.datasource.url")); dsMap.put("username", env.getProperty("spring.datasource.username")); dsMap.put("password", env.getProperty("spring.datasource.password")); return buildDataSource(dsMap); } // 读取从数据源 @Bean public DataSource sqlServerDataSource(Environment env) { Map<String, Object> dsMap = new HashMap<>(); dsMap.put("type", env.getProperty("slave.datasource.type")); dsMap.put("driver", env.getProperty("slave.datasource.driver-class-name")); dsMap.put("url", env.getProperty("slave.datasource.url")); dsMap.put("username", env.getProperty("slave.datasource.username")); dsMap.put("password", env.getProperty("slave.datasource.password")); return buildDataSource(dsMap); } /** * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错 * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例） */ @Bean @Primary public DynamicDataSource dataSource(@Qualifier("mysqlDataSource") DataSource mysqlDataSource, @Qualifier("sqlServerDataSource") DataSource sqlServerDataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DatabaseType.mysqlDataSource, mysqlDataSource); targetDataSources.put(DatabaseType.sqlServerDataSource, sqlServerDataSource); DynamicDataSource dataSource = new DynamicDataSource(); dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法 dataSource.setDefaultTargetDataSource(mysqlDataSource);// 默认的datasource设 return dataSource; } /** * 根据数据源创建SqlSessionFactory */ @Bean public SqlSessionFactory sqlSessionFactory(DynamicDataSource ds) throws Exception { SqlSessionFactoryBean fb = new SqlSessionFactoryBean(); fb.setDataSource(ds);// 指定数据源(这个必须有，否则报错) // 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加 //fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));// 指定基包 fb.setConfigLocation(new PathMatchingResourcePatternResolver().getResource(env.getProperty("mybatis.config-location"))); fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapper-locations"))); return fb.getObject(); } /** * 配置事务管理器 */ @Bean public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception { return new DataSourceTransactionManager(dataSource); } /** * 创建DataSource * @param dataSourceMap * @return */ public DataSource buildDataSource(Map<String, Object> dataSourceMap) { try { Object type = dataSourceMap.get("type"); Class<? extends DataSource> dataSourceType = (Class<? extends DataSource>) Class.forName((String) type); String driverClassName = dataSourceMap.get("driver").toString(); String url = dataSourceMap.get("url").toString(); String username = dataSourceMap.get("username").toString(); String password = dataSourceMap.get("password").toString(); // 自定义DataSource配置 DataSourceBuilder factory = DataSourceBuilder.create().driverClassName(driverClassName).url(url) .username(username).password(password).type(dataSourceType); return factory.build(); } catch (ClassNotFoundException e) { e.printStackTrace(); } return null; } }
三、yml文件配置
application.yml
server: tomcat: uri-encoding: UTF-8 max-threads: 1000 min-spare-threads: 30 port: 8888 spring: profiles: active: dev #mybatis mybatis: mapper-locations: classpath:mybatis/mappers/*.xml config-location: classpath:mybatis/mybatis-config.xml
定义application-dev.yml
#mysql spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:33306/test1?useAffectedRows=true&characterEncoding=utf8 username: root password: root type: com.zaxxer.hikari.HikariDataSource hikari: connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 maximum-pool-size: 10 #sqlserver slave: datasource: driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test2; username: sa password: 123456 type: com.zaxxer.hikari.HikariDataSource hikari: connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 maximum-pool-size: 10
四、使用方式
建议在service实现类里面使用
public List<Demo> queryList(){ //切换数据源 DatabaseContextHolder.setDatabaseType(DatabaseType.sqlServerDataSource); Map<String,Object> map = new HashMap<>(); return demoDao.queryList(map); } public List<Demo> selectList(){ //切换数据源 DatabaseContextHolder.setDatabaseType(DatabaseType.mysqlDataSource); Map<String,Object> map = new HashMap<>(); return demoDao.queryList(map); }
20190709更新
之前hikari的配置未生效
hikari: connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000 maximum-pool-size: 10
今天优化了一下MyBatisConfig，增加了配置Hikari dataSource的超时设置，详细看代码
package com.xxx.config; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.env.Environment; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration // 该注解类似于spring配置文件 @MapperScan(basePackages = "com.xxx.dao") public class MyBatisConfig { @Autowired private Environment env; // 读取主数据源 @Bean public DataSource mysqlDataSource(Environment env) { HikariConfig config = new HikariConfig(); config.setDriverClassName(env.getProperty("spring.datasource.driver-class-name")); config.setJdbcUrl(env.getProperty("spring.datasource.url")); config.setUsername(env.getProperty("spring.datasource.username")); config.setPassword(env.getProperty("spring.datasource.password")); config.setConnectionTestQuery(env.getProperty("spring.datasource.ConnectionTestQuery")); config.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring.datasource.hikari.maximum-pool-size"))); config.setIdleTimeout(Integer.parseInt(env.getProperty("spring.datasource.hikari.idle-timeout"))); config.setMaxLifetime(Integer.parseInt(env.getProperty("spring.datasource.hikari.max-lifetime"))); config.setConnectionTimeout(Integer.parseInt(env.getProperty("spring.datasource.hikari.connection-timeout"))); DataSource dataSource = new HikariDataSource(config); return dataSource; } // 读取从数据源 @Bean public DataSource sqlServerDataSource(Environment env) { HikariConfig config = new HikariConfig(); config.setDriverClassName(env.getProperty("slave.datasource.driver-class-name")); config.setJdbcUrl(env.getProperty("slave.datasource.url")); config.setUsername(env.getProperty("slave.datasource.username")); config.setPassword(env.getProperty("slave.datasource.password")); config.setConnectionTestQuery(env.getProperty("slave.datasource.ConnectionTestQuery")); config.setMaximumPoolSize(Integer.parseInt(env.getProperty("slave.datasource.hikari.maximum-pool-size"))); config.setIdleTimeout(Integer.parseInt(env.getProperty("slave.datasource.hikari.idle-timeout"))); config.setMaxLifetime(Integer.parseInt(env.getProperty("slave.datasource.hikari.max-lifetime"))); config.setConnectionTimeout(Integer.parseInt(env.getProperty("slave.datasource.hikari.connection-timeout"))); DataSource dataSource = new HikariDataSource(config); return dataSource; } /** * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错 * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例） */ @Bean @Primary public DynamicDataSource dataSource(@Qualifier("mysqlDataSource") DataSource mysqlDataSource, @Qualifier("sqlServerDataSource") DataSource sqlServerDataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DatabaseType.mysqlDataSource, mysqlDataSource); targetDataSources.put(DatabaseType.sqlServerDataSource, sqlServerDataSource); DynamicDataSource dataSource = new DynamicDataSource(); dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法 dataSource.setDefaultTargetDataSource(mysqlDataSource);// 默认的datasource设 return dataSource; } /** * 根据数据源创建SqlSessionFactory */ @Bean public SqlSessionFactory sqlSessionFactory(DynamicDataSource ds) throws Exception { SqlSessionFactoryBean fb = new SqlSessionFactoryBean(); fb.setDataSource(ds);// 指定数据源(这个必须有，否则报错) // 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加 //fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));// 指定基包 fb.setConfigLocation(new PathMatchingResourcePatternResolver().getResource(env.getProperty("mybatis.config-location"))); fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapper-locations")));// return fb.getObject(); } /** * 配置事务管理器 */ @Bean public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception { return new DataSourceTransactionManager(dataSource); } }
