SpringBoot配置多数据源

最新推荐文章于 2023-07-15 17:39:56 发布

千千千qqq

最新推荐文章于 2023-07-15 17:39:56 发布

阅读量258

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_31906861/article/details/89400176

版权

java 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

1、背景与目的

背景： SpringBoot
目的： 配置多个数据源

2、直接上代码

1、application-dev.properties


#-------------------------- 第一个数据库test1 --------------------------
spring.datasource.druid.test1.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.druid.test1.url = jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&autoReconnect=true&useUnicode=true&characterEncoding=utf-8
spring.datasource.druid.test1.username=root
spring.datasource.druid.test1.password=root

#-------------------------- druid --------------------------
spring.datasource.druid.test1.type=com.alibaba.druid.pool.DruidDataSource
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化连接数量
spring.datasource.druid.test1.initial-size=5
# 最小空闲连接数
spring.datasource.druid.test1.min-idle=5
# 最大并发连接数
spring.datasource.druid.test1.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.test1.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.test1.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.test1.min-evictable-idle-time-millis=300000
# 用来检测连接是否有效的sql，要求是一个查询语句
spring.datasource.druid.test1.validation-query=SELECT 'lll'
# 申请连接的时候检测
spring.datasource.druid.test1.test-while-idle=true
# 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test1.test-on-borrow=false
# 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test1.test-on-return=false
# 是否缓存preparedStatement，也就是PSCache。mysql下关闭
spring.datasource.druid.test1.pool-prepared-statements=false
# 设置MaxOpenPreparedStatements小于Oracle Open_Cursor的数值
spring.datasource.druid.test1.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.test1.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.test1.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

#-------------------------第二个数据库test2-----------------------
spring.datasource.druid.test2.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.druid.test2.url = jdbc:mysql://127.0.0.1:3306/test2?allowMultiQueries=true&autoReconnect=true&useUnicode=true&characterEncoding=utf-8
spring.datasource.druid.test2.username=root
spring.datasource.druid.test2.password=root

logging.level.com.powater.projecttemplate.dao=debug

#-------------------------- druid --------------------------
spring.datasource.druid.test2.type=com.alibaba.druid.pool.DruidDataSource
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化连接数量
spring.datasource.druid.test2.initial-size=5
# 最小空闲连接数
spring.datasource.druid.test2.min-idle=5
# 最大并发连接数
spring.datasource.druid.test2.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.test2.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.test2.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.test2.min-evictable-idle-time-millis=300000
# 用来检测连接是否有效的sql，要求是一个查询语句
spring.datasource.druid.test2.validation-query=SELECT 'lll'
# 申请连接的时候检测
spring.datasource.druid.test2.test-while-idle=true
# 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test2.test-on-borrow=false
# 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test2.test-on-return=false
# 是否缓存preparedStatement，也就是PSCache。mysql下关闭
spring.datasource.druid.test2.pool-prepared-statements=false
# 设置MaxOpenPreparedStatements小于Oracle Open_Cursor的数值
spring.datasource.druid.test2.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.test2.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.test2.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true


#-------------------------- 第三个数据库test3 --------------------------
spring.datasource.druid.test3.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.druid.test3.url = jdbc:mysql://127.0.0.1:3306/test3?allowMultiQueries=true&autoReconnect=true&useUnicode=true&characterEncoding=utf-8
spring.datasource.druid.test3.username=root
spring.datasource.druid.test3.password=root

#-------------------------- druid --------------------------
spring.datasource.druid.test3.type=com.alibaba.druid.pool.DruidDataSource
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化连接数量
spring.datasource.druid.test3.initial-size=5
# 最小空闲连接数
spring.datasource.druid.test3.min-idle=5
# 最大并发连接数
spring.datasource.druid.test3.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.test3.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.test3.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.test3.min-evictable-idle-time-millis=300000
# 用来检测连接是否有效的sql，要求是一个查询语句
spring.datasource.druid.test3.validation-query=SELECT 'lll'
# 申请连接的时候检测
spring.datasource.druid.test3.test-while-idle=true
# 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test3.test-on-borrow=false
# 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能
spring.datasource.druid.test3.test-on-return=false
# 是否缓存preparedStatement，也就是PSCache。mysql下关闭
spring.datasource.druid.test3.pool-prepared-statements=false
# 设置MaxOpenPreparedStatements小于Oracle Open_Cursor的数值
spring.datasource.druid.test3.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.test3.filters=stat,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.test3.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

2、pom.xml

		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

3、创建Test1DataSourceConfig类

@Configuration
@MapperScan(basePackages = {"com.powater.projecttemplate.dao.test11","com.powater.projecttemplate.dao.test1"}, sqlSessionTemplateRef = "Test1SqlSessionTemplate")
public class Test1DataSourceConfig {

	//static  String[] PACKAGE = {"com.powater.projecttemplate.dao.base","com.powater.projecttemplate.dao.tst"};
	static final String MAPPER_LOCATION = "classpath:mapper/*/*.xml";

	@Bean(name = "test1DataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid.test1")
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		List<Filter> filters = new ArrayList<>();
		dataSource.setProxyFilters(filters);

		return dataSource;
	}

	@Bean(name = "Test1TransactionManager")
	public DataSourceTransactionManager setTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "Test1SqlSessionFactory")
	public SqlSessionFactory setSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		return bean.getObject();
	}

	@Bean(name = "Test1SqlSessionTemplate")
	public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("Test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}



	@Bean(name = "Test1WallFilter")
	@DependsOn("Test1WallConfig")
    public WallFilter Test1WallFilter(WallConfig wallConfig) {
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig);
        return wallFilter;
    }

    @Bean(name = "Test1WallConfig")
    public WallConfig Test1WallConfig() {
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
        wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句
        return wallConfig;
    }

}

说明：@MapperScan 中basePackage 接受一个字符串数组，里面可以扫描多个包像这样
basePackages = {"com.powater.projecttemplate.dao.test11","com.powater.projecttemplate.dao.test1"}，

上面代码中

@Bean(name = "Test1WallFilter")
	@DependsOn("Test1WallConfig")
    public WallFilter Test1WallFilter(WallConfig wallConfig) {
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig);
        return wallFilter;
    }

    @Bean(name = "Test1WallConfig")
    public WallConfig Test1WallConfig() {
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
        wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句
        return wallConfig;
    }

说明：这个是为了设置mybatis一个标签可以运行多个sql语句，除了上面的配置，application-dev.properties中也要修改，
一般在数据库连接池的配置中有这样一个配置 spring.datasource.druid.test2.filters=stat,wall,log4j，把wall去掉
再配合上面代码就可以实现。
还有一个要注意的是@ConfigurationProperties(prefix = "spring.datasource.druid.test1")
这个注解prefix中的值要与application-dev.properties 配置属性的前缀相同

其他两个数据库配置类基本一样配置，改动一点点就可以，下面两个数据库没有配置一个标签执行多个sql语句。要配置参照第一个配置类

4、创建Test2DataSourceConfig类

@Configuration
@MapperScan(basePackages = {"com.powater.projecttemplate.dao.test22","com.powater.projecttemplate.dao.test2"}, sqlSessionTemplateRef = "Test2SqlSessionTemplate")
public class Test2DataSourceConfig {

	static final String MAPPER_LOCATION = "classpath:mapper/*/*.xml";

	@Bean(name = "test2DataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid.test2")
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		List<Filter> filters = new ArrayList<>();
		dataSource.setProxyFilters(filters);

		return dataSource;
	}

	@Bean(name = "Test2TransactionManager")
	public DataSourceTransactionManager setTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "Test2SqlSessionFactory")
	public SqlSessionFactory setSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		return bean.getObject();
	}

	@Bean(name = "Test2SqlSessionTemplate")
	public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("Test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

5、创建Test3DataSourceConfig类

@Configuration
@MapperScan(basePackages = {"com.powater.projecttemplate.dao.test33","com.powater.projecttemplate.dao.test3"}, sqlSessionTemplateRef = "Test2SqlSessionTemplate")
public class Test2DataSourceConfig {

	static final String MAPPER_LOCATION = "classpath:mapper/*/*.xml";

	@Bean(name = "test3DataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid.test3")
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		List<Filter> filters = new ArrayList<>();
		dataSource.setProxyFilters(filters);

		return dataSource;
	}

	@Bean(name = "Test3TransactionManager")
	public DataSourceTransactionManager setTransactionManager(@Qualifier("test3DataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "Test3SqlSessionFactory")
	public SqlSessionFactory setSqlSessionFactory(@Qualifier("test3DataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dataSource);
		bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
		return bean.getObject();
	}

	@Bean(name = "Test3SqlSessionTemplate")
	public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("Test3SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}

}

千千千qqq

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot配置多数据源

1、背景与目的背景： SpringBoot目的：配置多个数据源2、直接上代码1、application-dev.properties#-------------------------- 第一个数据库test1 --------------------------spring.datasource.druid.test1.driver-class-name = com.mysql....
复制链接

扫一扫