Springboot+mybatis实现双数据源读写分离

Springboot+mybatis实现双数据源读写分离

注：需要配置两台服务器的MySQL数据库，数据同步：点我跳转

源码地址：https://gitee.com/liu-miaos-xiaojing/springboot-mybatis-master_slave.git

效果如下：

查询：

插入：

目录结构如下：

1. 创建数据表

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

-- test.`user` definition

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.插入数据

INSERT INTO test.`user` (name,age) VALUES
     ('张三','18'),
     ('李四','18'),
     ('赵六','18'),
     ('赵六','18');

3.导入maven依赖

<!--数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

        <!--双数据源依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

        <!--数据库连接-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--数据库连接驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

        <!--mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

4.创建application.yaml文件

server:
  port: 8888
spring:
  datasource:
    dynamic:
      primary: master # 默认使用数据库
      datasource:
        master: #----------------主数据库配置(写入)----------------
          url: jdbc:mysql://10.70.20.78:3306/test?serverTimezone=Asia/Shanghai
          username: root
          password: MySQL123456.
          driver-class-name: com.mysql.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
        slave: #----------------从库(读取)----------------
          url: jdbc:mysql://10.70.20.146:3306/test?serverTimezone=Asia/Shanghai
          username: root
          password: MySQL123456.
          driver-class-name: com.mysql.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
      durid:
        initial-size: 1
        max-active: 20
        min-idle: 1
        max-wait: 60000
  autoconfigure: # 去除druid配置
    exclude:  com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

mybatis:
  mapper-locations: classpath:mapper/*                                  #扫描mapper接口映射层
  configuration:
    map-underscore-to-camel-case: true                                  #开启驼峰命名规则
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl               #控制台打印sql语句

5.创建实体类

package com.lm.master_slave.bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private Integer id;

    private String name;

    private String age;

}

6.创建mapper接口

package com.lm.master_slave.mapper;

import com.lm.master_slave.bean.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbggenerated Tue Feb 14 21:53:41 GMT+08:00 2023
     */
    int deleteByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbggenerated Tue Feb 14 21:53:41 GMT+08:00 2023
     */
    int insert(User record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbggenerated Tue Feb 14 21:53:41 GMT+08:00 2023
     */
    User selectByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbggenerated Tue Feb 14 21:53:41 GMT+08:00 2023
     */
    List<User> selectAll();

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table user
     *
     * @mbggenerated Tue Feb 14 21:53:41 GMT+08:00 2023
     */
    int updateByPrimaryKey(User record);
}

7.创建service层

解释：

这里，service层的@DS("slave") 、@DS("master")注解
在调用对应方法时，会自动对应数据源（durid数据连接池）中的

package com.lm.master_slave.service;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.lm.master_slave.bean.User;
import com.lm.master_slave.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: lm
 * @createDate: 2023-02-14 21:55
 * @description:
 */
@Service
public class UserService {

    @Autowired
    private UserMapper mapper;

    @DS("slave")        //自动连接从库
    public List<User> selectAll() {
        return mapper.selectAll();
    }

    @DS("master")        //自动连接注库
    public int insert(User record) {
        return mapper.insert(record);
    }

}

8.创建controller层

package com.lm.master_slave.controller;

import com.lm.master_slave.bean.User;
import com.lm.master_slave.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.RestController;

import java.util.List;

/**
 * @author: lm
 * @createDate: 2023-02-14 21:57
 * @description:
 */
@RestController
public class UserController {

    @Autowired
    private UserService service;


    @RequestMapping("/users")
    public List<User> selectAll() {
        return service.selectAll();
    }

    @RequestMapping(value = "/user",method = RequestMethod.POST)
    public int insert(User user) {
        return service.insert(new User(null,user.getName(),user.getAge()));
    }

}

使用api接口测试工具发送对应请求，进行测试，效果见文章顶