springboot + mybatis + druid + 多數(shù)據源的問題詳解
更新時間：2021年09月17日 16:48:11 作者：請叫我猿叔叔
這篇文章主要介紹了springboot + mybatis + druid + 多數(shù)據源的問題詳解,示例代碼文字相結合給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
一. 簡介
倆個數(shù)據庫db1,db2， db1數(shù)據庫的mapper.xml和db2數(shù)據庫的mapper.xml分別放到不同的目錄下, 通過給不同的目錄配置不同的數(shù)據源，并分別監(jiān)控各自的事務。
已有新版方案： Mybatis Plus整合多數(shù)據源和讀寫分離，請使用新版；
二. sql腳本
db1數(shù)據庫的user表：
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
db2數(shù)據庫的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數(shù)據庫連接池 -->
<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 數(shù)據源配置類
3.4.1 db1數(shù)據庫的數(shù)據源 （主數(shù)據源@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數(shù)據庫的數(shù)據源
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();
}
// 注：不是主數(shù)據源必須要聲明其數(shù)據源，否則事務不起作用
@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);
}
}
四. 測試
可以直接在瀏覽器測試，測試事務的時候可以將異常打開。
需要注意的是： 非主數(shù)據源必須要在@Transactional注解中指定數(shù)據源，否則事務不起作用。主數(shù)據庫不需要。
到此這篇關于springboot + mybatis + druid + 多數(shù)據源的問題詳解的文章就介紹到這了,更多相關springboot druid多數(shù)據源內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
實戰(zhàn)干貨之基于SpringBoot的RabbitMQ多種模式隊列
RabbitMQ 是一個由Erlang語言開發(fā)的AMQP的開源實現(xiàn)，支持多種客戶端。用于在分布式系統(tǒng)中存儲轉發(fā)消息，在易用性、擴展性、高可用性等方面表現(xiàn)不俗，下文將帶你深入了解 RabbitMQ 多種模式隊列2021-09-09
MyBatis動態(tài)創(chuàng)建表的實例代碼
在項目需求中，我們經常會遇到動態(tài)操作數(shù)據表的需求，常見的我們會把日志、設備實時位置信息等存入數(shù)據表，并且以一定時間段生成一個表來存儲。接下來通過本文給大家介紹MyBatis動態(tài)創(chuàng)建表的方法，感興趣的朋友一起看看吧2018-07-07
最近將IDEA 升級到 IntelliJ IDEA 2021.3.2，在將maven項目導入IDEA后，maven build時報異常，這個問題是IntelliJ IDEA 2021.3.2 不兼容導致的，下面小編給大家?guī)砹薸dea?與?maven?使用過程中遇到的問題及解決方案，感興趣的朋友一起看看吧2022-05-05
這篇文章主要介紹了java異步寫日志到文件中實現(xiàn)代碼的相關資料,需要的朋友可以參考下2017-04-04
詳解Spring?Security?捕獲?filter?層面異常返回我們自定義的內容
Spring?的異常會轉發(fā)到?BasicErrorController?中進行異常寫入，然后才會返回客戶端。所以，我們可以在?BasicErrorController?對?filter異常進行捕獲并處理，下面通過本文給大家介紹Spring?Security?捕獲?filter?層面異常，返回我們自定義的內容，感興趣的朋友一起看看吧2022-05-05
這篇文章主要介紹了SpringMVC請求域對象的數(shù)據共享問題，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-02-02
這篇文章主要介紹了Java中求Logn/log2 的精度問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-08-08
下面小編就為大家分享一篇基于JVM 中常見垃圾收集算法介紹，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-01-01
Spring?Boot項目中使用?TrueLicense?生成和驗證License的詳細步驟
這篇文章主要介紹了Spring?Boot項目中使用?TrueLicense?生成和驗證License,本文分步驟給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-10-10
這篇文章主要為大家詳細介紹了JavaWeb Servlet實現(xiàn)網頁登錄功能，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-07-07