526互联

集成 Mybatis 和多数据源

发布时间 2023-08-14 21:51:15作者: 江南烟雨行舟

在继承的时候先看看版本说明，引入以下依赖：

<druid-spring-version>1.2.15</druid-spring-version>
<mybatis-spring-version>2.3.0</mybatis-spring-version>

<!--        <dependency>-->
<!--            <groupId>com.mysql</groupId>-->
<!--            <artifactId>mysql-connector-j</artifactId>-->
<!--        </dependency>-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-version}</version>
            <exclusions>
                <exclusion>
                    <groupId>com.zaxxer</groupId>
                    <artifactId>HikariCP</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid-spring-version}</version>
        </dependency>

mysql 的依赖坐标变了，不是以前的 groupId：mysql，artifactId：mysql-connector-java。

通用配置文件

文件路径&文件名 resources/mybatis/mybatis-config.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 全局参数 -->
    <settings>
        <!-- 使全局的映射器启用或禁用缓存 -->
        <setting name="cacheEnabled"             value="true"   />
        <!-- 允许JDBC 支持自动生成主键 -->
        <setting name="useGeneratedKeys"         value="true"   />
        <!-- 配置默认的执行器.SIMPLE就是普通执行器;REUSE执行器会重用预处理语句(prepared statements);BATCH执行器将重用语句并执行批量更新 -->
        <setting name="defaultExecutorType"      value="SIMPLE" />
		<!-- 指定 MyBatis 所用日志的具体实现 -->
        <setting name="logImpl"                  value="SLF4J"  />
        <!-- 使用驼峰命名法转换字段 -->
		<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
	</settings>
	
</configuration>

单数据源配置

# MyBatis 配置
mybatis:
  # 自动配置别名，mxl 中可以直接使用类名
  typeAliasesPackage: com.xxx.xxx.domain
  mapperLocations: classpath*:mybatis/mapper/*Mapper.xml
  configLocation: classpath:mybatis/mybatis-config.xml

我使用的是 Free MyBatis Tool 插件生成的，没有在 Mybatis mapper 接口类上使用 @Mapper 和 @Repository 注解，可以使用 @MapperScan 注解指定包路径来注册接口类。

如果接口使用了多个参数可以通过 @Param 注解指定参数名。

多数据源配置

创建多数据源配置类, 将下面的 XXXX 改为数据源名称:

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceWrapper;
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.autoconfigure.condition.ConditionalOnMissingBean;
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.ResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "mapper 层接口",
        sqlSessionFactoryRef = "XXXXSqlSessionFactory", sqlSessionTemplateRef = "XXXXSqlSessionTemplate")
public class XXXXDataSourceConfig {
    private static final String MAPPER_LOCATION = "classpath*:mybatis/mapper/*Mapper.xml";
    private static final String CONFIG_LOCATION = "classpath:mybatis/mybatis-config.xml";

    // 随便指定一个数据源为主
    @Primary
    @Bean(name = "XXXXDataSource", initMethod = "init")
    @ConfigurationProperties("spring.datasource.druid.multiple.master")
    public DataSource XXXXDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

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

    @Primary
    @Bean("XXXXSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("XXXXDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        sqlSessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource(CONFIG_LOCATION));
        return sqlSessionFactoryBean.getObject();
    }

    @Primary
    @Bean("XXXXSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("XXXXDataSource") DataSource dataSource) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory(dataSource));
    }

}

还需要 DruidProperties 类:

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

/**
 * 配置 DruidDataSource 数据源的属性
 */
@Configuration
public class DruidProperties {
    @Value("${spring.datasource.druid.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.druid.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.druid.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.druid.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
    private int maxEvictableIdleTimeMillis;

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

    @Value("${spring.datasource.druid.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.testOnReturn}")
    private boolean testOnReturn;

    public DruidDataSource dataSource(DruidDataSource datasource) {
        /** 配置初始化大小、最小、最大 */
        datasource.setInitialSize(initialSize);
        datasource.setMaxActive(maxActive);
        datasource.setMinIdle(minIdle);

        /** 配置获取连接等待超时的时间 */
        datasource.setMaxWait(maxWait);

        /** 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 */
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

        /** 配置一个连接在池中最小、最大生存的时间，单位是毫秒 */
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);

        /**
         * 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
         */
        datasource.setValidationQuery(validationQuery);
        /** 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 */
        datasource.setTestWhileIdle(testWhileIdle);
        /** 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnBorrow(testOnBorrow);
        /** 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
        datasource.setTestOnReturn(testOnReturn);
        return datasource;
    }
}

连接池和数据源配置

创建 application-druid.yml 文件，可以通过 spring.profiles.include=druid 导入配置：

spring:
  datasource:
    type: "com.alibaba.druid.pool.DruidDataSource"
    driverClassName: "com.mysql.cj.jdbc.Driver"
    druid:
      # 单一数据源
      url: jdbc:mysql://localhost:3306/database_name?serverTimezone=Asia/Shanghai&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=true&allowMultiQueries=true&rewriteBatchedStatements=true
      username: root
      password: root

      # 多数据源
      multiple:
        master:
          url: jdbc:mysql://localhost:3306/database_name?serverTimezone=Asia/Shanghai&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=true&allowMultiQueries=true&rewriteBatchedStatements=true
          username: root
          password: root

      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 10
      # 最大连接池数量
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      maxEvictableIdleTimeMillis: 900000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      webStatFilter:
        enabled: true
      statViewServlet:
        enabled: true
        # 设置白名单，不填则允许所有访问
        allow:
        url-pattern: /druid/*
        login-username: xxxx
        login-password: xxxx
      filter:
        slf4j:
          enabled: true
        stat:
          enabled: true
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: true
        wall:
          config:
            multi-statement-allow: true

参考资料

https://blogs.oracle.com/mysql/post/mysql-connectorj-has-new-maven-coordinates

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-whats-new.html

https://plugins.jetbrains.com/plugin/18617-free-mybatis-tool