Spring Boot笔记之多数据源（Mysql+MyBatis）

╰+攻爆jí腚メ 2022-04-04 15:29 128阅读 0赞

### Mysql+MyBatis多数据源配置 ###

*  SpringBoot单数据源配置
 *   *  pom.xml
     *  application.yml
 *  SpringBoot多数据源配置
 *   *  修改application.yml
     *  Configuration类配置
 *  结语

# SpringBoot单数据源配置 #

## pom.xml ##

在pom.xml中添加相关依赖：

MySQL驱动

<dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>6.0.6</version>
    </dependency>

SpringBoot对MyBatis的集成

<dependency>
    	<groupId>org.mybatis.spring.boot</groupId>
    	<artifactId>mybatis-spring-boot-starter</artifactId>
    	<version>${mybatis.spring.boot.starter.version}</version>
    </dependency>

阿里出得一个druid，据说性能很好，不是生产环境与可以不引入

<dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid-spring-boot-starter</artifactId>
    	<version>${druid.version}</version>
    </dependency>

## application.yml ##

spring:
      datasource:
        url: jdbc:mysql://localhost:3306/demo?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Hongkong
        username: root
        password: 123456
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
    	validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
    
    mybatis:
      mapper-locations: classpath:mapper/*/*.xml
      configuration:
        map-underscore-to-camel-case: true
        use-generated-keys: true
        default-fetch-size: 100
        default-statement-timeout: 300

如此即可，单数据源配置完毕，将Mapper.xml扔到classpath:mapper下面，实体、Example和mapper接口跟随公司习惯随便放。

# SpringBoot多数据源配置 #

## 修改application.yml ##

单纯的使用yml文件已经不能满足多数据源的配置，沿袭单数据源的配置项和结构，在`spring.datasource`后增加一级以区分不同数据源：

spring:
      datasource:
        project:
          url: jdbc:mysql://localhost:3306/demo?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Hongkong
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
          initialSize: 5
          minIdle: 5
          maxActive: 20
          maxWait: 60000
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: SELECT 1
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
        exchange:
          jdbc-url: jdbc:mysql://localhost:3306/exchange?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&serverTimezone=Hongkong
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver

**说明**

*  `project`是项目的名字，`exchange`是另一个项目的名字，此处是两个数据源的名称。
 *  `spring.datasource.project.url`与`spring.datasource.exchange.jdbc-url`是不同的两种配置，其中名为`project`的数据源会使用`com.alibaba.druid.pool.DruidDataSource`，配置比较复杂（后面讲）,而名为`exchange`的数据源使用`com.zaxxer.hikari.HikariDataSource`，配置相对简单。

## Configuration类配置 ##

yml不能自动配置多数据源，需要用`@Configuration`进行配置

首先是比较简单的`HikariDataSource`

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 = "one.hht.api.exchange", sqlSessionTemplateRef = "exchangeSqlSessionTemplate")
    public class ExchangeDataSourceConfiguration { 
        @Bean(name = "exchangeDataSource")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.exchange")
        public DataSource exchangeDataSource() { 
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "exchangeSqlSessionFactory")
        @Primary
        public SqlSessionFactory exchangeSqlSessionFactory(@Qualifier("exchangeDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/exchange/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "exchangeDataSourceTransactionManager")
        @Primary
        public DataSourceTransactionManager exchangeDataSourceTransactionManager(@Qualifier("exchangeDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "exchangeSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate exchangeSqlSessionTemplate(@Qualifier("exchangeSqlSessionFactory") SqlSessionFactory sqlSessionFactory) { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

然后是`DruidDataSource`的配置

import com.alibaba.druid.pool.DruidDataSource;
    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.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
    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;
    
    @Configuration
    @MapperScan(basePackages = "one.hht.api.project", sqlSessionTemplateRef = "projectSqlSessionTemplate")
    public class ProjectDataSourceConfiguration { 
        @Autowired
        private DataSourceProperties properties;
    
        @Value("${spring.datasource.project.initialSize}")
        private Integer initialSize;
    
        @Value("${spring.datasource.project.minIdle}")
        private Integer minIdle;
    
        @Value("${spring.datasource.project.maxActive}")
        private Integer maxActive;
    
        @Value("${spring.datasource.project.maxWait}")
        private Integer maxWait;
    
        @Value("${spring.datasource.project.timeBetweenEvictionRunsMillis}")
        private Integer timeBetweenEvictionRunsMillis;
    
        @Value("${spring.datasource.project.validationQuery}")
        private String validationQuery;
    
        @Value("${spring.datasource.project.testWhileIdle}")
        private Boolean testWhileIdle;
    
        @Value("${spring.datasource.project.testOnBorrow}")
        private Boolean testOnBorrow;
    
        @Value("${spring.datasource.project.testOnReturn}")
        private Boolean testOnReturn;
    
        @Value("${spring.datasource.project.poolPreparedStatements}")
        private Boolean poolPreparedStatements;
    
        @Value("${spring.datasource.project.maxPoolPreparedStatementPerConnectionSize}")
        private Integer maxPoolPreparedStatementPerConnectionSize;
    
        @Bean(name = "projectDataSource")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.project")
        public DataSource projectDataSource() { 
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setUrl(properties.getUrl());
            dataSource.setUsername(properties.getUsername());
            dataSource.setPassword(properties.getPassword());
            dataSource.setDriverClassName(properties.getDriverClassName());
            dataSource.setInitialSize(initialSize);
            dataSource.setMinIdle(minIdle);
            dataSource.setMaxActive(maxActive);
            dataSource.setMaxWait(maxWait);
            dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            dataSource.setValidationQuery(validationQuery);
            dataSource.setTestWhileIdle(testWhileIdle);
            dataSource.setTestOnBorrow(testOnBorrow);
            dataSource.setTestOnReturn(testOnReturn);
            dataSource.setPoolPreparedStatements(poolPreparedStatements);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            return dataSource;
        }
    
        @Bean(name = "projectSqlSessionFactory")
        @Primary
        public SqlSessionFactory projectSqlSessionFactory(@Qualifier("projectDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/project/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "projectDataSourceTransactionManager")
        @Primary
        public DataSourceTransactionManager projectDataSourceTransactionManager(@Qualifier("projectDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "projectSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate projectSqlSessionTemplate(@Qualifier("projectSqlSessionFactory") SqlSessionFactory sqlSessionFactory) { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

# 结语 #

如此配置，将mapper接口放到相应的`basePackages`指定包中，mapper.xml放到`MapperLocations`指定路径下，即可。。。

两种配置方法个人感觉还是用`com.alibaba.druid.pool.DruidDataSource`的这种比较靠谱。