SpringBoot+Druid+MyBatis实现多数据源

头像
睁眼看世界
    阅读 10 分钟
    1

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

    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<String> CONTEXT_HOLDER= new ThreadLocal<>();

    /**
     * 配置DataSource, defaultTargetDataSource为主数据库
     */
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

    public static void setDataSource(String dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static String getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

    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<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DataSourceNames.ONE, oneDataSource);
        targetDataSources.put(DataSourceNames.TWO, twoDataSource);
        // 还有数据源,在targetDataSources中继续添加
        log.info("DataSources:{}", targetDataSources);
        return new DynamicDataSource(oneDataSource, targetDataSources);
    }

    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }
}

    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<User> 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<ChatApp> 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<ChatApp> chatApps = chatAppService.selectList(app);
    System.out.println(chatApps);
}

// 测试读取TWO的数据源
@Test
public void selectList() {
    User user = new User();
    user.setUsername("Tom");
    List<User> 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           : <==      Total: 1
2020-03-04 16:13:26.920  INFO 21336 --- [           main] c.w.m.datasource.DataSourceAspect        : clean datasource
[ChatApp(id=1, username=TONY, qq=12312312, wx=098765)]


2020-03-04 16:13:49.673  INFO 34140 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
当前数据源: ONE
2020-03-04 16:13:49.888  INFO 34140 --- [           main] c.w.m.datasource.DataSourceAspect        : set datasource is ONE
2020-03-04 16:13:49.972 DEBUG 34140 --- [           main] c.w.m.dao.UserMapper.insertSelective     : ==>  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     : <==    Updates: 1
2020-03-04 16:13:50.015 DEBUG 34140 --- [           main] c.w.m.d.U.insertSelective!selectKey      : ==>  Executing: SELECT LAST_INSERT_ID() 
2020-03-04 16:13:50.044 DEBUG 34140 --- [           main] c.w.m.d.U.insertSelective!selectKey      : <==      Total: 1
2020-03-04 16:13:50.051 DEBUG 34140 --- [           main] c.w.m.dao.RoleMapper.insertSelective     : ==>  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     : <==    Updates: 1
2020-03-04 16:13:50.054 DEBUG 34140 --- [           main] c.w.m.d.R.insertSelective!selectKey      : ==>  Executing: SELECT LAST_INSERT_ID() 
2020-03-04 16:13:50.055 DEBUG 34140 --- [           main] c.w.m.d.R.insertSelective!selectKey      : <==      Total: 1
2020-03-04 16:13:50.056  INFO 34140 --- [           main] c.w.m.datasource.DataSourceAspect        : clean datasource


java.lang.ArithmeticException: / by zero

    4. 具体源码

    https://gitee.com/whyCodeData...

    javamysql
    睁眼看世界
    36 声望3 粉丝

    喜欢java，热爱技术，做一个快乐的程序员！

    « 上一篇
    Redis实现延迟队列

    引用和评论

    推荐阅读
    头像
    搞定服务端面试 - HTTP协议知识点整理

    白菜1031赞 71阅读 8.6k

    头像
    如何实现一个楼中楼的评论系统

    小蚊酱赞 24阅读 10.4k

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2k

    头像
    Jerry和您聊聊Chrome开发者工具

    注销赞 9阅读 4.5k评论 3

    头像
    Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

    程序员小富赞 8阅读 1.8k评论 1

    头像
    Just for fun——迅速写完快速排序

    ufdf赞 3阅读 2.6k

    头像
    Spring 实现 3 种异步流式接口，干掉接口超时烦恼

    程序员小富赞 5阅读 1.8k

    0 条评论
    得票最新