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

2023-03-12 18:03:05

项目中需要数据库分布式事物的实现，于是采用了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.

码农公寓

相关文章