springboot和mybatis 配置多数据源

 主数据源（由于代码没有办法复制的原因，下面图片和文字不一致）

 

 

package com.zhianchen.mysqlremark.toword.config;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
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;
 
 
/**
 * @program: test
 * @description:
* @author: chenzhian
 * @create: 2021-10-13 20:31:54
 * @version: 1.0
 **/
@Configuration
// 扫描 Mapper 接口并容器管理
// @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
 
    // 精确到 master 目录，以便跟其他数据源隔离
static final String PACKAGE = "com.zhianchen.mysqlremark.toword.dao";
    static final String MAPPER_LOCATION = "classpath:mapping/master/*.xml";



    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.master.datasource")
    public DataSource masterDataSource() {
        return new HikariDataSource();
    }

    @Primary
    @Bean(name = "masterTransactionManager")
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    /*  可以另外手动指定目录  */
@Bean(name="masterMapperScannerConfigurer")
    public MapperScannerConfigurer masterMapperScannerConfigurer(){
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setSqlSessionFactoryBeanName("masterSqlSessionFactory");
        configurer.setBasePackage(PACKAGE);
        return configurer;
    }
 
}

 

 

 

 第二个数据源（由于代码没有办法复制的原因，下面图片和文字不一致）

 

 

package com.zhianchen.mysqlremark.toword.config;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
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;
 
 
/**
 * @program: test
 * @description:
* @author: chenzhian
 * @create: 2021-10-13 20:31:54
 * @version: 1.0
 **/
@Configuration
// 扫描 Mapper 接口并容器管理
// @MapperScan(basePackages = secondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {
 
 
    // 精确到 second 目录，以便跟其他数据源隔离
static final String PACKAGE = "com.zhianchen.mysqlremark.toword.seconddao";
    static final String MAPPER_LOCATION = "classpath:mapping/second/*.xml";
 

 
    @Bean(name = "secondDataSource")
    @ConfigurationProperties("spring.second.datasource")
    public DataSource secondDataSource() {
        return new HikariDataSource();
    }
 
    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager secondTransactionManager() {
        return new DataSourceTransactionManager(secondDataSource());
    }
 
    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource secondDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(secondDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

  /*  可以另外手动指定目录  */
@Bean(name="secondMapperScannerConfigurer")
  public MapperScannerConfigurer crwalerMapperScannerConfigurer(){
      MapperScannerConfigurer configurer = new MapperScannerConfigurer();
      configurer.setSqlSessionFactoryBeanName("secondSqlSessionFactory");
      configurer.setBasePackage(PACKAGE);
      return configurer;
  }

 
}

 

 

 

application.yml的配置

 

 

# master 数据源配置
master:
  datasource:
    url: jdbc:mysql://localhost:33306/mastertest?serverTimezone=GMT&useUnicode=true&characterEncoding=utf8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: sa
    password: sa!@123
    type: com.zaxxer.hikari.HikariDataSource
    minimum-idle: 5   #最小空闲连接数量
    maximum-pool-size: 50   #连接池中最大连接数
    max-lifetime: 180000  #连接最长生命周期,当连接存活时间达到30分钟之后会被关闭作退休处理
    idle-timeout: 30000   # 连接允许最长空闲时间,如果连接空闲时间超过1分钟,则会被关闭
    connection-timeout: 30000  客户端创建连接等待超时时间,如果30秒内没有获取连接则抛异常,不再继续等待
　　 connection-test-query: SELECT 1
# second 数据源配置
second:
  datasource:
    url: jdbc:mysql://localhost:33306/sencondtest?serverTimezone=GMT&useUnicode=true&characterEncoding=utf8
    username: root
    password: 1q2w#E4r
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    minimum-idle: 5   #最小空闲连接数量
    maximum-pool-size: 50   #连接池中最大连接数
    max-lifetime: 180000  #连接最长生命周期,当连接存活时间达到30分钟之后会被关闭作退休处理
    idle-timeout: 30000   # 连接允许最长空闲时间,如果连接空闲时间超过1分钟,则会被关闭
    connection-timeout: 30000  客户端创建连接等待超时时间,如果30秒内没有获取连接则抛异常,不再继续等待
　　 connection-test-query: SELECT 1

 

更多Hikari的配置请看最后的来源

 

Hikari是SpringBoot2.0以后默认使用的数据库连接池,如果是老的话可能会使用到Druid 用来做数据库连接池,下面是对两个的了解介绍

SpringBoot基础篇(二)连接池hikari和druid

 

HiKariCP和Druid对比使用整理自测

 

mybatis 配置 多个数据源 org.apache.ibatis.binding.BindingException Vakud bound statement (not found)

原因是没有配置@MapperScan ,因为我这里是多个目录，所以也要配置多个，在Application的启动上面加

@MapperScan(value{"com.xxxx","com.aaaa"})

如果上面有用到MapperScannerConfigurer 就不需要这个了。

 

 

The bean 'xxxService' could not be injected as a 'com.xxxxImpl' because it is a JDK dynamic proxy that implements:
com.xxxxService

Action:

Consider injecting the bean as one of its interfaces or forcing the use of CGLib-based proxies by setting proxyTargetClass=true on @EnableAsync and/or @EnableCaching.

原因居然是因为用到是@Resouce 来直接调用ServiceImpl，造成程序加载的时候还没有Service的Impl实现类，让程序加载的时候找不到这个类

解决方案

改成调用Service 接口，另外把@Resouce 改成 @Autowired 就可以了。

 

 

 若偶尔报错no operations allowed after connection closed

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

原因分析：

连接被关闭了，线程池没有进行重新检查，所以打断点看了下dataSource的配置。 

一下是服务器上面的application.yml配置

spring:
  datasource:
    # 数据源-1
    primary:
      url: jdbc:mysql://127.0.0.1:3306/test1?useSSL=false&autoReconnect=true&characterEncoding=utf8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 12345678
      # 指定为HikariDataSource
      type: com.zaxxer.hikari.HikariDataSource
      # hikari连接池配置 对应 HikariConfig 配置属性类
      hikari:
        pool-name: HikariCP-Primary
        #最小空闲连接数
        minimum-idle: 5
        # 空闲连接存活最大时间，默认10分钟
        idle-timeout: 600000
        # 连接池最大连接数，默认是10
        maximum-pool-size: 10
        # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
        auto-commit: true
        # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
        max-lifetime: 1800000
        # 数据库连接超时时间,默认30秒
        connection-timeout: 30000
        # 连接测试query
        connection-test-query: SELECT 1
    # 数据源-2
    secondary:
      url: jdbc:mysql://127.0.0.1:3306/test2?useSSL=false&autoReconnect=true&characterEncoding=utf8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 12345678
      # 指定为HikariDataSource
      type: com.zaxxer.hikari.HikariDataSource
      # hikari连接池配置
      hikari:
        pool-name: HikariCP-Secondary
        minimum-idle: 5
        idle-timeout: 600000
        maximum-pool-size: 10
        auto-commit: true
        max-lifetime: 1800000
        connection-timeout: 30000
        connection-test-query: SELECT 1

 

结果hikari 节点下面的配置都找不到

 

后面改成

spring:
  datasource:
    # 数据源-1
    primary:
      url: jdbc:mysql://127.0.0.1:3306/test1?useSSL=false&autoReconnect=true&characterEncoding=utf8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 12345678
      # 指定为HikariDataSource
      type: com.zaxxer.hikari.HikariDataSource
      # hikari连接池配置 对应 HikariConfig 配置属性类
      pool-name: HikariCP-Primary
      #最小空闲连接数
      minimum-idle: 5
      # 空闲连接存活最大时间，默认10分钟
      idle-timeout: 600000
      # 连接池最大连接数，默认是10
      maximum-pool-size: 10
      # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒
      connection-timeout: 30000
      # 连接测试query
      connection-test-query: SELECT 1
    # 数据源-2
    secondary:
      url: jdbc:mysql://127.0.0.1:3306/test2?useSSL=false&autoReconnect=true&characterEncoding=utf8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 12345678
      # 指定为HikariDataSource
      # hikari连接池配置 对应 HikariConfig 配置属性类
      pool-name: HikariCP-Primary
      #最小空闲连接数
      minimum-idle: 5
      # 空闲连接存活最大时间，默认10分钟
      idle-timeout: 600000
      # 连接池最大连接数，默认是10
      maximum-pool-size: 10
      # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒
      connection-timeout: 30000
      # 连接测试query
      connection-test-query: SELECT 1

去掉hikari的节点，然后把这个对应的子节点前移就行了

 

 

 

资料整理来源

 

SpringBoot和Mybatis配置多数据源连接多个数据库

 

数据库连接池之Hikari源码解析

 

 

 

 

package com.zhianchen.mysqlremark.toword.config;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
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;
 
 
/**
 * @program: test
 * @description:
* @author: chenzhian
 * @create: 2021-10-13 20:31:54
 * @version: 1.0
 **/
@Configuration
// 扫描 Mapper 接口并容器管理
// @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
 
    // 精确到 master 目录，以便跟其他数据源隔离
static final String PACKAGE = "com.zhianchen.mysqlremark.toword.dao";
    static final String MAPPER_LOCATION = "classpath:mapping/master/*.xml";



    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.master-datasource")
    public DataSource masterDataSource() {
        return new HikariDataSource();
    }

    @Primary
    @Bean(name = "masterTransactionManager")
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    /*  可以另外手动指定目录  */
@Bean(name="masterMapperScannerConfigurer")
    public MapperScannerConfigurer masterMapperScannerConfigurer(){
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setSqlSessionFactoryBeanName("masterSqlSessionFactory");
        configurer.setBasePackage(PACKAGE);
        return configurer;
    }
 
}