SpringBoot+Mybatis配置多源数据库

发表于

方法一

  1. 配置多个数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
yml复制代码spring:
  datasource:
    master:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3305/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
      username: root
      password: root
   
    movies:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3307/se_movies?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
      username: root
      password: root
  1. 配置数据库连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
java复制代码package com.my.equipment.config.oldConfig;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.my.equipment.web.seMoviesDao", sqlSessionTemplateRef  = "seMoviesSqlSessionTemplate")
public class SeMoviesDatasourceConfig {

    @Bean(name = "seMoviesDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.movies")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

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

    @Bean(name = "seMoviesTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("seMoviesDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "seMoviesSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("seMoviesSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
java复制代码package com.my.equipment.config.oldConfig;


import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.my.equipment.web.dao",sqlSessionTemplateRef = "seSqlSessionTemplate")
public class SeDatasourceConfig {


    @Bean(name = "seDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

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

    @Bean(name = "seTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("seDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "seSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("seSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

方法2

1.配置多个数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
yml复制代码spring:
  datasource:
    master:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3305/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
      username: root
      password: root
    slave:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3310/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
      username: root
      password: root
    movies:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3307/se_movies?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8
      username: root
      password: root

2.配置数据库连接

定义多元数据库

1
2
3
4
5
6
java复制代码package com.my.equipment.utils;

public enum DBTypeEnum {

    MASTER,SLAVE,MOVIES;
}

定义数据源切换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
csharp复制代码package com.my.equipment.utils;

public class DBContextHolder {


    private static final ThreadLocal<DBTypeEnum> contextHolder=new ThreadLocal<>();

    public static void set(DBTypeEnum dbTypeEnum){
        contextHolder.set(dbTypeEnum);
    }

    public static DBTypeEnum get(){
        return contextHolder.get();
    }

    public static void master(){
        set(DBTypeEnum.MASTER);
        System.out.println("写");
    }

    public static void slave(){
        set(DBTypeEnum.SLAVE);
        System.out.println("读");
    }

    public static void movies(){
        set(DBTypeEnum.MOVIES);
        System.out.println("movies");
    }

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

}

重写路由选择类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
scala复制代码package com.my.equipment.utils;


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

public class MyRoutingDataSource extends AbstractRoutingDataSource {


    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {

        return DBContextHolder.get();
    }
}

配置Mybatis SqlSessionFactory 和事务管理器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
kotlin复制代码package com.my.equipment.config;

import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;



@Configuration
@EnableTransactionManagement
public class MyBatisConfig {

    @Value("${mybatis.mapper-locations}")
    private String mapperLocation;

    @Resource(name = "myRoutingDataSource")
    private DataSource myRoutingDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
       SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
       sqlSessionFactoryBean.setDataSource(myRoutingDataSource);

       ResourcePatternResolver resolver=new PathMatchingResourcePatternResolver();
       sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocation));
       return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager(){
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

配置数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
kotlin复制代码package com.my.equipment.config;


import com.my.equipment.utils.DBTypeEnum;
import com.my.equipment.utils.MyRoutingDataSource;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DatasourceConfig {

    /**
     * 配置从数据库
     * @return
     */
    @Bean(name = "slaveDataSource")
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置主数据库
     * @return
     */
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

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


    @Bean
    public DataSource myRoutingDataSource(@Qualifier("slaveDataSource") DataSource slaveDataSource,
                                          @Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("moviesDataSource") DataSource moviesDataSource){



        Map<Object,Object> targetDataSource=new HashMap<>();
        targetDataSource.put(DBTypeEnum.MASTER,masterDataSource);
        targetDataSource.put(DBTypeEnum.SLAVE,slaveDataSource);
        targetDataSource.put(DBTypeEnum.MOVIES,moviesDataSource);

        MyRoutingDataSource myRoutingDataSource=new MyRoutingDataSource();
        //找不到用默认数据源
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        //可选择的目标数据源
        myRoutingDataSource.setTargetDataSources(targetDataSource);

        return myRoutingDataSource;


    }

}

切面实现数据源切换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
java复制代码package com.my.equipment.aspect;


import com.my.equipment.utils.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DataSourceAop {



    //com.my.equipment.web.dao及其子包下所有的方法
    @Pointcut("execution(* com.my.equipment.web.dao..*.*(..))")
    public void writePoint(){

    }

    //com.my.equipment.web.seSlaveDao及其子包下所有的方法(..*代表当前及其子目录)
    @Pointcut("execution(* com.my.equipment.web.seSlaveDao..*.*(..))")
    public void readPoint(){

    }

    @Pointcut("execution(* com.my.equipment.web.seMoviesDao..*.*(..))")
    public void moviesPoint(){

    }

    @Before("readPoint()")
    public void read(){
        DBContextHolder.slave();
    }

    @Before("writePoint()")
    public void write(){
        DBContextHolder.master();
    }

    @Before("moviesPoint()")
    public void movies(){
        DBContextHolder.movies();
    }
}

注意：事务问题，可以发现一但添加了@Transactional,那么它的数据源只会去使用默认的数据源。（由于 AbstractRoutingDataSource中有个机制，如果当前上下文的连接对象为空，获取一个连接对象，然后保存起来，下次doBegin再调用时，就直接用这个连接了，根本不做任何切换(类似于缓存命中！），这就导致切换失败，也许有人会想到提高注入优先级，但是本文的切面是基于Dao的,那么会导致无论你怎么调整优先级，务必是@Transctional优先注入，那么要解决该问题个人目前的思路是：在Controller优先切换数据源，在services中使用@Transactional该方法）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
less复制代码@ApiOperation(value = "结束录制")
@GetMapping(value = "/stop")
public String stop() {
    try {
        for (Map.Entry<Integer, Process> entry : map.entrySet()) {
            rtspToMP4.stopRecord(entry.getValue());
        }
        DBContextHolder.set(DBTypeEnum.MOVIES);
        masterSensorService.saveVideoUrl(names,urls);
    } catch (Exception e) {
        e.printStackTrace();
        return "录制失败";
    }
    return "结束录制";
}
1
2
3
4
5
6
7
8
9
10
less复制代码@Override
@Transactional
public void saveVideoUrl(List<String> names, List<String> urls) {
    for (int i = 0; i < names.size(); i++) {
        RecordVideo recordVideo=new RecordVideo();
        recordVideo.setUrl(urls.get(i));
        recordVideo.setName(names.get(i));
        recordVideoMapper.insertSelective(recordVideo);
    }
}

但是该方法仍然属于非分布式数据库事务层面，无法做到在一个方法中回滚两个不同的数据源。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%