Spring Boot + Mybatis 配置多数据源

 

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-mybatis多数据源的两种整合方法

mybatis多数据源踩坑，数据库连接经常断开问题

Springboot多数据源自动断开connection连接问题

springboot下配置mybatis的call-setters-on-nulls属性