分库分表插入（二）springboot分库分表插入数据库_分库分表后如何插入数据

最近在做数据的分库分表插入，考虑了几种方式，一种是spring+mybatis的动态数据源方式插入数据库，另一种是选择springboot + mycat方式，这里使用的是springboot固定配置两个数据源，一个数据库用来查询，另一个用来存储数据，相对固定没有了动态选择，也相对简单了一点。

1、首先 .yml配置数据库

#spring properties
spring:
  profiles: local
  application:
    name: syncService
  datasource:
  # master主数据源
    master:
      url: jdbc:mysql://127.0.0.1:3306/cdot_business?useUnicode=true&characterEncoding=utf-8
      username: root
      password: *******
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 2
      maxActive: 20
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      logSlowSql: true
 
  # cluster从数据源
    cluster:
      url: jdbc:mysql://192.168.1.243:8066/cdot_anyue_business?useUnicode=true&characterEncoding=utf-8
      username: ****
      password: ******
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 2
      maxActive: 20
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      logSlowSql: true

2、两个数据库配置：

（1）MasterSourceConfig.java

该数据库如何确定对应哪个dao下mapper呢？

即@MapperScan(basePackage="com.chargedot.syncservice.dao.mapper.master")

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
 
import javax.sql.DataSource;
 
/**
 * @author yanghao
 * @Description:
 * @date 2019/3/22 15:41
 */
@Configuration
@MapperScan(basePackages = "com.chargedot.syncservice.dao.mapper.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterSourceConfig {
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
 
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
 
    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

（2）ClusterSourceConfig.java

该数据库如何确定对应哪个dao下mapper呢？

即@MapperScan(basePackage="com.chargedot.syncservice.dao.mapper.cluster")

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
 
/**
 * @author yanghao
 * @Description:
 * @date 2019/3/22 15:47
 */
@Configuration
@MapperScan(basePackages = "com.chargedot.syncservice.dao.mapper.cluster", sqlSessionTemplateRef = "clusterSqlSessionTemplate")
public class ClusterSourceConfig {
 
    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("clusterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
 
    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("clusterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
 
    @Bean(name = "clusterSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("clusterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
 
}

（3）MultiDataSourceConfig.java

配置两个数据源，@Primary设置主数据源，注意点：两个数据源必须两个不同的实例名，否则会被覆盖

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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 javax.sql.DataSource;
 
/**
 * @author yanghao
 * @Description:
 * @date 2019/3/22 15:54
 */
@Configuration
public class MultiDataSourceConfig {
    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        return DruidDataSourceBuilder.create().build();
    }
    @Bean(name = "clusterDataSource")
    @ConfigurationProperties("spring.datasource.cluster")
    public DataSource clusterDataSource(){
        return DruidDataSourceBuilder.create().build();
    }
}