2020.03.06 springboot+mybatis+mysql+sqlserver多数据源

最新推荐文章于 2024-09-23 08:45:18 发布

毛毛禾苗

最新推荐文章于 2024-09-23 08:45:18 发布

阅读量776

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/never_mm/article/details/104691061

版权

java 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本文详细介绍了在一个项目中整合mysql和sqlserver两种数据库的过程，包括配置application-test.yml，实现Master和Cluster数据源的配置，创建相应的DbConfiguration，以及在service层的调用方式，为多数据源场景提供了一套完整的解决方案。

摘要由CSDN通过智能技术生成

1.前言
项目中本来用的是单一的mysql数据库，最近增加了一个sqlserver数据库，在整合的过程中遇到了很多坑，反复进行调整参考了很多资料不太行，一通骚操作突然好了，疑惑.jpg

2.application-test.yml

spring:
  datasource:
    druid:
      master:
        username: root
        password: root
        jdbc-url: jdbc:mysql://127.0.0.1:3306/aaa?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&autoReconnect=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      cluster:
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        jdbc-url: jdbc:sqlserver://127.0.0.1;databaseName=bbb;
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
          thymeleaf:
    prefix: classpath:/templates/
    cache: false
    mode: html
    encoding: utf-8
  devtools:
    restart:
      enabled: true
      additional-paths: src/main/java
mybatis:
#项目中xml文件放在了resources文件下
  mapper-locations: classpath:master/*Mapper.xml,classpath*:/cluster/*Mapper.xml
  type-aliases-package: com.test.

3.MasterDbConfiguration

package com.test.interceptor;
import lombok.extern.slf4j.Slf4j;
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.beans.factory.annotation.Value;
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
@Slf4j
@MapperScan(basePackages ={"com.test.mapper.master"} , sqlSessionTemplateRef  = "masterSqlSessionFactory")
public class MasterDbConfiguration {

    /**
     *  主数据源配置信息
     * @return
     */
    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master")
    @Primary
    public DataSource masterDataSource(){
        log.info("主数据源启动...");
        return DataSourceBuilder.create().build();
    }

    /**
     * 主数据源会话工厂
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource" ) DataSource dataSource) throws Exception {
        try {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            // 如果不写这里，会导致 mybatis 出现 invalid bound statement (not found) 的问题
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:master/*Mapper.xml"));
            bean.setTypeAliasesPackage("com.test.pojo");
            bean.setDataSource(dataSource);
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 主数据源事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterDataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     *
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4.ClusterDbConfiguration

package com.test.interceptor;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
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.beans.factory.annotation.Value;
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
@Slf4j
@MapperScan(basePackages = {"com.test.service.cluster.clusterMapper"}, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDbConfiguration {

 
    /**
     * 副数据源配置信息
     * @return
     */
    @Bean(name = "clusterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.cluster")
    public DataSource clusterDataSource(){
        log.info("副数据源启动...");
        return DataSourceBuilder.create().build();
    }

    /**
     * 副数据源会话工厂
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource" ) DataSource dataSource) throws Exception {
        try {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            // 如果不写这里，会导致 mybatis 出现 invalid bound statement (not found) 的问题
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:cluster/*Mapper.xml"));
            bean.setTypeAliasesPackage("com.test.pojo");
            bean.setDataSource(dataSource);

            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 副数据源事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterDataSourceTransactionManager(@Qualifier("clusterDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     *
    *  @param sqlSessionFactory
     * @return
     */
    @Bean(name = "clusterSqlSessionTemplate")
    public SqlSessionTemplate clusterSqlSessionTemplate(@Qualifier("clusterSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

5.service层
在这里插入图片描述

6.启动项

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class,MybatisAutoConfiguration.class})
@MapperScan(basePackages ={"com.test.service.master.masterMapper","com.test.service.cluster.clusterMapper"})
public class TestApplication {
	public static void main(String[] args) {
		SpringApplication.run(TestApplication .class, args);
	}
}

7.调用方式
和但数据源的方式一样，该怎么样就是怎么样