Springboot+mybatis实现双数据源读写分离
注:需要配置两台服务器的MySQL数据库,数据同步:点我跳转
源码地址:https://gitee.com/liu-miaos-xiaojing/springboot-mybatis-master_slave.git
效果如下:
查询:
插入:
目录结构如下:
创建数据表
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接口测试工具发送对应请求,进行测试,效果见文章顶