springboot + mybatis + druid + 多數據源的問題詳解

Posted on 2021-09-18 by WalkonNet

一. 簡介

倆個數據庫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多數據源內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

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 啟動類

四. 測試

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

一. 簡介

二. 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 啟動類

四. 測試

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆