springboot + mybatis + druid 多数据源管理

最新推荐文章于 2024-05-03 11:43:40 发布

lzq199528

最新推荐文章于 2024-05-03 11:43:40 发布

阅读量275

点赞数 1

分类专栏：后端文章标签：多数据源 druid mybatis

本文链接：https://blog.csdn.net/lzq199528/article/details/110382920

版权

后端专栏收录该内容

65 篇文章 0 订阅

订阅专栏

引入 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>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

        <!--druid(数据库连接池)-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <!--mybatis-->
        <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>

配置数据库信息（两个数据源及连接池信息）和 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

创建一个 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. 指定使用此数据源的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;
    }
}

注解说明

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

定义第二个数据源

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.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.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
 * @MapperScan(basePackages = {"com.boot.multi.source.mapper.producer"}, sqlSessionFactoryRef = "producerSqlSessionFactory")
 * com.boot.multi.source.mapper.producer 声明数据源生效目录
 */
@Configuration
@MapperScan(basePackages = {"com.boot.multi.source.mapper.producer"}, sqlSessionFactoryRef = "producerSqlSessionFactory")
public class ProducerDataSourceConfiguration {

    /**
     * 加载 producer库 配置
     * @return
     */
    @Bean("producerDatasourceModel")
    @ConfigurationProperties(prefix = "spring.datasource.producer")
    public DatasourceModel producerDatasourceModel() {
        DatasourceModel datasourceModel = new DatasourceModel();
        datasourceModel.setType(com.alibaba.druid.pool.DruidDataSource.class);
        return datasourceModel;
    }

    /**
     * 配置数据源producer
     *
     * @return
     */
    @Bean(name = "producerDataSource")
    public DataSource producerDataSource(
            @Qualifier("poolProperties") Properties poolProperties,
            @Qualifier("producerDatasourceModel") DatasourceModel producerDatasourceModel) throws SQLException {
        // 创建DruidDataSource
        DruidDataSource druidDataSource = (DruidDataSource) DataSourceBuilder.create()
                // 设置数据源类型
                .type(producerDatasourceModel.getType())
                .driverClassName(producerDatasourceModel.getDriverClassName())
                .url(producerDatasourceModel.getUrl())
                .username(producerDatasourceModel.getUserName())
                .password(producerDatasourceModel.getPassword())
                .build();
        // 加载链接池信息
        druidDataSource.configFromPropety(poolProperties);
        // 数据源初始化
        druidDataSource.init();
        return druidDataSource;
    }

    /**
     * producer数据库的会话工厂
     *
     * @param producerDataSource
     * @param properties
     * @param resourceLoader
     * @return
     * @throws Exception
     */
    @Bean(name = "producerSqlSessionFactory")
    public SqlSessionFactory producerSqlSessionFactory(
            @Qualifier("producerDataSource") DataSource producerDataSource,
            @Qualifier("mybatisData") MybatisProperties properties,
            ResourceLoader resourceLoader) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        // 设置数据源
        bean.setDataSource(producerDataSource);
        // 设置类型包
        bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        // 设置mybatis配置文件
        bean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
        // 设置Mapper.xml位置
        bean.setMapperLocations(properties.resolveMapperLocations());
        return bean.getObject();
    }

    /**
     * producer数据库的会话模板
     *
     * @param producerSqlSessionFactory
     * @return
     */
    @Bean(name = "producerSqlSessionTemplate")
    public SqlSessionTemplate producerSqlSessionTemplate(@Qualifier("producerSqlSessionFactory") SqlSessionFactory producerSqlSessionFactory) {
        return new SqlSessionTemplate(producerSqlSessionFactory);
    }

    /**
     * producer数据库的事务
     *
     * @param producerDataSource
     * @return
     */
    @Bean(name = "producerTransactionManager")
    public DataSourceTransactionManager producerTransactionManager(@Qualifier("producerDataSource") DataSource producerDataSource) {
        return new DataSourceTransactionManager(producerDataSource);
    }

}

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;
    }
}

创建测试 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. 在一个业务中操作多个库得增删改操作，会有分布式事务问题。
测试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);
    }
}

源码

多数据源分布式事务

lzq199528

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springboot + mybatis + druid 多数据源管理

引入 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 ht.
复制链接

扫一扫