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
投稿猜你喜欢
问题项目是springcloud项目，在maven install某一个项目时报错：程序包com.example.commons.appli- 当前单元格指的是DataGridView焦点所在的单元格，它可以通过DataGridView对象的CurrentCell属性取得。如果当前单
- 一、配置xml路径mybatis-plus:mapper-locations: classpath:mapper/*.xml二、编写Mapp
- 项目介绍医院管理系统,分为管理员、医生、病人三种角色；管理员主要功能包括：首页、系统管理：医生管理、患者管理、药品管理；预约管理；病史管理；
- • 创建目录和文件1、通过Path类的Combine方法可以合并路径。string activeDir = @"C:\myDir&
- 由于要做一个新项目，所以打算做一个简单的图片验证码。先说说思路吧：在服务端，从一个文件夹里面找出8张图片，再把8张图片合并成一张大图，在8个
- 简单介绍synchronized是Java中的关键字，是一种同步锁。它修饰的对象有以下几种：1. 修饰一个代码块，被修饰的代码块称为同步语句
- 昨天有个刚学java的师弟发了个程序给我，说死活编译不过，老是报编码问题，自己试了一下，也出问题了...当我们编辑了一个Java源文件保存时
- 序言springboot框架价值，可以简单快速的构建独立的spring生产级别应用。springboot主要有以下的特性：1.创建独立的Sp
- 二进制数据一般输入的格式是0x45, 0x3a, 0xc3, 这种数据格式看起来是16进制的字符串，但是实际上在存储的时候每个都对应一个字节
- ContentProvider是内容提供者，可以跨进程提供数据。大家都知道，ContentProvider的启动，是在Application
- C和指针相关基础知识：内存的分配（谭浩强版）1、整型变量的地址与浮点型/字符型变量的地址区别？（整型变量/浮点型变量的区别是什么）2、int
- 最近碰到一个大转盘的业务，奖品可根据数据后台灵活设置中奖概率，看起来挺简单的业务功能，但实现起来对我这个毫无经验的人来说并不容易，后面又碰到
- 项目地址：https://github.com/JeasonWong/SlackLoadingView老规矩，先上效果。图好大。。说下第一眼
- 原理：创建一个新的Bitmap，然后再根据它来创建一个Canvas，最后调用View的draw方法将View画到Canvas上，这样得到的B
- 前言本篇我们就来讲讲Fragment管理中的 Add() 方法Add()在我们动态的添加、管理Fragment中，Add属于最基础的方法了；
- 简介TCP简介TCP（Transmission Control Protocol 传输控制协议）是一种面向连接的、可靠的、基于字节流的传输层
- 关于实现网易新闻客户端的界面，以前写过很多博客，请参考：Android实现网易新闻客户端效果Android实现网易新闻客户端侧滑菜单（一）A
- 一、缓存的基本概念缓存 。这是一个简单但非常有效的概念，这个想法的核心是记录过程数据，重用操作结果。当执行繁重的操作时，我们会将结果保存在我
- 1.使用response对象提供的sendRedirect()方法可以将网页重定向到另一个页面。重定向操作支持将地址重定向到不同的主机上，这
