转载

springboot配置多数据源

工作环境

最近一直在写支持移动客户端的后台接口项目，项目中需要访问多个数据源。本篇文章对springboot2配置多数据源进行一个总结。如有不当，欢迎指正、拍砖！

代码展示

application-dev.yml

# hikari datasource config
hikari:
  eoms:
    jdbc-url: jdbc:mysql://xxxx:3306/db_chmdm_justsy_eoms?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    username: xxxx
    password: xxxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    minimum-idle: 5
    idle-timeout: 600000
    maximum-pool-size: 10
    auto-commit: true
    pool-name: MyHikariCPOfEoms
    max-lifetime: 1800000
    connection-timeout: 30000
    connection-test-query: SELECT 1
    validation-timeout: 5000
  opcl:
    jdbc-url: jdbc:mysql://xxxx:3306/db_chmdm_justsy_opcl?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    username: xxxx
    password: xxxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    minimum-idle: 5
    idle-timeout: 600000
    maximum-pool-size: 10
    auto-commit: true
    pool-name: MyHikariCPOfOpcl
    max-lifetime: 1800000
    connection-timeout: 30000
    connection-test-query: SELECT 1
    validation-timeout: 5000
  mdm:
    jdbc-url: jdbc:mysql://xxxx:3306/db_chmdm_zjsgs?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    username: xxx
    password: xxx
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    minimum-idle: 5
    idle-timeout: 600000
    maximum-pool-size: 10
    auto-commit: true
    pool-name: MyHikariCPOfOpcl
    max-lifetime: 1800000
    connection-timeout: 30000
    connection-test-query: SELECT 1
    validation-timeout: 5000
# mybatis config
mybatis:
  type-aliases-package: com.ai.api.model
  mapper-locations: classpath:mapper/*/*.xml
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true

EomsDataSourceConfig

package com.ai.api.config.datasource;

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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.ai.api.mapper.eoms",
        sqlSessionTemplateRef = "eomsSqlSessionTemplate")
public class EomsDataSourceConfig {

    /*生成数据源*/
    @Bean(name = "eomsDs")
    @ConfigurationProperties(prefix = "hikari.eoms")
    @Primary
    public DataSource createDataSource() {
        return DataSourceBuilder.create().build();
    }

    /*创建 SqlSessionFactory*/
    @Bean(name = "eomsSqlSessionFactory")
    @Primary
    public SqlSessionFactory createSqlSessionFactory(@Qualifier("eomsDs") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/eoms/*.xml"));
        return bean.getObject();
    }

    /*配置事务管理*/
    @Bean(name = "eomsTransactionManager")
    @Primary
    public DataSourceTransactionManager createTransactionManager(@Qualifier("eomsDs") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /*SqlSessionTemplate是MyBatis-Spring的核心。这个类负责管理MyBatis的SqlSession,调用MyBatis的SQL方法*/
    @Bean(name = "eomsSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("eomsSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

MdmDataSourceConfig

package com.ai.api.config.datasource;

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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.ai.api.mapper.mdm",
        sqlSessionTemplateRef = "mdmSqlSessionTemplate")
public class MdmDataSourceConfig {

    @Bean(name = "mdmDs")
    @ConfigurationProperties(prefix = "hikari.mdm")
    public DataSource createDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mdmSqlSessionFactory")
    public SqlSessionFactory createSqlSessionFactory(@Qualifier("mdmDs") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mdm/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "mdmTransactionManager")
    public DataSourceTransactionManager createTransactionManager(@Qualifier("mdmDs") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mdmSqlSessionTemplate")
    public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("mdmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

OpclDataSourceConfig

package com.ai.api.config.datasource;

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 org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.ai.api.mapper.opcl",
        sqlSessionTemplateRef = "opclSqlSessionTemplate")
public class OpclDataSourceConfig {

    @Bean(name = "opclDs")
    @ConfigurationProperties(prefix = "hikari.opcl")
    public DataSource createDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "opclSqlSessionFactory")
    public SqlSessionFactory createSqlSessionFactory(@Qualifier("opclDs") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/opcl/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "opclTransactionManager")
    public DataSourceTransactionManager createTransactionManager(@Qualifier("opclDs") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "opclSqlSessionTemplate")
    public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("opclSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

mapper中使用

package com.ai.api.mapper.eoms;

import com.ai.api.commons.dto.Node;
import com.ai.api.model.*;
import org.apache.ibatis.annotations.Param;
import org.hibernate.validator.constraints.Length;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.validation.constraints.NotBlank;
import java.util.List;
import java.util.Map;

@Qualifier("eomsSqlSessionTemplate")
@Repository
public interface ApplyMapper {


    int getListCount(@Param("mngCode") String mngDeptCode,
                     @Param("status")String status);

    List<Map<String,Object>> list(@Param("start") Integer start,
                                  @Param("rows") Integer rows,
                                  @Param("mngCode") String mngDeptCode,
                                  @Param("status")String status);

    List<Map<String,Object>> search(@Param("input")String input,
                                    @Param("mngCode")String mngDeptCode);

    Map<String,Object> listByBatch(@Param("batch") String batch);

    int getTotalCount(@Param("batch") String batch);

    int getDoneCount(@Param("batch") String batch);

    Map<String,Object> getBatchProcess(@Param("batch") String batch);

    List<ApprovalProcess> getHistory(@Param("batch") String batch);

    Map<String,Object> getBatch(@Param("batch") String batch);

    void updateBatchState(@Param("batch") String batch, @Param("set") String set);

    void updateBatchAppearState(@Param("batch") String batch, @Param("set") String set);

    List<UserAppear> getUserAppears(@Param("batch") String batch);

    void makeUserAppearRight(@Param(value="userAppear") UserAppear userAppear);

    List<UserAppear> getUserAppearFromView(@Param("batch") String batch, @Param("start") Integer start, @Param("rows") Integer rows);

    ImsiType getImsiType(@NotBlank @Length(min = 15, max = 15) @Param("imsi1") String imsi1);

    int getPhoneNum(@Param("phone") String phone);

    int getImsiNum(@Param("imsi1") String imsi1);

    void updateUserAppearApplyType(EditBatch editBatch);

    void updateEditImsi(EditBatch editBatch);

    List<String> getTerminalList();

    List<String> getAuthtypeList();

    int getOpenCardNumBySfzh(String sfzh);

    int getSjhCount(String sjh);

    int getImsi1Count(String imsi1);

    List<UserAppear> getZhuxiaoUserAppear(String sjh);

    int addBatch(BatchAppear batchAppear);

    int addUserAppear(UserAppear userAppear);

    int addImsi(IMSI imsi);

    int addTermial(Terminal terminal);

    int addBatchHandlerRecords(BatchHandlerRecords batchHandlerRecords);

    int addBatchHandler(BatchHandler batchHandler);

    BatchAppear getBatchAppear(String batch);

    BatchImg getBatchImg(@Param("batch") String batch, @Param("orderId") String orderId);

    void updateBatchImg(@Param("path") String path,@Param("batch") String batch, @Param("orderId") String orderId);

    void updateUserAppearState(@Param("batch") String batch, @Param("state") String state);

    void addBatchImg(BatchImg newBatchImg);

    void deleteBatchHandler(String batch);
}

springboot配置多数据源