SpringBoot + Mybatis-Plus 实现多数据源简单示例

梦里梦外; 2023-10-06 13:48 35阅读 0赞

##### 1. 简介 #####

在单体项目中，经常出现想要访问多个数据源的情况，或者因为某些性能瓶颈，将大数据量的业务表分离到另一个库等情况。  
  实现多数据源的方案有很多，Mybatis-Plus提供了非常简单的实现方案，以此为例。

##### 2. 示例代码 #####

*  创建数据库及初始化表数据

CREATE DATABASE `db_master`;
    
    USE `db_master`;
    
    --
    -- Table structure for table `user`
    --
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `username` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `age` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    --
    -- Dumping data for table `user`
    --
    LOCK TABLES `user` WRITE;
    INSERT INTO `user` VALUES (1,'master',18);
    UNLOCK TABLES;
    
    --
    -- Current Database: `db_slave_1`
    --
    CREATE DATABASE `db_slave_1`;
    
    USE `db_slave_1`;
    
    --
    -- Table structure for table `user`
    --
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `age` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    
    --
    -- Dumping data for table `user`
    --
    LOCK TABLES `user` WRITE;
    INSERT INTO `user` VALUES (1,'slave_1',20);
    UNLOCK TABLES;
    
    --
    -- Current Database: `db_slave_2`
    --
    CREATE DATABASE `db_slave_2` ;
    
    USE `db_slave_2`;
    
    --
    -- Table structure for table `user`
    --
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `age` int DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    --
    -- Dumping data for table `user`
    --
    LOCK TABLES `user` WRITE;
    INSERT INTO `user` VALUES (1,'slave_2',22);
    UNLOCK TABLES;

*  创建项目  
    ![format_png][]
 *  修改pom.xml

<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>com.c3stones</groupId>
    	<artifactId>spring-boot-dynamic-datasource-demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>spring-boot-dynamic-datasource-demo</name>
    	<description>Spring Boot Dynamic Datasource Demo</description>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.2.8.RELEASE</version>
    		<relativePath />
    	</parent>
    
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>com.baomidou</groupId>
    			<artifactId>mybatis-plus-boot-starter</artifactId>
    			<version>3.3.2</version>
    		</dependency>
    		<dependency>
    			<groupId>com.baomidou</groupId>
    			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    			<version>3.1.1</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

*  添加配置文件application.yml  
      可配置多数据源。

spring:
      datasource:
        dynamic:
          primary: master # 设置默认的数据源或者数据源组，默认值即为master
          strict: false # 设置严格模式，默认false不启动。启动后在未匹配到指定数据源时候回抛出异常，不启动会使用默认数据源。
          datasource:
            master:
              url: jdbc:mysql://127.0.0.1:3306/db_master
              username: root
              password: 123456
              driver-class-name: com.mysql.jdbc.Driver
            slave_1:
              url: jdbc:mysql://127.0.0.1:3306/db_slave_1
              username: root
              password: 123456
              driver-class-name: com.mysql.jdbc.Driver
            slave_2:
              url: jdbc:mysql://127.0.0.1:3306/db_slave_2
              username: root
              password: 123456
              driver-class-name: com.mysql.jdbc.Driver
              
    mybatis-plus:
       mapper-locations: classpath:/mapper/*/*Mapper.xml
       configuration:
          log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

*  创建实体

import com.baomidou.mybatisplus.annotation.IdType;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableName;
    
    /**
     * 用户Entity
     * @author CL
     *
     */
    @TableName(value = "user")
    public class User {
    
    	/**
    	 * ID
    	 */
    	@TableId(value = "id", type = IdType.AUTO)
    	private Long id;
    
    	/**
    	 * 用户名称
    	 */
    	private String username;
    
    	/**
    	 * 年龄
    	 */
    	private int age;
    
    	public User() {
    		super();
    	}
    
    	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 int getAge() {
    		return age;
    	}
    
    	public void setAge(int age) {
    		this.age = age;
    	}
    
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", username=" + username + ", age=" + age + "]";
    	}
    	
    }

*  创建Mapper

import org.apache.ibatis.annotations.Mapper;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.c3stones.entity.User;
    
    /**
     * 用户Mapper
     * 
     * @author CL
     *
     */
    @Mapper
    public interface UserMapper extends BaseMapper<User> {
    
    }

*  在resource目录下创建mapper文件夹并添加Mapper.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.c3stones.mapper.UserMapper">
    
    </mapper>

*  创建Service

import java.util.List;
    
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.c3stones.entity.User;
    
    /**
     * 用户Service
     * 
     * @author CL
     *
     */
    public interface UserService extends IService<User> {
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	List<User> selectMasterAll();
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	List<User> selectSlave1All();
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	List<User> selectSlave2All();
    
    }

*  创建Service实现  
      注解`@DS`配置当前Service的数据源，建议将此注解添加到Service的类或方法上，注意：**方法上的优先级高于类**。

import java.util.List;
    
    import org.springframework.stereotype.Service;
    
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.c3stones.entity.User;
    import com.c3stones.mapper.UserMapper;
    import com.c3stones.service.UserService;
    
    /**
     * 用户Service实现
     * 
     * @author CL
     *
     */
    @Service
    @DS("master")
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	@Override
    	public List<User> selectMasterAll() {
    		return baseMapper.selectList(new QueryWrapper<>());
    	}
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	@DS("slave_1")
    	@Override
    	public List<User> selectSlave1All() {
    		return baseMapper.selectList(new QueryWrapper<>());
    	}
    
    	/**
    	 * 查询全部用户
    	 * 
    	 * @return 用户列表
    	 */
    	@DS("slave_2")
    	@Override
    	public List<User> selectSlave2All() {
    		return baseMapper.selectList(new QueryWrapper<>());
    	}
    
    }

*  创建启动类

import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * 启动类
     * 
     * @author CL
     *
     */
    @SpringBootApplication
    public class Application {
    
    	public static void main(String[] args) {
    		SpringApplication.run(Application.class, args);
    	}
    	
    }

##### 3. 单元测试 #####

*  创建单元测试类

import java.util.List;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import com.c3stones.Application;
    import com.c3stones.entity.User;
    import com.c3stones.service.UserService;
    
    /**
     * 用户Service测试
     * 
     * @author CL
     *
     */
    @RunWith(SpringRunner.class)
    @SpringBootTest(classes = { Application.class })
    public class UserServiceImplTest {
    
    	@Autowired
    	private UserService userService;
    
    	/**
    	 * 测试查询master库用户信息
    	 */
    	@Test
    	public void selectMasterAllTest() {
    		List<User> list = userService.selectMasterAll();
    		list.forEach(System.out::println);
    	}
    
    	/**
    	 * 测试查询slave_1库用户信息
    	 */
    	@Test
    	public void selectSlave1AllTest() {
    		List<User> list = userService.selectSlave1All();
    		list.forEach(System.out::println);
    	}
    
    	/**
    	 * 测试查询slave_2库用户信息
    	 */
    	@Test
    	public void selectSlave2AllTest() {
    		List<User> list = userService.selectSlave2All();
    		list.forEach(System.out::println);
    	}
    
    }

单元测试时仔细观察控制台日志，及结果打印信息。

##### 4. 项目地址 #####

[spring-boot-dynamic-datasource-demo][]

[format_png]: http://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDIwLmNuYmxvZ3MuY29tL2Jsb2cvMTExOTA5Ny8yMDIwMDcvMTExOTA5Ny0yMDIwMDcyODE1MTAwNTE1My0yOTIyODMwMTQucG5n?x-oss-process=image/format,png
[spring-boot-dynamic-datasource-demo]: https://github.com/C3Stones/blog