SpringBoot整合多数据源

今天主要通过代码来演示一下在springboot中如何整合多个数据源,注意这里只是说整合多个数据源,不是真正意义上的分布式,所以这里的事务管理也不是真正意义上的分布式事务,这点需要搞清楚.

业务背景:在传统企业中,往往会涉及到一个业务中会查询多个数据库中的表,那么这里就产生了多数据源,比如接下来要演示的业务就是,有关和用户相关的都查询emp这个数据库，而和部门相关的都查询dept这个数据,看一下项目的整体结构:

首先看一下pom.xml的配置文件内容:

<?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">
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.2.RELEASE</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>spring_boot_multi_datasource</artifactId>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.36</version>
            <scope>runtime</scope>
        </dependency>

        <!--阿里巴巴数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.5</version>
        </dependency>

        <!--多数据源事务管理-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>

        <!--lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <!--引入mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <!--springboot自动配置文件解析注入-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


    </dependencies>
</project>

思路:怎么样解决这个问题,其实有一种很简单的方法,就是隔离,(可以根据业务类型或者业务的包名来区分选择哪个数据源),

定义用户实体:

package com.springboot.ds.bean;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.Accessors;

/**
 * @Author 18011618
 * @Description
 * @Date 14:22 2018/7/19
 * @Modify By
 */
@Setter
@Getter
@ToString
@Accessors(chain = true)
public class UserBean {
    private int id;
    private String username;
}

定义部门实体:

package com.springboot.ds.bean;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.Accessors;

/**
 * @Author 18011618
 * @Description
 * @Date 14:23 2018/7/19
 * @Modify By
 */
@Setter
@Getter
@ToString
@Accessors(chain = true)
public class DeptBean {
    private int id;
    private String detpname;
}

看一下全局的数据源配置文件:这里配置用户数据源和部门数据源

#user database
suning.db.user.dbtype=com.alibaba.druid.pool.xa.DruidXADataSource
suning.db.user.name=userds
suning.db.user.url=jdbc:mysql://localhost/emp
suning.db.user.driver-name=com.mysql.jdbc.Driver
suning.db.user.username=root
suning.db.user.password=123123
suning.db.user.initialSize=5
suning.db.user.minIdle=5
suning.db.user.maxActive=20
suning.db.user.maxWait=60000
suning.db.user.timeBetweenEvictionRunsMillis=60000
suning.db.user.minEvictableIdleTimeMillis=40000
suning.db.user.validationQuery=SELECT 1
suning.db.user.validationQueryTimeout=10000
suning.db.user.testWhileIdle=true
suning.db.user.testOnBorrow=false
suning.db.user.testOnReturn=false
suning.db.user.poolPreparedStatements=true
suning.db.user.maxPoolPreparedStatementPerConnectionSize=20

#dept datasource
suning.db.dept.dbtype=com.alibaba.druid.pool.xa.DruidXADataSource
suning.db.dept.driver-name = com.mysql.jdbc.Driver
suning.db.dept.name=deptds
suning.db.dept.url=jdbc:mysql://localhost/dept
suning.db.dept.username=root
suning.db.dept.password=123123
suning.db.dept.initialSize=5
suning.db.dept.minIdle=5
suning.db.dept.maxActive=20
suning.db.dept.maxWait=60000
suning.db.dept.timeBetweenEvictionRunsMillis=60000
suning.db.dept.minEvictableIdleTimeMillis=40000
suning.db.dept.validationQuery=SELECT 1
suning.db.dept.validationQueryTimeout=10000
suning.db.dept.testWhileIdle=true
suning.db.dept.testOnBorrow=false
suning.db.dept.testOnReturn=false
suning.db.dept.poolPreparedStatements=true
suning.db.dept.maxPoolPreparedStatementPerConnectionSize=20

这里配置了两个数据源 分别是查询用户和查询部门的,为了隔离,所以每个数据源用单独的配置文件来承载:

用户的数据源配置文件保存类:

package com.springboot.ds.config.user;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * @Author 18011618
 * @Description 基于User的数据源配置
 * @Date 14:47 2018/7/19
 * @Modify By
 */
@ConfigurationProperties(prefix = "suning.db.user")
@Getter
@Setter
@ToString
public class UserDbProperties {
    private String dbtype;
    private String name;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private int validationQueryTimeout;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String driverName;
}

部门的配置文件类:

package com.springboot.ds.config.dept;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * @Author 18011618
 * @Description 基于部门的数据源配置
 * @Date 14:48 2018/7/19
 * @Modify By
 */
@ConfigurationProperties(prefix = "suning.db.dept")
@Getter
@Setter
@ToString
public class DeptDbProperties {
    private String dbtype;
    private String name;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private int validationQueryTimeout;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String driverName;
}

用户数据源的Mybatis配置:

package com.springboot.ds.config.user;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @Author 18011618
 * @Description基于user的mybatis配置
 * @Date 14:49 2018/7/19
 * @Modify By
 */
@Configuration
@EnableConfigurationProperties(UserDbProperties.class)
@MapperScan(basePackages = "com.springboot.ds.type.user",sqlSessionTemplateRef = "userSqlSessionTemplate")
public class UserMybatisConfig {
    @Primary
    @Bean(name = "userDataSource")
    public DataSource testDataSource(UserDbProperties userDbProperties) throws SQLException {
        System.err.println("init:"+userDbProperties);
        DruidXADataSource druidXADataSource = new DruidXADataSource();

        druidXADataSource.setName(userDbProperties.getName());
        druidXADataSource.setMaxActive(userDbProperties.getMaxActive());
        druidXADataSource.setDriverClassName(userDbProperties.getDriverName());
        druidXADataSource.setUrl(userDbProperties.getUrl());
        druidXADataSource.setPassword(userDbProperties.getPassword());
        druidXADataSource.setUsername(userDbProperties.getUsername());
        druidXADataSource.setTestOnBorrow(userDbProperties.isTestOnBorrow());
        druidXADataSource.setTestOnReturn(userDbProperties.isTestOnBorrow());
        druidXADataSource.setTestWhileIdle(userDbProperties.isTestWhileIdle());
        druidXADataSource.setValidationQuery(userDbProperties.getValidationQuery());
        druidXADataSource.setValidationQueryTimeout(userDbProperties.getValidationQueryTimeout());
        druidXADataSource.setMinIdle(userDbProperties.getMinIdle());
        druidXADataSource.setMaxPoolPreparedStatementPerConnectionSize(userDbProperties.getMaxPoolPreparedStatementPerConnectionSize());
        druidXADataSource.setPoolPreparedStatements(userDbProperties.isPoolPreparedStatements());
        druidXADataSource.setInitialSize(userDbProperties.getInitialSize());
        druidXADataSource.setMaxWait(userDbProperties.getMaxWait());
        druidXADataSource.setTimeBetweenEvictionRunsMillis(userDbProperties.getTimeBetweenEvictionRunsMillis());
        druidXADataSource.setMinEvictableIdleTimeMillis(userDbProperties.getMinEvictableIdleTimeMillis());
        //创建atom管理数据源
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(druidXADataSource);
        atomikosDataSourceBean.setUniqueResourceName("userDataSource");
        atomikosDataSourceBean.setMinPoolSize(2);
        atomikosDataSourceBean.setMaxPoolSize(5);
        return atomikosDataSourceBean;
    }

    @Primary
    @Bean(name = "userSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("userDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Primary
    @Bean(name = "userSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("userSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

部门数据源的mybatis配置:

package com.springboot.ds.config.dept;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.springboot.ds.config.user.UserDbProperties;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

/**
 * @Author 18011618
 * @Description 基于Dept的mybatis配置
 * @Date 14:49 2018/7/19
 * @Modify By
 */
@EnableConfigurationProperties(DeptDbProperties.class)
@Configuration
@MapperScan(basePackages = "com.springboot.ds.type.dept",sqlSessionTemplateRef = "deptSqlSessionTemplate")
public class DeptMybatisConfig {

    @Bean(name = "deptDataSource")
    public DataSource testDataSource(DeptDbProperties deptDbProperties) throws SQLException {
        System.err.println("init...:"+deptDbProperties);
        DruidXADataSource druidXADataSource = new DruidXADataSource();
        druidXADataSource.setName(deptDbProperties.getDbtype());
        druidXADataSource.setMaxActive(deptDbProperties.getMaxActive());
        druidXADataSource.setDriverClassName(deptDbProperties.getDriverName());
        druidXADataSource.setUrl(deptDbProperties.getUrl());
        druidXADataSource.setPassword(deptDbProperties.getPassword());
        druidXADataSource.setUsername(deptDbProperties.getUsername());
        druidXADataSource.setTestOnBorrow(deptDbProperties.isTestOnBorrow());
        druidXADataSource.setTestOnReturn(deptDbProperties.isTestOnBorrow());
        druidXADataSource.setTestWhileIdle(deptDbProperties.isTestWhileIdle());
        druidXADataSource.setValidationQuery(deptDbProperties.getValidationQuery());
        druidXADataSource.setValidationQueryTimeout(deptDbProperties.getValidationQueryTimeout());
        druidXADataSource.setMinIdle(deptDbProperties.getMinIdle());
        druidXADataSource.setMaxPoolPreparedStatementPerConnectionSize(deptDbProperties.getMaxPoolPreparedStatementPerConnectionSize());
        druidXADataSource.setPoolPreparedStatements(deptDbProperties.isPoolPreparedStatements());
        druidXADataSource.setInitialSize(deptDbProperties.getInitialSize());
        druidXADataSource.setMaxWait(deptDbProperties.getMaxWait());
        druidXADataSource.setTimeBetweenEvictionRunsMillis(deptDbProperties.getTimeBetweenEvictionRunsMillis());
        druidXADataSource.setMinEvictableIdleTimeMillis(deptDbProperties.getMinEvictableIdleTimeMillis());
        //创建atom管理数据源
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(druidXADataSource);
        atomikosDataSourceBean.setUniqueResourceName("deptDataSource");
        atomikosDataSourceBean.setMinPoolSize(2);
        atomikosDataSourceBean.setMaxPoolSize(5);
        return atomikosDataSourceBean;
    }


    @Bean(name = "deptSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("deptDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }


    @Bean(name = "deptSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("deptSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

上面这个代码主要实现了以下功能:

1 指定一个支持XA的数据源,可以是MYSQL原生的,这里为了提供效率使用阿里巴巴的XA

2 实例化一个Atom实例,指定相关属性配置

3 使用创建好的数据源来创建对应的sqlsessionfactory

4 使用创建好的sqlsessionfactory来创建sqlsessiontemplate

@MapperScan：这个是用来进行包扫描的,很重要就是根据这个来区分数据源的选择

@Primary:当系统中有多个数据源的时候,在应用刚启动的时候需要指定一个默认的数据源,否则程序无法正常启动

上面这个是针对user数据源的相关mybatis配置，而dept的数据源的mybatis配置代码和这个是完全一样,只是几个引用的地方不一样

OK 配置相关代码就完成了,现在来建立数据访问层和业务层，这个就要区分开了

看user相关的mapper和service：

package com.springboot.ds.type.user.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

/**
 * @Author 18011618
 * @Description 用户数据访问接口
 * @Date 14:28 2018/7/19
 * @Modify By
 */
public interface UserMapper {
    @Insert("INSERT INTO USER(USERNAME) VALUES(#{username})")
    int insertUser(@Param("username")String username);
}

package com.springboot.ds.type.user.service;

import com.springboot.ds.type.dept.service.DeptService;
import com.springboot.ds.type.user.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * @Author 18011618
 * @Description
 * @Date 14:33 2018/7/19
 * @Modify By
 */
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;

    @Autowired
    private DeptService deptService;

    @Transactional
    public int insertUser(String username){
        int i= deptService.insertDept("it");
        return userMapper.insertUser(username);
    }
}

定义dept相关的mapper和service:

package com.springboot.ds.type.dept.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

/**
 * @Author 18011618
 * @Description 部门数据访问接口
 * @Date 14:29 2018/7/19
 * @Modify By
 */
public interface DeptMapper {

    @Insert("INSERT INTO DEPT(DEPTNAME) VALUES(#{deptname})")
    int insertDept(@Param("deptname")String deptname);
}

package com.springboot.ds.type.dept.service;

import com.springboot.ds.type.dept.mapper.DeptMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * @Author 18011618
 * @Description
 * @Date 14:33 2018/7/19
 * @Modify By
 */
@Service
public class DeptService {

    @Autowired
    private DeptMapper deptMapper;

    @Transactional
    public int insertDept(String deptname){
        int i =1/0;
        return deptMapper.insertDept(deptname);
    }
}

@Transactional:方法上加上这个注解,就可以通过atom来管理多数据源的事务,开发者不用做任何事情.

接下来写一个启动应用类:

package com.springboot.ds;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Author 18011618
 * @Description
 * @Date 16:04 2018/7/19
 * @Modify By
 */
@SpringBootApplication
public class JTAMultiDsApplication {
    public static void main(String[] args) {
        SpringApplication.run(JTAMultiDsApplication.class,args);
    }
}

最后再写一个测试类,看看测试效果

import com.springboot.ds.JTAMultiDsApplication;
import com.springboot.ds.type.dept.service.DeptService;
import com.springboot.ds.type.user.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/**
 * @Author 18011618
 * @Description
 * @Date 16:08 2018/7/19
 * @Modify By
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = JTAMultiDsApplication.class)
public class TestJTA {

    @Autowired
    private UserService userService;

    @Autowired
    private DeptService deptService;

    @Test
    public void testUser(){
        userService.insertUser("jhp");
    }

    @Test
    public void testDept(){
        deptService.insertDept("developer");
    }
}

演示事务机制:可以看红色标注的错误代码

演示多数据源功能:把红色标注的代码注释掉

总结:多数据源的功能是实现了,在这里可能有很多的人会觉得上面的代码是有冗余的,比如如果系统中有10个数据源的话,那么对应的config就会有10个，这个的确是不好.所以有人提出好的解决方案,那就是通过aop来做动态切换,但是如果使用aop有一个问题,就是貌似对于transactional的支持是有点问题的,因为它总是在事务之前执行的,所以不会有任何效果的,

如果要aop的话,必须要解决先后的问题。

建议:虽然代码有一定的冗余,但是它没用任何难度,上手快,且达到隔离的效果,以后就算其它数据源的配置以及扩展有变化也不会影响其它的,稳定性是有保障的.所以实际使用过程中,可以综合衡量一下,选择一套适合自己的方案.

版权声明:转载请标明博客地址,谢谢!