最近在工作中遇到了一个业务,跑job时,不同的任务需要分别向不同的数据库插入数据,因此需要配置多个数据源,避免了搭两套服务
多数据源的实现原理:通过@MapperScan注解扫描不同目录下的DAO文件和mapper.xml文件,实现不同的目录的dao,创建不同的sqlSession实例,从而创建两个JdbcTemplate
的Bean,分别使用这两个数据源从而实现操作不同的库
首先创建 DataSource,然后创建 SqlSessionFactory 再创建事务,最后包装到 SqlSessionTemplate 中,。其中需要指定分库的 mapper 文件地址,以及分库dao层代码
项目背景:springBoot2.0 +mybatis+mysql5.6+Druid的连接池
1、禁用掉spring boot的自动配置
首先要将自己项目的spring boot自带的DataSourceAutoConfiguration禁掉,因为它会读取application.properties文件的spring.datasource.*属性并自动配置单数据源。在@SpringBootApplication注解中添加exclude属性即可:
@EnableDiscoveryClient
@EnableFeignClients
@SpringBootApplication(scanBasePackages = "com.rept.*",exclude = DataSourceAutoConfiguration.class)
@EnableCircuitBreaker
public class ReptApplication {
public static void main(String[] args) {
SpringApplication.run(ReptApplication.class, args);
}
}
2、 添加多数据源的application.yml配置
在原有的application.yml配置中加上另一个数据源的配置
report1: datasource: driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://mysql-dev.XXXX.com:3306/dev1?useUnicode=true&characterEncoding=utf8 username: dev1 password: pasworddev1 initialSize: 10 minIdle: 10 maxActive: 50 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 'X' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: false maxPoolPreparedStatementPerConnectionSize: -1 filters: stat redis: host: redis-dev.****.com port: 6379 database: 6 password: pasword123 timeout: 2000 cachetime: 600 pool: maxActive: 80 maxWait: 2000 maxIdle: 40 minIdle: 0 report2: datasource: driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://mysql-dev.XXXX.com:3306/dev2?useUnicode=true&characterEncoding=utf8 username: dev2 password: pasworddev2 initialSize: 10 minIdle: 10 maxActive: 50 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 'X' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: false maxPoolPreparedStatementPerConnectionSize: -1 filters: stat
3、自定义数据源的配置类,通过@value属性注入的方式,注入dataSource的配置
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingClass;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* 用于正常情况下非自定义数据源<br/>
* 在微服务的配置文件中没有关于数据源的配置时根据主配置文件中的配置创建<br/>
* 如果微服务中配置的有自定义分表数据源则不创建datasource。
*/
@RefreshScope
@Configuration
@Slf4j
public class Report1DruidDataSourceConfig {
//用于默认数据源时
@Value("${report1.datasource.url}")
private String dbUrl;
@Value("${report1.datasource.username}")
private String username;
@Value("${report1.datasource.password}")
private String password;
@Value("${report1.datasource.driverClassName}")
private String driverClassName;
@Value("${report1.datasource.initialSize}")
private int initialSize;
@Value("${report1.datasource.minIdle}")
private int minIdle;
@Value("${report1.datasource.maxActive}")
private int maxActive;
@Value("${report1.datasource.maxWait}")
private int maxWait;
@Value("${report1.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${report1.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${report1.datasource.validationQuery}")
private String validationQuery;
@Value("${report1.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${report1.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${report1.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${report1.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${report1.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${report1.datasource.filters}")
private String filters;
/**
* 如果使用分表分库数据源则不再自动初始化数据源
* @return DataSource
*/
@Bean("reportOneDataSource")
@ConditionalOnMissingClass("com.baomidou.dynamic.datasource.DynamicGroupDataSource")
@Primary // 该配置是默认加载该数据源,若没有指定的使用该数据源即可
public DataSource reportDataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setDriverClassName(driverClassName);
datasource.setUrl(dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
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);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
System.err.println("=======================================================");
System.err.println("Report1 数据源: "+datasource.getUrl());
System.err.println("=======================================================");
try {
datasource.setFilters(filters);
} catch (SQLException e) {
log.error("druid configuration initialization filter", e);
}
return datasource;
}
@Bean(name = "reportOneJdbcTemplate")
@ConditionalOnMissingClass("com.baomidou.dynamic.datasource.DynamicGroupDataSource")
public JdbcTemplate getReportJdbcTemplate(){
return new JdbcTemplate(reportDataSource());
}
}
4、增加MyBatistConfig类创建SqlSessionFactory,得到不同的SqlSessionTemplate,从而通过不同的sqlSession去操作不同的数据库
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.annotation.Resource;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.rept.common.reportDao"},sqlSessionFactoryRef = "reportSqlSessionFactory")
@Slf4j
public class ReportMyBatistConfig {
@Resource
DataSource reportDataSource;
@Value("${mybatis.typeAliasesPackage:com.rept.*.entity,com.rept.*.request,com.rept.*.response}")
String typeAliasesPackage;
@Value("${mybatis.mapperLocations:reportMapper/*.xml}")
String mapperLocations;
@Bean(name = "reportOneSqlSessionFactory")
public SqlSessionFactory reportSqlSessionFactory() {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(reportDataSource);
bean.setTypeAliasesPackage(typeAliasesPackage);
//添加XML目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath*:" + mapperLocations));
bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
return bean.getObject();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
@Bean("reportOneSqlSessionTemplate")
public SqlSessionTemplate reportSqlSessionTemplate(@Qualifier("reportOneSqlSessionFactory") SqlSessionFactory reportSqlSessionFactory) {
return new SqlSessionTemplate(reportSqlSessionFactory);
}
@Bean(name = "reportOneTransactionManager")
public DataSourceTransactionManager reportTransactionManager(@Qualifier("reportOneDataSource") DataSource reportDataSource) {
return new DataSourceTransactionManager(reportDataSource);
}
}
5、同理你可以创建第二个数据源的ReportSecMyBatistConfig和ReportSecDruidDataSourceConfig
注意事项:
1、默认的数据源一定要加上@Primary注解,因为不加的话spring将分不清楚那个为主数据源(默认数据源),另外的就不用加了
2、setMapperLocations()一定要设置,mapper的xml形式文件位置必须要配置,不然将报错
3、service逻辑需要剥离开了,在service层中根据不同的业务注入不同的dao层,如果是分布式结构的话,不同模块操作各自的数据库就好,如果掺杂着两个数据源的编辑操作的话,这时候可能会产生事务问题.