SpringBoot多数据源配置

最新推荐文章于 2024-09-24 18:03:04 发布

String NUll

最新推荐文章于 2024-09-24 18:03:04 发布

阅读量344

点赞数

分类专栏： # MySql 文章标签：数据库

本文链接：https://blog.csdn.net/java_liuyuan/article/details/102932283

版权

MySql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前言：最近在做各种批处理项目，发现公司使用的batchcore服务连接8个数据源，之前没有研究过多数据源配置，写了一个小Demo，总结一下。

1.项目结构

在这里插入图片描述

2.项目依赖

<dependencies>
        <!-- spring-boot 相关 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
        <!-- mysql connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector.version}</version>
        </dependency>
        <!-- druid 连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${alibaba.druid.version}</version>
        </dependency>
        <!--hutool 工具-->
        <dependency>
            <groupId>com.xiaoleilu</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.1.6</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.10</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>
        <!--分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>${pagehelper.version}</version>
        </dependency>
    </dependencies>

3.数据源config

数据源1：

/**
 * fshows.com
 * Copyright (C) 2013-2019 All Rights Reserved.
 */
package com.example.config.datasource1;

import com.alibaba.druid.pool.DruidDataSource;
import com.example.config.DataSourceProperty;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @author liuyuan
 * @version DataSourceConfig1.java, v 0.1 2019-11-05 20:17
 */
@Configuration
@EnableConfigurationProperties({DataSourceProperty.class})
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan(basePackages = {"com.example.dal.financetransfer.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory1")
public class DataSourceConfig1 {

    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceConfig1.class);

    @Resource
    private DataSourceProperty dataSourceProperty;

    /**
     * 数据源
     */
    @Primary
    @Bean(name = "getDataSource1")
    public DataSource getDataSource1() throws SQLException {
        LOGGER.info("dataSource  build start ");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dataSourceProperty.getTest1Url());
        datasource.setDriverClassName(dataSourceProperty.getDriverClassName());
        datasource.setUsername(dataSourceProperty.getTest1Username());
        datasource.setPassword(dataSourceProperty.getTest1Password());
        datasource.setInitialSize(dataSourceProperty.getInitialSize());
        datasource.setMinIdle(dataSourceProperty.getMinIdle());
        datasource.setMaxWait(dataSourceProperty.getMaxWait());
        datasource.setMaxActive(dataSourceProperty.getMaxActive());
        datasource.setValidationQuery(dataSourceProperty.getValidationQuery());
        datasource.setMinEvictableIdleTimeMillis(dataSourceProperty.getMinEvictableIdleTimeMillis());
        datasource.setTimeBetweenEvictionRunsMillis(dataSourceProperty.getTimeBetweenEvictionRunsMillis());
        datasource.setTestWhileIdle(true);
        datasource.setTestOnBorrow(false);
        datasource.setTestOnReturn(false);
        // 配置监控统计拦截的filters
        datasource.setFilters("wall");
        LOGGER.info("dataSource  build end ");
        return datasource;
    }

    /**
     * 事务
     */
    @Primary
    @Bean(name = "transactionManager1")
    public DataSourceTransactionManager transactionManager1(@Qualifier("getDataSource1") DataSource authDataSource) {
        return new DataSourceTransactionManager(authDataSource);
    }

    @Primary
    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("getDataSource1") DataSource authDataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(authDataSource);
        String path = "classpath*:com/example/dal/financetransfer/mapper/xml/*.xml";
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(path));
        return sessionFactoryBean.getObject();
    }


    @Primary
    @Bean(name = "sqlSessionTemplate1")
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

数据源2：(其实两个数据源配置一摸一样，我们只需要保证关键的：数据源名称、事务管理器名称、sqlSession工厂名称、sql模板名称不一样就行)

/**
 * fshows.com
 * Copyright (C) 2013-2019 All Rights Reserved.
 */
package com.example.config.datasource2;

import com.alibaba.druid.pool.DruidDataSource;
import com.example.config.DataSourceProperty;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author liuyuan
 * @version DataSourceConfig2.java, v 0.1 2019-11-05 20:17
 */
@Configuration
@EnableConfigurationProperties({DataSourceProperty.class})
@EnableTransactionManagement(proxyTargetClass = true)
// @MapperScan(basePackages = {"com.example.dal.financeplatform.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class DataSourceConfig2 {

    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceConfig2.class);

    @Resource
    private DataSourceProperty dataSourceProperty;

    /**
     * Mapper接口所在包名，Spring会自动查找其下的类,和@MapperScan注解实现的功能一样
     *
     * @return
     */
    @Bean
    public static MapperScannerConfigurer odpsConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setBasePackage("com.example.dal.financeplatform.mapper");
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory2");
        return mapperScannerConfigurer;
    }

    /**
     * 数据源
     */
    @Primary
    @Bean(name = "getDataSource2")
    public DataSource getDataSource2() throws SQLException {
        LOGGER.info("dataSource  build start ");
        DruidDataSource datasource = new DruidDataSource();
        //  基本属性 url、user、password
        datasource.setUrl(dataSourceProperty.getTest2Url());
        datasource.setUsername(dataSourceProperty.getTest2Username());
        datasource.setPassword(dataSourceProperty.getTest2Password());
        // 驱动
        datasource.setDriverClassName(dataSourceProperty.getDriverClassName());
        //  配置初始化连接池大小、最小、最大值
        datasource.setInitialSize(dataSourceProperty.getInitialSize());
        datasource.setMinIdle(dataSourceProperty.getMinIdle());
        datasource.setMaxActive(dataSourceProperty.getMaxActive());
        //  配置获取连接等待超时的时间
        datasource.setMaxWait(dataSourceProperty.getMaxWait());
        // 用来检测连接是否有效
        datasource.setValidationQuery(dataSourceProperty.getValidationQuery());
        // 配置一个连接在池中最小生存的时间，单位是毫秒
        datasource.setMinEvictableIdleTimeMillis(dataSourceProperty.getMinEvictableIdleTimeMillis());
        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        datasource.setTimeBetweenEvictionRunsMillis(dataSourceProperty.getTimeBetweenEvictionRunsMillis());
        datasource.setTestWhileIdle(true);
        datasource.setTestOnBorrow(false);
        datasource.setTestOnReturn(false);
        // 打开PSCache，并且指定每个连接上PSCache的大小
        datasource.setPoolPreparedStatements(true);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(dataSourceProperty.getMaxPoolPreparedStatementPerConnectionSize());
        //  配置监控统计拦截的filters
        datasource.setFilters("wall");
        LOGGER.info("dataSource  build end ");
        return datasource;
    }

    /**
     * 事务
     */
    @Primary
    @Bean(name = "transactionManager2")
    public DataSourceTransactionManager transactionManager2(@Qualifier("getDataSource2") DataSource authDataSource) {
        return new DataSourceTransactionManager(authDataSource);
    }

    /**
     * 配置 分页插件 pageHelper(使用该类是放开注释)
     */
    @Primary
    @Bean(name = "mysqlPageHelperPlugin")
    public PageInterceptor buildPageHelperPlugin() {
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }

    /**
     * 创建 SqlSessionFactory 工厂
     */
    @Primary
    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("getDataSource2") DataSource authDataSource,
                                                @Qualifier("mysqlPageHelperPlugin") PageInterceptor pageInterceptor) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(authDataSource);
        String path = "classpath*:com/example/dal/financeplatform/mapper/xml/*.xml";
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(path));

        // 添加分页插件(两种方式任选一种)
        // Objects.requireNonNull(sessionFactoryBean.getObject()).getConfiguration().addInterceptor(pageInterceptor);
        sessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor});
        return sessionFactoryBean.getObject();
    }

    /**
     * MyBatis提供的持久层访问模板化的工具
     * 线程安全，可通过构造参数或依赖注入SqlSessionFactory实例
     */
    @Primary
    @Bean(name = "sqlSessionTemplate2")
    public SqlSessionTemplate sqlSessionTemplate2(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

如果你觉得这样获取datasource比较麻烦，需要set很多的属性，也可以直接使用注解进行注入，例如：

@Primary
    @Bean(name = "getDataSource1")
    @ConfigurationProperties(prefix = "datasource.mysql")
    public DataSource getDataSource1() {
        //DataSourceBuilder.create().build() 默认数据源类型是 org.apache.tomcat.jdbc.pool.DataSource
        //这里指定使用类型 -- 阿里DruidDataSource 连接池
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

@ConfigurationProperties(prefix = “datasource.mysql”)注解可以将配置文件中前缀为"datasource.mysql"的配置项，默认注入到DruidDataSource中，并且将"-"转换为驼峰，比如setUrl属性，在配置文件中就是：datasource.mysql.url=xxx；比如setDriverClassName属性，配置文件可以写成：datasource.mysql.driver-class-name=com.mysql.jdbc.Driver;

4.配置文件

#########################################mysql配置###########################################
#数据源配置-1
datasource.mysql.test1Url=jdbc:mysql://数据库地址1:3306/表名?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&rewriteBatchedStatements=TRUE
datasource.mysql.test1Username=用户名
datasource.mysql.test1Password=密码

#数据源配置-2
datasource.mysql.test2Url=jdbc:mysql://数据库地址2:3306/表名?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&rewriteBatchedStatements=TRUE
datasource.mysql.test2Username=用户名
datasource.mysql.test2Password=密码

#驱动
datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
datasource.mysql.max-active=64
#用来检测连接是否有效
datasource.mysql.validation-query=SELECT 1
#获取链接最大等待时间
datasource.mysql.max-wait=10000
#最小连接池数量
datasource.mysql.min-idle=5
#最大连接池数量
datasource.mysql.max-idle=10
#初始化大小
datasource.mysql.initial-size=5
#配置一个连接在池中最小生存的时间，单位是毫秒
datasource.mysql.min-evictable-idle-time-millis=300000
#空闲链接检测线程检测周期。如果为负值，表示不运行检测线程。（单位：毫秒，默认为-1）
datasource.mysql.time-between-eviction-runs-millis=60000
#指定每个连接上PSCache的大小
datasource.mysql.maxPoolPreparedStatementPerConnectionSize=20

/**
 * fshows.com
 * Copyright (C) 2013-2019 All Rights Reserved.
 */
package com.example.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * @author liuyuan
 * @version DataSourceProperty.java, v 0.1 2019-11-05 20:28
 */
@Data
@ConfigurationProperties(prefix = "datasource.mysql")
public class DataSourceProperty {

    private String test1Url;

    private String test1Username;

    private String test1Password;

    private String test2Url;

    private String test2Username;

    private String test2Password;

    private String driverClassName;

    private long maxIdle;

    private Integer minIdle;

    private Integer maxWait;

    private Integer initialSize;

    private Integer maxActive;

    private Long minEvictableIdleTimeMillis;

    private Long timeBetweenEvictionRunsMillis;

    private String validationQuery;

    private Integer maxPoolPreparedStatementPerConnectionSize;
}