Java整合多数据源
说明:以access数据库为例,其他数据库一样
1、引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>net.sf.ucanaccess</groupId>
<artifactId>ucanaccess</artifactId>
<version>5.0.1</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
2、配置
## 配置数据源相关信息,读源码可知,自动装配和手动装配,缩进不一致
spring:
datasource:
db1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:ucanaccess://F:/AccessFile/database1.mdb;openExclusive=false;ignoreCase=true
driver-class-name: net.ucanaccess.jdbc.UcanaccessDriver
#初始化大小
initialSize: 5
#最小值
minIdle: 3
#最大值
maxActive: 20
#最大等待时间,配置获取连接等待超时,时间单位都是毫秒ms
maxWait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接
timeBetweenEvictionRunsMillis: 60000
#配置一个连接在池中最小生存的时间
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,
#'wall'用于防火墙,SpringBoot中没有log4j,用log4j2
# filters: stat,wall,log4j2
# 最大PSCache连接
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
db2:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:ucanaccess://F:/AccessFile/datamdb.mdb;openExclusive=false;ignoreCase=true
driver-class-name: net.ucanaccess.jdbc.UcanaccessDriver
#初始化大小
initialSize: 5
#最小值
minIdle: 3
#最大值
maxActive: 20
#最大等待时间,配置获取连接等待超时,时间单位都是毫秒ms
maxWait: 60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接
timeBetweenEvictionRunsMillis: 60000
#配置一个连接在池中最小生存的时间
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,
#'wall'用于防火墙,SpringBoot中没有log4j,用log4j2
# filters: stat,wall,log4j2
# 最大PSCache连接
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
3、手动装配
数据源1:DataSourceOneConfig
package com.access.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import lombok.extern.slf4j.Slf4j;
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.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;
/**
* 数据源1
*
* @author 微笑
* @version 1.0
* @since 2023/3/3 15:22
*/
@Slf4j
@Configuration
//下面的sqlSessionTemplateRef 值需要和生成的SqlSessionTemplate bean name相同,如果没有指定name,那么就是方法名
@MapperScan(basePackages = {"com.access.dao.one"}, sqlSessionTemplateRef = "sqlSessionTemplateOne")
public class DataSourceOneConfig {
/**
* mapper文件的相对路径
*/
private static final String MAPPER_LOCATION = "classpath:com/access/mapper/one/*Mapper.xml";
private static final String TYPE_ALIASES_PACKAGE = "com.access.entity";
@Primary
@Bean(name = "datasourceOne")
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource datasourceOne() {
return DruidDataSourceBuilder.create().build();
}
@Bean
public SqlSessionFactory sqlSessionFactoryOne(@Qualifier("datasourceOne") DataSource dataSource) throws Exception {
//在基本的 MyBatis 中,session 工厂可以使用 SqlSessionFactoryBuilder 来创建。
// 而在 MyBatis-spring 中,则使用SqlSessionFactoryBean 来替代:
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//如果重写了 SqlSessionFactory 需要在初始化的时候手动将 mapper 地址 set到 factory 中,否则会报错:
//org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
bean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
configuration.setMapUnderscoreToCamelCase(true);
bean.setConfiguration(configuration);
return bean.getObject();
}
/**
* SqlSessionTemplate 是 SqlSession接口的实现类,是spring-mybatis中的,实现了SqlSession线程安全
*/
@Bean
public SqlSessionTemplate sqlSessionTemplateOne(@Qualifier("sqlSessionFactoryOne") SqlSessionFactory sqlSessionFactory) {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory);
return template;
}
/**
* 为选中的数据源 datasourceOne 添加事务管理
*
* @param dataSource
* @return transactionManagerOne service层@Transactional(value引用的就是这里的值)
*/
@Bean
public DataSourceTransactionManager transactionManagerOne(@Qualifier("datasourceOne") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
数据源2:DataSourceTwoConfig
package com.access.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import lombok.extern.slf4j.Slf4j;
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.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
/**
* 数据源2
*
* @author 微笑
* @version 1.0
* @since 2023/3/3 15:22
*/
@Slf4j
@Configuration
//下面的sqlSessionTemplateRef 值需要和生成的SqlSessionTemplate bean name相同,如果没有指定name,那么就是方法名
@MapperScan(basePackages = {"com.access.dao.two"}, sqlSessionTemplateRef = "sqlSessionTemplateTwo")
public class DataSourceTwoConfig {
/**
* mapper文件的相对路径
*/
private static final String MAPPER_LOCATION = "classpath:com/access/mapper/two/*Mapper.xml";
private static final String TYPE_ALIASES_PACKAGE = "com.access.entity";
@Bean(name = "datasourceTwo")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource datasourceTwo() {
return DruidDataSourceBuilder.create().build();
}
@Bean
public SqlSessionFactory sqlSessionFactoryTwo(@Qualifier("datasourceTwo") DataSource dataSource) throws Exception {
//在基本的 MyBatis 中,session 工厂可以使用 SqlSessionFactoryBuilder 来创建。
// 而在 MyBatis-spring 中,则使用SqlSessionFactoryBean 来替代:
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//如果重写了 SqlSessionFactory 需要在初始化的时候手动将 mapper 地址 set到 factory 中,否则会报错:
//org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
bean.setTypeAliasesPackage(TYPE_ALIASES_PACKAGE);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
configuration.setMapUnderscoreToCamelCase(true);
bean.setConfiguration(configuration);
return bean.getObject();
}
/**
* SqlSessionTemplate 是 SqlSession接口的实现类,是spring-mybatis中的,实现了SqlSession线程安全
*/
@Bean
public SqlSessionTemplate sqlSessionTemplateTwo(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sqlSessionFactory) {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory);
return template;
}
/**
* 为选中的数据源 datasourceOne 添加事务管理
*
* @param dataSource
* @return transactionManagerOne service层@Transactional(value引用的就是这里的值)
*/
@Bean
public DataSourceTransactionManager transactionManagerTwo(@Qualifier("datasourceTwo") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}