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

137 阅读 0 评论 91 点赞

我是靠谱客的博主迷人裙子，最近开发中收集的这篇文章主要介绍springboot + mybatis + druid + 多数据源的问题详解，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一. 简介

俩个数据库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多数据源内容请搜索靠谱客以前的文章或继续浏览下面的相关文章希望大家以后多多支持靠谱客！

最后

以上就是迷人裙子为你收集整理的springboot + mybatis + druid + 多数据源的问题详解的全部内容，希望文章能够帮你解决springboot + mybatis + druid + 多数据源的问题详解所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：Java编程
浏览次数：137 次浏览
发布日期：2022-05-31 13:10:05
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujo_22_fz_13__23_cz.html

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 启动类

四. 测试

最后

评论列表共有 0 条评论

发表评论取消回复

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 启动类

四. 测试

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复