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

标签：SpringBoot 自定义数据源 springframework annotation import org public

一、数据库配置文件（这里用的是阿波罗配置中心，也可以是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);
    }
 
}

四、遇到的问题

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

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

来源于：(52条消息) SpringBoot多数据源（自定义注解，动态数据源，事务实现）_springboot多数据源注解_Ldpy的博客-CSDN博客，感谢分享

标签：SpringBoot,自定义,数据源,springframework,annotation,import,org,public
From： https://www.cnblogs.com/goPush/p/17262244.html

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

相关文章

赞助商

阅读排行