springBoot + Hikari 配置多数据源连接数据库
springBoot + Hikari 配置多数据源连接数据库
·
springBoot + Hikari 配置多数据源连接数据库
一、修改配置文件 application.yml
server:
port: 8001
spring:
#定义多数据源
datasource-test1:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
dbcp2:
#验证连接的有效性
test-while-idle: true
#验证语句
validation-query: SELECT 1
#空闲连接回收的时间间隔,与test-while-idle一起使用,设置5分钟
time-between-eviction-runs-millis: 300000
#连接池空闲连接的有效时间 ,设置30分钟
soft-min-evictable-idle-time-millis: 1800000
hikari:
#一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired)
# max-lifetime: 60000
#连接池中允许的最大连接数
maximum-pool-size: 15
#<!-- 生效超时 -->
validation-timeout: 3000
#连接只读数据库时配置为true, 保证安全
read-only: false
#等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException
connection-timeout: 60000
#一个连接idle状态的最大时长(毫秒),超时则被释放(retired)
idle-timeout: 60000
# 一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒,参考MySQLwait_timeout参数(show variables like '%timeout%';)
max-lifetime: 120000
datasource-test2:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
dbcp2:
#验证连接的有效性
test-while-idle: true
#验证语句
validation-query: SELECT 1
#空闲连接回收的时间间隔,与test-while-idle一起使用,设置5分钟
time-between-eviction-runs-millis: 300000
#连接池空闲连接的有效时间 ,设置30分钟
soft-min-evictable-idle-time-millis: 1800000
hikari:
#一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired)
# max-lifetime: 60000
#连接池中允许的最大连接数
maximum-pool-size: 15
#<!-- 生效超时 -->
validation-timeout: 3000
#连接只读数据库时配置为true, 保证安全
read-only: false
#等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException
connection-timeout: 60000
#一个连接idle状态的最大时长(毫秒),超时则被释放(retired)
idle-timeout: 60000
# 一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒,参考MySQLwait_timeout参数(show variables like '%timeout%';)
max-lifetime: 120000
#mybatis配置
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
二、创建config文件夹
1.创建 DataSourceConfig
package com.majiameng.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/**
* 数据源配置
*/
@Configuration
public class DataSourceConfig {
@Bean(name="datasourceTest1")
@ConfigurationProperties(prefix = "spring.datasource-test1")
public DataSource datasourceTest1() {
return DataSourceBuilder.create().build();
}
@Bean(name="datasourceTest2")
@ConfigurationProperties(prefix = "spring.datasource-test2")
public DataSource datasourceTest2() {
return DataSourceBuilder.create().build();
}
}
2.创建 MybatisTest1Config
package com.majiameng.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
/**
* 定义test1数据源
*/
@Configuration
@MapperScan(basePackages = {"com.majiameng.mapper.test1"},sqlSessionFactoryRef = "sqlSessionFactoryTest1")
public class MybatisTest1Config {
@Autowired
@Qualifier(value="datasourceTest1")
private DataSource test1;
@Bean
public SqlSessionFactory sqlSessionFactoryTest1() throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(test1);
//指定mapper位置
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
return factory.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplateTest1() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactoryTest1());
return template;
}
@Bean(name = "transactionManagerTest1")
public PlatformTransactionManager transactionManagerTest1() {
return new DataSourceTransactionManager(test1);
}
}
3.创建 MybatisTest2Config
package com.majiameng.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
/**
* 定义test2数据源
*/
@Configuration
@MapperScan(basePackages = {"com.majiameng.mapper.test2"},sqlSessionFactoryRef = "sqlSessionFactoryTest2")
public class MybatisTest2Config {
@Autowired
@Qualifier(value="datasourceTest2")
private DataSource test2;
@Bean
public SqlSessionFactory sqlSessionFactoryTest2() throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(test2);
//指定mapper位置
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));
return factory.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplateTest2() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactoryTest2());
return template;
}
@Bean(name = "transactionManagerTest2")
public PlatformTransactionManager transactionManagerTest2() {
return new DataSourceTransactionManager(test2);
}
}
三、然后对应创建包
com.majiameng.mapper.test1;
com.majiameng.mapper.test2;
resources.mybatis.mapper.test1
resources.mybatis.mapper.test2
四、在启动类上加注解,启动事务
//@ServletComponentScan 注解后,Servlet、Filter、Listener 可以直接通过 @WebServlet、@WebFilter、@WebListener 注解自动注册
@ServletComponentScan
//事务管理注解
@EnableTransactionManagement
五、千万切记:检查连接池配置是否生效
按照上面四步骤做完,你就以为OK了吗?NO,NO,NO,数据库连接多了,立即报错:
HikariPool-1 - Connection is not available, request timed out after 30000ms...
(数据库连接池请求超时)
这都是本博主踩过的坑啊!!! 原因是application.yml
里连接池配置未生效,默认Hikari
的配置了。
检查办法:
MybatisTest1Config
类的sqlSessionFactoryTest1()
方法里第一行,就打印一下DataSource test1
的连接池配置信息!部分示例代码如下:
public SqlSessionFactory sqlSessionFactoryTest1() throws Exception {
HikariDataSource hds = (HikariDataSource ) test1;
System.out.println(hds.getConnectionTimeout()); //打印连接超时时间
....... //打印hds的其他连接池配置信息,我就不写了
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(test1);
//指定mapper位置
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
return factory.getObject();
}
解决办法:
public SqlSessionFactory sqlSessionFactoryTest1() throws Exception {
HikariDataSource hds = (HikariDataSource ) test1;
hds.setConnectionTimeout(600000); //设置连接超时时间
....... //设置hds的其他连接池配置信息,我就不写了
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(test1);
//指定mapper位置
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));
return factory.getObject();
}
最后
多数据源场景中,如果你用到了SqlSession
统一提交,那么你在获取SqlSession
使用时,可能会遇到报错:mapped statements collection does not contain......
是因为默认用的是主数据源的SqlSessionFactory
,获取的是主数据源的SqlSession
,如果你用它调用别的数据源的mapper
接口,肯定报错找不到啊!
如何解决?等我抽时间写一篇《Mybatis多数据源下,SqlSessionFactory的动态切换》
若有帮助,请点赞+关注!
更多推荐
已为社区贡献2条内容
所有评论(0)