SpringBoot多数据源（自定义注解，动态数据源，事务实现）

       本文记录下通过注解动态配置数据源的实现方式，可以是不同的数据库（如读写分离），也可以是mycat代理的分表数据源。废话不多，上干货——

一、数据库配置文件（这里用的是阿波罗配置中心，也可以是application.yml文件）

#mysql本地数据源1
spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db1.jdbc-url=jdbc:mysql://127.0.0.1:3306/mysqlDB1?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false
spring.datasource.db1.username=root
spring.datasource.db1.password=123456

#mysql本地数据源2
spring.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.db2.jdbc-url=jdbc:mysql://127.0.0.1:3306/mysqlDB2?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false
spring.datasource.db2.username=root
spring.datasource.db2.password=123456

#mycat本地数据源
spring.datasource.mycat.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.mycat.jdbc-url=jdbc:mysql://192.168.0.110:3306/mycatDB?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&useSSL=false
spring.datasource.mycat.username=root
spring.datasource.mycat.password=123456

二、java代码功能部分（根据自己的需求配置实现，不需要mycat分表的可以不配置mycat部分）

   1、首先实现数据源配置

import com.xxx.xxx.aop.DataSourceNames;
import com.xxx.xxx.aop.DynamicDataSource;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;

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

/**
 * 数据源配置
 *
 * @Author Ldpy
 * @Date 2021/11/30 16:50
 * @Version 1.0
 **/
@Configuration
@Component
@MapperScan(basePackages = DataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class DataSourceConfig {

    static final String PACKAGE = "com.xxx.xxx.mapper";
    static final String MAPPER_LOCATION = "classpath*:com/xxx/xxx/mapper/xml/*.xml";

    @Bean(name = "mysqlDatasource1")
    @ConfigurationProperties("spring.datasource.db1")
    public DataSource mysqlDataSource1(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlDatasource2")
    @ConfigurationProperties("spring.datasource.db2")
    public DataSource mysqlDataSource2(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mycatDatasource")
    @ConfigurationProperties("spring.datasource.mycat")
    public DataSource mycatDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(
            @Qualifier("mysqlDatasource1") DataSource mysqlSqlSessionFactory) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysqlSqlSessionFactory);
        sessionFactory.setDataSource(dataSource(mysqlDataSource1(),mysqlDataSource2(),mycatDataSource()));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Primary
    @Bean("mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(
            @Qualifier("mysqlSqlSessionFactory") SqlSessionFactory mysqlSqlSessionFactory) {
        return new SqlSessionTemplate(mysqlSqlSessionFactory);
    }

    @Bean(name = "dataSource")
    @Primary
    public DynamicDataSource dataSource(@Qualifier("mysqlDatasource1") DataSource mysqlDataSource1, 
                                        @Qualifier("mysqlDatasource2") DataSource mysqlDataSource2, 
                                        @Qualifier("mycatDatasource") DataSource mycatDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceNames.MYSQLDB1, mysqlDataSource1);
        targetDataSources.put(DataSourceNames.MYSQLDB2, mysqlDataSource2);
        targetDataSources.put(DataSourceNames.MYCATDB, mycatDataSource);
        return new DynamicDataSource(mysqlDataSource1, targetDataSources);
    }

    /**
     * 注入事务管理器
     * @author Ldpy
     * @return
     */
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSource") DataSource ds) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(ds);
        return dataSourceTransactionManager;
    }

    /**
     * 注入事务管理器也可以简单这样写,与上面一个意思
     * @author Ldpy
     * @return
     */
    /*@Bean
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("mysqlDatasource1") DataSource mysqlDataSource1, 
                                        @Qualifier("mysqlDatasource2") DataSource mysqlDataSource2, 
                                        @Qualifier("mycatDatasource") DataSource mycatDataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource(mysqlDataSource1,mysqlDataSource2,mycatDataSource));
        return dataSourceTransactionManager;
    }*/

}

   2、定义数据源注解常量 

/**
 * 数据源注解常量
 *
 * @Author Ldpy
 * @Date 2021/11/30 16:55
 * @Version 1.0
 **/
public interface DataSourceNames {
    String MYSQLDB1 = "mysqlDB1";
    String MYSQLDB2 = "mysqlDB2";
    String MYCATDB = "mycatDB";
}

   3、定义数据源注解 

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 数据源注解
 *
 * @Author Ldpy
 * @Date 2021/11/30 16:55
 * @Version 1.0
 **/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface DataSource {

    String dataSourceName() default DataSourceNames.MYSQLDB1;

}

   4、实现数据源注解 

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * 数据源注解切面实现
 *
 * @Author Ldpy
 * @Date 2021/11/30 16:58
 * @Version 1.0
 **/
@Component
@Aspect
@Order(0)
public class DataSourceSection {

    @Pointcut("@annotation(com.xxx.xxx.aop.DataSource)")
    public void pointCut() {
    }

    @Around("pointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();

        DataSource ds = method.getAnnotation(DataSource.class);
        if(ds == null){
            DynamicDataSource.setDataSource(DataSourceNames.MYSQLDB1);
        }else {
            DynamicDataSource.setDataSource(ds.dataSourceName());
        }

        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
        }
    }

}

    5、动态数据源实现

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

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

/**
 * 动态数据源
 *
 * @Author Ldpy
 * @Date 2021/11/30 16:50
 * @Version 1.0
 **/
public class DynamicDataSource extends AbstractRoutingDataSource {

    // 用来保存数据源与获取数据源
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    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) {
        contextHolder.set(dataSource);
    }

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

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

}

三、测试部分

    1、编写启动类（注意添加@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class })屏蔽springboot的数据源自动配置）

import com.ctrip.framework.apollo.spring.annotation.EnableApolloConfig;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class })
@EnableScheduling
@EnableApolloConfig
@MapperScan("com.xxx.xxx.mysql.mapper*")
public class MyApplication {

    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class, args);
    }

}

    2、常规测试

import com.xxx.xxx.mysql.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;


/**
 * @Description 测试
 * @Author Ldpy
 * @Date 2021/11/30
 */
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private TestService testService;

    /**
     * 查询
     *
     * @Author Ldpy
     * @Date 2021/11/30
     */
    @PostMapping("manualOperation")
    public List<Map<String, Object>> manualOperation(String table, String imei) {
        return testService.manualOperation(table, imei);
    }

    /**
     * 修改
     *
     * @Author Ldpy
     * @Date 2021/11/30
     */
    @PostMapping("manualUpdate")
    public Map<String, Object> manualUpdate(String table, String imei) {
        return testService.manualUpdate(table, imei);
    }

}

import java.util.List;
import java.util.Map;


public interface TestService {

    /**
     * @Author Ldpy
     * @Description 查询测试
     * @Date 2021/12/1 18:34
     **/
    List<Map<String, Object>> manualOperation(String table, String imei);

    /**
     * @Author Ldpy
     * @Description 更新测试
     * @Date 2021/12/1 18:34
     **/
    Map<String, Object> manualUpdate(String table, String imei);

}

import com.xxx.xxx.aop.DataSource;
import com.xxx.xxx.aop.DataSourceNames;
import com.xxx.xxx.mysql.TestService;
import com.xxx.xxx.mysql.mapper.TestMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
public class TestServiceImpl implements TestService {

    @Autowired
    private TestMapper testMapper;

    // 需要哪个数据源直接添加注解即可，不加默认用MYSQLDB1
    @Override
    @DataSource(dataSourceName = DataSourceNames.MYSQLDB2)
    public List<Map<String, Object>> manualOperation(String table, String imei) {
        return testMapper.manualOperation(table, imei);
    }

    // 测试事务
    @Override
    @Transactional
    @DataSource(dataSourceName = DataSourceNames.MYCATDB)
    public Map<String, Object> manualUpdate(String table, String imei) {
        testMapper.manualDelete(table, imei);
        testMapper.manualUpdate(table, imei);
        Map<String, Object> map = new HashMap<>();
        map.put("result", 2/0);
        return map;
    }
}

import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;


public interface TestMapper {

    List<Map<String, Object>> manualOperation(@Param("table") String table, @Param("imei") String imei);

    int manualDelete(@Param("table") String table, @Param("imei") String imei);

    int manualUpdate(@Param("table") String table, @Param("imei") String imei);

}

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxx.xxx.mysql.mapper.TestMapper">

    <select id="manualOperation" resultType="map">
        SELECT
            字段1, 字段2
        FROM
            表名
        WHERE
            imei = #{imei}
    </select>

    <update id="manualDelete">
        DELETE FROM
            表名
        WHERE
            imei = #{imei}
    </update>

    <update id="manualUpdate">
        INSERT INTO `表名`(`imei`, `len`, `run_time`)
        VALUES ('124526', 381.50, 24)
    </update>

四、遇到的问题

    1、数据库连接问题：（dataSource or dataSourceClassName or jdbcUrl is required.）MySQL版本不同，jdbc-url 和 driver-class-name 的写法不同，需要注意；

    2、事务不生效：首先考虑MySQL引擎，InnoDB 引擎支持事务，而 MyISAM 引擎不支持事务，期次看下是否在配置数据源时忘记注入事务管理器。