springboot + mybatis + druid + 多数据源的问题详解

湛蓝之海

这篇文章主要介绍了springboot + mybatis + druid + 多数据源的问题详解,示例代码文字相结合给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
目录

一. 简介
二. sql脚本
三. 工程搭建
- 3.1 目录结构图
- 3.2 pom.xml文件
- 3.3 application.yml
- 3.4 数据源配置类
- 3.5 Controller
- 3.6 Service
- 3.7 serviceImpl
- 3.8 mapper
- 3.9 mapper.xml
- 3.10 entity
- 3.11 启动类
四. 测试

一. 简介
俩个数据库db1,db2， db1数据库的mapper.xml和db2数据库的mapper.xml分别放到不同的目录下, 通过给不同的目录配置不同的数据源，并分别监控各自的事务。
已有新版方案： Mybatis Plus整合多数据源和读写分离，请使用新版；

二. sql脚本
db1数据库的user表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

db2数据库的role表：

CREATE TABLE `role` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三. 工程搭建

3.1 目录结构图

3.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.example</groupId>
  <artifactId>datasources</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>
 
  <name>datasources</name>
  <description>Demo project for Spring Boot</description>
 
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.4.RELEASE</version>
    <relativePath/>
  </parent>
 
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>
 
  <dependencies>
 
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
 
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.2</version>
    </dependency>
 
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
    </dependency>
 
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
 
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.4</version>
    </dependency>
 
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.datatype</groupId>
      <artifactId>jackson-datatype-joda</artifactId>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.module</groupId>
      <artifactId>jackson-module-parameter-names</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
 
    <!-- 分页插件 -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.2.5</version>
    </dependency>
 
    <!-- alibaba的druid数据库连接池 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.1.9</version>
    </dependency>
 
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
 
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
 
    <!-- atomikos transaction management -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jta-atomikos</artifactId>
    </dependency>
 
    <!-- swagger -->
    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger2</artifactId>
      <version>2.6.1</version>
    </dependency>
    <dependency>
      <groupId>io.springfox</groupId>
      <artifactId>springfox-swagger-ui</artifactId>
      <version>2.6.1</version>
    </dependency>
    <!-- swagger -->
 
 
  </dependencies>
 
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
 
</project>

3.3 application.yml

server:
  port: 8080
 
 
spring:
  datasource:
  db1:
    driverClassName: com.mysql.jdbc.Driver
    username: 用户名
    password: 密码
    # spring2.0此处为jdbc-url
    jdbc-url: jdbc:mysql://IP:3306/db1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    type: com.alibaba.druid.pool.DruidDataSource
  db2:
    driverClassName: com.mysql.jdbc.Driver
    username: 用户名
    password: 密码
    # spring2.0此处为jdbc-url
    jdbc-url: jdbc:mysql://IP:3306/db2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    type: com.alibaba.druid.pool.DruidDataSource

3.4 数据源配置类
3.4.1 db1数据库的数据源（主数据源@Primary）

package com.example.datasources.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.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.example.datasources.mapper.db1", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DataSource1Config {
 
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.db1")
  @Primary
  public DataSource db1DataSource() {
    return DataSourceBuilder.create().build();
  }
 
  @Bean
  @Primary
  public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/example/datasources/mapper/db1/*.xml"));
    return bean.getObject();
  }
 
  @Bean
  @Primary
  public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
 
  @Bean
  @Primary
  public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
 
}

3.4.2 db2数据库的数据源

package com.example.datasources.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.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan(basePackages = "com.example.datasources.mapper.db2", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DataSource2Config {
 
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.db2")
  public DataSource db2DataSource() {
    return DataSourceBuilder.create().build();
  }
 
  @Bean
  public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/example/datasources/mapper/db2/*.xml"));
    return bean.getObject();
  }
 
  @Bean
  public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
 
  @Bean
  public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
 
 
}

3.5 Controller
3.5.1 db1的UserController

package com.example.datasources.controller;
 
import com.example.datasources.entity.db1.User;
import com.example.datasources.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
 
import java.util.List;
 
@RestController
@RequestMapping("/user")
public class UserController {
 
  @Autowired
  private UserService userService;
 
  @GetMapping("/select/list")
  public List<User> selectUserList() {
    return this.userService.selectUserList();
  }
 
  @GetMapping("/save")
  public void saveUser(User user) {
    this.userService.saveUser(user);
  }
 
 
}

3.5.2 db2的RoleController

package com.example.datasources.controller;
 
import com.example.datasources.entity.db2.Role;
import com.example.datasources.service.RoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
 
@RestController
@RequestMapping("/role")
public class RoleController {
 
  @Autowired
  private RoleService roleService;
 
  @GetMapping("/select/list")
  public List<Role> selectRoleList() {
    return this.roleService.selectRoleList();
  }
 
  @GetMapping("/save")
  public void saveRole(Role role) {
    this.roleService.saveRole(role);
  }
 
 
}

3.6 Service
3.6.1 db1的UserService

package com.example.datasources.service;
 
import com.example.datasources.entity.db1.User;
 
import java.util.List;
 
public interface UserService {
 
  List<User> selectUserList();
 
  void saveUser(User user);
 
}

3.6.2 db2的RoleService

package com.example.datasources.service;
 
import com.example.datasources.entity.db2.Role;
 
import java.util.List;
 
public interface RoleService {
 
  List<Role> selectRoleList();
 
  void saveRole(Role role);
 
}

3.7 serviceImpl
3.7.1 db1的UserServiceImpl

package com.example.datasources.service.impl;
 
import com.example.datasources.entity.db1.User;
import com.example.datasources.mapper.db1.UserMapper;
import com.example.datasources.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
@Service
public class UserServiceImpl implements UserService {
 
  @Autowired
  private UserMapper userMapper;
 
  @Override
  public List<User> selectUserList() {
    return this.userMapper.selectUserList();
  }
 
  @Transactional
  @Override
  public void saveUser(User user) {
    this.userMapper.saveUser(user);
//    throw new RuntimeException();
  }
}

3.7.2 db2的RoleServiceImpl

package com.example.datasources.service.impl;
 
import com.example.datasources.entity.db2.Role;
import com.example.datasources.mapper.db2.RoleMapper;
import com.example.datasources.service.RoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
@Service
public class RoleServiceImpl implements RoleService {
 
  @Autowired
  private RoleMapper roleMapper;
 
  @Override
  public List<Role> selectRoleList() {
    return this.roleMapper.selectRoleList();
  }
 
  // 注：不是主数据源必须要声明其数据源，否则事务不起作用
  @Transactional(value = "db2TransactionManager")
  @Override
  public void saveRole(Role role) {
    this.roleMapper.saveRole(role);
//    throw new RuntimeException();
  }
}

3.8 mapper
3.8.1 db1的UserMapper

package com.example.datasources.mapper.db1;
 
import com.example.datasources.entity.db1.User;
 
import java.util.List;
 
public interface UserMapper {
 
  List<User> selectUserList();
 
  void saveUser(User user);
 
}

3.8.2 db2的RoleMapper

package com.example.datasources.mapper.db2;
 
import com.example.datasources.entity.db2.Role;
 
import java.util.List;
 
public interface RoleMapper {
 
  List<Role> selectRoleList();
 
  void saveRole(Role role);
}

3.9 mapper.xml
3.9.1 db1的UserMapper.xml

<?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.example.datasources.mapper.db1.UserMapper" >
 
  <resultMap id="BaseResultMap" type="com.example.datasources.entity.db1.User" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>
 
 
  <sql id="Base_Column_List" >
    id, `name`
  </sql>
 
 
  <select id="selectUserList" resultMap="BaseResultMap"  >
    SELECT
      <include refid="Base_Column_List" />
    FROM `user`
  </select>
 
 
  <insert id="saveUser" parameterType="com.example.datasources.entity.db1.User">
    INSERT INTO `user`
       (id, `name`)
    VALUES
       ( #{id}, #{name} )
  </insert>
 
</mapper>

3.9.2 db2的RoleMapper.xml

<?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.example.datasources.mapper.db2.RoleMapper" >
 
  <resultMap id="BaseResultMap" type="com.example.datasources.entity.db2.Role" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>
 
  <sql id="Base_Column_List" >
    id, name
  </sql>
 
  <select id="selectRoleList" resultMap="BaseResultMap"  >
    SELECT
      <include refid="Base_Column_List" />
    FROM
      role
  </select>
 
 
  <insert id="saveRole" parameterType="com.example.datasources.entity.db2.Role">
    INSERT INTO `role`
       (id, `name`)
    VALUES
       ( #{id}, #{name} )
  </insert>
 
</mapper>

3.10 entity
3.10.1 db1的User

package com.example.datasources.entity.db1;
 
public class User {
 
  private Integer id;
  private String name;
 
  public User() {
  }
 
  public User(Integer id, String name) {
    this.id = id;
    this.name = name;
  }
 
  public Integer getId() {
    return id;
  }
 
  public void setId(Integer id) {
    this.id = id;
  }
 
  public String getName() {
    return name;
  }
 
  public void setName(String name) {
    this.name = name;
  }
}

3.10.2 db2的Role

package com.example.datasources.entity.db2;
 
public class Role {
 
  private Integer id;
  private String name;
 
  public Role() {
  }
 
  public Role(Integer id, String name) {
    this.id = id;
    this.name = name;
  }
 
  public Integer getId() {
    return id;
  }
 
  public void setId(Integer id) {
    this.id = id;
  }
 
  public String getName() {
    return name;
  }
 
  public void setName(String name) {
    this.name = name;
  }
 
}

3.11 启动类

package com.example.datasources;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
public class DatasourcesApplication {
 
  public static void main(String[] args) {
    SpringApplication.run(DatasourcesApplication.class, args);
  }
}

四. 测试
可以直接在浏览器测试，测试事务的时候可以将异常打开。
需要注意的是：非主数据源必须要在@Transactional注解中指定数据源，否则事务不起作用。主数据库不需要。
到此这篇关于springboot + mybatis + druid + 多数据源的问题详解的文章就介绍到这了,更多相关springboot druid多数据源内容请搜索CodeAE代码之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持CodeAE代码之家！
原文链接：https://blog.csdn.net/qq_35206261/article/details/81778224

[Java] springboot + mybatis + druid + 多数据源的问题详解

相关帖子

浅谈Mysql8和mysql5.7的区别

动态在线扩容root根分区大小的方法详解

一篇带给你MySQL索引知识详解

MySQL视图和索引专篇精讲

Java8的Lambda和排序

利用Linux防火墙隔离本地欺骗地址的方法详解

Spring Security登陆流程讲解

retina显示屏的好处详解

详解 SSL（三）：SSL 证书该如何选择？

湛蓝之海 LV3