springboot mybtis 多数据源动态切换

最新推荐文章于 2024-07-29 15:07:01 发布

kylin_essay

最新推荐文章于 2024-07-29 15:07:01 发布

阅读量1.4k

点赞数

分类专栏： spring boot java 文章标签： springboot mybatis 通用mapper 多数据源

本文链接：https://blog.csdn.net/kylin_essay/article/details/83304802

版权

java 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

spring boot

2 篇文章 0 订阅

订阅专栏

注释：本例使用springboot+mybatis+通用mapper实现多数据源动态切换；@Transactional只能控制默认数据源的事物，同一个方法中使用多个数据源时，如需要控制事物请使用分布式事物

其中事物只能控制默认数据源，如需使用事物控制需使用分布式事物

本文贴出demo关键代码，结尾附上demo地址

创建三个库multiple-data-source-a，multiple-data-source-b，multiple-data-source-c

multiple-data-source-a库中创建user-tab表

CREATE TABLE `user_tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_number` varchar(50) DEFAULT NULL COMMENT '用户编号',
  `nick_name` varchar(20) DEFAULT NULL COMMENT '昵称',
  `user_name` varchar(20) DEFAULT NULL COMMENT '账号',
  `password` varchar(30) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户表';

multiple-data-source-b库中创建goods_tab表

CREATE TABLE `goods_tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_number` varchar(50) DEFAULT NULL COMMENT '编号',
  `goods_name` varchar(20) DEFAULT NULL COMMENT '名称',
  `goods_price` double(5,2) DEFAULT NULL COMMENT '价格',
  `goods_num` int(8) DEFAULT NULL COMMENT '商品数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='商品表';

multiple-data-source-c库中创建order_tab表

CREATE TABLE `order_tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_number` varchar(50) DEFAULT NULL COMMENT '订单编号',
  `goods_number` varchar(50) DEFAULT NULL COMMENT '商品编号',
  `user_number` varchar(50) DEFAULT NULL COMMENT '用户编号',
  `pay_money` double(5,2) DEFAULT NULL COMMENT '支付金额',
  `buy_count` int(5) DEFAULT NULL COMMENT '购买数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='订单表';

创建一个springboot项目，笔者boot版本是1.5.2
添加如下maven依赖

		<!--ali数据库连接池-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.8</version>
		</dependency>
		
		<!--mysql 连接 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
		<!--mybatis-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
		
		<!--通用mapper-->
		<dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>1.2.4</version>
		</dependency>
		
		<!--阿里巴巴json-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.47</version>
		</dependency>

此处使用的yml方式配置端口，数据库连接（使用的druid），mybatis配置

server:
  port: 8088
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # 用户
    user:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/multiple-data-source-a?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull
      username: root
      password: 123456
    # 商品
    goods:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/multiple-data-source-b?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull
      username: root
      password: 123456
    # 订单
    order:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://118.24.151.228:3306/multiple-data-source-c?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull
      username: root
      password: 123456  
   
    maximum-pool-size: 100
    max-idle: 10
    max-wait: 10000
    min-idle: 5
    initial-size: 5
    validation-query: SELECT 1
    test-on-borrow: false
    test-while-idle: true
    time-between-eviction-runs-millis: 18800


#mybatis配置
mybatis:
  mapper-locations: classpath:mybatis/*Mapper.xml
  type-aliases-package: com.multipledatasource.web.bean

接下来是多数据源配置；以上4步如有不清楚的请参文末demo

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

/**
 * 创建数据源
 */
@Configuration
@MapperScan(basePackages = "com.multipledatasource.web.mapper")//扫描包路径下的mapper
public class DataSourceConfig {

    /**
     * 读取yml或者properties配置文件
     */
    @Autowired
    private Environment env;

    /**
     * 配置数据源
     * @return
     */
    //用户
    @Bean(name = "dataSourceUser")//指定名称，未指定默认为方法名
    @ConfigurationProperties(prefix = "spring.datasource.user")//prefix对应yml中的前缀
    public DataSource dataSourceUser(){
        return DataSourceBuilder.create().build();
    }
    //商品
    @Bean(name = "dataSourceGoods")
    @ConfigurationProperties(prefix = "spring.datasource.goods")
    public DataSource dataSourceGoods(){
        return DataSourceBuilder.create().build();
    }
    //订单
    @Bean(name = "dataSourceOrder")
    @ConfigurationProperties(prefix = "spring.datasource.order")
    public DataSource dataSourceOrder(){
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态切换数据源类
     * @Primary 指定默认数据源
     * @param user
     * @param goods
     * @param order
     * @return
     */
    @Primary //指定默认数据源，此处为第一个是dataSourceUser
    @Bean("dataSource")
    public DynamicDataSource dataSource(@Qualifier("dataSourceUser") DataSource user,
                                        @Qualifier("dataSourceGoods") DataSource goods,
                                        @Qualifier("dataSourceOrder") DataSource order) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.USER, user);
        targetDataSources.put(DataSourceEnum.GOODS, goods);
        targetDataSources.put(DataSourceEnum.ORDER, order);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        // 设置默认的数据源
        dynamicDataSource.setDefaultTargetDataSource(user);
        return dynamicDataSource;

    }

    /**
     * 根据动态数据源配置，创建sqlSessionFactory
     * @param user
     * @param goods
     * @param order
     * @return
     * @throws Exception
     */
    @Primary
    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceUser") DataSource user,
                                               @Qualifier("dataSourceGoods") DataSource goods,
                                               @Qualifier("dataSourceOrder") DataSource order) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(this.dataSource(user, goods,order));
        sessionFactory.setTypeAliasesPackage(env.getProperty("mybatis.type-aliases-package"));//对应yml中mybatis的bean目录
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapper-locations")));//对应yml中mybatis的xml
        return sessionFactory.getObject();

    }


    /**
     * 根据动态数据源配置，创建transactionManager
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean("transactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }


}

/**
 *   数据源类型枚举类
 */
public enum DataSourceEnum {
    USER,GOODS,ORDER
}

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

/**
 * 动态切换数据源类
 *      使用DatabaseContextHolder获取当前线程的DataSourceEnum
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }

}

/**
 *数据源切换类
 *      构建一个DataSourceEnum容器，并提供了向其中设置和获取DataSorceEnum的方法
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<DataSourceEnum> contextHolder = new ThreadLocal<DataSourceEnum>(){
        @Override
        protected DataSourceEnum initialValue() {
            return DataSourceEnum.USER;//初始为用户数据源
        }
    };

    public static void setDatabaseType(DataSourceEnum type){
        contextHolder.set(type);
    }
    public static DataSourceEnum getDataSourceType(){
        return contextHolder.get();
    }
    public static void clearDataSourceType(){
        contextHolder.remove();
    }
}

测试，通过DataSourceContextHolder.setDatabaseType(DataSourceEnum.ORDER);设置动态切换数据源

@Service
public class TestServiceImpl implements TestService {

    @Autowired
    OrderTabMapper orderTabMapper;
    @Autowired
    GoodsTabMapper goodsTabMapper;


    @Override
    public JSONObject addOrder(JSONObject jsonObject) {
        String goodsNumber =  jsonObject.getString("goodsNumber");
        String userNumber = jsonObject.getString("userNumber");
        Double payMoney = jsonObject.getDouble("payMoney");
        //新增订单
        OrderTab orderTab = new OrderTab();
        orderTab.setBuyCount(1);
        orderTab.setGoodsNumber(goodsNumber);
        orderTab.setOrderNumber(UUID.randomUUID().toString());
        orderTab.setPayMoney(payMoney);
        orderTab.setUserNumber(userNumber);
        DataSourceContextHolder.setDatabaseType(DataSourceEnum.ORDER);//设置订单数据源
        orderTabMapper.addOrder(orderTab);

        //商品数量减一
        DataSourceContextHolder.setDatabaseType(DataSourceEnum.GOODS);//设置商品数据源
        int identify = goodsTabMapper.goodsDecrement(goodsNumber);
        JSONObject json = new JSONObject();
        json.put("code",1);
        json.put("msg","成功");
        return json;
    }
}

postman示例
本文大多为代码，其中的注解与关键类并未做释义，仅仅实现了功能，想知其所以然请自行百度或者评论，项目demo地址：https://gitee.com/kylin-easy/multiple-data-source