抱歉,你查看的文章不存在

SpringBoot实现Mybatis多数据源方案

背景

目前报表导出需要多数据库的数据,因此我们需要做Mybatis多数据源的配置

我们之前使用Spring的AbstractRoutingDataSource

做资源隔离redis限制请求频率及资源隔离

但是事实上我们确实存在两个数据源【非读写分离】

两个数据源完全不同 换言之在业务上完全不等价【即A数据源的数据和B数据源的数据不同】

而读写分离是A数据源和B数据源的数据相同【至少逻辑等同,比如分片比如读写分离】

当然利用上述方法依然是可以完成多数据源,只是需要做动态切换

本次我们使用另一种实现方式

在SpringBoot+MyBatis实现多个SqlSessionFactory

步骤

由于我们系统使用多数据源我们需要定义两个数据源

在application.properties中需要定义两个数据源

spring.datasource.url=jdbc:mysql://192.168.1.7:3306/f6dms_20160522?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource2.url=jdbc:mysql://192.168.1.7:3306/f6dms_1116_prod_backup?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource2.username=root
spring.datasource2.password=root

我们使用spring.dataSource2作为第二个数据源的prefix

当只有一个数据源的时候由于druid-starter会自动注册

@Configuration
@ConditionalOnClass(com.alibaba.druid.pool.DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
        DruidStatViewServletConfiguration.class,
        DruidWebStatFilterConfiguration.class,
        DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {
 
    @Bean
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        return new DruidDataSourceWrapper();
    }
}
@ConfigurationProperties("spring.datasource.druid")
class DruidDataSourceWrapper extends DruidDataSource implements InitializingBean {
    @Autowired
    private DataSourceProperties basicProperties;
 
    @Override
    public void afterPropertiesSet() throws Exception {
        //if not found prefix 'spring.datasource.druid' jdbc properties ,'spring.datasource' prefix jdbc properties will be used.
        if (super.getUsername() == null) {
            super.setUsername(basicProperties.determineUsername());
        }
        if (super.getPassword() == null) {
            super.setPassword(basicProperties.determinePassword());
        }
        if (super.getUrl() == null) {
            super.setUrl(basicProperties.determineUrl());
        }
        if (super.getDriverClassName() == null) {
            super.setDriverClassName(basicProperties.determineDriverClassName());
        }
    }
 
    @Autowired(required = false)
    public void addStatFilter(StatFilter statFilter) {
        super.filters.add(statFilter);
    }
 
    @Autowired(required = false)
    public void addConfigFilter(ConfigFilter configFilter) {
        super.filters.add(configFilter);
    }
 
    @Autowired(required = false)
    public void addEncodingConvertFilter(EncodingConvertFilter encodingConvertFilter) {
        super.filters.add(encodingConvertFilter);
    }
 
    @Autowired(required = false)
    public void addSlf4jLogFilter(Slf4jLogFilter slf4jLogFilter) {
        super.filters.add(slf4jLogFilter);
    }
 
    @Autowired(required = false)
    public void addLog4jFilter(Log4jFilter log4jFilter) {
        super.filters.add(log4jFilter);
    }
 
    @Autowired(required = false)
    public void addLog4j2Filter(Log4j2Filter log4j2Filter) {
        super.filters.add(log4j2Filter);
    }
 
    @Autowired(required = false)
    public void addCommonsLogFilter(CommonsLogFilter commonsLogFilter) {
        super.filters.add(commonsLogFilter);
    }
 
    @Autowired(required = false)
    public void addWallFilter(WallFilter wallFilter) {
        super.filters.add(wallFilter);
    }
 
 
}

当DataSource未注册时会自动注册DruidWrapper

但是我们需要两个数据源因此必须自己注册

我们定义一个抽象DataSource

public abstract class AbstractDataSourceConfig {
    private String driverClassName;
 
    /**
     * JDBC url of the database.
     */
    private String url;
 
    /**
     * Login user of the database.
     */
    private String username;
 
    /**
     * Login password of the database.
     */
    private String password;
 
    public String getDriverClassName() {
        return driverClassName;
    }
 
    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }
 
    public String getUrl() {
        return url;
    }
 
    public void setUrl(String url) {
        this.url = url;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    protected DataSource getDatasource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(this.getUrl());
        dataSource.setUsername(this.getUsername());
        dataSource.setPassword(this.getPassword());
        dataSource.setDriverClassName(this.getDriverClassName());
        return dataSource;
    }
 
}

定义数据源1【注意使用了Primary】 primary的作用是当按照类型注册的时候当容器中存在多个将会注入这个Bean

/**
 * @author qixiaobo
 */
@Configuration
@ConfigurationProperties("spring.datasource")
public class DataSourceConfig1 extends AbstractDataSourceConfig {
    @Bean(PRIMARY_DATA_SOURCE_NAME)
    @Primary
    public DataSource dataSource1() {
        DataSource datasource = getDatasource();
        return datasource;
    }
}

定义数据源2【注意ConditionalOnProperty会监控系统中存在该property才会注册该Bean】

/**
 * @author qixiaobo
 */
@Configuration
@ConfigurationProperties("spring.datasource2")
@ConditionalOnProperty(name = "spring.datasource2.url", matchIfMissing = false)
public class DataSourceConfig2 extends AbstractDataSourceConfig {
    @Bean(SECOND_DATA_SOURCE_NAME)
    public DataSource dataSource2() {
        DataSource datasource = getDatasource();
        return datasource;
    }
}

如下我们注册MybatisConfiguar

public class AbstractMyBatisConfigurer {
    protected static final String SQL_SESSION_FACTORY_NAME = "SqlSessionFactoryBean";
    protected static final String TRANSACTION_MANAGER_NAME = "TransactionManager";
    protected static final String DATA_SOURCE_NAME = "DataSource";
 
    protected SqlSessionFactoryBean getSqlSessionFactoryBean(DataSource dataSource) {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTypeAliasesPackage(MODEL_PACKAGE);
 
        //配置分页插件,详情请查阅官方文档
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("pageSizeZero", "true");
        //分页尺寸为0时查询所有纪录不再执行分页
        properties.setProperty("reasonable", "true");
        //页码<=0 查询第一页,页码>=总页数查询最后一页
        properties.setProperty("supportMethodsArguments", "false");
        //支持通过 Mapper 接口参数来传递分页参数
        pageHelper.setProperties(properties);
 
        //添加插件
        factory.setPlugins(new Interceptor[]{pageHelper, new SoInterceptor(), new MybatisTransactionTimeoutInterceptor()});
 
        org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
        config.setDefaultStatementTimeout(5);
        config.setDefaultFetchSize(10000);
        config.setDefaultExecutorType(ExecutorType.REUSE);
        config.setLogImpl(Slf4jImpl.class);
        config.setLogPrefix("dao.");
        factory.setConfiguration(config);
        return factory;
    }
 
    protected MapperScannerConfigurer getMapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        //配置通用Mapper,详情请查阅官方文档
        Properties properties = new Properties();
        properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);
        properties.setProperty("notEmpty", "false");
        //insert、update是否判断字符串类型!='' 即 test="str != null"表达式内是否追加 and str != ''
        properties.setProperty("IDENTITY", "MYSQL");
        mapperScannerConfigurer.setProperties(properties);
        return mapperScannerConfigurer;
    }
}
@Configuration
public class MybatisConfigurer extends AbstractMyBatisConfigurer {
 
    public static final String PRIMARY_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_PRIMARY + SQL_SESSION_FACTORY_NAME;
    public static final String PRIMARY_TRANSACTION_MANAGER_NAME = Constants.LEVEL_PRIMARY + TRANSACTION_MANAGER_NAME;
    public static final String PRIMARY_DATA_SOURCE_NAME = Constants.LEVEL_PRIMARY + DATA_SOURCE_NAME;
 
 
    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean(@Autowired DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
 
 
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();
        mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE);
        return mapperScannerConfigurer;
    }
 
 
    @Bean
    @Primary
    public DataSourceTransactionManager transactionManager1(@Autowired DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
 
    }
 
 
}
/*
 * Copyright (c) 2017. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
 * Morbi non lorem porttitor neque feugiat blandit. Ut vitae ipsum eget quam lacinia accumsan.
 * Etiam sed turpis ac ipsum condimentum fringilla. Maecenas magna.
 * Proin dapibus sapien vel ante. Aliquam erat volutpat. Pellentesque sagittis ligula eget metus.
 * Vestibulum commodo. Ut rhoncus gravida arcu.
 */
 
package com.f6car.base.config;
 
import com.f6car.base.constant.Constants;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import tk.mybatis.spring.mapper.MapperScannerConfigurer;
 
import javax.sql.DataSource;
 
import static com.f6car.base.config.MyBatisConfigurer2.SECOND_DATA_SOURCE_NAME;
import static com.f6car.base.constant.Constants.MAPPER_PACKAGE;
 
/**
 * @author qixiaobo
 */
@Configuration
@ConditionalOnBean(name = SECOND_DATA_SOURCE_NAME)
public class MyBatisConfigurer2 extends AbstractMyBatisConfigurer {
    public static final String SECOND_TRANSACTION_MANAGER_NAME = Constants.LEVEL_SECOND + TRANSACTION_MANAGER_NAME;
    public static final String SECOND_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_SECOND + SQL_SESSION_FACTORY_NAME;
    public static final String SECOND_DATA_SOURCE_NAME = Constants.LEVEL_SECOND + DATA_SOURCE_NAME;
 
 
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer2() {
        MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName(SECOND_SQL_SESSION_FACTORY_NAME);
        mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE + "2");
        return mapperScannerConfigurer;
    }
 
    @Bean(name = SECOND_TRANSACTION_MANAGER_NAME)
    public DataSourceTransactionManager transactionManager2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
 
    }
 
    @Bean(name = SECOND_SQL_SESSION_FACTORY_NAME)
    public SqlSessionFactory sqlSessionFactoryBean2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper2/**/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

mapper注册为第一个数据源

mapper2注册为第二个数据源

我们如下文件结构

这样就可以完成多数据源的配置

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

编辑于

后端之路

0 篇文章0 人订阅

相关文章

来自专栏闵开慧

通过多种方式将数据导入hive表

hive官方手册 http://slaytanic.blog.51cto.com/2057708/939950 通过多种方式将数据导入hive表 1.通过外部...

3717
来自专栏Kubernetes

Controlling Access to the Kubernetes API

? API Server Ports and IPs By default the Kubernetes API server serves HTTP o...

2828
来自专栏蓝天

Redis模块开发示例

实现一个Redis module,支持两个扩展命令: 1) 可同时对hash的多个field进行incr操作; 2) incrby同时设置一个key的过期时...

1053
来自专栏10km的专栏

cmake:vs2015/MinGW静态编译leveldb

leveldb是google的开源项目(https://github.com/google/leveldb), 在linux下编译很方便,然而官方版本却没有提供...

5356
来自专栏java闲聊

第二篇 SSM运行Demo

2015
来自专栏码匠的流水账

聊聊jesque的event机制

jesque-2.1.2-sources.jar!/net/greghaines/jesque/worker/WorkerEvent.java

771
来自专栏happyJared

Spring Boot 1.0 && 2.0 + Mybatis 多数据源配置与使用

mysql 对应的数据源配置中,定义了实体 Boy 和对应的数据层接口 BoyMapper:

1093
来自专栏数据库新发现

使用Oracle Wrap工具加密你的代码

Last Updated: Monday, 2004-11-15 22:31 Eygle

1012
来自专栏linux驱动个人学习

高通 sensor 从native到HAL

前几篇sensor相关的文章介绍了sensor的hal的知识,以press_sensor实时显示气压坐标来分析,app层数据获取的过程,其实实现数据监控非常简单...

3612
来自专栏技术博文

php QR Code二维码生成类

<?php /* * PHP QR Code encoder * * This file contains MERGED version of PHP ...

3825

扫码关注云+社区

领取腾讯云代金券