springboot分布式数据源（Mysql）

本文环境接上文多数据源配置的环境。

如果采用不同的数据源，当同时对不同的数据源进行操作时，事务无法正确的回滚，此时需要使用MysqlXADataSource来代理数据源。

MybatisDBD1Config.java:

1package com.bxw.configuration;
2
3import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
4import org.apache.ibatis.session.SqlSessionFactory;
5import org.mybatis.spring.SqlSessionFactoryBean;
6import org.mybatis.spring.SqlSessionTemplate;
7import org.mybatis.spring.annotation.MapperScan;
8import org.springframework.beans.factory.annotation.Qualifier;
9import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
10import org.springframework.context.annotation.Bean;
11import org.springframework.context.annotation.Configuration;
12import org.springframework.context.annotation.Primary;
13import javax.sql.DataSource;
14import java.sql.SQLException;
15
16/**
17 * 分布式事务管理
18 */
19@Configuration
20@MapperScan(basePackages = "com.bxw.mapperTA", sqlSessionTemplateRef = "sqlSessionTemplateT1")
21public class MybatisDBD1Config {
22    @Primary
23    @Bean(name = "datasourceT1")
24    public DataSource dataSource(DBConfig1 dbConfig1)throws SQLException{
25        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
26        mysqlXaDataSource.setUrl(dbConfig1.getUrl());
27        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
28        mysqlXaDataSource.setPassword(dbConfig1.getPassword());
29        mysqlXaDataSource.setUser(dbConfig1.getUsername());
30        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
31
32        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
33        xaDataSource.setXaDataSource(mysqlXaDataSource);
34        xaDataSource.setUniqueResourceName("dbb1");
35
36//        xaDataSource.setMinPoolSize(dbConfig1.getMinPoolSize());
37//        xaDataSource.setMaxPoolSize(dbConfig1.getMaxPoolSize());
38//        xaDataSource.setMaxLifetime(dbConfig1.getMaxLifetime());
39//        xaDataSource.setBorrowConnectionTimeout(dbConfig1.getBorrowConnectionTimeout());
40//        xaDataSource.setLoginTimeout(dbConfig1.getLoginTimeout());
41//        xaDataSource.setMaintenanceInterval(dbConfig1.getMaintenanceInterval());
42//        xaDataSource.setMaxIdleTime(dbConfig1.getMaxIdleTime());
43//        xaDataSource.setTestQuery(dbConfig1.getTestQuery());
44        return xaDataSource;
45    }
46
47    @Bean(name = "sqlSessionFactoryT1")
48    public SqlSessionFactory sqlSessionFactory(@Qualifier("datasourceT1") DataSource dataSource) throws Exception {
49        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
50        factoryBean.setDataSource(dataSource); // 使用db1数据源, 连接hibernate库
51
52        return factoryBean.getObject();
53
54    }
55
56    @Bean(name="sqlSessionTemplateT1")
57    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryT1") SqlSessionFactory sqlSessionFactory) throws Exception {
58        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
59        return template;
60    }
61}
62
63MybatisDBD2Config.java:
64
65package com.bxw.configuration;
66
67import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
68import org.apache.ibatis.session.SqlSessionFactory;
69import org.mybatis.spring.SqlSessionFactoryBean;
70import org.mybatis.spring.SqlSessionTemplate;
71import org.mybatis.spring.annotation.MapperScan;
72import org.springframework.beans.factory.annotation.Qualifier;
73import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
74import org.springframework.context.annotation.Bean;
75import org.springframework.context.annotation.Configuration;
76import javax.sql.DataSource;
77import java.sql.SQLException;
78
79/**
80 * 分布式事务管理
81 */
82@Configuration
83@MapperScan(basePackages = "com.bxw.mapperTB", sqlSessionTemplateRef = "sqlSessionTemplateT2")
84public class MybatisDBD2Config {
85    @Bean(name = "datasourceT2")
86    public DataSource dataSource(DBConfig2 dbConfig2)throws SQLException{
87        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
88        mysqlXaDataSource.setUrl(dbConfig2.getUrl());
89        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
90        mysqlXaDataSource.setPassword(dbConfig2.getPassword());
91        mysqlXaDataSource.setUser(dbConfig2.getUsername());
92        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
93
94        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
95        xaDataSource.setXaDataSource(mysqlXaDataSource);
96        xaDataSource.setUniqueResourceName("dbb2");
97
98//        xaDataSource.setMinPoolSize(dbConfig1.getMinPoolSize());
99//        xaDataSource.setMaxPoolSize(dbConfig1.getMaxPoolSize());
100//        xaDataSource.setMaxLifetime(dbConfig1.getMaxLifetime());
101//        xaDataSource.setBorrowConnectionTimeout(dbConfig1.getBorrowConnectionTimeout());
102//        xaDataSource.setLoginTimeout(dbConfig1.getLoginTimeout());
103//        xaDataSource.setMaintenanceInterval(dbConfig1.getMaintenanceInterval());
104//        xaDataSource.setMaxIdleTime(dbConfig1.getMaxIdleTime());
105//        xaDataSource.setTestQuery(dbConfig1.getTestQuery());
106        return xaDataSource;
107    }
108
109    @Bean(name = "sqlSessionFactoryT2")
110    public SqlSessionFactory sqlSessionFactory(@Qualifier("datasourceT2") DataSource dataSource) throws Exception {
111        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
112        factoryBean.setDataSource(dataSource); // 使用db1数据源, 连接hibernate库
113
114        return factoryBean.getObject();
115
116    }
117
118    @Bean(name="sqlSessionTemplateT2")
119    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryT2") SqlSessionFactory sqlSessionFactory) throws Exception {
120        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory); // 使用上面配置的Factory
121        return template;
122    }
123}

创建sqlSessionFactory,SqlSessionTemplate

StudentService.java:

1package com.bxw.service;
2
3import com.bxw.annotation.DB;
4import com.bxw.entity.Student;
5import com.bxw.mapperTA.StudentMapperTA;
6import com.bxw.mapperTB.StudentMapperTB;
7import com.bxw.mapperDynamic.StudentMapperC;
8import org.springframework.beans.factory.annotation.Autowired;
9import org.springframework.jdbc.core.JdbcTemplate;
10import org.springframework.jdbc.core.RowMapper;
11import org.springframework.stereotype.Service;
12
13import javax.transaction.Transactional;
14import java.sql.ResultSet;
15import java.sql.SQLException;
16import java.util.List;
17
18@Service
19public class StudentService {
20
21    @Autowired
22    private StudentMapperTA studentMapperTA;
23    @Autowired
24    private StudentMapperTB studentMapperTB;/*
25分布式事务
26 */
27    @Transactional
28    public boolean saveStudent(Student s){
29        studentMapperTA.addStudent(s);
30        studentMapperTB.addStudent(s);
31        int i = 1/0;
32        return true;
33    }
34}