wap网站优化,什么是网络营销最基本的职能之一是网络营销的基础工作,丹阳网站优化,婚纱摄影影楼SpringBoot实现数据库读写分离 参考博客https://blog.csdn.net/qq_31708899/article/details/121577253 实现原理#xff1a;翻看AbstractRoutingDataSource源码我们可以看到其中的targetDataSource可以维护一组目标数据源(采用map数据结构)#xff0c;并且做了路由key与目标…SpringBoot实现数据库读写分离 参考博客https://blog.csdn.net/qq_31708899/article/details/121577253 实现原理翻看AbstractRoutingDataSource源码我们可以看到其中的targetDataSource可以维护一组目标数据源(采用map数据结构)并且做了路由key与目标数据源之间的映射提供基于key查找数据源的方法。看到了这个我们就可以想到怎么实现数据源切换了 #### 一 maven依赖 ?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.4.12-SNAPSHOT/versionrelativePath/ !-- lookup parent from repository --/parentgroupIdcom.ReadAndWriteSeparate/groupIdartifactIddemo/artifactIdversion0.0.1-SNAPSHOT/versionnameReadAndWriteSeparate/namedescriptionDemo project for Spring Boot/descriptionpropertiesjava.version1.8/java.version/propertiesdependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jdbc/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion2.2.0/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion5.1.26/versionscoperuntime/scope/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependencydependencygroupIdorg.aspectj/groupIdartifactIdaspectjweaver/artifactIdversion1.9.5/version/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactIdconfigurationexcludesexcludegroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/exclude/excludes/configuration/plugin/pluginsresourcesresourcedirectorysrc/main/java/directoryincludesinclude**/*.xml/include/includes/resource/resources/build/project
二 数据源配置
yaml配置 这里我只用一个账号模拟生产环境下必须要分开只读账号和可读可写账号因为主从复制中主机可不会同步从机的数据哟
spring:datasource:master:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverslave1:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driverslave2:jdbc-url: jdbc:mysql://localhost:3306/demo?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/Shanghaiusername: rootpassword: rootdriver-class-name: com.mysql.jdbc.Driver
数据源配置
package com.readandwriteseparate.demo.Config;import com.readandwriteseparate.demo.Enum.DbEnum;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;
import java.beans.ConstructorProperties;
import java.util.HashMap;
import java.util.Map;/*** author OriginalPerson* date 2021/11/25 20:25* Email 2568500308qq.com*/
Configuration
public class DataSourceConfig {//主数据源用于写数据,特殊情况下也可用于读BeanConfigurationProperties(spring.datasource.master)public DataSource masterDataSource(){return DataSourceBuilder.create().build();}BeanConfigurationProperties(spring.datasource.slave1)public DataSource slave1DataSource(){return DataSourceBuilder.create().build();}BeanConfigurationProperties(spring.datasource.slave2)public DataSource slave2DataSource(){return DataSourceBuilder.create().build();}Beanpublic DataSource routingDataSource(Qualifier(masterDataSource) DataSource masterDataSource,Qualifier(slave1DataSource) DataSource slave1DataSource,Qualifier(slave2DataSource) DataSource slave2DataSource){MapObject,Object targetDataSourcenew HashMap();targetDataSource.put(DbEnum.MASTER,masterDataSource);targetDataSource.put(DbEnum.SLAVE1,slave1DataSource);targetDataSource.put(DbEnum.SLAVE2,slave2DataSource);RoutingDataSource routingDataSourcenew RoutingDataSource();routingDataSource.setDefaultTargetDataSource(masterDataSource);routingDataSource.setTargetDataSources(targetDataSource);return routingDataSource;}} 这里我们配置了4个数据源其中前三个数据源都是为了生成第四个路由数据源产生的路由数据源的key我们使用枚举类型来标注,三个枚举类型分别代表数据库的类型。 package com.readandwriteseparate.demo.Enum;/*** author OriginalPerson* date 2021/11/25 20:45* Email: 2568500308qq.com*/
public enum DbEnum {MASTER,SLAVE1,SLAVE2;
}
三 数据源切换 这里我们使用ThreadLocal将路由key设置到每个线程的上下文中这里也进行一个简单的负载均衡轮询两个只读数据源,而访问哪个取决于counter的值每增加1切换一下数据源该值为juc并发包下的原子操作类保证其线程安全。 设置路由键获取当前数据源的key
package com.readandwriteseparate.demo.Config;import com.readandwriteseparate.demo.Enum.DbEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.util.concurrent.atomic.AtomicInteger;/*** author OriginalPerson* date 2021/11/25 20:49* Email: 2568500308qq.com*/
public class DBContextHolder {private static final ThreadLocalDbEnum contextHoldernew ThreadLocal();private static final AtomicInteger counternew AtomicInteger(-1);public static void set(DbEnum type){contextHolder.set(type);}public static DbEnum get(){return contextHolder.get();}public static void master(){set(DbEnum.MASTER);System.out.println(切换到master数据源);}public static void slave(){//轮询数据源进行读操作int indexcounter.getAndIncrement() % 2;if(counter.get()9999){counter.set(-1);}if(index0){set(DbEnum.SLAVE1);System.out.println(切换到slave1数据源);}else {set(DbEnum.SLAVE2);System.out.println(切换到slave2数据源);}}
}
确定当前数据源 这个比较重要其继承AbstractRoutingDataSource类重写了determineCurrentLookupKey方法该方法决定当前数据源的key对应于上文配置数据源的map集合中的key让该方法返回我们定义的ThreadLocal中存储的key即可实现数据源切换。 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;/*** author OriginalPerson* date 2021/11/25 20:47* Email: 2568500308qq.com*/
public class RoutingDataSource extends AbstractRoutingDataSource {NullableOverrideprotected Object determineCurrentLookupKey() {return DBContextHolder.get();}
}
mybatis配置三个数据源
package com.readandwriteseparate.demo.Config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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;/*** author OriginalPerson* date 2021/11/25 22:17* Email 2568500308qq.com*/
EnableTransactionManagement
Configuration
public class MybatisConfig {Resource(name routingDataSource)private DataSource routingDataSource;Beanpublic SqlSessionFactory sessionFactory() throws Exception {SqlSessionFactoryBean sqlSessionFactoryBeannew SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(routingDataSource);return sqlSessionFactoryBean.getObject();}Beanpublic PlatformTransactionManager platformTransactionManager(){return new DataSourceTransactionManager(routingDataSource);}
}
四 特殊处理master主库读数据操作、 在某些场景下我们需要实时读取到更新过的值例如某个业务逻辑在插入一条数据后需要立即查询据因为读写分离我们用的是主从复制架构它是异步操作串行复制数据所以必然存在主从延迟问题对于刚插入的数据如果要马上取出读从库是没有数据的因此需要直接读主库这里我们通过一个Master注解来实现被该注解标注的方法将直接在主库数据 注解
package com.readandwriteseparate.demo.annotation;/*** author OriginalPerson* date 2021/11/26 13:28* Email 2568500308qq.com*/public interface Master {
}
APO切面处理
package com.readandwriteseparate.demo.Aspect;import com.readandwriteseparate.demo.Config.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;/*** author OriginalPerson* date 2021/11/26 13:23* Email 2568500308qq.com*/Aspect
Component
public class DataSourceAop {// 非master注解且有关读的方法操作从库Pointcut(!annotation(com.readandwriteseparate.demo.annotation.Master) (execution(* com.readandwriteseparate.demo.Service..*.select*(..))) || execution(* com.readandwriteseparate.demo.Service..*.get*(..))))public void readPointcut(){}// 有master注解或者有关处理数据的操作主库 Pointcut(annotation(com.readandwriteseparate.demo.annotation.Master) || execution(* com.readandwriteseparate.demo.Service..*.insert*(..)) || execution(* com.readandwriteseparate.demo.Service..*.add*(..)) || execution(* com.readandwriteseparate.demo.Service..*.update*(..)) || execution(* com.readandwriteseparate.demo.Service..*.edit*(..)) || execution(* com.readandwriteseparate.demo.Service..*.delete*(..)) || execution(* com.readandwriteseparate.demo.Service..*.remove*(..)))public void writePointcut() {}Before(readPointcut())public void read(){DBContextHolder.slave();}Before(writePointcut())public void write(){DBContextHolder.master();}
}
五 读写分离案例使用
实体类
package com.readandwriteseparate.demo.Domain;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;/*** author OriginalPerson* date 2021/11/26 23:15* Email 2568500308qq.com*/
Data
AllArgsConstructor
NoArgsConstructor
public class User implements Serializable {private Integer id;private String name;private String sex;
}
Dao层
package com.readandwriteseparate.demo.Dao;import com.readandwriteseparate.demo.Domain.User;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** author OriginalPerson* date 2021/11/26 23:16* Email 2568500308qq.com*/
public interface UserMapper {public ListUser selectAllUser();public Integer insertUser(Param(user) User user);public User selectOneById(Param(id) Integer id);
}
xml
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.readandwriteseparate.demo.Dao.UserMapperresultMap iduser typecom.readandwriteseparate.demo.Domain.Userid propertyid columnid/idresult propertyname columnname/resultresult propertysex columnsex/result/resultMapselect resultMapuser idselectAllUser resultTypecom.readandwriteseparate.demo.Domain.Userselect * from user/selectinsert idinsertUser parameterTypecom.readandwriteseparate.demo.Domain.Userinsert into user(name,sex) values(#{user.name},#{user.sex})/insertselect idselectOneById parameterTypejava.lang.Integer resultMapuserselect * from user where id#{id}/select
/mapper
service
package com.readandwriteseparate.demo.Service;import com.readandwriteseparate.demo.Dao.UserMapper;
import com.readandwriteseparate.demo.Domain.User;
import com.readandwriteseparate.demo.annotation.Master;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;/*** author OriginalPerson* date 2021/11/27 0:07* Email 2568500308qq.com*/Service
public class UserService {Autowiredprivate UserMapper userMapper;public ListUser getAllUser(){return userMapper.selectAllUser();}public Integer addUser(User user){return userMapper.insertUser(user);}/** 特殊情况下需要从主库查询时* 例如某些业务更新数据后需要马上查询因为主从复制有延迟所以需要从主库查询* 添加Master注解即可从主库查询** 该注解实现比较简单在aop切入表达式中进行判断即可* */Masterpublic User selectOneById(Integer id){return userMapper.selectOneById(id);}
}
单元测试代码
package com.readandwriteseparate.demo;import com.readandwriteseparate.demo.Dao.UserMapper;
import com.readandwriteseparate.demo.Domain.User;
import com.readandwriteseparate.demo.Service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;SpringBootTest
class ReadAndWriteSeparateApplicationTests {Autowiredprivate UserService userService;Testvoid contextLoads() throws InterruptedException {User usernew User();user.setName(赵六);user.setSex(男);System.out.println(插入一条数据);userService.addUser(user);for (int i 0; i 4 ; i) {System.out.println(开始查询数据);System.out.println(第(i1)次查询);userService.getAllUser();System.out.println(-------------------------分割线------------------------);}System.out.println(强制查询主库);userService.selectOneById(1);}}
查询结果
