读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行,至于谁来做选择数据库这件事儿,无非两个,要么中间件帮我们做,要么程序自己做。
成都创新互联公司是一家专业提供曲松企业网站建设,专注与网站设计、成都做网站、成都h5网站建设、小程序制作等业务。10年已为曲松众多企业、政府机构等服务。创新互联专业网站设计公司优惠进行中。
因此,一般来讲,读写分离有两种实现方式。第一种是依靠中间件(比如:MyCat),也就是说应用程序连接到中间件,中间件帮我们做SQL分离;第二种是应用程序自己去做分离。这里我们选择程序自己来做,主要是利用Spring提供的路由数据源,以及AOP
然而,应用程序层面去做读写分离最大的弱点(不足之处)在于无法动态增加数据库节点,因为数据源配置都是写在配置中的,新增数据库意味着新加一个数据源,必然改配置,并重启应用。当然,好处就是相对简单。
基于特定的查找key路由到特定的数据源。它内部维护了一组目标数据源,并且做了路由key与目标数据源之间的映射,提供基于key查找数据源的方法。
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0 com.cjs.example cjs-datasource-demo 0.0.1-SNAPSHOT jar cjs-datasource-demo org.springframework.boot spring-boot-starter-parent 2.0.5.RELEASE UTF-8 UTF-8 1.8 org.springframework.boot spring-boot-starter-aop org.springframework.boot spring-boot-starter-jdbc org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2 org.apache.commons commons-lang3 3.8 mysql mysql-connector-java runtime org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-maven-plugin
application.yml
- spring:
- datasource:
- master:
- jdbc-url: jdbc:mysql://192.168.102.31:3306/test
- username: root
- password: 123456
- driver-class-name: com.mysql.jdbc.Driver
- slave1:
- jdbc-url: jdbc:mysql://192.168.102.56:3306/test
- username: pig # 只读账户
- password: 123456
- driver-class-name: com.mysql.jdbc.Driver
- slave2:
- jdbc-url: jdbc:mysql://192.168.102.36:3306/test
- username: pig # 只读账户
- password: 123456
- driver-class-name: com.mysql.jdbc.Driver
多数据源配置
- package com.cjs.example.config;
- import com.cjs.example.bean.MyRoutingDataSource;
- import com.cjs.example.enums.DBTypeEnum;
- 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.util.HashMap;
- import java.util.Map;
- /**
- * 关于数据源配置,参考SpringBoot官方文档第79章《Data Access》
- * 79. Data Access
- * 79.1 Configure a Custom DataSource
- * 79.2 Configure Two DataSources
- */
- @Configuration
- public class DataSourceConfig {
- @Bean
- @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();
- }
- @Bean
- public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
- @Qualifier("slave1DataSource") DataSource slave1DataSource,
- @Qualifier("slave2DataSource") DataSource slave2DataSource) {
- Map
- targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
- targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
- targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
- MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
- myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
- myRoutingDataSource.setTargetDataSources(targetDataSources);
- return myRoutingDataSource;
- }
- }
这里,我们配置了4个数据源,1个master,2两个slave,1个路由数据源。前3个数据源都是为了生成第4个数据源,而且后续我们只用这最后一个路由数据源。
Spring Boot 最新基础教程和示例源码:https://github.com/javastacks/spring-boot-best-practice
MyBatis配置
- package com.cjs.example.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.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
- @Configuration
- public class MyBatisConfig {
- @Resource(name = "myRoutingDataSource")
- private DataSource myRoutingDataSource;
- @Bean
- public SqlSessionFactory sqlSessionFactory() throws Exception {
- SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
- sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
- sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
- return sqlSessionFactoryBean.getObject();
- }
- @Bean
- public PlatformTransactionManager platformTransactionManager() {
- return new DataSourceTransactionManager(myRoutingDataSource);
- }
- }
由于Spring容器中现在有4个数据源,所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。另外,Spring 系列面试题和答案全部整理好了,微信搜索Java技术栈,在后台发送:面试,可以在线阅读。
目标数据源就是那前3个这个我们是知道的,但是使用的时候是如果查找数据源的呢?
首先,我们定义一个枚举来代表这三个数据源
- package com.cjs.example.enums;
- public enum DBTypeEnum {
- MASTER, SLAVE1, SLAVE2;
- }
接下来,通过ThreadLocal将数据源设置到每个线程上下文中
- package com.cjs.example.bean;
- import com.cjs.example.enums.DBTypeEnum;
- import java.util.concurrent.atomic.AtomicInteger;
- public class DBContextHolder {
- private static final ThreadLocal
contextHolder = new ThreadLocal<>(); - private static final AtomicInteger counter = new AtomicInteger(-1);
- public static void set(DBTypeEnum dbType) {
- contextHolder.set(dbType);
- }
- public static DBTypeEnum get() {
- return contextHolder.get();
- }
- public static void master() {
- set(DBTypeEnum.MASTER);
- System.out.println("切换到master");
- }
- public static void slave() {
- // 轮询
- int index = counter.getAndIncrement() % 2;
- if (counter.get() > 9999) {
- counter.set(-1);
- }
- if (index == 0) {
- set(DBTypeEnum.SLAVE1);
- System.out.println("切换到slave1");
- }else {
- set(DBTypeEnum.SLAVE2);
- System.out.println("切换到slave2");
- }
- }
- }
获取路由key
- package com.cjs.example.bean;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- import org.springframework.lang.Nullable;
- public class MyRoutingDataSource extends AbstractRoutingDataSource {
- @Nullable
- @Override
- protected Object determineCurrentLookupKey() {
- return DBContextHolder.get();
- }
- }
设置路由key
默认情况下,所有的查询都走从库,插入/修改/删除走主库。我们通过方法名来区分操作类型(CRUD)
- package com.cjs.example.aop;
- import com.cjs.example.bean.DBContextHolder;
- import org.apache.commons.lang3.StringUtils;
- import org.aspectj.lang.JoinPoint;
- 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 {
- @Pointcut("!@annotation(com.cjs.example.annotation.Master) " +
- "&& (execution(* com.cjs.example.service..*.select*(..)) " +
- "|| execution(* com.cjs.example.service..*.get*(..)))")
- public void readPointcut() {
- }
- @Pointcut("@annotation(com.cjs.example.annotation.Master) " +
- "|| execution(* com.cjs.example.service..*.insert*(..)) " +
- "|| execution(* com.cjs.example.service..*.add*(..)) " +
- "|| execution(* com.cjs.example.service..*.update*(..)) " +
- "|| execution(* com.cjs.example.service..*.edit*(..)) " +
- "|| execution(* com.cjs.example.service..*.delete*(..)) " +
- "|| execution(* com.cjs.example.service..*.remove*(..))")
- public void writePointcut() {
- }
- @Before("readPointcut()")
- public void read() {
- DBContextHolder.slave();
- }
- @Before("writePointcut()")
- public void write() {
- DBContextHolder.master();
- }
- /**
- * 另一种写法:if...else... 判断哪些需要读从数据库,其余的走主数据库
- */
- // @Before("execution(* com.cjs.example.service.impl.*.*(..))")
- // public void before(JoinPoint jp) {
- // String methodName = jp.getSignature().getName();
- //
- // if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
- // DBContextHolder.slave();
- // }else {
- // DBContextHolder.master();
- // }
- // }
- }
有一般情况就有特殊情况,特殊情况是某些情况下我们需要强制读主库,针对这种情况,我们定义一个主键,用该注解标注的就读主库
- package com.cjs.example.annotation;
- public @interface Master {
- }
例如,假设我们有一张表member
- package com.cjs.example.service.impl;
- import com.cjs.example.annotation.Master;
- import com.cjs.example.entity.Member;
- import com.cjs.example.entity.MemberExample;
- import com.cjs.example.mapper.MemberMapper;
- import com.cjs.example.service.MemberService;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- import org.springframework.transaction.annotation.Transactional;
- import java.util.List;
- @Service
- public class MemberServiceImpl implements MemberService {
- @Autowired
- private MemberMapper memberMapper;
- @Transactional
- @Override
- public int insert(Member member) {
- return memberMapper.insert(member);
- }
- @Master
- @Override
- public int save(Member member) {
- return memberMapper.insert(member);
- }
- @Override
- public List
selectAll() { - return memberMapper.selectByExample(new MemberExample());
- }
- @Master
- @Override
- public String getToken(String appId) {
- // 有些读操作必须读主数据库
- // 比如,获取微信access_token,因为高峰时期主从同步可能延迟
- // 这种情况下就必须强制从主数据读
- return null;
- }
- }
- package com.cjs.example;
- import com.cjs.example.entity.Member;
- import com.cjs.example.service.MemberService;
- import org.junit.Test;
- import org.junit.runner.RunWith;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.boot.test.context.SpringBootTest;
- import org.springframework.test.context.junit4.SpringRunner;
- @RunWith(SpringRunner.class)
- @SpringBootTest
- public class CjsDatasourceDemoApplicationTests {
- @Autowired
- private MemberService memberService;
- @Test
- public void testWrite() {
- Member member = new Member();
- member.setName("zhangsan");
- memberService.insert(member);
- }
- @Test
- public void testRead() {
- for (int i = 0; i < 4; i++) {
- memberService.selectAll();
- }
- }
- @Test
- public void testSave() {
- Member member = new Member();
- member.setName("wangwu");
- memberService.save(member);
- }
- @Test
- public void testReadFromMaster() {
- memberService.getToken("1234");
- }
- }
查看控制台
分享名称:SpringBoot+MyBatis+MySQL实现读写分离!
标题URL:http://www.mswzjz.cn/qtweb/news1/85701.html
攀枝花网站建设、攀枝花网站运维推广公司-贝锐智能,是专注品牌与效果的网络营销公司;服务项目有等
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 贝锐智能