Springboot MyBatis 配置多数据源和多数据库识别

 

网上配置多数据源与多数据库的文章大多是分开的，同时考虑多数据源和多数据库的情况较少，因为项目中碰到了这种情况，特别记录下来与大家共享。

1. 配置application.yml

spring:
  datasource:
    db1:
      jdbc-url: jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;DatabaseName=xxx
      username: xxx
      password: xxx
      # 使用druid数据源 ,指定其连接池
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      filters: stat
      maxActive: 20
      initialSize: 1
      maxWait: 60000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20
    db2:
      jdbc-url: jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;DatabaseName=xxx
      username: xxx
      password: xxx
      # 使用druid数据源 ,指定其连接池
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      filters: stat
      maxActive: 20
      initialSize: 1
      maxWait: 60000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20

2. 数据库类型识别配置类

config类中配置DatabaseIdProvider

    /**
     * 自动识别使用的数据库类型
     * setProperty("数据库类型","databaseId"),在mapper.xml中databaseId的值就是跟这里对应，
     * 如果没有databaseId选择则说明该sql适用所有数据库
     * */
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle","oracle");
        properties.setProperty("MySQL","mysql");
        properties.setProperty("DB2","db2");
        properties.setProperty("Derby","derby");
        properties.setProperty("H2","h2");
        properties.setProperty("HSQL","hsql");
        properties.setProperty("Informix","informix");
        properties.setProperty("MS-SQL","ms-sql");
        properties.setProperty("SQL Server","sqlserver");
        properties.setProperty("PostgreSQL","postgresql");
        properties.setProperty("Sybase","sybase");
        properties.setProperty("Hana","hana");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

3. 数据源配置类

主数据源：扫描mapper文件夹，放在此文件夹下mapper文件使用db1数据源

@Configuration
@MapperScan(basePackages = "com.inetsoft.mapper", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DataSource1Config {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource db1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource, DatabaseIdProvider databaseIdProvider) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setDatabaseIdProvider(databaseIdProvider);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

从数据源：扫描mapper2文件夹，放在此文件夹下mapper文件使用db2数据源

@Configuration
@MapperScan(basePackages = "com.inetsoft.mapper2", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DataSource2Config {
    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource, DatabaseIdProvider databaseIdProvider) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setDatabaseIdProvider(databaseIdProvider);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper2/*.xml"));
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

 4. 在mapper.xml中使用

方法1

    <select id="queryMaxSort"  resultType="java.lang.Integer" databaseId="mysql">
        select  ifnull(max(sort_),0)+1 sort_ from sys_user
    </select>

    <select id="queryMaxSort"  resultType="java.lang.Integer" databaseId="sqlserver">
        select  isnull(max(sort_),0)+1 sort_ from sys_user
    </select>

    <select id="queryMaxSort"  resultType="java.lang.Integer" databaseId="oracle">
        select  nvl(max(sort_),0)+1 sort_ from sys_user
    </select>

方法2

<select id="queryMaxSort" parameterType="String" resultMap="SuperResultMap">
    SELECT
      <if test="_databaseId == 'mysql'">
          ifnull(max(sort_),0)+1 sort_
      </if>
      <if test="_databaseId == 'sqlserver'">
          isnull(max(sort_),0)+1 sort_
      </if>
      <if test="_databaseId == 'oracle'">
          nvl(max(sort_),0)+1 sort_
      </if>
    from sys_user
  </select>