<small id='1loOuxm'></small> <noframes id='TbcL9rgOd'>

  • <tfoot id='svtO'></tfoot>

      <legend id='zewdQR73TK'><style id='rYmDn19bV'><dir id='U9jOFqX'><q id='2rN6gpd'></q></dir></style></legend>
      <i id='cMdx'><tr id='jiVFbG'><dt id='MHL6oblf'><q id='P1kqjS'><span id='27ndACTl'><b id='2weUp8CF'><form id='J31WhPfGR'><ins id='pGd9yWf'></ins><ul id='YGhIzxy8'></ul><sub id='VEygq'></sub></form><legend id='o87qTw'></legend><bdo id='EKox'><pre id='VoW1geDhLb'><center id='UWliqNsL'></center></pre></bdo></b><th id='5PEf'></th></span></q></dt></tr></i><div id='3ejRO'><tfoot id='LjlCc1'></tfoot><dl id='hntFi7KO'><fieldset id='lTDU'></fieldset></dl></div>

          <bdo id='8adFhp'></bdo><ul id='fE8t'></ul>

          1. <li id='tki0'></li>
            登陆

            SpringBoot,MyBatis,MySQL读写别离实战

            admin 2019-09-07 246人围观 ,发现0个评论

            1. 导言


            读写别离要做的工作便是关于一条SQL该挑选哪个数据库去履行,至于谁来做挑选数据库这件事儿,无非两个,要么中间件帮咱们做,要么程序自己做。因而,一般来讲,读写别离有两种完成办法。第一种是依托中间件(比方:MyCat),也便是说运用程序连接到中间件,中间件帮咱们做SQL别离;第二种是运用程序自己去做别离。这儿咱们挑选程序自己来做,主要是运用Spring供给的路由数据源,以及AOP

            可是,运用程序层面去做读写别离最大的缺点(不足之处)在于无法动态添加数据库节点,由于数据源装备都是写在装备中的,新增数据库意味着新加一个数据源,必定改装备,并重启运用。当然,优点便是相对简略。


            2. AbstractRoutingDataSource


            根据特定的查找key路由到特定的数据源。它内部保护了一组方针数据源,并且做了路由key与方针数据源之间的映射,供给根据key查找数据源的办法。


            3. 实践

            关于装备请参阅:https://www.cnblogs.com/cjsblog/p/9706370.html


            3.1. maven依靠



            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
            mybatSpringBoot,MyBatis,MySQL读写别离实战is-spring-boot-starter
            1.3.2


            org.apache.commons
            commons-lang3
            3.8


            mysql
            mysql-connector-java
            runtime


            org.spSpringBoot,MyBatis,MySQL读写别离实战ringframework.boot
            spring-boot-starter-test
            test





            org.springframework.boot
            spring-boot-maven-plugin






            3.2. 数据源装备

            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 = new HashMap<>();
            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个数据源,并且后续咱们只用这最终一个路由数据源。

            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手动指定一个清晰的数据源。


            3.3. 设置路由key / 查找数据源

            方针数据源便是那前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 staSpringBoot,MyBatis,MySQL读写别离实战tic 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 voSpringBoot,MyBatis,MySQL读写别离实战id 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;
            }
            }


            4. 测验

            package com.cjs.example;
            import com.cjs.example.entity.Member;
            import com.cjs.example.service.MemberService;
            import org.junit.Test;
            impoSpringBoot,MyBatis,MySQL读写别离实战rt 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.sSpringBoot,MyBatis,MySQL读写别离实战electAll();
            }
            }
            @Test
            public void testSave() {
            Member member = new Member();
            member.setName("wangwu");
            memberService.save(member);
            }
            @Test
            public void testReadFromMaster() {
            memberService.getToken("1234");
            }
            }

            检查控制台






            5. 工程结构







            6. 参阅


            https://www.jianshu.com/p/f2f4256a2310

            http://www.cnblogs.com/gl-developer/p/6170423.html

            https://www.cnblogs.com/huangjuncong/p/8576935.html

            https://blog.csdn.net/liu976180578/article/details/77684583

            小伙伴们有不明白的能够私信沟通哈

            私信我:“材料”,可免费收取更多学习材料

            请关注微信公众号
            微信二维码
            不容错过
            Powered By Z-BlogPHP