有些项目场景需要支持不同数据源切换或者多种数据源(mysql、sqlserver、oracle),本次分享就可以满足这一需求。
1.配置第一数据源(我这里写为主数据源、有些不太准确)
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
/**
*
* @Description: 主数据源切换 mysql sqlServer oracle
* @Author: xyh
* @Date: 2019/8/15 13:33
*/
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.master")
@MapperScan(basePackages = "com.test.mapper.master", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
@Value("${spring.datasource.master.url}")
private String url;
@Value("${spring.datasource.master.username}")
private String username;
@Value("${spring.datasource.master.password}")
private String password;
@Value("${spring.datasource.master.driverClassName}")
private String driverClass;
@Value("${spring.datasource.master.type}")
private String type;
/**
* mybatis xml路径
*/
private String mybatisPattern;
@Primary
@Bean(name = "masterDataSource")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDbType(type);
mybatisPattern = "classpath:mybatis/master/*.xml";
if (url.contains("sqlserver")) {
dataSource.setValidationQuery("select 1");
} else if (url.contains("oracle")) {
dataSource.setValidationQuery("select 1 from dual");
}
return dataSource;
}
@Primary
@Bean(name = "masterSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setTypeAliasesPackage("com.ewell.portal.domain");
factoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(mybatisPattern));
return factoryBean.getObject();
}
@Primary
@Bean(name = "masterTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "masterSqlSessionTemplate")
@Primary
public SqlSessionTemplate sqlSessionTemplate(
@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
2.配置文件配置
#mysql数据源
spring.datasource.master.url=jdbc:mysql://192.168.10.74:3306/test?useUnicode=true&characterEncoding=UTF8
spring.datasource.master.username=test
spring.datasource.master.password=test
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.master.driverClassName=com.mysql.cj.jdbc.Driver
#sqlserver数据源
#spring.datasource.master.url=jdbc:jtds:sqlserver://192.168.10.76:1433;DatabaseName=test
#spring.datasource.master.username=test
#spring.datasource.master.password=test
#spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.master.driverClassName=net.sourceforge.jtds.jdbc.Driver
#oracle数据源
#spring.datasource.master.url=jdbc:oracle:thin:@192.168.150.160:1521:test
#spring.datasource.master.username=test
#spring.datasource.master.password=test
#spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.master.driverClassName=oracle.jdbc.driver.OracleDriver
#其他数据源
#spring.datasource.slave.url=jdbc:jtds:sqlserver://192.168.10.76:1433;DatabaseName=test
#spring.datasource.slave.username=test
#spring.datasource.slave.password=test
#spring.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.slave.driverClassName=net.sourceforge.jtds.jdbc.Driver
3.配置第二数据源(或者更多)
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
/**
*
* @Description: 其他数据源配置 mysql sqlServer oracle
* @Author: xyh
* @Date: 2019/8/15 13:33
*/
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.slave")
@MapperScan(basePackages = "com.test.mapper.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
@Value("${spring.datasource.slave.url}")
private String url;
@Value("${spring.datasource.slave.username}")
private String username;
@Value("${spring.datasource.slave.password}")
private String password;
@Value("${spring.datasource.slave.driverClassName}")
private String driverClass;
@Value("${spring.datasource.slave.type}")
private String type;
/**
* mybatis xml路径
*/
private String mybatisPattern;
@Bean(name = "slaveDataSource")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDbType(type);
mybatisPattern = "classpath:mybatis/slave/*.xml";
if (url.contains("sqlserver")) {
dataSource.setValidationQuery("select 1");
} else if (url.contains("oracle")) {
dataSource.setValidationQuery("select 1 from dual");
}
return dataSource;
}
@Bean(name = "slaveSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setTypeAliasesPackage("com.ewell.portal.domain");
factoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(mybatisPattern));
return factoryBean.getObject();
}
@Bean(name = "slaveTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "slaveSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(
@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
4.项目结构
5.maven引用
<!-- mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- oracle驱动包 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<!-- MS SQL驱动包 -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
总结:数据源配置类通过读取配置文件配置,连接数据库。配置类中配置mapper、xml扫描路径,service根据业务逻辑调用不同的数据源的mapper,以此实现该需求。