Spring Boot 整合 Mybatis 实现 Druid 多数据源配置

浩骞

已于 2022-02-03 18:17:35 修改

阅读量3.1k

点赞数 2

文章标签： spring boot java 数据库

于 2022-01-27 23:39:44 首次发布

本文链接：https://blog.csdn.net/qiuhaoqian/article/details/122724818

版权

1 多数据源的应⽤场景

当业务数据量达到了⼀定程度，DBA 需要合理配置数据库资源。即配置主库的机器⾼配置，把核⼼⾼频的数据放在主库上；把次要的数据放在从库，低配置。开源节流嘛，就这个意思。把数据放在不同的数据库⾥，就需要通过不同的数据源进⾏操作数据。

这⾥我们举个 springboot-mybatis-mutil-datasource ⼯程案例： user ⽤户表在主库 master 上，地址表 city 在从库 cluster 上。下⾯实现获取根据⽤户名获取⽤户信息，包括从库的地址信息，那么需要从主库和从库中分别获取数据，并在业务逻辑层组装返回。逻辑如图：

在这里插入图片描述

2 数据库脚本

创建主数据库：

CREATE DATABASE masterdb;

主库中创建表 user

DROP TABLE IF EXISTS `city`; 
CREATE TABLE user (
id INT(10) unsigned PRIMARY KEY NOT NULL COMMENT '⽤户编号' AUTO_INCREMENT, 
user_name VARCHAR(25) COMMENT '⽤户名称', 
description VARCHAR(25) COMMENT '描述' 
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插⼊数据

INSERT user VALUES (1 ,'小明','他有⼀个小学生');

创建从数据库：

CREATE DATABASE clusterdb;

从库中创建表：city

DROP TABLE IF EXISTS `city`; 
CREATE TABLE `city` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市编号', 
`province_id` int(10) unsigned NOT NULL COMMENT '省份编号', 
`city_name` varchar(25) DEFAULT NULL COMMENT '城市名称', 
`description` varchar(25) DEFAULT NULL COMMENT '描述',
 PRIMARY KEY (`id`) 
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据：

INSERT city VALUES (1 ,1,'杭州市','西湖区');

3 项目结构

在这里插入图片描述

4 代码

依赖 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>springboot</groupId>
    <artifactId>springboot-mybatis-mutil-datasource</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mybatis-mutil-datasource</name>

    <!-- Spring Boot 启动父依赖 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
    </parent>

    <properties>
        <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
        <mysql-connector>5.1.39</mysql-connector>
        <druid>1.0.18</druid>
    </properties>

    <dependencies>
        <!-- Spring Boot Web 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- Spring Boot Test 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot}</version>
        </dependency>
        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector}</version>
        </dependency>
        <!-- Druid 数据连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid}</version>
        </dependency>
        <!-- Junit单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

配置文件

application.properties 中配置两个数据源

## master 主数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/masterdb?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.jdbc.Driver

## cluster 从数据源配置
cluster.datasource.url=jdbc:mysql://localhost:3306/clusterdb?useUnicode=true&characterEncoding=utf8
cluster.datasource.username=root
cluster.datasource.password=123456
cluster.datasource.driverClassName=com.mysql.jdbc.Driver

数据源配置类

多数据源配置的时候注意，必须要有⼀个主数据源，即 MasterDataSourceConfig 配置。

主数据源

package org.spring.springboot.config.ds;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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
// 扫描 Mapper 接口
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {

    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

    @Value("${master.datasource.url}")
    private String url;

    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driverClassName}")
    private String driverClass;

    /** 
     * 数据源 
     */
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    /** 
     * 事务管理器
     * 注意：@Primary进行修饰以后，当@Transational注解修饰的方法中设计到两个数据原时，
     *      发生事务回滚只会回滚masterDataSource的sql。
     */
    @Bean(name = "masterTransactionManager")
    @Primary 
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    /**
     * 编程式事务模板
     */
    @Bean(name = "masterTransactionTemplate")
    public TransactionTemplate masterTransactionTemplate() {
        return new TransactionTemplate(masterTransactionManager());
    }

    /** 
     * SqlSession工厂
     */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

从数据源

package org.spring.springboot.config.ds;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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
// 扫描 Mapper 接口
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {

    // 精确到 cluster 目录，以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.cluster";
    static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";

    @Value("${cluster.datasource.url}")
    private String url;

    @Value("${cluster.datasource.username}")
    private String user;

    @Value("${cluster.datasource.password}")
    private String password;

    @Value("${cluster.datasource.driverClassName}")
    private String driverClass;

    /** 
     * 数据源 
     */
    @Bean(name = "clusterDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    /** 
     * 事务管理器 
     */
    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    /**
     * 编程式事务模板
     */
    @Bean(name = "clusterTransactionTemplate")
    public TransactionTemplate clusterTransactionTemplate() {
        return new TransactionTemplate(clusterTransactionManager());
    }

    /** 
     * SqlSession工厂
     */
    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

实体类

package org.spring.springboot.domain;

import java.io.Serializable;

/**
 * 城市实体类
 */
public class City {
    /**
     * 城市编号
     */
    private Long id;
    /**
     * 省份编号
     */
    private Long provinceId;
    /**
     * 城市名称
     */
    private String cityName;
    /**
     * 描述
     */
    private String description;

    public Long getId() {
        return id;
    }
    
    public void setId(Long id) {
        this.id = id;
    }

    public Long getProvinceId() {
        return provinceId;
    }

    public void setProvinceId(Long provinceId) {
        this.provinceId = provinceId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

package org.spring.springboot.domain;

/**
 * 用户实体类
 */
public class User {
    /**
     * 城市编号
     */
    private Long id;
    /**
     * 城市名称
     */
    private String userName;
    /**
     * 描述
     */
    private String description;
    /**
     * 城市
     */
    private City city;

    public City getCity() {
        return city;
    }

    public void setCity(City city) {
        this.city = city;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

sql映射文件

CityMapper.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="org.spring.springboot.dao.cluster.CityDao">
	<resultMap id="BaseResultMap" type="org.spring.springboot.domain.City">
		<result column="id" property="id" />
		<result column="province_id" property="provinceId" />
		<result column="city_name" property="cityName" />
		<result column="description" property="description" />
	</resultMap>

	<sql id="Base_Column_List">
		id, province_id, city_name, description
	</sql>

	<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
		select
		<include refid="Base_Column_List" />
		from city
		where city_name = #{cityName}
	</select>

</mapper>

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="org.spring.springboot.dao.master.UserDao">
	<resultMap id="BaseResultMap" type="org.spring.springboot.domain.User">
		<result column="id" property="id" />
		<result column="user_name" property="userName" />
		<result column="description" property="description" />
	</resultMap>

	<sql id="Base_Column_List">
		id, user_name, description
	</sql>

	<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
		select
		<include refid="Base_Column_List" />
		from user
		where id = 1
	</select>

</mapper>

dao

package org.spring.springboot.dao.cluster;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.spring.springboot.domain.City;

/**
 * 城市 DAO 接口类
 */
@Mapper
public interface CityDao {
    /**
     * 根据城市名称，查询城市信息
     */
    City findByName(@Param("cityName") String cityName);
}

package org.spring.springboot.dao.master;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.spring.springboot.domain.User;

/**
 * 用户 DAO 接口类
 */
@Mapper
public interface UserDao {

    /**
     * 根据用户名获取用户信息
     */
    User findByName(@Param("userName") String userName);
}

srvice

package org.spring.springboot.service;

import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;

/**
 * 用户业务接口层
 */
public interface UserService {

    /**
     * 根据用户名获取用户信息，包括从库的地址信息
     *
     * @param userName
     * @return
     */
    User findByName(String userName);
}

package org.spring.springboot.service.impl;

import org.spring.springboot.dao.cluster.CityDao;
import org.spring.springboot.dao.master.UserDao;
import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;
import org.spring.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * 用户业务实现层
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao; // 主数据源

    @Autowired
    private CityDao cityDao; // 从数据源

    @Override
    public User findByName(String userName) {
        User user = userDao.findByName(userName);
        City city = cityDao.findByName("温岭市");
        user.setCity(city);
        return user;
    }
}

controller

package org.spring.springboot.controller;

import org.spring.springboot.domain.City;
import org.spring.springboot.domain.User;
import org.spring.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * 用户控制层
 */
@RestController
public class UserRestController {

    @Autowired
    private UserService userService;

    /**
     * 根据用户名获取用户信息，包括从库的地址信息
     *
     * @param userName
     * @return
     */
    @RequestMapping(value = "/api/user", method = RequestMethod.GET)
    public User findByName(@RequestParam(value = "userName", required = true) String userName) {
        return userService.findByName(userName);
    }

}

启动类

package org.spring.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Spring Boot 应用启动类
 */
@SpringBootApplication
@EnableTransactionManagement
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

5 小节

不同的 DataSource ，不同的 SqlSessionFactory 和不同的 DAO 层，在业务逻辑层做整合。总结的架构图如下：
在这里插入图片描述

6 事务问解决

多数据源的事务处理是有问题的，一定要注意（可以使用分布式事务，但是很复杂）。

spring自带的声明式事务无法实现多数据源的事务管理，因为 @Transactional只能指定一个事务管理器。
spring的编程式事务可以实现，但是比较麻烦，代码示例如下所示：

    @Autowired
    @Qualifier("masterTransactionTemplate")
    TransactionTemplate masterTransactionTemplate;

    @Autowired
    @Qualifier("clusterTransactionTemplate")
    TransactionTemplate clusterTransactionTemplate;

    public void saveAll(Map<String, String> data) {
        masterTransactionTemplate.execute((mStatus) -> {
            clusterTransactionTemplate.execute((cStatus) -> {

                try {
                    // 保存数据到主库
                    saveToMasterDatabase(data);
                    // 保存数据到从库
                    saveToClusterDatabase(data);
                    int a = 1 / 0;
                    return true;
                } catch (Exception e) {
                    // 回滚主库
                    mStatus.setRollbackOnly();
                    // 回滚从库
                    cStatus.setRollbackOnly();
                    return false;
                }
                
            });
            return true;
        });
    }