Spring Boot + MyBatis + Druid + SQL Server 配置多数据源

原创已于 2023-02-09 11:11:38 修改

· 6.7k 阅读

5 ·

版权

文章标签：

#spring boot #java #数据库

于 2023-02-08 18:12:51 首次发布

Java 同时被 3 个专栏收录

180 篇文章

订阅专栏

Spring Boot

43 篇文章

订阅专栏

SQL Server

7 篇文章

订阅专栏

版本配置

SpringBoot	2.4.11
Druid	1.1.17
sqljdbc4	4.0
MyBatis	2.1.1

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.11</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <sqljdbc4.version>4.0</sqljdbc4.version>
        <druid-alibaba.version>1.1.17</druid-alibaba.version>
        <mybatis.version>2.1.1</mybatis.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid-alibaba.version}</version>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>${sqljdbc4.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
    </dependencies>
</project>

配置数据源

目前有2个数据源，分别是master和slave。

第一步：配置application.yml

spring:
  profiles:
    active: dev
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  datasource:
    name: druid_ds
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 1
      min-idle: 1
      max-active: 10
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,slf4j # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true

druid的相同配置可以在application.yml中配置，通过active的值激活不同的配置文件，达到“开发环境”，“测试环境”，“生产环境”的差异性。

本文以dev（开发环境）作为配置值。

第二步：配置application-dev.yml

server:
  port: 8305
  servlet:
    context-path: /
spring:
  datasource:
    druid:
      master:
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://localhost:1433;DatabaseName=test_01
        username: sa
        password: 12345678
      slave:
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://localhost:1433;DatabaseName=test_02
        username: sa
        password: 12345678

master数据库名称为“test_01”,slave数据库名称为“test_02”。

第三步：配置master数据源

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    protected static final String PACKAGE = "com.test.domain.repository.master";
    protected static final String MAPPER_LOCATION = "classpath:orm/*.xml";

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    @Primary
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

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

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

第四步：配置slave数据源

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
    public static final String PACKAGE = "com.test.domain.repository.slave";
    public static final String MAPPER_LOCATION = "classpath:orm/*.xml";

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

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource slaveDataSource) {
        return new DataSourceTransactionManager(slaveDataSource);
    }

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(slaveDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(SlaveDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

第五步：配置service（业务）

5.1 master数据源

@Service
@RequiredArgsConstructor
public class UserInfoService {
    private final UserInfoService userInfoService;
    
    @Transactional(value = "masterTransactionManager")
    public void insertUserInfo(UserInfo userInfo) {
        userInfoService.insertUserInfo(userInfo);
    }
}

5.2 slave数据源

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@Service
@RequiredArgsConstructor
public class ContactService {
    private final ContactMapper contactMapper;

    @Transactional(value = "slaveTransactionManager")
    public Contact getByOpenId(String openId) {
        return contactMapper.getByOpenId(openId);
    }
}

通过 @Transactional选择相应的数据库

问题： @Transactional不生效

问题描述

起初，为了方便，我将master/slave对应的mapper放在一个目录下。

经验小结：将master与slave的dao层放在同一个目录时，例如都放在repository目录下。

项目运行时，总是报错Invalid object name 'xxxxxx',slave数据库没有选择成功。

方案1：指定执行环境数据库

https://blog.youkuaiyun.com/alone_in_/article/details/93848044

<select id="getByOpenId" resultMap="baseContactMap">
  use test_02
  select * from contact where OpenID = #{openId}
</select>

在mapper.xml文件中添加语句“use test_02”，指定数据库。

问题虽然得以解决，但是之前的配置就似乎没有必要了。进一步研究，得到方案2。

方案2：设定dao层

注意：master与slave的dao层是分开的。

将mater/slave对应的mapper层分别放在不同的目录里，运行程序后问题解决。