SpringBoot集成Atomikos使用Oracle数据库mybatis、jta框架

项目中需要数据库分布式事物的实现，于是采用了atumikos技术。

因为生产上需要稳定，所以采用了springboot 1.5.9.RELEASE版本。

本文代码gitlab下载地址： https://gitlab.com/atomikos/springBootMultDB-druidOracle.git

新建一个springboot项目，然后依次添加本文所有代码。我的项目结构如下：

1、pom.xml 代码

<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">
    <modelVersion>4.0.0</modelVersion>
    <groupId>springBootMultDB</groupId>
    <artifactId>springBootMultDB-druidOracle</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springBootMultDB-druidOracle</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.9.RELEASE</version>
        <relativePath />
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <!-- 排除spring boot默认使用的tomcat，使用jetty -->
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jetty</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4.0</version>
        </dependency>
    </dependencies>
</project>

这里的ojdbc6的jar注意一下，不是maven中央库能够下载到的，所以需要去oracle官方下载驱动jar包：

https://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

下载好驱动jar包之后，cmd命令窗口进入jar包所在目录，执行mvn安装jar到本地repository库，mvn命令：

mvn install:install-file -Dfile=d:/java-jar/ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.4.0 -Dpackaging=jar

把-Dfile的参数替换成你本地的ojdbc6的目录即可。

-Dversion版本参数查看方法：打开ojdbc6.jar，进入META-INF，打开MANIFEST.MF，找到 Implementation-Version的值，我这里是: 11.2.0.4.0

2、application.properties配置

server.port=8082
spring.application.name=springBootMultDB-druidOracle

# Oracle 1
spring.datasource.test1.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.test1.username=system
spring.datasource.test1.password=ZHUwen12

spring.datasource.test1.minPoolSize = 3
spring.datasource.test1.maxPoolSize = 25
spring.datasource.test1.maxLifetime = 20000
spring.datasource.test1.borrowConnectionTimeout = 30
spring.datasource.test1.loginTimeout = 30
spring.datasource.test1.maintenanceInterval = 60
spring.datasource.test1.maxIdleTime = 60
spring.datasource.test1.testQuery = select 1 from dual

# # Oracle 2
spring.datasource.test2.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.test2.username=zhuwen
spring.datasource.test2.password=ZHUwen12
spring.datasource.test2.minPoolSize = 3
spring.datasource.test2.maxPoolSize = 25
#连接最大存活时间
spring.datasource.test2.maxLifetime = 20000
#获取连接失败重新获等待最大时间
spring.datasource.test2.borrowConnectionTimeout = 30
#登入超时
spring.datasource.test2.loginTimeout = 30
# 连接回收时间
spring.datasource.test2.maintenanceInterval = 60
#最大闲置时间，超过最小连接池连接的连接将将关闭
spring.datasource.test2.maxIdleTime = 60
spring.datasource.test2.testQuery = select 1 from dual

这里的oracle两个库配置，只有username不一样，在oracle里面，两个user可视为两个数据库。

3、App.java启动类

package com.zhuguang;

import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;

import com.zhuguang.datasource.DBConfig1;
import com.zhuguang.datasource.DBConfig2;

/**
 * 非常感谢腾讯课堂烛光学院的lisa老师
 * @author zhuwen
 *
 */
@SpringBootApplication
@EnableConfigurationProperties(value = { DBConfig1.class, DBConfig2.class })
@MapperScan(basePackages = { "com.zhuguang.mapper" })
public class App {
    private static final org.slf4j.Logger LOG = LoggerFactory.getLogger(App.class);

    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }

}

4、两个Oracle数据库配置类

package com.zhuguang.datasource;
import org.springframework.boot.context.properties.ConfigurationProperties;                  
@ConfigurationProperties(prefix = "spring.datasource.test1")
public class DBConfig1 {

    private String url;
    private String username;
    private String password;
    private int minPoolSize;
    private int maxPoolSize;
    private int maxLifetime;
    private int borrowConnectionTimeout;
    private int loginTimeout;
    private int maintenanceInterval;
    private int maxIdleTime;
    private String testQuery;

    getter and setter...
}

第二个数据库配置类与DBConfig1一样，唯一不同的地方在于 prefix = "spring.datasource.test2"，类名叫DBConfig2

5、atomikos分布式数据源配置类
TestMyBatisConfig1

package com.zhuguang.datasource;
import java.sql.SQLException;
import javax.sql.DataSource;
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.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
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 com.alibaba.druid.pool.xa.DruidXADataSource;
@Configuration
// basePackages 最好分开配置 如果放在同一个文件夹可能会报错
@MapperScan(basePackages = "com.zhuguang.db1", sqlSessionTemplateRef = "testSqlSessionTemplate")
public class TestMyBatisConfig1 {
    private static final org.slf4j.Logger LOG = LoggerFactory.getLogger(TestMyBatisConfig1.class);

    // 配置数据源
    @Primary
    @Bean(name = "dataSource1")
    public DataSource testDataSource(DBConfig1 testConfig) throws SQLException {
        //Atomikos统一管理分布式事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();

//        Properties p = new Properties();
//        p.setProperty ( "user" , testConfig.getUsername() );
//        p.setProperty ( "password" , testConfig.getPassword() );
//        p.setProperty ( "URL" , testConfig.getUrl() );
//        xaDataSource.setXaProperties ( p );

        //用druidXADataSource方式或者上面的Properties方式都可以
        DruidXADataSource druidXADataSource = new DruidXADataSource();
        druidXADataSource.setUrl(testConfig.getUrl());
        druidXADataSource.setUsername(testConfig.getUsername());
        druidXADataSource.setPassword(testConfig.getPassword());
        
        xaDataSource.setUniqueResourceName("oracle1");
        xaDataSource.setXaDataSource(druidXADataSource);
        xaDataSource.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
        xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());
        xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());
        xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());
        xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());
        xaDataSource.setTestQuery(testConfig.getTestQuery());
        
        LOG.info("分布式事物dataSource1实例化成功");
        return xaDataSource;
    }

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

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

再建一个一模一样的TestMyBatisConfig1类，取名叫TestMyBatisConfig2，不同的地方是注解和bean命名：

package com.zhuguang.datasource;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
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.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import oracle.jdbc.xa.client.OracleXADataSource;
// basePackages 最好分开配置 如果放在同一个文件夹可能会报错
@Configuration
@MapperScan(basePackages = "com.zhuguang.db2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class TestMyBatisConfig2 {
    private static final org.slf4j.Logger LOG = LoggerFactory.getLogger(TestMyBatisConfig2.class);

    // 配置数据源
    @Bean(name = "dataSource2")
    public DataSource testDataSource(DBConfig2 testConfig) throws SQLException {
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        
//        Properties p = new Properties();
//        p.setProperty ( "user" , testConfig.getUsername() );
//        p.setProperty ( "password" , testConfig.getPassword() );
//        p.setProperty ( "URL" , testConfig.getUrl() );
//        xaDataSource.setXaProperties ( p );

        //用druidXADataSource方式或者上面的Properties方式都可以
        DruidXADataSource druidXADataSource = new DruidXADataSource();
        druidXADataSource.setUrl(testConfig.getUrl());
        druidXADataSource.setUsername(testConfig.getUsername());
        druidXADataSource.setPassword(testConfig.getPassword());
        
        xaDataSource.setUniqueResourceName("oracle2");
        xaDataSource.setXaDataSource(druidXADataSource);
        xaDataSource.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
        xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());
        xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());
        xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());
        xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());
        xaDataSource.setTestQuery(testConfig.getTestQuery());
        
        LOG.info("分布式事物dataSource2实例化成功");
        return xaDataSource;
    }

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

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

6、为两个数据库分别建立USERS表

首先在两个Oracle数据库里都建立表：

CREATE TABLE users  (
  name varchar2(20 BYTE),
  age NUMBER(*,0) 
);

并建立entity:

package com.zhuguang.entity;
public class Users {
    private String id;
    private String name;
    private Integer age;
    
    getter and setter..
}

7、为users建立mapper类

package com.zhuguang.db1.dao;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.zhuguang.entity.Users;

public interface DB1_UserMapper {
    @Select("SELECT * FROM USERS WHERE NAME = #{name}")
    Users findByName(@Param("name") String name);

    @Insert("INSERT INTO USERS(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

    @Delete("Delete from USERS")
    void deleteAll();

    @Select("select 'oracle1' as id,t.* from USERS t")
    List<Users> queryAll();
}

package com.zhuguang.db2.dao;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.zhuguang.entity.Users;

//test1 DB
public interface DB2_UserMapper {
    @Select("SELECT * FROM USERS WHERE NAME = #{name}")
    Users findByName(@Param("name") String name);

    @Insert("INSERT INTO USERS(NAME, AGE) VALUES(#{name}, #{age})")
    int insert(@Param("name") String name, @Param("age") Integer age);

    /**
     * 用于演示插入数据库异常的情况
     */
    @Insert("INSERT INTO not_exists_table_USERS(NAME, AGE) VALUES(#{name}, #{age})")
    int insertNotExistsTable(@Param("name") String name, @Param("age") Integer age);
    
    @Delete("Delete from USERS")
    void deleteAll();

    @Select("select 'oracle2' as id,t.* from USERS t")
    List<Users> queryAll();
}

8、建立Controller

package com.zhuguang.controller;

import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.zhuguang.db1.service.DB1_UserService;
import com.zhuguang.db2.service.DB2_UserService;
import com.zhuguang.service.IndexService;
/**
 *  感谢腾讯课堂烛光学院lisa老师
 * @author zhuwen
 *
 */
@RestController
public class IndexController {
    private static Logger log = LoggerFactory.getLogger(IndexController.class);

    @Autowired
    private DB1_UserService userService1;
    @Autowired
    private DB2_UserService userService2;
    @Autowired
    private IndexService indexService;
    
    //想查看数据源，可以这么注解
    @Resource
    @Qualifier("dataSource1")
    private DataSource dataSource1;

    @RequestMapping("/insertDB1")
    public String insertTest001(String name, Integer age) {
        // userMapperTest01.insert(name, age);
        userService1.insertDB1(name, age);
        return "success insertDB1";
    }

    @RequestMapping("/insertDB2")
    public String insertTest002(String name, Integer age) {
        userService2.insertDB2(name, age);
        return "success insertDB2";
    }

    /**
     * atomikos效果：分布式事物。两个数据库都插入值
     * 
     * @param name
     * @param age
     * @return
     */
    @RequestMapping("/insertTwoDBs")
    public String insertTwoDBs(String name, Integer age) {
        indexService.insertTwoDBs(name, age);
        return "success insertTwoDBs";
    }

    /**
     * atomikos效果：分布式事物。 演示发生异常分布式事物回滚
     * 
     * @param name
     * @param age
     * @return
     */
    @RequestMapping("/insertTwoDBsWithError")
    public String insertTwoDBsWithError(String name, Integer age) {
        indexService.insertTwoDBsWithError(name, age);
        return "success insertTwoDBs";
    }

    /**
     * atomikos效果：分布式事物。 演示发生异常分布式事物回滚
     *  直接调用mapper方式
     * @param name
     * @param age
     * @return
     */
    @RequestMapping("/insertTwoDBsUseMapperWithError")
    public String insertTwoDBsUseMapperWithError(String name, Integer age) {
        indexService.insertTwoDBsUseMapperWithError(name, age);
        return "success insertTwoDBsUseMapperWithError";
    }

    /**
     * 获取两个数据库的所有数据
     * @return
     */
    @RequestMapping("/queryAll")
    public List queryAll() {
        List list = indexService.queryAll();
        list.add(new Date().toLocaleString()); //加上时间戳，方便postman观察结果
        return list;
    }

    /**
     *  删除两个数据库的所有数据
     * @return
     */
    @RequestMapping("/deleteAll")
    public String deleteAll() {
        indexService.deleteAll();
        return "success delete all";
    }

}

9、建立service

package com.zhuguang.service;

import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import com.zhuguang.db1.dao.DB1_UserMapper;
import com.zhuguang.db1.service.DB1_UserService;
import com.zhuguang.db2.dao.DB2_UserMapper;
import com.zhuguang.db2.service.DB2_UserService;
import com.zhuguang.entity.Users;

@Service
public class IndexService {
    @Autowired
    private DB1_UserMapper db1UserMapper;
    @Autowired
    private DB2_UserMapper db2UserMapper;
    @Autowired
    private DB1_UserService db1UserService;
    @Autowired
    private DB2_UserService db2UserService;
    /**
     *  atomikos效果：分布式事物。两个数据库都插入值
     * @return
     */
    @Transactional
    public void insertTwoDBs(String name, Integer age) {
        db1UserMapper.insert(name, age);
        db2UserMapper.insert(name, age);
    }

    @Transactional
    public void deleteAll() {
        db1UserMapper.deleteAll();
        //不同数据库。test1,test2
        //userService2.insertDB2(name, age);
        db2UserMapper.deleteAll();//test2   
//        int i = 1 / 0;//
    }

    /**
     * atomikos效果：分布式事物。
      *  演示发生异常分布式事物回滚
      *  这里无论error 1、2、3，任何一处发生异常，分布式事物都会回滚
     */
    @Transactional //(rollbackFor = { Exception.class })
    public void insertTwoDBsWithError(String name, Integer age) {
        db1UserService.insert2DB1(name, age);
        db2UserService.insert2DB2(name, age);
        //int i = 1 / 0; // error 1
    }    
    
    /**
     * atomikos效果：分布式事物。
      *  演示发生异常分布式事物回滚
      *  这里无论error 1、2、3，任何一处发生异常，分布式事物都会回滚
      *  此方法效果等同于insertTwoDBsWithError
     */
    @Transactional
    public void insertTwoDBsUseMapperWithError(String name, Integer age) {
        db1UserMapper.insert(name, age);
        db2UserMapper.insert(name, age);
        db2UserMapper.insertNotExistsTable(name, age);
    }

    public List queryAll() {
        List all = new ArrayList();
        
        List<Users> list1 = db1UserService.queryAll();
        if(CollectionUtils.isEmpty(list1)) {
            all.add("db1 没有任何数据！");
        }else {
            all.addAll(list1);
        }
        
        List<Users> list2 = db2UserService.queryAll();
        if(CollectionUtils.isEmpty(list2)) {
            all.add("db2 没有任何数据！");
        }else {
            all.addAll(list2);
        }
        
        return all;
    }
    
}

10、使用postman验证

主要验证：/insertTwoDBsUseMapperWithError 这个效果，是否任何一处DB产生错误，都会使分布式事物回滚。

11、druid监控页面

启动App.java之后，只要在浏览器里输入 http://localhost:8082/druid/index.html 就可以进入druid监控页面：

但是由于这里使用的是atomikos分布式事物DataSource，不是原本的druidDataSource，所以这里几乎监控不到任何有价值的东西，基本作废。

end.

posted on 2019-01-20 16:41 梦幻朵颜阅读(3309) 评论(2) 编辑收藏举报