springboot多数据源配置和配置mybatis支持多种数据库

具体项目代码见github

项目结构

 

yml配置

server:
  port: 8091
spring:
  datasource:
    comm:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://****/*?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
      username: *
      password: *
      commMapperLocations: classpath:com/asiainfo/comm/**/mapper/*.xml
      configLocation: classpath:mybatis/mybatis-config.xml
    settle:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@****:1521:*
      username: *
      password: *
      settleMapperLocations: classpath:com/asiainfo/settle/**/mapper/*.xml
      configLocation: classpath:mybatis/mybatis-config.xml

logging:
  level:
    root: info
    com.asiainfo: debug

启动类（无需配置，会自动扫描同级包及子包下的组件）

@SpringBootApplication
public class SpringbootDatasourceApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootDatasourceApplication.class, args);
    }

}

数据源配置类（不同数据源扫描不同包）

注意：多个同类型bean要使用@Primary指定优先选择，并指定名称name进行区分

主数据源

@Configuration
@MapperScan(basePackages = {"com.asiainfo.comm.**.dao"},sqlSessionFactoryRef = "commSqlSessionFactory")
public class CommDruidDataSourceConfig {

    @Value("${spring.datasource.comm.commMapperLocations}")
    private String commMapperLocations ;

    @Value("${spring.datasource.comm.configLocation}")
    private String configLocation;

    @ConfigurationProperties(prefix = "spring.datasource.comm")

    @Bean(name = "commDataSource")
    @Primary
    public DataSource commDataSource() {
        return new DruidDataSource();
    }

    @Bean("commJdbcTemplate")
    @Primary
    public JdbcTemplate template(@Qualifier(value = "commDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }


    /**
     * 自动识别使用的数据库类型
     * 在mapper.xml中databaseId的值就是跟这里对应，
     * 如果没有databaseId选择则说明该sql适用所有数据库
     * */
    @Bean(name = "commDatabaseIdProvider")
    @Primary
    public DatabaseIdProvider getDatabaseIdProvider() {
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle", "oracle");
        properties.setProperty("MySQL", "mysql");
        properties.setProperty("H2", "h2");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

    /**
     * SqlSessionFactory配置
     *
     * @return
     * @throws Exception
     */
    @Bean(name = "commSqlSessionFactory")
    @Primary
    public SqlSessionFactory commSqlSessionFactory(@Qualifier("commDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        //自动识别 支持多种数据库关键代码
        sqlSessionFactoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
        // 配置mapper文件位置
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(commMapperLocations));
        sqlSessionFactoryBean.setConfigLocation(resolver.getResource(configLocation));
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 配置事物管理器
     *
     * @return
     */
    @Bean(name = "commTransactionManager")
    @Primary
    public DataSourceTransactionManager commTransactionManager(@Qualifier("commDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    } 
}

从数据源

@Configuration
@MapperScan(basePackages = {"com.asiainfo.settle.**.dao"},sqlSessionFactoryRef = "settleSqlSessionFactory")
public class SettleDruidDataSourceConfig {

    @Value("${spring.datasource.settle.settleMapperLocations}")
    private String settleMapperLocations;

    @Value("${spring.datasource.settle.configLocation}")
    private String configLocation;

    @ConfigurationProperties(prefix = "spring.datasource.settle")
    @Bean(name = "settleDataSource")
    public DataSource settleDataSource() {
        return new DruidDataSource();
    }


    @Bean("settleJdbcTemplate")
    @Primary
    public JdbcTemplate template(@Qualifier(value = "settleDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * 自动识别使用的数据库类型
     * 在mapper.xml中databaseId的值就是跟这里对应，
     * 如果没有databaseId选择则说明该sql适用所有数据库
     * */
    @Bean(name = "settleDatabaseIdProvider")
    public DatabaseIdProvider getDatabaseIdProvider() {
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle", "oracle");
        properties.setProperty("MySQL", "mysql");
        properties.setProperty("H2", "h2");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

    /**
     * SqlSessionFactory配置
     *
     * @return
     * @throws Exception
     */
    @Bean(name = "settleSqlSessionFactory")
    public SqlSessionFactory settleSqlSessionFactory(@Qualifier("settleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        //自动识别 支持多种数据库关键代码
        sqlSessionFactoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
        // 配置mapper文件位置
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources(settleMapperLocations));
        sqlSessionFactoryBean.setConfigLocation(resolver.getResource(configLocation));
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 配置事物管理器
     *
     * @return
     */
    @Bean(name = "settleTransactionManager")
    public DataSourceTransactionManager settleTransactionManager(@Qualifier("settleDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}

Mapper文件中

使用databaseId配置支持多种数据库

<mapper namespace="com.asiainfo.comm.dao.TestDao1">
    
    <select id="getData" resultType="java.util.HashMap" databaseId="mysql">
        select  * from stt_privilege 
    </select>

    <select id="getData" resultType="java.util.HashMap" databaseId="oracle">
        select  * from stt_privilege
    </select>

</mapper>

附记

项目搭建时出现启动报错，数据源配置类中无法读取yml中数据源配置，排查发现yml没有输出到target文件夹classes目录下

原因：pom文件中使用了includes标签(注释2)，指定resources目录下编译输出包含xml文件，但未指定yml文件(如下)，可去除此配置，默认包含所有。注释1部分去除会导致src/java目录下xml文件未编译输出到target下classes目录下，造成Invalid bound statement异常，如果mapper文件时放置在resource目录下则不需要此设置，具体问题要根据项目结构进行配置解决

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
    <resources>
     <!--注释1-->
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
     <!--注释2-->
        <!--<resource>-->
              <!--<directory>src/main/resources</directory>-->
              <!--<includes>-->
                  <!--<include>**/*.xml</include>-->
                <!--</includes>-->
                <!--<filtering>false</filtering>-->
        <!--</resource>-->
    </resources>
</build>

另有时指定了xml文件目录时却未生效，如下

spring:
  datasource:
    comm:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://*:8454/*?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
      username: *
      password: *
      commMapperLocations: classpath:com/asiainfo/comm/**/mapper/*.xml
      configLocation: classpath:mybatis/mybatis-config.xml

解决：此时可指定为classpath*