引言
读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，至于谁来做选择数据库这件事儿，无非两个，要么中间件帮我们做，要么程序自己做。因此，一般来讲，读写分离有两种实现方式。
第一种是依靠中间件（比如：MyCat），也就是说应用程序连接到中间件，中间件帮我们做SQL分离；第二种是应用程序自己去做分离。 这里我们选择程序自己来做，主要是利用Spring提供的路由数据源，以及AOP
然而，应用程序层面去做读写分离最大的弱点（不足之处）在于无法动态增加数据库节点，因为数据源配置都是写在配置中的，新增数据库意味着新加一个数据源，必然改配置，并重启应用。当然，好处就是相对简单。
简要原理
配置多个数据源，连接不同的数据库，当执行修改数据的操作时，用master数据源，然后master 将数据同步到从库slave,当 查询时，切换到从库数据源，这个过程时靠aop 的方式控制的。
准备工作
配置多个数据库 https://blog.csdn.net/houdezaiwu1/article/details/90260359配置主从复制 https://blog.csdn.net/houdezaiwu1/article/details/90481530
配置多个数据源
1. properties文件
## 主数据源配置
spring.datasource.master.driver-class-name=@datasource.driver@
spring.datasource.master.url=@datasource.url@
spring.datasource.master.username=@datasource.username@
spring.datasource.master.password=@datasource.password@
spring.datasource.master.max-idle=10
spring.datasource.master.max-wait=10000
spring.datasource.master.min-idle=5
spring.datasource.master.initial-size=5
## slqve1 第二个数据源配置
spring.datasource.slave1.driver-class-name=@slave1.driver@
spring.datasource.slave1.url=@slave1.url@
spring.datasource.slave1.username=@slave1.username@
spring.datasource.slave1.password=@slave1.password@
spring.datasource.slave1.max-idle=10
spring.datasource.slave1.max-wait=10000
spring.datasource.slave1.min-idle=5
spring.datasource.slave1.initial-size=5
## slqve1 第三个数据源配置
spring.datasource.slave2.driver-class-name=@slave2.driver@
spring.datasource.slave2.url=@slave2.url@
spring.datasource.slave2.username=@slave2.username@
spring.datasource.slave2.password=@slave2.password@
spring.datasource.slave2.max-idle=10
spring.datasource.slave2.max-wait=10000
spring.datasource.slave2.min-idle=5
spring.datasource.slave2.initial-size=5
2 pom文件
<!-- mysql 主库 -->
<datasource.driver>com.mysql.jdbc.Driver</datasource.driver>
<datasource.url>jdbc:mysql://127.0.0.1:3306/master1</datasource.url>
<datasource.username>root</datasource.username>
<datasource.password>123456</datasource.password>
<!-- slave1 从库-->
<slave1.driver>com.mysql.jdbc.Driver</slave1.driver>
<slave1.url>jdbc:mysql://127.0.0.1:3310/master1</slave1.url>
<slave1.username>root</slave1.username>
<slave1.password>123456</slave1.password>
<!-- slave2 从库-->
<slave2.driver>com.mysql.jdbc.Driver</slave2.driver>
<slave2.url>jdbc:mysql://127.0.0.1:3320/master1</slave2.url>
<slave2.username>root</slave2.username>
<slave2.password>123456</slave2.password>
3. DatabaseType
列出所有的数据源的key—key
package com.example.config.datasource;
/**
* 1.定义枚举类数据源类型 key
*/
public enum DatabaseType {
master,slave1,slave2
}
4 DatabaseContextHolder
创建线程安全的类，作为databaseType容器，放master,slave1,slave2，并提供了向其中设置和获取DatabaseType的方法，由于从库有两个，这里简单设置了一个负载均衡。
package com.example.config.datasource;
import java.util.concurrent.atomic.AtomicInteger;
/**
* 2. 创建线程安全的类，作为databaseType容器，放master,slave1,slave2
*/
public class DatabaseContextHolder {
private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
private static final AtomicInteger counter = new AtomicInteger(-1);
public static void set(DatabaseType databaseType){
contextHolder.set(databaseType);
}
public static DatabaseType get(){
return contextHolder.get();
}
public static void setDataBaseMaster() {
set(DatabaseType.master);
System.out.println("=====================> 切换到master");
}
public static void setDataBaseSlave() {
// 轮询
int index = counter.getAndIncrement() % 2;
if (counter.get() > 9999) {
counter.set(-1);
}
if (index == 0) {
set(DatabaseType.slave1);
System.out.println("==================> 切换到slave1");
}else {
set(DatabaseType.slave2);
System.out.println("==================> 切换到slave2");
}
}
}
5 DynamicDataSource
DynamicDataSource继承AbstractRoutingDataSource并重写其中的方法determineCurrentLookupKey()，在该方法中使用DatabaseContextHolder获取当前线程的DatabaseType
package com.example.config.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 3 利用DatabaseContextHolder 获取当前线程的 datasourcetype
* 动态数据源（需要继承AbstractRoutingDataSource）
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DatabaseContextHolder.get();
}
}
6 DataSourceConfig
配置多个数据源，@Primary 解决循环依赖问题
package com.example.config;
import com.example.config.datasource.DatabaseType;
import com.example.config.datasource.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 4. 创建多个数据源实例,最后一个是通过 动态路由的数据源，我们要使用的就是这个
*/
@Configuration
public class DataSourceConfig {
// @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
@Bean
@Primary
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource(){
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource(){
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource(){
return DataSourceBuilder.create().build();
}
/**
*
* @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）
* @param masterDataSource
* @param slave1DataSource
* @param slave2DataSource
* @return
*/
@Bean
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slave1DataSource") DataSource slave1DataSource,
@Qualifier("slave2DataSource") DataSource slave2DataSource){
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.master,masterDataSource);
targetDataSources.put(DatabaseType.slave1,slave1DataSource);
targetDataSources.put(DatabaseType.slave2,slave2DataSource);
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setDefaultTargetDataSource(masterDataSource); // 设置默认的datasource
dynamicDataSource.setTargetDataSources(targetDataSources); // 该方法是AbstractRoutingDataSource的方法
return dynamicDataSource;
}
}
7 MybatisConfig
将DynamicDataSource作为数据源注入到SqlSessionFactory的dataSource属性中去
package com.example.config;
import com.example.config.datasource.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
@AutoConfigureAfter({ DataSourceConfig.class })
public class MybatisConfig {
@Resource(name = "myRoutingDataSource")
DataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
// 下面的不可少，这个有了，就不用配置文件里的
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
AOP 配置
1. 自定义两个注解
标注写和读操作
package com.example.dao.annotation;
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface MasterDataSource {
}
package com.example.dao.annotation;
import java.lang.annotation.*;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface ReadDataSouce {
}
2 定义读写切面
package com.example.config;
import com.example.config.datasource.DatabaseContextHolder;
import com.example.config.datasource.DatabaseType;
import com.example.dao.annotation.MasterDataSource;
import io.swagger.annotations.Extension;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
@Aspect
@Component
public class DataSourceAop {
@Before("@annotation(com.example.dao.annotation.ReadDataSouce)")
public void readPoint(){
DatabaseContextHolder.setDataBaseSlave();
}
@Before("@annotation(com.example.dao.annotation.MasterDataSource)")
public void writePoint(){
DatabaseContextHolder.setDataBaseMaster();
}
}
dao层和service层
package com.example.service;
import com.example.config.datasource.DatabaseContextHolder;
import com.example.config.datasource.DatabaseType;
import com.example.dao.StudentMapper;
import com.example.dao.annotation.MasterDataSource;
import com.example.dao.annotation.ReadDataSouce;
import com.example.model.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2019/3/15.
*/
@Service
@EnableTransactionManagement
public class StudentService implements StudentMapper {
@Autowired
private StudentMapper studentMapper;
@Override
@ReadDataSouce
public int getCount() {
return studentMapper.getCount();
}
@Override
@ReadDataSouce
public List<Student> getStuList() {
return studentMapper.getStuList();
}
@Override
@MasterDataSource
public int insert(Student student) {
return studentMapper.insert(student);
}
@Override
@ReadDataSouce
public List<Student> getStuByAge(int age) {
return studentMapper.getStuByAge(age);
}
@Override
@ReadDataSouce
public List<Student> getStuByIdScope(Map<String,Object> map) {
return studentMapper.getStuByIdScope(map);
}
}
package com.example.dao;
import com.example.model.Student;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2019/3/15.
*/
public interface StudentMapper {
int getCount();
List<Student> getStuList();
int insert(Student student);
List<Student> getStuByAge(int age);
List<Student> getStuByIdScope(Map<String,Object> map);
}
控制层调试
package com.example.controller;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.common.BeanMapUtil;
import com.example.common.JsonUtil;
import com.example.common.ResponseInfo;
import com.example.common.ResponseUtil;
import com.example.dto.StuAddDto;
import com.example.dto.StuByIdScopeDto;
import com.example.model.Student;
import com.example.service.StudentService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.NotBlank;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2019/3/15.
*/
@Api(value="StudentController",description = "学生信息相关接口")
@Controller
@RequestMapping("/stu")
public class StudentController {
private static final Logger log = LoggerFactory.getLogger(StudentController.class);
@Autowired
private StudentService studentService;
@RequestMapping(value = "/getCount",method = RequestMethod.GET)
@ResponseBody
@ApiOperation("查询所有学生数量")
public String getStuCount(){
System.out.print("==========");
int count = studentService.getCount();
String str = "总计数量："+count;
return str;
}
@RequestMapping(value = "/getAllStudent",method = {RequestMethod.POST})
@ResponseBody
@ApiOperation("得到所有学生信息列表")
public List<Student> getAllStudent(){
log.info("======== getAllStudent start ==============");
List<Student> studentList = studentService.getStuList();
log.info("=============== studentList message ==============");
log.info("studentList: "+studentList);
log.info("======== getAllStudent end ==============");
return studentList;
}
@RequestMapping(value = "/getStuByAge/{age}",method = {RequestMethod.GET})
@ResponseBody
@ApiOperation(value="查询年龄小于age的学生信息",notes = "查询年龄大于age的学生信息 ")
//@ApiImplicitParam(name = "age", value = "年龄", required = true, dataType = "Integer")
public List<Student> getStuByAge(@PathVariable(value="age") Integer age){
log.info("======== getStuByAge start ==============");
log.info("=======================age:>"+age);
List<Student> studentList = studentService.getStuByAge(age);
return studentList;
}
@RequestMapping(value = "/getStuByIdScope",method = {RequestMethod.POST})
@ResponseBody
@ApiOperation(value="根据Id的范围查询学生信息",notes = "json 格式 ：{}")
public ResponseInfo<Object> getStuByIdScope(@RequestBody StuByIdScopeDto stuByIdScopeDto){
log.info("===getStuByIdScope request data is:"+ JsonUtil.toJsonString(stuByIdScopeDto));
ResponseInfo<Object> responseInfo = new ResponseInfo<>();
Map<String,Object> requestMap = (Map<String,Object>)BeanMapUtil.objectToMap(stuByIdScopeDto);
JSONObject result = new JSONObject();
Map<String,Object> params = new HashMap<>();
int minId = (Integer) requestMap.get("id1");
int maxId = (Integer) requestMap.get("id2");
params.put("minId",minId);
params.put("maxId",maxId);
log.info("===========minId:"+minId+",maxId: "+maxId+"==============");
List<Student> studentList = studentService.getStuByIdScope(params);
//将list 集合转换为jsonObject需要写工具类
responseInfo.setData(studentList);
responseInfo.setRtnCode(ResponseUtil.SUCCESS_CODE);
responseInfo.setRtnMsg(ResponseUtil.SUCCESS_MSG);
log.info(responseInfo.toString());
return responseInfo;
}
@ApiOperation(value = "新增学生信息",notes = "")
@RequestMapping(value = "/insertStu",method = RequestMethod.POST)
@ResponseBody
public int insertStu(@RequestBody StuAddDto stuAddDto){
log.info("===insertStu request data is:"+ JsonUtil.toJsonString(stuAddDto));
Map<String,Object> requestMap = (Map<String,Object>)BeanMapUtil.objectToMap(stuAddDto);
Student student = new Student();
String name = (String)requestMap.get("name");
int age = (int)requestMap.get("age");
String address = (String)requestMap.get("address");
student.setName(name);
student.setAge(age);
student.setAddress(address);
int count = studentService.insert(student);
return count;
}
}
swagger 页面
done!!!
