Spring Boot 入门实战(6)--JdbcTempalte、Mybatis 、多数据源及 Atomicos 整合(XA 事务)

当前位置:

首页 > 编程开发 > Java教程 >

Spring Boot 入门实战(6)--JdbcTempalte、Mybatis 、多数据源及 Atomicos 整合(XA 事务)

本文主要介绍 JdbcTempalte、Mybatis 在多数据源下的配置及使用，实际的应用里可以根据情况选择其中之一或同时使用；事务使用基于 Atomicos 的 XA 事务。文中所使用到的软件版本：Spring Boot 2.4.4、jdk1.8.0_181、Mybatis 3.5.6、Atomicos 4.0.6。

1、工程整体结构

2、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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.abc.demo</groupId>
    <artifactId>demo-mdxa</artifactId>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.4</version>
        <relativePath />
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.2.0</version>
            <!--systemPath>E:/bin/jar/oracle/ojdbc6.jar</systemPath>
            <scope>system</scope-->
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>
			

3、application.yml

spring:
  datasource:
    atomic:
      datasource1:
        uniqueResourceName: datasource1
        xaDataSourceClassName: com.mysql.cj.jdbc.MysqlXADataSource
        testQuery: select 1
        maxPoolSize: 2
        minPoolSize: 2
        poolSize: 2
        borrowConnectionTimeout: 60
        reapTimeout: 360
        maxIdleTime: 60
        maintenanceInterval: 60
        loginTimeout: 60
        xaProperties:
          URL: jdbc:mysql://10.198.0.10:3306/itest?useUnicode=true&characterEncoding=UTF-8
          user: root
          password: 123456
      datasource2:
        uniqueResourceName: datasource2
        xaDataSourceClassName: oracle.jdbc.xa.client.OracleXADataSource
        testQuery: select 1 from dual
        maxPoolSize: 2
        minPoolSize: 2
        poolSize: 2
        borrowConnectionTimeout: 60
        reapTimeout: 360
        maxIdleTime: 60
        maintenanceInterval: 60
        loginTimeout: 60
        xaProperties:
          URL: jdbc:oracle:thin:@10.198.0.10:2521:test
          user: test
          password: 123456

4、配置

4.1、数据源配置

配置XA事务管理器及两个数据源。

package com.abc.demo.config;

import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.jta.JtaTransactionManager;

import javax.sql.DataSource;
import javax.transaction.UserTransaction;

@Configuration
public class DataSourceConfig {
    @Bean("userTransaction")
    public UserTransaction userTransaction() throws Throwable {
        UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(10000);
        return userTransactionImp;
    }

    @Bean(name = "userTransactionManager", initMethod = "init", destroyMethod = "close")
    public UserTransactionManager userTransactionManager() {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(false);
        return userTransactionManager;
    }

    @Bean(name = "xatm")
    @DependsOn({"userTransaction", "userTransactionManager"})
    public PlatformTransactionManager transactionManager() throws Throwable {
        return new JtaTransactionManager(userTransaction(), userTransactionManager());
    }

    @Bean(name = "dataSource1")
    @DependsOn({"xatm"})
    @ConfigurationProperties(prefix="spring.datasource.atomic.datasource1")
    public DataSource dataSource1() {
        return new AtomikosDataSourceBean();
    }

    @Bean(name = "dataSource2")
    @DependsOn({"xatm"})
    @ConfigurationProperties(prefix="spring.datasource.atomic.datasource2")
    public DataSource dataSource2() {
        return new AtomikosDataSourceBean();
    }

}

4.2、JdbcTempalte配置

package com.abc.demo.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class JdbcTempalteConfig {

    @Bean("jdbcTemplate1")
    public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean("jdbcTemplate2")
    public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

4.3、Mybatis配置

Mybatis不同数据源的DAO接口需放在不同的包下面。

package com.abc.demo.config;

import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.mapping.VendorDatabaseIdProvider;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.annotation.MapperScans;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScans(value = {@MapperScan(basePackages = {"com.abc.demo.dao1"}, sqlSessionFactoryRef = "sqlSessionFactory1"),
                      @MapperScan(basePackages = {"com.abc.demo.dao2"}, sqlSessionFactoryRef = "sqlSessionFactory2")})
public class MybatisConfig {
    @Primary
    @Bean("sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(datasource);
        factoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
        factoryBean.setConfigLocation(
                new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml"));
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/abc/demo/dao1/*.xml"));
        return factoryBean.getObject();
    }

    @Bean("sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("dataSource2") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(datasource);
        factoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
        factoryBean.setConfigLocation(
                new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml"));
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/abc/demo/dao2/*.xml"));
        return factoryBean.getObject();
    }

    @Bean
    public DatabaseIdProvider getDatabaseIdProvider() {
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle", "oracle");
        properties.setProperty("MySQL", "mysql");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

}

Mybatis的全局配置resources/mybatis/mybatis-config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--配置全局属性 -->
    <settings>
        <setting name="useGeneratedKeys" value="true" />
        <setting name="useColumnLabel" value="true" />
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="logImpl" value="SLF4J"/>
    </settings>
</configuration>

5、业务代码

5.1、实体类

			
package com.abc.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;


@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class School {
    private String name;

    private String location;
}
			

School实体类对应a_school表，放在第一个数据库里面。

			
package com.abc.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class Student {
    private String name;

    private Integer age;
}
			

Student实体类对应a_student表，放在第一二个数据库里面。

5.2、DAO及对应的XML文件

5.2.1、ISchoolDao

package com.abc.demo.dao1;

import com.abc.demo.entity.School;

import java.util.List;

public interface ISchoolDao {
    void insertSchool(School school);

    List<School> selectSchool();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.abc.demo.dao1.ISchoolDao">
    <select id="insertSchool" parameterType="com.abc.demo.entity.School">
        insert into a_school(name,location) values(#{name}, #{location})
    </select>

    <select id="selectSchool" resultType="com.abc.demo.entity.School">
        select name,location from a_school
    </select>
</mapper>

5.2.2、IStudentDao

package com.abc.demo.dao2;

import com.abc.demo.entity.Student;

import java.util.List;

public interface IStudentDao {
    void insertStudent(Student student);

    List<Student> selectStudent();
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.abc.demo.dao2.IStudentDao">
    <select id="insertStudent" parameterType="com.abc.demo.entity.Student">
        insert into a_student(name,age) values(#{name}, #{age})
    </select>

    <select id="selectStudent" resultType="com.abc.demo.entity.Student">
        select name,age from a_student
    </select>
</mapper>

5.3、Service

package com.abc.demo.service;


import java.util.List;
import java.util.Map;

public interface IBusinessService {
    void addSchoolAndStudent();

    Map<String, List<Object>> querySchoolAndStudent();

    void addSchoolAndStudentMybatis();

    Map<String, List<Object>> querySchoolAndStudentMybatis();
}

package com.abc.demo.service.impl;

import com.abc.demo.dao1.ISchoolDao;
import com.abc.demo.dao2.IStudentDao;
import com.abc.demo.entity.School;
import com.abc.demo.entity.Student;
import com.abc.demo.service.IBusinessService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class BusinessServiceImpl implements IBusinessService {
    protected static Logger logger = LoggerFactory.getLogger(BusinessServiceImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private JdbcTemplate jdbcTemplate2;

    @Autowired
    private ISchoolDao schoolDao;

    @Autowired
    private IStudentDao studentDao;

    @Transactional
    @Override
    public void addSchoolAndStudent() {
        jdbcTemplate1.update("insert into a_school(name,location) values(?,?)", "南京大学", "南京");
        jdbcTemplate1.update("insert into a_school(name,location) values(?,?)", "北京大学", "北京");

        jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "李白", 20);
        jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "杜甫", 21);
    }

    @Override
    public Map<String, List<Object>> querySchoolAndStudent() {
        List<School> schools = jdbcTemplate1.query("select * from a_school", new BeanPropertyRowMapper<>(School.class));
        List<Student> students = jdbcTemplate2.query("select * from a_student", new BeanPropertyRowMapper<>(Student.class));
        return new HashMap(){{
            put("school", schools);
            put("student", students);
        }};
    }

    @Transactional
    @Override
    public void addSchoolAndStudentMybatis() {
        schoolDao.insertSchool(new School("南京大学", "南京"));
        schoolDao.insertSchool(new School("北京大学", "北京"));

        studentDao.insertStudent(new Student("李白", 20));
        studentDao.insertStudent(new Student("杜甫", 21));
    }

    @Override
    public Map<String, List<Object>> querySchoolAndStudentMybatis() {
        List<School> schools = schoolDao.selectSchool();
        List<Student> students = studentDao.selectStudent();
        return new HashMap(){{
            put("school", schools);
            put("student", students);
        }};
    }
}

6、Controller

			
package com.abc.demo.controller;

import com.abc.demo.entity.R;
import com.abc.demo.service.IBusinessService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/business")
public class BusinessController {
    protected static Logger logger = LoggerFactory.getLogger(BusinessController.class);

    @Autowired
    private IBusinessService businessService;


    @RequestMapping("addSchoolAndStudent")
    public R addSchoolAndStudent() {
        businessService.addSchoolAndStudent();
        return R.ok();
    }

    @RequestMapping("querySchoolAndStudent")
    public R querySchool() {
        Map<String, List<Object>> map =  businessService.querySchoolAndStudent();
        return R.ok(map);
    }

    @RequestMapping("addSchoolAndStudentMybatis")
    public R addSchoolAndStudentMybatis() {
        businessService.addSchoolAndStudentMybatis();
        return R.ok();
    }

    @RequestMapping("querySchoolAndStudentMybatis")
    public R querySchoolAndStudentMybatis() {
        Map<String, List<Object>> map =  businessService.querySchoolAndStudentMybatis();
        return R.ok(map);
    }
}
			

Controller用到的返回对象R：

			
package com.abc.demo.entity;

/**
 * 返回数据
 */
public class R {
    private static final long serialVersionUID = 1L;

    /**
     * 返回码
     * 0 正常，其他异常
     */
    private int returnCode = 0;

    /**
     * 描述
     */
    private String description = "OK";

    /**
     * 结果数据
     */
    private Object result;

    public int getReturnCode() {
        return returnCode;
    }
    public String getDescription() {
        return description;
    }
    public Object getResult() {
        return result;
    }

    public static R ok() {
        return new R();
    }

    public static R ok(String description) {
        R r = new R();
        r.description = description;
        return r;
    }

    public static R ok(Object result) {
        R r = new R();
        r.result = result;
        return r;
    }
    
    public static R error() {
        R r = new R();
        r.returnCode = -1;
        r.description = "未知异常，请联系管理员";
        return r;
    }
    
    public static R error(int returnCode, String description) {
        R r = new R();
        r.returnCode = returnCode;
        r.description = description;
        return r;
    }

}
			

来源：

https://www.cnblogs.com/wuyongyin/p/14572236.html

栏目列表