写文章

SpringBoot实现Mybatis多数据源方案

2018-11-30 02:01:03

2818 | 1 | 0

背景

目前报表导出需要多数据库的数据,因此我们需要做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注册为第二个数据源

我们如下文件结构

images/TR34cKWr2B2MbRfiBPDnJQtN4imhbySX.png

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

本文地址为https://my.oschina.net/qixiaobo025/blog/1584810

0

收藏
分享
全部评论1

徐成 2017-12-15 11:06:35

大哥哥事务怎么处理
没有更多了