手机如何制作网站,wordpress分类链接,商务网站规划与设计,接私活做网站要不要签合同读写分离的作用是为了缓解写库#xff0c;也就是主库的压力#xff0c;但一定要基于数据一致性的原则#xff0c;就是保证主从库之间的数据一定要一致。如果一个方法涉及到写的逻辑#xff0c;那么该方法里所有的数据库操作都要走主库。
一、环境部署
数据库#xff1a;…读写分离的作用是为了缓解写库也就是主库的压力但一定要基于数据一致性的原则就是保证主从库之间的数据一定要一致。如果一个方法涉及到写的逻辑那么该方法里所有的数据库操作都要走主库。
一、环境部署
数据库MySql2个一主一从
DROP TABLE IF EXISTS user;
CREATE TABLE user (user_id bigint(20) NOT NULL COMMENT 用户id,user_name varchar(255) DEFAULT COMMENT 用户名称,user_phone varchar(50) DEFAULT COMMENT 用户手机,address varchar(255) DEFAULT COMMENT 住址,weight int(3) NOT NULL DEFAULT 1 COMMENT 权重大者优先,created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间,PRIMARY KEY (user_id)
) ENGINEInnoDB DEFAULT CHARSETutf8;INSERT INTO user VALUES (1196978513958141952, 测试1, 18826334748, 广州市海珠区, 1, 2019-11-20 10:28:51, 2019-11-22 14:28:26);
INSERT INTO user VALUES (1196978513958141953, 测试2, 18826274230, 广州市天河区, 2, 2019-11-20 10:29:37, 2019-11-22 14:28:14);
INSERT INTO user VALUES (1196978513958141954, 测试3, 18826273900, 广州市天河区, 1, 2019-11-20 10:30:19, 2019-11-22 14:28:30);二、依赖
dependenciesdependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.1.10/version/dependencydependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion1.3.2/version/dependencydependencygroupIdtk.mybatis/groupIdartifactIdmapper-spring-boot-starter/artifactIdversion2.1.5/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.16/version/dependency!-- 动态数据源 所需依赖 ### start--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jdbc/artifactIdscopeprovided/scope/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-aop/artifactIdscopeprovided/scope/dependency!-- 动态数据源 所需依赖 ### end--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdcom.alibaba/groupIdartifactIdfastjson/artifactIdversion1.2.4/version/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependency
/dependencies三、application.yml配置主从数据源
server:port: 8001
spring:jackson:date-format: yyyy-MM-dd HH:mm:sstime-zone: GMT8datasource:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Drivermaster:url: jdbc:mysql://127.0.0.1:3307/user?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttruefailOverReadOnlyfalseuseSSLfalsezeroDateTimeBehaviorconvertToNullallowMultiQueriestrueusername: rootpassword:slave:url: jdbc:mysql://127.0.0.1:3308/user?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttruefailOverReadOnlyfalseuseSSLfalsezeroDateTimeBehaviorconvertToNullallowMultiQueriestrueusername: rootpassword:四、Config配置
Getter
public enum DynamicDataSourceEnum {MASTER(master),SLAVE(slave);private String dataSourceName;DynamicDataSourceEnum(String dataSourceName) {this.dataSourceName dataSourceName;}
}Configuration
MapperScan(basePackages com.xjt.proxy.mapper, sqlSessionTemplateRef sqlTemplate)
public class DataSourceConfig{//主库BeanConfigurationProperties(prefix spring.datasource.master)public DataSource masterDb(){return DruidDataSourceBuilder.create().build();}//从库BeanConditionalOnProperty(prefix spring.datasource, name slave, matchIfMissing true)ConfigurationProperties(prefix spring.datasource.slave)public DataSource slaveDb() {return DruidDataSourceBuilder.create().build();}//主从动态配置Beanpublic DynamicDataSource dynamicDb(Qualifier(masterDb) DataSource masterDataSource,Autowired(required false) Qualifier(slaveDb) DataSource slaveDataSource){DynamicDataSource dynamicDataSource new DynamicDataSource();MapObject,Object targetDataSources new HashMap();targetDataSources.put(DynamicDataSourceEnum.MASTER.getDataSourceName(), masterDataSource);if(slaveDataSource ! null){targetDataSources.put(DynamicDataSourceEnum.SLAVE.getDataSourceName(), slaveDataSource);}dynamicDataSource.setTargetDataSources(targetDataSources);dynamicDataSource.setDefaultTargetDataSource(masterDataSource);return dynamicDataSource;}Beanpublic SqlSessionFactory sessionFactory(){SqlSessionFactoryBean bean new SqlSessionFactoryBean();bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(classpath*:mapper/*Mapper.xml));bean.setDataSource(dynamicDataSource);return bean.getObject();}Beanpublic SqlSessionTemplate sqlTemplate(){return new SqlSessionTemplate(sqlSessionFactory);}Bean(name dataourceTx)public DataSourceTransactionManager dataSourceTx(){DataSourceTransactionManager dataSourceTransactionManager new DataSourceTransactionManager();dataSourceTransactionManager.setDataSource(dynamicDataSource);return dataSourceTransactionManager;}
}五、设置路由
为了方便查找对应的数据源我们可以用ThreadLocal保存数据源的信息到每个线程中方便我们需要时获取
pubic class DataSourceContextHolder{private static final ThreadLocalString DYNAMIC_DATASOURCE_CONTEXT new ThreadLocal();public static void set(String datasourceType) {DYNAMIC_DATASOURCE_CONTEXT.set(datasourceType);}public static String get() {return DYNAMIC_DATASOURCE_CONTEXT.get();}public static void clear() {DYNAMIC_DATASOURCE_CONTEXT.remove();}
}AbstractRoutingDataSource的作用是基于查找key路由到对应的数据源它内部维护了一组目标数据源并且做了路由key与目标数据源之间的映射提供基于key查找数据源的方法。
public class DynamicDataSource extends AbstractRoutingDataSource {Overrideprotected Object determineCurrentLookupKey() {return DataSourceContextHolder.get();}
}六、数据源的注解
方便切换数据源注解中包含数据源对应的枚举值默认是主库
Retention(RetentionPolicy.RUNTIME)
Target(ElementType.METHOD)
Documented
public interface DataSourceSelector {DynamicDataSourceEnum value() default DynamicDataSourceEnum.MASTER;boolean clear() default true;
}七、Aop切换数据源
定义一个aop类对有注解的方法做切换数据源的操作
Slf4j
Aspect
Order(value 1)
Component
public class DataSourceContextAop {Around(annotation(com.xjt.proxy.dynamicdatasource.DataSourceSelector))public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {boolean clear true;try {Method method this.getMethod(pjp);DataSourceSelector dataSourceImport method.getAnnotation(DataSourceSelector.class);//获取注解标注的方法clear dataSourceImport.clear();DataSourceContextHolder.set(dataSourceImport.value().getDataSourceName());log.info(数据源切换至{}, dataSourceImport.value().getDataSourceName());return pjp.proceed();} finally {if (clear) {DataSourceContextHolder.clear();}}}private Method getMethod(JoinPoint pjp) {MethodSignature signature (MethodSignature)pjp.getSignature();return signature.getMethod();}}八、测试
写好Service文件包含读取和更新两个方法
Service
public class UserService {Autowiredprivate UserMapper userMapper;DataSourceSelector(value DynamicDataSourceEnum.SLAVE)public ListUser listUser() {ListUser users userMapper.selectAll();return users;}DataSourceSelector(value DynamicDataSourceEnum.MASTER)public int update() {User user new User();user.setUserId(Long.parseLong(1196978513958141952));user.setUserName(修改后的名字2);return userMapper.updateByPrimaryKeySelective(user);}DataSourceSelector(value DynamicDataSourceEnum.SLAVE)public User find() {User user new User();user.setUserId(Long.parseLong(1196978513958141952));return userMapper.selectByPrimaryKey(user);}
}根据方法上的注解可以看出读的方法走从库更新的方法走主库更新的对象是userId为1196978513958141953 的数据
RunWith(SpringRunner.class)
SpringBootTest
class UserServiceTest {AutowiredUserService userService;Testvoid listUser() {ListUser users userService.listUser();for (User user : users) {System.out.println(user.getUserId());System.out.println(user.getUserName());System.out.println(user.getUserPhone());}}Testvoid update() {userService.update();User user userService.find();System.out.println(user.getUserName());}
}读取方法
更新方法 执行之后比对数据库就可以发现主从库都修改了数据说明我们的读写分离是成功的。当然更新方法可以指向从库这样一来就只会修改到从库的数据而不会涉及到主库。
