Springboot + Mybatis 配置多数据源

Springboot + Mybatis 配置多数据源

1、引入依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

2、编写启动类

@ServletComponentScan
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class DynamicDataSourceDemoApplication {

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

3、编写配置文件

server:
  port: 8888

spring:
  application:
    name: dynamic-data-source-mybatis-demo
  datasource:
    # 主数据源
    master:
      driverClassName: com.mysql.jdbc.Driver
      jdbcUrl: jdbc:mysql://127.0.0.1:3306/test_master?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT
      username: root
      password: root
    # 从数据源
    slave:
      enabled: true  # 从数据源开关/默认关闭
      driverClassName: com.mysql.jdbc.Driver
      jdbcUrl: jdbc:mysql://127.0.0.1:3306/test_slave?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT
      username: root
      password: root
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: select count(*) from dual
    testWhileIdle: true
    testOnBorrow: true
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    filters: stat
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    timeBetweenLogStatsMillis: 0
      
mybatis:
  mapper-locations: classpath:/mapper/*
  configuration:
    mapUnderscoreToCamelCase: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    

4、编写数据源枚举类

public enum DataSourceType {
    MASTER,
    
    SLAVE,
}

5、编写自定义注解

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    /**
     * 切换数据源名称
     */
    DataSourceType value() default DataSourceType.MASTER;
}

6、编写相关配置类

DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        // afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
        super.afterPropertiesSet();
    }

    /**
     * 根据Key获取数据源的信息
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

DynamicDataSourceContextHolder

public class DynamicDataSourceContextHolder {
    public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);

    /**
     * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     *  所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 设置数据源变量
     * @param dataSourceType
     */
    public static void setDataSourceType(String dataSourceType){
        log.info("切换到{}数据源", dataSourceType);
        CONTEXT_HOLDER.set(dataSourceType);
    }

    /**
     * 获取数据源变量
     * @return
     */
    public static String getDataSourceType(){
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空数据源变量
     */
    public static void clearDataSourceType(){
        CONTEXT_HOLDER.remove();
    }
}

DataSourceConfig

@Configuration
public class DataSourceConfig {
    private static final Logger log = LoggerFactory.getLogger("DataSourceConfig");
    //主数据源
    @Value("${spring.datasource.master.jdbcUrl}")
    private String masterJdbcUrl;
    @Value("${spring.datasource.master.username}")
    private String masterUsername;
    @Value("${spring.datasource.master.password}")
    private String masterPassword;
    @Value("${spring.datasource.master.driverClassName}")
    private String masterDriverClassName;
    //从数据源
    @Value("${spring.datasource.slave.jdbcUrl}")
    private String slaveJdbcUrl;
    @Value("${spring.datasource.slave.username}")
    private String slaveUsername;
    @Value("${spring.datasource.slave.password}")
    private String slavePassword;
    @Value("${spring.datasource.slave.driverClassName}")
    private String slaveDriverClassName;

    //其他相关配置
    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("${spring.datasource.connectionProperties}")
    private String connectionProperties;
    @Value("${spring.datasource.timeBetweenLogStatsMillis}")
    private int timeBetweenLogStatsMillis;

    @Bean
    public DataSource masterDataSource() {
        return generateDataSource(masterJdbcUrl,masterUsername,masterPassword,masterDriverClassName);
    }
    @Bean
    @ConditionalOnProperty(prefix = "spring.datasource.slave", name = "enabled", havingValue = "true")
    public DataSource slaveDataSource() {
        return generateDataSource(slaveJdbcUrl, slaveUsername, slavePassword, slaveDriverClassName);
    }

    private DruidDataSource generateDataSource(String url, String username, String password, String driverClassName){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            System.err.println("druid configuration initialization filter: " + e);
        }
        datasource.setConnectionProperties(connectionProperties);
        datasource.setTimeBetweenLogStatsMillis(timeBetweenLogStatsMillis);
        return datasource;
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource, DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
        targetDataSources.put(DataSourceType.SLAVE.name(), slaveDataSource);
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/**/?*Mapper.xml"));
        sessionFactory.setConfiguration(globalMyBatisConfig());
        return sessionFactory.getObject();
    }

    @Bean
    @ConfigurationProperties(prefix = "mybatis.configuration")
    public org.apache.ibatis.session.Configuration globalMyBatisConfig() {
        return new org.apache.ibatis.session.Configuration();
    }

    /**
     * 配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    
}

DataSourceAspect

@Aspect
@Order(1)
@Component
public class DataSourceAspect {
    
    //@within在类上设置
    //@annotation在方法上进行设置
    @Pointcut("execution(public * com.qzh.*.dao..*.*(..))")
    public void pointcut() {}

    @Before("pointcut()")
    public void doBefore(JoinPoint joinPoint)
    {
        Method method = ((MethodSignature)joinPoint.getSignature()).getMethod();
        DataSource dataSource = method.getAnnotation(DataSource.class);//获取方法上的注解
        if(dataSource == null){
            Class<?> clazz= joinPoint.getTarget().getClass().getInterfaces()[0];
            dataSource =clazz.getAnnotation(DataSource.class);//获取类上面的注解
            if(dataSource == null) {
                return;
            }
        }
        if (dataSource != null) {
            DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name());
        }
    }

    @After("pointcut()")
    public void after(JoinPoint point) {
        //清理掉当前设置的数据源，让默认的数据源不受影响
        DynamicDataSourceContextHolder.clearDataSourceType();
    }
}

7、实体类

@Data
@ToString
public class User {
    
    private String name;
    
    private Integer age;
    
    public User(){}
    
}

8、编写Dao层

接口

//没有DataSource注解，则默认在主数据源中进行sql操作
@Repository
@Mapper
public interface UserMapper {
    
    int insert(User user);

    @Select("SELECT * FROM user")
    List<User> selectList();
}

@Repository
@Mapper
//从数据源中进行sql操作
@DataSource(DataSourceType.SLAVE)
public interface SlaveUserMapper {
    
    int insert(User user);

    @Select("SELECT * FROM user")
    List<User> selectList();
}

xml文件

• SlaveUserMapper.xml

  <?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.qzh.dynamicDataSourceDemo.dao.SlaveUserMapper">
      <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="com.qzh.dynamicDataSourceDemo.entity.User">
          INSERT INTO `user` (`name`,age)
          VALUES (#{name},#{age})
      </insert>
  </mapper>
  

• UserMapper.xml

  <?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.qzh.dynamicDataSourceDemo.dao.UserMapper">
      <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="com.qzh.dynamicDataSourceDemo.entity.User">
          INSERT INTO `user` (`name`,age)
          VALUES (#{name},#{age})
      </insert>
  </mapper>
  
  

9、编写Service层

接口

public interface UserService {
    String addUser();

    String getUserFromMaster();

    String getUserFromSlave();
}

实现类

@Service
public class UserServiceImpl implements UserService {
    
    @Autowired
    private UserMapper masterUserMapper;
    @Autowired
    private SlaveUserMapper slaveUserMapper;

    @Override
    public String addUser() {
        User master = new User();
        master.setName("test_master");
        master.setAge(10);
        masterUserMapper.insert(master);
        User slave = new User();
        slave.setName("test_slave");
        slave.setAge(100);
        slaveUserMapper.insert(slave);
        return "success";
    }

    @Override
    public String getUserFromMaster() {
        return masterUserMapper.selectList().toString();
    }

    @Override
    public String getUserFromSlave() {
        return slaveUserMapper.selectList().toString();
    }
}

10、编写controller层

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private UserService userService;
    
    @PostMapping("/testAddUser")
    public String testAddUser() {
        return userService.addUser();
    }

    @GetMapping("/testGetUserFromMaster")
    public String testGetUserFromMaster() {
        return userService.getUserFromMaster();
    }

    @GetMapping("/testGetUserFromSlave")
    public String testGetUserFromSlave() {
        return userService.getUserFromSlave();
    }
}

启动服务，调用接口测试即可。