Spring Boot 入门实战(5)--JdbcTempalte、Mybatis及多数据源整合(单库事务)

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

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-md</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>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </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:
    druid:
      datasource1:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.10:3306/itest?useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
        initialSize: 2
        minIdle: 1
        maxActive: 2
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: true
        testOnReturn: false
        maxWait: 6000
        filters: wall,stat,slf4j
      datasource2:
        driverClassName: oracle.jdbc.OracleDriver
        url: jdbc:oracle:thin:@10.49.196.10:1521:test
        username: test
        password: 123456
        initialSize: 2
        minIdle: 1
        maxActive: 2
        validationQuery: SELECT 1 from dual
        testWhileIdle: true
        testOnBorrow: true
        testOnReturn: false
        maxWait: 6000

4、配置

4.1、数据源配置

配置两个数据源及对应的事务管理器。

package com.abc.demo.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    @Primary
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix="spring.datasource.druid.datasource1")
    public DataSource dataSource1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix="spring.datasource.druid.datasource2")
    public DataSource dataSource2() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "transactionManager1")
    public DataSourceTransactionManager dataSourceTransactionManager1() {
        return new DataSourceTransactionManager(dataSource1());
    }

    @Bean(name = "transactionManager2")
    public DataSourceTransactionManager dataSourceTransactionManager2() {
        return new DataSourceTransactionManager(dataSource2());
    }
}

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

5.3.1、ISchoolService

package com.abc.demo.service;

import com.abc.demo.entity.School;

import java.util.List;

public interface ISchoolService {
    void addSchool();

    List<School> querySchool();

    void addSchoolMybatis();

    List<School> querySchoolMybatis();
}

package com.abc.demo.service.impl;

import com.abc.demo.dao1.ISchoolDao;
import com.abc.demo.entity.School;
import com.abc.demo.service.ISchoolService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.List;

@Service
public class SchoolServiceImpl implements ISchoolService {
    @Autowired
    @Qualifier("jdbcTemplate1")
    private JdbcTemplate jdbcTemplate1;

    @Autowired
    private ISchoolDao schoolDao;

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

    @Override
    public List<School> querySchool() {
        return jdbcTemplate1.query("select * from a_school", new BeanPropertyRowMapper<>(School.class));
    }

    @Transactional(transactionManager = "transactionManager1")
    @Override
    public void addSchoolMybatis() {
        schoolDao.insertSchool(new School("南京大学", "南京"));
        schoolDao.insertSchool(new School("北京大学", "北京"));
    }

    @Override
    public List<School> querySchoolMybatis() {
        return schoolDao.selectSchool();
    }
}

5.3.2、IStudentService

package com.abc.demo.service;

import com.abc.demo.entity.Student;

import java.util.List;

public interface IStudentService {
    void addStudent();

    List<Student> queryStudent();

    void addStudentMybatis();

    List<Student> queryStudentMybatis();
}

package com.abc.demo.service.impl;

import com.abc.demo.dao2.IStudentDao;
import com.abc.demo.entity.Student;
import com.abc.demo.service.IStudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.List;

@Service
public class StudentServiceImpl implements IStudentService {
    @Autowired
    @Qualifier("jdbcTemplate2")
    private JdbcTemplate jdbcTemplate2;

    @Autowired
    private IStudentDao studentDao;

    @Transactional(transactionManager = "transactionManager2")
    @Override
    public void addStudent() {
        jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "李白", 20);
        jdbcTemplate2.update("insert into a_student(name,age) values(?,?)", "杜甫", 21);
    }

    @Override
    public List<Student> queryStudent() {
        return jdbcTemplate2.query("select * from a_student", new BeanPropertyRowMapper<>(Student.class));
    }

    @Transactional(transactionManager = "transactionManager2")
    @Override
    public void addStudentMybatis() {
        studentDao.insertStudent(new Student("李白", 20));
        studentDao.insertStudent(new Student("杜甫", 21));
    }

    @Override
    public List<Student> queryStudentMybatis() {
        return studentDao.selectStudent();
    }
}

6、Controller

package com.abc.demo.controller;

import com.abc.demo.entity.R;
import com.abc.demo.entity.School;
import com.abc.demo.entity.Student;
import com.abc.demo.service.ISchoolService;
import com.abc.demo.service.IStudentService;
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;

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

    @Autowired
    private ISchoolService schoolService;

    @Autowired
    private IStudentService studentService;

    @RequestMapping("addSchool")
    public R addSchool() {
        schoolService.addSchool();
        return R.ok();
    }

    @RequestMapping("querySchool")
    public R querySchool() {
        List<School> list =  schoolService.querySchool();
        return R.ok(list);
    }

    @RequestMapping("addSchoolMybatis")
    public R addSchoolMybatis() {
        schoolService.addSchoolMybatis();
        return R.ok();
    }

    @RequestMapping("querySchoolMybatis")
    public R querySchoolMybatis() {
        List<School> list =  schoolService.querySchoolMybatis();
        return R.ok(list);
    }

    @RequestMapping("addStudent")
    public R addStudent() {
        studentService.addStudent();
        return R.ok();
    }

    @RequestMapping("queryStudent")
    public R queryStudent() {
        List<Student> list =  studentService.queryStudent();
        return R.ok(list);
    }

    @RequestMapping("addStudentMybatis")
    public R addStudentMybatis() {
        studentService.addStudentMybatis();
        return R.ok();
    }

    @RequestMapping("queryStudentMybatis")
    public R queryStudentMybatis() {
        List<Student> list =  studentService.queryStudentMybatis();
        return R.ok(list);
    }
}

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;
    }

}