SpringBoot中配置mybatis多數(shù)據(jù)源

多數(shù)據(jù)源配置

背景

在日常業(yè)務(wù)開發(fā)中，偶爾還是會(huì)遇到多數(shù)據(jù)源配置需求的項(xiàng)目，比如在業(yè)務(wù)系統(tǒng)遷移時(shí)，需要將SQLServer數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)入MySQL數(shù)據(jù)庫(kù)中。再比如在我經(jīng)過的項(xiàng)目中，有靜態(tài)化網(wǎng)站數(shù)據(jù)庫(kù)和搜索引擎數(shù)據(jù)庫(kù)兩個(gè)數(shù)據(jù)源。

如何配置

在數(shù)據(jù)訪問中包含數(shù)據(jù)源、事務(wù)管理器、會(huì)話管理工廠、會(huì)話模板、mybatis配置文件、mybatis數(shù)據(jù)庫(kù)腳本映射文件(SQL Map)。其他的常用配置，比如駝峰命名映射配置(mybatis.configuration.map-underscore-to-camel-case)、類型別名配置（mybatis.type-aliases-package）、類型處理器配置(mybatis.type-handlers-package)等，不管是單數(shù)據(jù)源場(chǎng)景還是多數(shù)據(jù)源場(chǎng)景都建議統(tǒng)一在mybatis.xml配置文件中進(jìn)行集中配置管理。

  1. 在基類中配置數(shù)據(jù)源
@Slf4j
public class BaseDataSourceConfig {

    @Value("${spring.datasource.driverClassName}")
    protected String driverClass;

    @Value("${spring.datasource.druid.filters}")
    protected String filters;

    @Value("${spring.datasource.druid.initialSize}")
    protected Integer initialSize;

    @Value("${spring.datasource.druid.minIdle}")
    protected Integer minIdle;

    @Value("${spring.datasource.druid.maxActive}")
    protected Integer maxActive;

    @Value("${spring.datasource.druid.maxWait}")
    protected Integer maxWait;

    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    protected Integer timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    protected Integer minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.validationQuery}")
    protected String validationQuery;

    @Value("${spring.datasource.druid.testWhileIdle}")
    protected Boolean testWhileIdle;

    @Value("${spring.datasource.druid.testOnBorrow}")
    protected Boolean testOnBorrow;

    @Value("${spring.datasource.druid.testOnReturn}")
    protected Boolean testOnReturn;

    @Value("${spring.datasource.druid.poolPreparedStatements}")
    protected Boolean poolPreparedStatements;

    @Value("${spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
    protected Integer maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.druid.connectionProperties}")
    protected Properties connectionProperties;

    /**
     * 初始化
     *
     * @return DataSource
     */
    protected DruidDataSource initDataSource(String url, String userName, String password) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setMaxActive(maxActive);
        dataSource.setMinIdle(minIdle);
        dataSource.setInitialSize(initialSize);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        dataSource.setConnectProperties(connectionProperties);
        try {
            dataSource.setFilters(filters);
        } catch (SQLException e) {
            log.error("BaseDataSourceConfig error", e);
        }
        return dataSource;
    }

    /**
     * 初始化SessionFactory
     *
     * @param dataSource          數(shù)據(jù)源
     * @param configLocation      配置文件地址
     * @param mapperLocations     mapper地址
     * @return SqlSessionFactory
     * @throws Exception 異常
     */
    public SqlSessionFactory initSessionFactory(DataSource dataSource, String configLocation, String mapperLocations)
            throws Exception {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(resolver.getResources(mapperLocations));
        sessionFactory.setVfs(SpringBootVFS.class);
        sessionFactory.setConfigLocation(resolver.getResource(configLocation));
        return sessionFactory.getObject();
    }
  1. 擴(kuò)展基類，創(chuàng)建第一個(gè)數(shù)據(jù)源
@Configuration
@MapperScan(basePackages = OwDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "owSqlSessionFactory")
public class OwDataSourceConfig extends BaseDataSourceConfig {
    static final String PACKAGE = "com.xxx.ow.dao";

    @Value("${ow.mybatis.mapper-locations}")
    private String mapperLocations;

    @Value("${ow.mybatis.config-location}")
    private String configLocation;

    @Value("${ow.datasource.url}")
    private String url;

    @Value("${ow.datasource.username}")
    private String userName;

    @Value("${ow.datasource.password}")
    private String password;

    @Bean(name = "owDataSource")
    @Primary
    public DataSource owDataSource() {
        return initDataSource(url, userName, password);
    }

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

    @Bean(name = "owSqlSessionFactory")
    @Primary
    public SqlSessionFactory owSqlSessionFactory(@Qualifier("owDataSource") DataSource dataSource)
            throws Exception {
        return initSessionFactory(dataSource, configLocation, mapperLocations);
    }
    @Primary
    @Bean(name = "owSqlSessionTemplate")
    public SqlSessionTemplate owSqlSessionTemplate(
            @Qualifier("owSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
  1. 擴(kuò)展基類，創(chuàng)建第二個(gè)數(shù)據(jù)源
@Configuration
@MapperScan(basePackages = SearchDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "searchSqlSessionFactory")
public class SearchDataSourceConfig extends BaseDataSourceConfig {
   static final String PACKAGE = "com.xxx.ow.search.dao";
   @Value("${search.mybatis.mapper-locations}")
   private String mapperLocations;

   @Value("${search.mybatis.config-location}")
   private String configLocation;

   @Value("${search.datasource.url}")
   private String url;

   @Value("${search.datasource.username}")
   private String userName;

   @Value("${search.datasource.password}")
   private String password;

   @Bean(name = "searchDataSource")
   public DataSource searchDataSource() {
       return initDataSource(url, userName, password);
   }

   @Bean(name = "searchTransactionManager")
   public DataSourceTransactionManager masterTransactionManager() {
       return new DataSourceTransactionManager(searchDataSource());
   }

   @Bean(name = "searchSqlSessionFactory")
   public SqlSessionFactory masterSqlSessionFactory(@Qualifier("searchDataSource") DataSource dataSource)
           throws Exception {
       return initSessionFactory(dataSource, configLocation, mapperLocations, typeAliasesPackage, typeHandlersPackage);
   }

   @Bean(name = "searchSqlSessionTemplate")
   public SqlSessionTemplate searchSqlSessionTemplate(
           @Qualifier("searchSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
       return new SqlSessionTemplate(sqlSessionFactory);
   }
}
  1. 在application.properties中增加配置
    配置每一個(gè)數(shù)據(jù)源的config-location和mapper-locations。
ow.mybatis.config-location=mybatis.xml
ow.mybatis.mapper-locations=classpath*:/com/xxx/ow/mapper/**/*.xml

search.mybatis.config-location=mybatis-search.xml
search.mybatis.mapper-locations=classpath*:/com/xxx/ow/search/mapper/**/*.xml

在mybatis.xml配置文件中配置更多選項(xiàng)，比如駝峰命名映射配置(mybatis.configuration.map-underscore-to-camel-case)、類型別名配置（mybatis.type-aliases-package）、類型處理器配置(mybatis.type-handlers-package)，如下所示

<!DOCTYPE configuration
    PUBLIC "-//config.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.xxx.ow.entity"/>
        <package name="com.xxx.ow.enums"/>
        <package name="com.xxx.ow.dto"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="com.xxx.ow.enums.typehandler.UniversalEnumHandler"
                     javaType="com.xxx.ow.enums.ArticleOfProclamationEnum"/>
    </typeHandlers>
</configuration>

第二種方式

還有一種簡(jiǎn)單的方法是使用SpringBoot的注入數(shù)據(jù)源配置屬性，比如DruidDataSource。比如像下面這樣：

@Configuration
@MapperScan(basePackages = {"com.xxx.dataimport.dao.mysql"}, sqlSessionFactoryRef = "mySqlSessionFactory")
public class MySqlConfig {

    @Value("${spring.datasource.mysql.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.mysql.jdbc-url}")
    private String jdbcUrl;

    @Value("${spring.datasource.mysql.username}")
    private String username;

    @Value("${spring.datasource.mysql.password}")
    private String password;

    @Primary
    @Bean(name = "mysqlDataSource")
    public DataSource dataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
        dataSourceBuilder.driverClassName(driverClassName);
        dataSourceBuilder.url(jdbcUrl);
        dataSourceBuilder.username(username);
        dataSourceBuilder.password(password);
        return dataSourceBuilder.build();
    }

    @Primary
    @Bean(name = "mysqlTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);

    }

    @Primary
    @Bean(name = "mySqlSessionFactory")
    public SqlSessionFactory basicSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource basicDataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
        
        factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis.xml")[0]);
        PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        Resource[] resources1 = resourcePatternResolver.getResources("classpath:com/xxx/ow/dataimport/mapper/mysql/**/*.xml");
        factoryBean.setMapperLocations(resources1);
        return factoryBean.getObject();
    }

    @Primary
    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("mySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

@Configuration
@MapperScan(basePackages = "com.xxx.ow.dataimport.dao.sqlserver", sqlSessionFactoryRef = "sqlServerSessionFactory")
public class SqlServerConfig {

    @Value("${spring.datasource.sqlserver.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.sqlserver.jdbc-url}")
    private String jdbcUrl;

    @Value("${spring.datasource.sqlserver.username}")
    private String username;

    @Value("${spring.datasource.sqlserver.password}")
    private String password;

    @Bean(name = "sqlServerDataSource")
    public DataSource dataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
        dataSourceBuilder.url(jdbcUrl);
        dataSourceBuilder.driverClassName(driverClassName);
        dataSourceBuilder.username(username);
        dataSourceBuilder.password(password);
        return dataSourceBuilder.build();
    }

    @Bean(name = "sqlServerTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("sqlServerDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);

    }

    @Bean(name = "sqlServerSessionFactory")
    public SqlSessionFactory basicSqlSessionFactory(@Qualifier("sqlServerDataSource") DataSource basicDataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
      
        factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis.xml")[0]);
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:com/xxx/ow/dataimport/mapper/sqlserver/**/*.xml"));
        return factoryBean.getObject();
    }

    @Bean(name = "sqlServerSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("sqlServerSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.mysql.jdbc-url=jdbc:mysql://xxx:3316/xxx_ow?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&connectTimeout=10000&serverTimezone=Asia/Shanghai
spring.datasource.mysql.username=app_owner
spring.datasource.mysql.password=

spring.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sqlserver.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;databaseName=ringcms_20210702_2
spring.datasource.sqlserver.username=sa
spring.datasource.sqlserver.password=

########### druid data source配置
# 配置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，'wall'用于防火墻
spring.datasource.druid.filters=stat
# 初始連接數(shù)
spring.datasource.druid.initialSize=5
# 最小空閑連接
spring.datasource.druid.minIdle=5
# 最大連接數(shù)
spring.datasource.druid.maxActive=20
# 配置獲取連接等待超時(shí)的時(shí)間
spring.datasource.druid.maxWait=60000
# 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
# 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
# 打開PSCache。如果用Oracle, 則把poolPreparedStatements配置為true, mysql 5.5之后建議true
spring.datasource.druid.poolPreparedStatements=true
# 指定每個(gè)連接上PSCache的大小
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
# 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# druid 登錄賬號(hào)
spring.datasource.druid.stat-view-servlet.login-username=superadmin
spring.datasource.druid.stat-view-servlet.login-password=Moshou@2018
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# IP白名單，多個(gè)用,分隔 (沒有配置或者為空，則允許所有訪問)
#spring.datasource.druid.stat-view-servlet.allow=
# IP黑名單 (存在共同時(shí)，deny優(yōu)先于allow)
#spring.datasource.druid.stat-view-servlet.deny=
# 禁用HTML頁面上的“Reset All”功能
spring.datasource.druid.stat-view-servlet.reset-enable=true

spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.map,*.woff,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
########### druid data source配置

第三種方式，使用@ConfigurationProperties注入數(shù)據(jù)源配置

  1. 數(shù)據(jù)庫(kù)配置
spring:
  datasource:
    test1:
        jdbc-url: jdbc:mysql://localhost:3306/alice_test?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: xxx
    test2:
        jdbc-url: jdbc:mysql://localhost:3306/alice_test_two?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: xxx
 
mybatis:
  mapper-locations: classpath:*/mapper/**.xml
  1. 配置類
  • 主數(shù)據(jù)源
package com.alice.springboot.config;
 
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.test", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSourceConfig1 {
 
    // 將這個(gè)對(duì)象放入Spring容器中
    @Bean(name = "test1DataSource")
    // 表示這個(gè)數(shù)據(jù)源是默認(rèn)數(shù)據(jù)源
    @Primary
    // 讀取application.properties中的配置參數(shù)映射成為一個(gè)對(duì)象
    // prefix表示參數(shù)的前綴
    @ConfigurationProperties(prefix = "spring.datasource.test1")
    public DataSource getDateSource1()
    {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "test1SqlSessionFactory")
    // 表示這個(gè)數(shù)據(jù)源是默認(rèn)數(shù)據(jù)源
    @Primary
    // @Qualifier表示查找Spring容器中名字為test1DataSource的對(duì)象
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource datasource)
            throws Exception
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                // 設(shè)置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test/*.xml"));
      bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis.xml"));
        return bean.getObject();
    }
 
    @Bean("test1SqlSessionTemplate")
    // 表示這個(gè)數(shù)據(jù)源是默認(rèn)數(shù)據(jù)源
    @Primary
    public SqlSessionTemplate test1SqlSessionTemplate(
            @Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}
  • 次數(shù)據(jù)源
package com.alice.springboot.config;
 
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.alice.springboot.mapper.testTwo", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSourceConfig2 {
    @Bean(name = "test2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test2")
    public DataSource getDateSource2()
    {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource datasource)
            throws Exception
    {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/testTwo/*.xml"));
bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-two.xml"));
        return bean.getObject();
    }
 
    @Bean("test2SqlSessionTemplate")
    public SqlSessionTemplate test2SqlSessionTemplate(
            @Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionFactory)
    {
        return new SqlSessionTemplate(sessionFactory);
    }
}

參考資料

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容