springboot + mybatis + druid + 多数据源的问题详解
作者：请叫我猿叔叔 发布时间：2023-01-24 15:59:58
标签：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注解中指定数据源，否则事务不起作用。主数据库不需要。
来源：https://blog.csdn.net/qq_35206261/article/details/81778224
0
投稿猜你喜欢
- 这篇文章主要介绍了Spring @Transactional注解失效解决方案,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的
对于跨域，相信同学们都有所了解。前端的跨域的若干种方式，大家也都知道，什么 JSONP，iframe+domain 等等。但是我们今天的主题- 对象内存分配与回收策略对象的内存分配，往大方向讲，就是在堆上分配〔但也可能经过JIT编译后被拆散为标量类型并间接地栈上分配），对象主要分配在
- 一、身份证结构和形式在通用的身份证号码有15位的和18位的；15位身份证号码各位的含义:1、1-2位省、自治区、直辖市代码；2、3-4位地级
- 本文实例讲述了java基于swing实现的五子棋游戏代码。分享给大家供大家参考。主要功能代码如下：import java.awt.*;imp
- 一、SpringMvc概述SpringMVC是一个基于MVC设计模式的WEB层框架。SpringMVC设计模式：MVC,全名是(Model
- 1.普通轮询算 * 询（Round Robin，RR）是依次将用户的访问请求，按循环顺序分配到web服务节点上，从1开始到最后一台服务器节点结
- AOP注解无效，切面不执行的解决想做一个api请求日志，想到使用aop，配置过程中遇到了一个坑，aop不起作用，我的aop是这样的：pack
- #line #line 使您可以修改编译器的行号以及（可选）错误和警告的文件名输出。下面的示例说明如何报告与行号关联的两个警告。#line
- 利用Javaweb开发的一个校园服务系统，通过发布自己的任务并设置悬赏金额，有些类似于赏金猎人，在这里分享给大家，有需要可以联系我:2186
- SpringCloud Gateway 简介SpringCloud Gateway 是 Spring Cloud 的一个全新项目，该项目是基
- 前言你一定会好奇：“老周，你去哪开飞机了？这么久没写博客了。”老周：“我买不起飞机，开了个铁矿，挖了一年半的石头。谁知铁矿垮了，压死了几条蜈
- 前言入职新公司到现在也有一个月了，完成了手头的工作，前几天终于有时间研究下公司旧项目的代码。在研究代码的过程中，发现项目里用到了Spring
- JAVA 枚举单例模式及源码分析的实例详解 单例模式的实现有很多种，网上也分析了
- C++虚类相当于java中的抽象类，与接口的不同之处是：1.一个子类只能继承一个抽象类（虚类），但能实现多个接口2.一个抽象类可以有构造方法
- 前文传送门：NioEventLoop处理IO事件执行任务队列继续回到NioEventLoop的run()方法:protected void
- 一、点睛邻接矩阵通常采用一个一维数组存储图中节点的信息，采用一个二维数组存储图中节点之间的邻接关系。邻接矩阵可以用来表示无向图、有向图和网。
- 简单工厂模式解释： 简单工厂模式（Simple Fact
- 一，项目简介经过调查研究进行开发设计的这款仓库管理系统，主要是为商家提供商品货物进销存的信息化管理，以便让商家在竞争如此激烈的今天占据一定的
- 一、背景1.1、前言当我们写好代码并测试功能符合要求时，有可能每天都要执行这个程序（比如我写了一个爬虫脚本，每天定时运行获取我想看的小说更新
