springboot+mybatis+mybatis +mysql读写分离(AOP方式)

    xiaoxiao2022-07-12  160

    引言

    读写分离要做的事情就是对于一条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!!!

    最新回复(0)