当前位置：首页 > 文章列表 > 数据库 > MySQL > SpringBoot+Druid+MyBatis实现多数据源

SpringBoot+Druid+MyBatis实现多数据源

来源：SegmentFault 2023-01-25 18:12:47 0浏览收藏

本篇文章给大家分享《SpringBoot+Druid+MyBatis实现多数据源》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

我们使用动态的方式进行多数据源的配置，更加灵活方便。

1. 配置文件

spring:
  datasource:
    druid:
      one:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/multiple_one?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
      two:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/multiple_two?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456

mapper:
  identity: MYSQL
  mappers: tk.mybatis.mapper.common.Mapper
  not-empty: false
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.why.zing.product.entity
logging:
  level:
    com.why.multipledatasource.dao: debug

2. 多数据源配置

2.1 数据源属性注入DataSourceConfig

package com.why.multipledatasource.datasource;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Slf4j
@Configuration
public class DataSourceConfig {

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

    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.two")
    public DataSource twoDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
}

2.2 动态路由控制 DynamicDataSource

package com.why.multipledatasource.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal CONTEXT_HOLDER= new ThreadLocal();

    /**
     * 配置DataSource, defaultTargetDataSource为主数据库
     */
    public DynamicDataSource(DataSource defaultTargetDataSource, Map

2.3 SqlSessionConfig

package com.why.multipledatasource.datasource;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
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;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Configuration
public class SqlSessionConfig {

    /**
     * 如果还有数据源,在这继续添加 DataSource Bean
     */
    @Bean
    @Primary
    public DataSource dataSource(@Qualifier("masterDataSource") DataSource oneDataSource, @Qualifier("slaveDataSource") DataSource twoDataSource) {
        Map

2.4 注解实现路由选择

/**
 * 数据源名称
 */
public interface DataSourceNames {
    String ONE = "ONE";
    String TWO = "TWO";
}


@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {

    String value() default DataSourceNames.ONE;
}

2.5 通过AOP针对不同数据源执行

package com.why.multipledatasource.datasource;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Slf4j
@Aspect
@Component
public class DataSourceAspect {

    /**
     * 切点: 所有配置 DataSource 注解的方法
     */
    @Pointcut("@annotation(com.why.multipledatasource.datasource.DataSource)")
    public void dataSourcePointCut() {}

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DataSource ds = method.getAnnotation(DataSource.class);
        // 通过判断 DataSource 中的值来判断当前方法应用哪个数据源
        DynamicDataSource.setDataSource(ds.value());
        System.out.println("当前数据源: " + ds.value());
        log.info("set datasource is " + ds.value());
        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
            log.info("clean datasource");
        }
    }
}

3. 应用

具体MyBatis的mapper文件、Entity属性文件就不一一展示了，主要展示注解应用的场景

3.1 使用ONE数据源的SQL

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RoleService roleService;


    @Override
    @DataSource
    public List selectList(User user){
        return userMapper.select(user);
    }

    @Override
    @DataSource
    public int update(User user) {
        return userMapper.updateByPrimaryKeySelective(user);
    }


    @Override
    @DataSource
    @Transactional(rollbackFor = Exception.class)
    public void saveUserRole(User user, Role role) {

        userMapper.insertSelective(user);
        roleService.save(role);
    }
}

3.2 使用TWO数据源的SQL

import com.why.multipledatasource.dao.ChatAppMapper;
import com.why.multipledatasource.datasource.DataSource;
import com.why.multipledatasource.datasource.DataSourceNames;
import com.why.multipledatasource.entity.ChatApp;
import com.why.multipledatasource.service.ChatAppService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ChatAppServiceImpl implements ChatAppService {

    @Autowired
    private ChatAppMapper appMapper;


    @Override
    @DataSource(value = DataSourceNames.TWO)
    public List selectList(ChatApp chatApp){
        return appMapper.select(chatApp);
    }

    @Override
    @DataSource(value = DataSourceNames.TWO)
    public int update(ChatApp chatApp) {
        return appMapper.updateByPrimaryKeySelective(chatApp);
    }
}

3.3 编写单元测试类

// 测试读取ONE的数据源
@Test
public void selectList() {
    ChatApp app = new ChatApp();
    app.setId(1);
    List chatApps = chatAppService.selectList(app);
    System.out.println(chatApps);
}

// 测试读取TWO的数据源
@Test
public void selectList() {
    User user = new User();
    user.setUsername("Tom");
    List users = userService.selectList(user);
    System.out.println(users);
}

// 测试读取ONE数据源并添加事务
/**
 * 验证同数据库事务情况，对于跨数据库的分布式事务，需要引入分布式事务的解决方案
 */
@Test
public void saveUserRole(){
    User user = new User();
    user.setUsername("睁眼看世界");
    user.setAge(19);
    user.setSex("男");
    Role role = new Role();
    role.setRole("管理员");
    userService.saveUserRole(user,role);
}

执行日志：

当前数据源: ONE
2020-03-04 14:51:49.023  INFO 18512 --- [           main] c.w.m.datasource.DataSourceAspect        : set datasource is ONE
2020-03-04 14:52:09.124  INFO 18512 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-03-04 14:52:10.944  INFO 18512 --- [           main] c.w.m.datasource.DataSourceAspect        : clean datasource
[User(id=1, username=Tom, age=18, sex=男)]


当前数据源: TWO
2020-03-04 16:13:26.431  INFO 21336 --- [           main] c.w.m.datasource.DataSourceAspect        : set datasource is TWO
2020-03-04 16:13:26.690  INFO 21336 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-03-04 16:13:26.850 DEBUG 21336 --- [           main] c.w.m.dao.ChatAppMapper.select           : ==>  Preparing: SELECT id,username,qq,wx FROM chat_app WHERE id = ? 
2020-03-04 16:13:26.879 DEBUG 21336 --- [           main] c.w.m.dao.ChatAppMapper.select           : ==> Parameters: 1(Integer)
2020-03-04 16:13:26.915 DEBUG 21336 --- [           main] c.w.m.dao.ChatAppMapper.select           :   Preparing: INSERT INTO user ( id,username,age,sex ) VALUES( ?,?,?,? ) 
2020-03-04 16:13:50.005 DEBUG 34140 --- [           main] c.w.m.dao.UserMapper.insertSelective     : ==> Parameters: null, 睁眼看世界(String), 19(Integer), 男(String)
2020-03-04 16:13:50.010 DEBUG 34140 --- [           main] c.w.m.dao.UserMapper.insertSelective     :   Executing: SELECT LAST_INSERT_ID() 
2020-03-04 16:13:50.044 DEBUG 34140 --- [           main] c.w.m.d.U.insertSelective!selectKey      :   Preparing: INSERT INTO role ( id,role ) VALUES( ?,? ) 
2020-03-04 16:13:50.052 DEBUG 34140 --- [           main] c.w.m.dao.RoleMapper.insertSelective     : ==> Parameters: null, 管理员(String)
2020-03-04 16:13:50.053 DEBUG 34140 --- [           main] c.w.m.dao.RoleMapper.insertSelective     :   Executing: SELECT LAST_INSERT_ID() 
2020-03-04 16:13:50.055 DEBUG 34140 --- [           main] c.w.m.d.R.insertSelective!selectKey      :