springboot + mybatis + druid 多数据源管理

骑猪看日落 2022-12-25 02:56 141阅读 0赞

1.  引入 mysql mybatis druid 开发包

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>springBoot-mybatis-multiSource</groupId>
        <artifactId>springBoot-mybatis-multiSource</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.1.7.RELEASE</version>
        </parent>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.11</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.21</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.2</version>
                <scope>provided</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <configuration>
                        <source>8</source>
                        <target>8</target>
                    </configuration>
                </plugin>
    
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <mainClass>com.boot.multi.source.Application</mainClass>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    </project>

1.  配置数据库信息（两个数据源及连接池信息） 和 mybatis配置

spring:
      application:
        name: springBoot-mybatis-multiSource
      datasource:
        # 数据源1
        consumer:
          url: jdbc:mysql://101.37.152.195:3306/consumer?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true
          username: root
          password: lzq199528
          driverClassName: com.mysql.cj.jdbc.Driver
        # 数据源2
        producer:
          url: jdbc:mysql://101.37.152.195:3306/producer?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true
          username: root
          password: lzq199528
          driverClassName: com.mysql.cj.jdbc.Driver
    
        #连接池的配置信息
        pool:
          initialSize: 5
          minIdle: 10
          maxActive: 20
          maxWait: 6000
          timeBetweenEvictionRunsMillis: 60000
          minEvictableIdleTimeMillis: 30000
          validationQuery: SELECT 1
          validationQueryTimeout: 10000
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          filters: stat,wall
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          useGlobalDataSourceStat: true
    #mybatis 配置信息
    mybatis:
      type-aliases-package: com.boot.multi.source.domain
      config-location: classpath:mybatis/mybatis-config.xml
      mapper-locations: classpath:mappers/consumer/*.xml,classpath:mappers/producer/*.xml
    server:
      port: 10080

1.  创建一个 Model类。用来创建数据库实例对象

package com.boot.multi.source.conf.datasource;
    
    import lombok.Data;
    
    /** * @author 刘志强 * @date 2020/11/27 15:25 */
    @Data
    public class DatasourceModel { 
    
        private String url;
    
        private String userName;
    
        private String password;
    
        private String driverClassName;
    
        private Class type;
    
    }

1.  配置第一个数据源信息
    
    1.  指定使用此数据源的mapper目录
    2.  创建此数据源DataSource，并加载数据源配置信息
    3.  创建此数据源的事务处理器
    4.  创建此数据源的 SqlSessionFactory 加载 mybatis配置
    5.  创建此数据源的 SqlSessionTemplate

package com.boot.multi.source.conf.datasource;
    
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
    import org.springframework.beans.factory.annotation.Autowired;
    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.env.Environment;
    import org.springframework.core.io.ResourceLoader;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    import java.util.Properties;
    
    /** * @author 刘志强 * @date 2020/11/27 14:02 * com.boot.multi.source.mapper.consumer 生效范围 */
    @Configuration
    @MapperScan(basePackages = { "com.boot.multi.source.mapper.consumer"},sqlSessionFactoryRef = "consumerSqlSessionFactory")
    public class ConsumerDataSourceConfiguration { 
    
        @Autowired
        private Environment env;
    
        /** * 构建 consumer 数据源信息 * @return */
        @Bean("consumerDatasourceModel")
        @ConfigurationProperties(prefix = "spring.datasource.consumer")
        public DatasourceModel consumerDatasourceModel() { 
            DatasourceModel datasourceModel = new DatasourceModel();
            datasourceModel.setType(com.alibaba.druid.pool.DruidDataSource.class);
            return datasourceModel;
        }
    
        /** * 配置数据源consumer * @Primary 默认装配的bean * @return */
        @Bean(name="consumerDataSource")
        @Primary
        public DataSource consumerDataSource(
                @Qualifier("poolProperties") Properties poolProperties,
                @Qualifier("consumerDatasourceModel") DatasourceModel consumerDatasourceModel) throws SQLException { 
            DruidDataSource druidDataSource = (DruidDataSource) DataSourceBuilder.create()
                    // 数据库 连接池类型 如果不设置类型默认类型为 com.zaxxer.hikari.HikariDataSource
                    .type(consumerDatasourceModel.getType())
                    // 驱动
                    .driverClassName(consumerDatasourceModel.getDriverClassName())
                    // 链接
                    .url(consumerDatasourceModel.getUrl())
                    // 账号
                    .username(consumerDatasourceModel.getUserName())
                    // 密码
                    .password(consumerDatasourceModel.getPassword())
                    .build();
            // 加载连接池配置信息
            druidDataSource.configFromPropety(poolProperties);
            // 初始化
            druidDataSource.init();
            return druidDataSource;
        }
    
        /** * consumer数据库的会话工厂 * @param consumerDataSource * @param properties * @param resourceLoader * @return * @throws Exception */
        @Bean(name = "consumerSqlSessionFactory")
        @Primary
        public SqlSessionFactory consumerSqlSessionFactory(
                @Qualifier("consumerDataSource") DataSource consumerDataSource,
                @Qualifier("mybatisData") MybatisProperties properties,
                ResourceLoader resourceLoader) throws Exception{ 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(consumerDataSource);
            bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
            bean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
            bean.setMapperLocations(properties.resolveMapperLocations());
            return bean.getObject();
        }
    
        /** * consumer数据库的会话模板 * @param consumerSqlSessionFactory * @return */
        @Bean(name = "consumerSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate consumerSqlSessionTemplate(@Qualifier("consumerSqlSessionFactory") SqlSessionFactory consumerSqlSessionFactory) { 
            return  new SqlSessionTemplate(consumerSqlSessionFactory);
        }
    
        /** * consumer数据库的事务 * @param consumerDataSource * @return */
        @Bean(name = "consumerTransactionManager")
        @Primary
        public DataSourceTransactionManager consumerTransactionManager(@Qualifier("consumerDataSource") DataSource consumerDataSource){ 
            return new DataSourceTransactionManager(consumerDataSource);
        }
    
        /** * +定义配置文件 * @return MybatisProperties */
        @Bean(name = "mybatisData")
        @ConfigurationProperties(prefix = "mybatis")
        @Primary
        public MybatisProperties mybatisProperties() { 
            MybatisProperties mybatisProperties = new MybatisProperties();
            return mybatisProperties;
        }
    
    
        /** * 构建Properties * @return */
        @Bean("poolProperties")
        public Properties poolProperties() { 
            String prefixPool = "spring.datasource.pool.";
            Properties prop = new Properties();
            prop.put("druid.initialSize", env.getProperty(prefixPool + "initialSize", String.class));
            prop.put("druid.maxActive", env.getProperty(prefixPool + "maxActive", String.class));
            prop.put("druid.minIdle", env.getProperty(prefixPool + "minIdle", String.class));
            prop.put("druid.maxWait", env.getProperty(prefixPool + "maxWait", String.class));
            prop.put("druid.poolPreparedStatements", env.getProperty(prefixPool + "poolPreparedStatements", String.class));
            prop.put("druid.maxPoolPreparedStatementPerConnectionSize", env.getProperty(prefixPool + "maxPoolPreparedStatementPerConnectionSize", String.class));
            prop.put("druid.validationQuery", env.getProperty(prefixPool + "validationQuery", String.class));
            prop.put("druid.validationQueryTimeout", env.getProperty(prefixPool + "validationQueryTimeout", String.class));
            prop.put("druid.testOnBorrow", env.getProperty(prefixPool + "testOnBorrow", String.class));
            prop.put("druid.testOnReturn", env.getProperty(prefixPool + "testOnReturn", String.class));
            prop.put("druid.testWhileIdle", env.getProperty(prefixPool + "testWhileIdle", String.class));
            prop.put("druid.timeBetweenEvictionRunsMillis", env.getProperty(prefixPool + "timeBetweenEvictionRunsMillis", String.class));
            prop.put("druid.minEvictableIdleTimeMillis", env.getProperty(prefixPool + "minEvictableIdleTimeMillis", String.class));
            prop.put("druid.filters", env.getProperty(prefixPool + "filters", String.class));
            return prop;
        }
    }

1.  注解说明

<table> 
 <thead> 
  <tr> 
   <th>注解</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>@Configuration</td> 
   <td>定义声明配置类等同于一个bean.xml配置文件。在此类中注册bean</td> 
  </tr> 
  <tr> 
   <td>@MapperScan</td> 
   <td>定义要扫描mapper类包的路径</td> 
  </tr> 
  <tr> 
   <td>@Autowired</td> 
   <td>自动装配实例</td> 
  </tr> 
  <tr> 
   <td>@Bean</td> 
   <td>创建一个bean对象</td> 
  </tr> 
  <tr> 
   <td>@ConfigurationProperties</td> 
   <td>加载外部配置。 外部配置属性名要和要构建对象的类的属性相匹配</td> 
  </tr> 
  <tr> 
   <td>@Primary</td> 
   <td>声明bean是默认的实例</td> 
  </tr> 
  <tr> 
   <td>@Qualifier</td> 
   <td>指定加载某个实例 未指定加载@Primary声明的</td> 
  </tr> 
 </tbody> 
</table>

1.  定义第二个数据源

1.  Druid配置

package com.boot.multi.source.conf.druid;
    
    
    import com.alibaba.druid.filter.stat.StatFilter;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import com.alibaba.druid.wall.WallConfig;
    import com.alibaba.druid.wall.WallFilter;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    
    /** * @author 刘志强 * @date 2020/11/27 14:02 * @ServletComponentScan // 用于扫描所有的Servlet、filter、listener */
    @Configuration
    public class DruidConfig { 
    
        @Bean
        public ServletRegistrationBean<StatViewServlet> druidServlet() { 
            ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
    
            servletRegistrationBean.addInitParameter("loginUsername", "root");
            servletRegistrationBean.addInitParameter("loginPassword", "1234");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() { 
            FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
            filterRegistrationBean.setFilter(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            filterRegistrationBean.addInitParameter("profileEnable", "true");
            return filterRegistrationBean;
        }
    
        @Bean
        public StatFilter statFilter(){ 
            StatFilter statFilter = new StatFilter();
            //slowSqlMillis用来配置SQL慢的标准，执行时间超过slowSqlMillis的就是慢。
            statFilter.setLogSlowSql(true);
            //SQL合并配置
            statFilter.setMergeSql(true);
            //slowSqlMillis的缺省值为3000，也就是3秒。
            statFilter.setSlowSqlMillis(1000);
            return statFilter;
        }
    
        @Bean
        public WallFilter wallFilter(){ 
            WallFilter wallFilter = new WallFilter();
            //允许执行多条SQL
            WallConfig config = new WallConfig();
            config.setMultiStatementAllow(true);
            wallFilter.setConfig(config);
            return wallFilter;
        }
    }

1.  创建测试 controller,service,impl.mapper(dao),domain,mapper.xml
    
    1.  在com.boot.multi.source.mapper目录下创建consumer和producer目录。在上面我们对每个数据源定义了生效的目录。consumer目录下就会走consumer数据源，producer目录下就会走producer数据源
    2.  在impl文件中的事务，上面我们创建了两个事务处理器。consumerTransactionManager和producerTransactionManager，其中consumerTransactionManager是默认处理器。在@Transactional注解中 指定对应得事务处理器 @Transactional(rollbackFor = Exception.class,transactionManager=“producerTransactionManager”)。
    3.  在一个业务中操作多个库得增删改操作，会有分布式事务问题。
2.  测试controller

package com.boot.multi.source.controller;
    
    import com.boot.multi.source.domain.consumer.ConsumerUser;
    import com.boot.multi.source.domain.producer.ProducerUser;
    import com.boot.multi.source.service.ConsumerUserService;
    import com.boot.multi.source.service.ProducerAndConsumerService;
    import com.boot.multi.source.service.ProducerUserService;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.CrossOrigin;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.Map;
    
    /** * @author 刘志强 * @date 2020/11/27 14:18 */
    @RestController
    @Slf4j
    @CrossOrigin
    public class TestController { 
    
        @Autowired
        private ConsumerUserService consumerUserService;
        @Autowired
        private ProducerUserService producerUserService;
        @Autowired
        private ProducerAndConsumerService producerAndConsumerService;
    
    
        /** * 获取Consumer来源的用户 * @param id * @return */
        @GetMapping("getConsumerUser")
        public ConsumerUser getConsumerUser(Long id) { 
            return consumerUserService.getConsumerUser(id);
        }
    
        /** * 获取Producer来源的用户 * @param id * @return */
        @GetMapping("getProducerUser")
        public ProducerUser getProducerUser(Long id) { 
            return producerUserService.getProducerUser(id);
        }
    
        /** * 获取Consumer来源和Producer来源的用户 * @param producerId * @param consumerId * @return */
        @GetMapping("getProducerUserAndConsumerUser")
        public Map<String,Object> getProducerUserAndConsumerUser(Long producerId, Long consumerId) { 
            return producerAndConsumerService.getProducerUserAndConsumerUser(producerId,consumerId);
        }
    
        /** * 添加consumer用户 * @param consumerUser * @return */
        @PostMapping("addConsumerUser")
        public String addConsumerUser(ConsumerUser consumerUser) { 
            return consumerUserService.addConsumerUser(consumerUser);
        }
    
        /** * 添加producer用户 * @param producerUser * @return */
        @PostMapping("addProducerUser")
        public String addProducerUser(ProducerUser producerUser) { 
            return producerUserService.addProducerUser(producerUser);
        }
    
        /** * 添加producer和consumer用户 * 会产生分布式事务问题 * @param producerUserName * @param consumerUserName * @return */
        @PostMapping("addProducerUserAndConsumerUser")
        public String addProducerUserAndConsumerUser(String producerUserName, String consumerUserName) { 
            return producerAndConsumerService.addProducerUserAndConsumerUser(producerUserName,consumerUserName);
        }
    }

#### [源码][Link 1] ####

#### [多数据源分布式事务][Link 2] ####

[Link 1]: https://gitee.com/lzq199528/spring-boot-mybatis-multi-source
[Link 2]: https://blog.csdn.net/lzq199528/article/details/106297340