Spring Boot + Mybatis 配置多数据源
- Mybatis拦截器，字段名大写转小写
package com.sgcc.tysj.s.common.mybatis; import java.sql.Statement; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.ibatis.executor.resultset.ResultSetHandler; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.springframework.stereotype.Component; @Component @Intercepts({ @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) }) public class MybatisResultSetSIntercept implements Interceptor { public Object intercept(Invocation invocation) throws Throwable { Object result = invocation.proceed(); //执行请求方法，并将所得结果保存到result中 if (result instanceof ArrayList) { ArrayList<?> resultList = (ArrayList<?>) result; if(resultList.size()>0&&resultList.get(0) instanceof Map ) { List<Map<String,Object>> resList = new ArrayList<Map<String,Object>>(); for (int i = 0; i < resultList.size(); i++) { if (resultList.get(i) instanceof Map) { Map<String, Object> nmap=new LinkedHashMap<String, Object>(); @SuppressWarnings("unchecked") Map <String, Object>map = (Map<String, Object>) resultList.get(i); for ( String key: map.keySet()) nmap.put(key.toLowerCase(), map.get(key)); resList.add(nmap); } } return resList; } } return result; } public Object plugin(Object target) { // 读取@Signature中的配置，判断是否需要生成代理类 if (target instanceof ResultSetHandler) { return Plugin.wrap(target, this); } else { return target; } } public void setProperties(Properties properties) { } }
- 数据源一
package com.sgcc.tysj.p.pinggao.config; import com.sgcc.tysj.s.common.mybatis.MybatisResultSetSIntercept; import org.apache.ibatis.plugin.Interceptor; 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.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: 默认数据源 * @author: tangsw * @date: 2019/11/18 23:20 */ // 表示这个类为一个配置类 @Configuration // 配置mybatis的接口类放的地方 @MapperScan(basePackages = "com.sgcc.tysj.p.pinggao.**.dao", sqlSessionTemplateRef = "db1SqlSessionTemplate") public class DatabaseConfigForSystem { @Value("${spring.datasource.system.driver-class-name}") private String driverClassName; @Value("${spring.datasource.system.url}") private String url; @Value("${spring.datasource.system.username}") private String username; @Value("${spring.datasource.system.password}") private String password; @Value("${spring.datasource.system.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.system.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.system.validation-query}") private String validationQuery; @Value("${spring.datasource.system.maxIdle}") private int maxIdle; @Value("${spring.datasource.system.minIdle}") private int minIdle; @Value("${spring.datasource.system.initialSize}") private int initialSize; @Value("${spring.datasource.system.maxActive}") private int maxActive; @Value("${spring.datasource.system.timeBeteenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.system.minEvictableIdelTimeMillis}") private int minEvictableIdelTimeMillis; @Value("${spring.datasource.system.maxWait}") private int maxWait; @Autowired private MybatisResultSetSIntercept mybatisResultSetSIntercept; // 将这个对象放入Spring容器中 @Bean(name = "db1DataSource") // 表示这个数据源是默认数据源 @Primary // 读取application.properties中的配置参数映射成为一个对象，prefix表示参数的前缀 @ConfigurationProperties(prefix = "spring.datasource.system") public DataSource getDateSource1() { org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(); dataSource.setDriverClassName(driverClassName); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setMaxActive(maxActive); dataSource.setMinIdle(minIdle); dataSource.setMaxIdle(maxIdle); dataSource.setTestOnBorrow(testOnBorrow); dataSource.setTestWhileIdle(testWhileIdle); dataSource.setValidationQuery(validationQuery); dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); dataSource.setMinEvictableIdleTimeMillis(minEvictableIdelTimeMillis); dataSource.setInitialSize(initialSize); dataSource.setMaxWait(maxWait); return dataSource; } @Bean(name = "db1SqlSessionFactory") // 表示这个数据源是默认数据源 @Primary // @Qualifier表示查找Spring容器中名字为 db1DataSource 的对象 public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource datasource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration(); // 如果字段值为空，返回null configuration.setCallSettersOnNulls(true); bean.setConfiguration(configuration); // 调用 Mybatis 拦截器 bean.setPlugins(new Interceptor[]{mybatisResultSetSIntercept}); bean.setDataSource(datasource); bean.setMapperLocations( // 设置mybatis的xml所在位置 new PathMatchingResourcePatternResolver().getResources("classpath:mappings/tysj/p/pinggao/*/*.xml")); return bean.getObject(); } /** * 配置事务管理 */ @Bean(name = "db1TransactionManager") @Primary public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "db1SqlSessionTemplate") @Primary public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
- 数据源二
package com.sgcc.tysj.p.pinggao.config; import com.sgcc.tysj.s.common.mybatis.MybatisResultSetSIntercept; import org.apache.ibatis.plugin.Interceptor; 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.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.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; /** * @Description: WebService接口使用此数据源 * @author: tangsw * @date: 2019/11/18 23:20 */ // 表示这个类为一个配置类 @Configuration // 配置mybatis的接口类放的地方 @MapperScan(basePackages = "com.sgcc.tysj.p.pinggao_ws.erp.dao", sqlSessionTemplateRef = "db2SqlSessionTemplate") public class DatabaseConfigForWebService { @Value("${spring.datasource.webService.driver-class-name}") private String driverClassName; @Value("${spring.datasource.webService.url}") private String url; @Value("${spring.datasource.webService.username}") private String username; @Value("${spring.datasource.webService.password}") private String password; @Value("${spring.datasource.webService.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.webService.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.webService.validation-query}") private String validationQuery; @Value("${spring.datasource.webService.maxIdle}") private int maxIdle; @Value("${spring.datasource.webService.minIdle}") private int minIdle; @Value("${spring.datasource.webService.initialSize}") private int initialSize; @Value("${spring.datasource.webService.maxActive}") private int maxActive; @Value("${spring.datasource.webService.timeBeteenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.webService.minEvictableIdelTimeMillis}") private int minEvictableIdelTimeMillis; @Value("${spring.datasource.webService.maxWait}") private int maxWait; @Autowired private MybatisResultSetSIntercept mybatisResultSetSIntercept; // 将这个对象放入Spring容器中 @Bean(name = "db2DataSource") // 表示这个数据源是默认数据源 // 读取application.properties中的配置参数映射成为一个对象，prefix表示参数的前缀 @ConfigurationProperties(prefix = "spring.datasource.webService") public DataSource getDateSource2() { org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource(); dataSource.setDriverClassName(driverClassName); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setMaxActive(maxActive); dataSource.setMinIdle(minIdle); dataSource.setMaxIdle(maxIdle); dataSource.setTestOnBorrow(testOnBorrow); dataSource.setTestWhileIdle(testWhileIdle); dataSource.setValidationQuery(validationQuery); dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); dataSource.setMinEvictableIdleTimeMillis(minEvictableIdelTimeMillis); dataSource.setInitialSize(initialSize); dataSource.setMaxWait(maxWait); return dataSource; } @Bean(name = "db2SqlSessionFactory") // 表示这个数据源是默认数据源 // @Qualifier表示查找Spring容器中名字为 db2DataSource 的对象 public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource datasource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration(); // 如果字段值为空，返回null configuration.setCallSettersOnNulls(true); bean.setConfiguration(configuration); // Mybatis拦截器 bean.setPlugins(new Interceptor[]{mybatisResultSetSIntercept}); bean.setDataSource(datasource); bean.setMapperLocations( // 设置mybatis的xml所在位置 new PathMatchingResourcePatternResolver().getResources("classpath:mappings/tysj/p/pinggao_ws/erp/*.xml")); return bean.getObject(); } /** * 配置事务管理 */ @Bean(name = "db2TransactionManager") public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "db2SqlSessionTemplate") public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
- 连接池配置参数：
#------------------------------------WebService 接口使用此数据源配置--------------------------------- spring.datasource.webService.url=jdbc:oracle:thin:@192.168.10.10:1521:orcl spring.datasource.webService.username=oracle spring.datasource.webService.password=oracle spring.datasource.webService.driver-class-name=oracle.jdbc.OracleDriver #最大连接数据库连接数,设 0 为没有限制 spring.datasource.webService.maxActive=20 #初始化连接数 spring.datasource.webService.initialSize=1 #最大等待毫秒数, 单位为 ms, 超过时间会出错误信息 spring.datasource.webService.maxWait=60000 #最小空闲连接 spring.datasource.webService.minIdle=1 #最大空闲连接 spring.datasource.webService.maxIdle=20 ##如果当前连接池中某个连接在空闲了timeBetweenEvictionRunsMillis时间后任然没有使用，则被物理性的关闭掉。 spring.datasource.webService.timeBeteenEvictionRunsMillis=60000 #配置一个连接在池中最小生存的时间，单位是毫秒 spring.datasource.webService.minEvictableIdelTimeMillis=300000 #在获取Connection对象时检测其可用性 spring.datasource.webService.test-on-borrow=true #保证从连接池中得到的Connection对象是可用的 spring.datasource.webService.test-while-idle=true #验证数据库连接的有效性 spring.datasource.webService.validation-query=select 1 from dual
- 遇到问题：
1.不调用拦截器代码；
2.查询数据库数据，如果值为空，字段名也不会查询出来；
3.系统闲置一段时间不访问就提示“ 关闭的连接”。
- 参考文章：
Springboot多数据源自动断开connection连接问题
springboot下配置mybatis的call-setters-on-nulls属性