SpringBoot2.0.3+Mybatis+Mysql+druid实现读写分离+事务+切换数据源失败

Bertha 。 2022-05-12 15:54 161阅读 0赞

mysql支持一主多从，即在写库的数据库发生变动时，会同步到所有从库，只是同步过程中，会有一定的延迟（除非业务中出现，立即写立即读，否则稍微的延迟是可以接收的）。

mysql的主从复制的配置参考：[https://blog.csdn.net/ydyang1126/article/details/70174334][https_blog.csdn.net_ydyang1126_article_details_70174334]

当数据库有主从之分了，那应用代码也应该读写分离了。这时候的事务就不像单个数据库那么简单了，为此整理出解决事务问题的方案：使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务。此方案的缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

下面对这一方案做详细介绍。

1、POM.xml

<parent>
    	    <groupId>org.springframework.boot</groupId>
    	    <artifactId>spring-boot-starter-parent</artifactId>
    	    <version>2.0.3.RELEASE</version>
    	    <relativePath /> 
    	</parent>
    	<properties>
    	    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    	    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    	    <java.version>1.8</java.version>
    	</properties>
    	
    	<dependencies>
    	    
    	    
    	    <dependency>
    	        <groupId>org.springframework.boot</groupId>
    	        <artifactId>spring-boot-starter-web</artifactId>
    	        <exclusions>
    	            <exclusion>
    	                <groupId>org.springframework.boot</groupId>
    	                <artifactId>spring-boot-starter-logging</artifactId>
    	            </exclusion>
    	        </exclusions>
    	    </dependency>
    	    
    	    
    	    <dependency>
    	        <groupId>org.springframework.boot</groupId>
    	        <artifactId>spring-boot-starter-log4j2</artifactId>
    	    </dependency>
    	    
    	    
    	    
    	    <dependency>
    	        <groupId>com.fasterxml.jackson.dataformat</groupId>
    	        <artifactId>jackson-dataformat-yaml</artifactId>
    	    </dependency>
    	    
    	    
    	    
    		<dependency>
    		    <groupId>org.springframework.boot</groupId>
    		    <artifactId>spring-boot-devtools</artifactId>
    		    <optional>true</optional>
    		    <scope>true</scope>
    		</dependency>
    		
            
            
            <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
            
            
            
    		<dependency>
    		    <groupId>org.mybatis.spring.boot</groupId>
    		    <artifactId>mybatis-spring-boot-starter</artifactId>
    		    <version>1.3.2</version>
    		</dependency>
    		
    		
    		
    		<dependency>
    		    <groupId>mysql</groupId>
    		    <artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		
    		
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		
    		
    		<dependency>
    		    <groupId>junit</groupId>
    		    <artifactId>junit</artifactId>
    		    <scope>test</scope>
    		</dependency>
    		
    		
    		<dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>druid-spring-boot-starter</artifactId>
               <version>1.1.10</version>
            </dependency>
            
    
    		
            
            <dependency>
    		    <groupId>commons-beanutils</groupId>
    		    <artifactId>commons-beanutils</artifactId>
    		    <version>1.9.3</version>
    		</dependency>
    		<dependency>
    		    <groupId>commons-collections</groupId>
    		    <artifactId>commons-collections</artifactId>
    		    <version>3.2.2</version>
    		</dependency>
    		<dependency>
    		    <groupId>commons-lang</groupId>
    		    <artifactId>commons-lang</artifactId>
    		    <version>2.6</version>
    		</dependency>
    		<dependency>
    		    <groupId>commons-logging</groupId>
    		    <artifactId>commons-logging</artifactId>
    		    <version>1.2</version>
    		</dependency>
    		<dependency>
    		    <groupId>net.sf.ezmorph</groupId>
    		    <artifactId>ezmorph</artifactId>
    		    <version>1.0.6</version>
    		</dependency>
    		<dependency>
    		    <groupId>net.sf.json-lib</groupId>
    		    <artifactId>json-lib</artifactId>
    		    <version>2.4</version>
    		    <classifier>jdk15</classifier>
    		</dependency>
            
            
        </dependencies>
    
    	<build>
    	    <plugins>
    		    <plugin>
    	            <groupId>org.springframework.boot</groupId>
    	            <artifactId>spring-boot-maven-plugin</artifactId>
    	            <configuration>
    	                
    	                <fork>true</fork>
    	            </configuration>
    	        </plugin>
    
    	    </plugins>
    	</build>

2、 application.yml和application-dev.yml

本例子的数据库，都是在本地的mysql中建立2个库，test,test\_01，例子是为了测试代码的读写分离。下面是application.yml：

spring: 
      profiles:
        active: dev
      thymeleaf:
        cache: true
        prefix: classpath:/templates/
        suffix: .html
        mode: HTML5
        encoding: UTF-8
        servlet:
          content-type: text/html
    
    mybatis:
      config-location: classpath:mybatis/mybatis-config.xml
      mapper-locations: classpath:mybatis/mapper/*.xml

下面是application-dev.yml：

server:
      port: 8080
    
    spring:
      aop:
          proxy-target-class: true
      datasource:
        #readSize为从库数量
        readSize: 1
        ###################以下为druid增加的配置###########################
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          master:
            url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: root
            initialSize: 5
            minIdle: 5
            maxActive: 20
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            min-evictableIdleTimeMillis: 300000
            validationQuery: SELECT 1 FROM DUAL
            testWhileIdle: true
            testOnBorrow: true
            testOnReturn: false
            poolPreparedStatements: true
            useGlobalDataSourceStat: true
            maxPoolPreparedStatementPerConnectionSize: 20
            filters: stat,wall
            WebStatFilter:
              exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
              enabled: true
              urlPattern: '/*'
            StatViewServlet:
              enabled: true
              urlPattern: '/druid/*'
              loginUsername: druid
              loginPassword: druid
          slave:
            url: jdbc:mysql://127.0.0.1:3306/test_01?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: root
            initialSize: 5
            minIdle: 5
            maxActive: 20
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            min-evictableIdleTimeMillis: 300000
            validationQuery: SELECT 1 FROM DUAL
            testWhileIdle: true
            testOnBorrow: true
            testOnReturn: false
            poolPreparedStatements: true
            useGlobalDataSourceStat: true
            maxPoolPreparedStatementPerConnectionSize: 20
            filters: stat,wall
            WebStatFilter:
              exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
              enabled: true
              urlPattern: '/*'
            StatViewServlet:
              enabled: true
              urlPattern: '/druid/*'
              loginUsername: druid
              loginPassword: druid
        ###############以上为配置druid添加的配置########################################

3、mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
            
            <setting name="useGeneratedKeys" value="true"/>
            
            <setting name="useColumnLabel" value="true"/>
            
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
    </configuration>

4、DataSourceConfiguration.java（读取配置多个数据源）

package com.wocloud.arch.ssm.mybatis;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Value;
    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 org.springframework.context.annotation.Primary;
    
    @Configuration
    public class DataSourceConfiguration {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Bean(name = "writeDataSource", destroyMethod = "close", initMethod = "init")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.druid.master")
        public DataSource masterDataSource() {
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    
        @Bean(name = "readDataSource", destroyMethod = "close", initMethod = "init")
        @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
        public DataSource slaveDataSource() {
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    
       @Bean("readDataSources")
        public List<DataSource> readDataSources() throws SQLException {
            List<DataSource> dataSources = new ArrayList<>();
            //dataSources.add(masterDataSource());
            dataSources.add(slaveDataSource());
            return dataSources;
        }
    }

5、MybatisConfiguration.java（数据库的sqlSessionFactorys、roundRobinDataSouceProxy、sqlSessionTemplate、annotationDrivenTransactionManager的设置。重点是roundRobinDataSouceProxy()方法，它把所有的数据库源交给MyAbstractRoutingDataSource类，这个类见第6项，并由它的determineCurrentLookupKey()进行决定数据源的选择，其中读库进行了简单的以轮询的方式的负载均衡）

package com.wocloud.arch.ssm.mybatis;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Import;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.core.io.support.ResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.transaction.PlatformTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import com.wocloud.arch.ssm.utils.SpringContextUtil;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    @Configuration
    @AutoConfigureAfter(DataSourceConfiguration.class)
    @ConditionalOnClass({EnableTransactionManagement.class})
    @Import({DataSourceConfiguration.class})
    @MapperScan(basePackages = {"com.wocloud.arch.ssm.mapper"})
    public class MybatisConfiguration {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
        @Value("${spring.datasource.readSize}")
        private String dataSourceSize;
    
        @Bean
        @ConditionalOnMissingBean
        public SqlSessionFactory sqlSessionFactory(ApplicationContext ac) throws Exception {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy(ac));
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mybatis/mapper/*.xml"));
            sqlSessionFactoryBean.setTypeAliasesPackage("com.wocloud.arch.ssm.model");
            sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
            return sqlSessionFactoryBean.getObject();
        }
    
        /**
         * 有多少个数据源就要配置多少个bean
         */
        @Bean
        public AbstractRoutingDataSource roundRobinDataSouceProxy(ApplicationContext ac) {
            int size = Integer.parseInt(dataSourceSize);
            MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
            Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
    
            DataSource writeDataSource = (DataSource) ac.getBean("writeDataSource");
            List<DataSource> readDataSources = (List<DataSource>) ac.getBean("readDataSources");
            for (int i = 0; i < size; i++) {
                targetDataSources.put(i, readDataSources.get(i));
            }
            proxy.setDefaultTargetDataSource(writeDataSource);
            proxy.setTargetDataSources(targetDataSources);
            return proxy;
        }
        
        @Bean
        public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        	return new SqlSessionTemplate(sqlSessionFactory);
        }
        
        //事务管理
        @Bean
        public PlatformTransactionManager annotationDrivenTransactionManager() {
            return new DataSourceTransactionManager((DataSource)SpringContextUtil.getBean("roundRobinDataSouceProxy"));
        }
        
    }

6、MyAbstractRoutingDataSource.java

package com.wocloud.arch.ssm.mybatis;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import java.util.concurrent.atomic.AtomicInteger;
    
    public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
        private final int dataSourceNumber;
        private AtomicInteger count = new AtomicInteger(0);
    
        public MyAbstractRoutingDataSource(int dataSourceNumber) {
            this.dataSourceNumber = dataSourceNumber;
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            String typeKey = DataSourceContextHolder.getJdbcType();
            if (DataSourceType.write.getType().equals(typeKey) || typeKey == null) {
                return DataSourceType.write.getType();
            }
            // 读 简单负载（因为从库数量为1，实际上目前没有负载效果）
            int number = count.getAndAdd(1);
            int lookupKey = number % dataSourceNumber;
            return new Integer(lookupKey);
        }
    }

7、DataSourceType.java（）

package com.wocloud.arch.ssm.mybatis;
    
    public enum DataSourceType {
        read("read", "从库"),
        write("write", "主库");
        private String type;
        private String name;
    
        DataSourceType(String type, String name) {
            this.type = type;
            this.name = name;
        }
    
        public String getType() {
            return type;
        }
    
        public void setType(String type) {
            this.type = type;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    }

8、DataSourceContextHolder.java

package com.wocloud.arch.ssm.mybatis;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    public class DataSourceContextHolder {
        private static final ThreadLocal<String> local = new ThreadLocal<String>();
    
        public static ThreadLocal<String> getLocal() {
            return local;
        }
    
        private final static Logger logger = LoggerFactory.getLogger(DataSourceContextHolder.class);
    
        public static void read() {
            local.set(DataSourceType.read.getType());
            logger.info("切换到读库...");
        }
    
        public static void write() {
            local.set(DataSourceType.write.getType());
            logger.info("切换到写库...");
        }
    
        //清除local中的值，用于数据源切换失败的问题
        public static void clear() {
            local.remove();
        }
    
        public static String getJdbcType() {
            return local.get();
        }
    }

9、写库、读库的注解

package com.wocloud.arch.ssm.mybatis;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ReadDataSource {
        String description() default "";
    }

package com.wocloud.arch.ssm.mybatis;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface WriteDataSource {
        String description() default "";
    }

10、 DataSourceAop.java（事务的决定者）

package com.wocloud.arch.ssm.mybatis;
    
    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.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.context.annotation.EnableAspectJAutoProxy;
    import org.springframework.core.PriorityOrdered;
    import org.springframework.stereotype.Component;
    
    /*
     * 在service层决定数据源
     * 
     * 必须在事务AOP之前执行，所以实现Ordered,order的值越小，越先执行
     * 如果一旦开始切换到写库，则之后的读都会走写库
     */
    
    @Aspect
    @EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)
    @Component
    public class DataSourceAop implements PriorityOrdered {
        private final static Logger logger = LoggerFactory.getLogger(DataSourceAop.class);
    
        @Pointcut("@annotation(com.wocloud.arch.ssm.mybatis.WriteDataSource)")
        public void writeMethod(){}
    
        @Pointcut("@annotation(com.wocloud.arch.ssm.mybatis.ReadDataSource)")
        public void readMethod(){}
    
        @Before("writeMethod() and execution(* com.wocloud.arch.ssm.service.impl..*.*(..)) ")
        public void beforeWrite(JoinPoint point) {
            //设置数据库为写数据
            DataSourceContextHolder.write();
            //调试代码，可注释
            String className = point.getTarget().getClass().getName();
            String methodName = point.getSignature().getName();
            logger.info("dataSource切换到：Write 开始执行:" + className + "." + methodName + "()方法...");
        }
    
        @Before("readMethod() and execution(* com.wocloud.arch.ssm.service.impl..*.*(..)) ")
        public void beforeRead(JoinPoint point) throws ClassNotFoundException {
            //设置数据库为读数据
            DataSourceContextHolder.read();
            //调试代码，可注释
            String className = point.getTarget().getClass().getName();//根据切点获取当前调用的类名
            String methodName = point.getSignature().getName();//根据切点获取当前调用的类方法
            logger.info("dataSource切换到：Read 开始执行:" + className + "." + methodName + "()方法...");
    //        Object[] args = point.getArgs();//根据切点获取当前类方法的参数
    //        Class reflexClassName = Class.forName(className);//根据反射获取当前调用类的实例
    //        Method[] methods = reflexClassName.getMethods();//获取该实例的所有方法
    //        for (Method method : methods) {
    //            if (method.getName().equals(methodName)) {
    //                String desrciption = method.getAnnotation(ReadDataSource.class).description();//获取该实例方法上注解里面的描述信息
    //                System.out.println("desrciption:" + desrciption);
    //            }
    //        }
        }
    
        @After("readMethod() || writeMethod()")
        public void after() {
            DataSourceContextHolder.clear();
        }
    
    	@Override
        public int getOrder() {
    	 /**
    	  * 值越小，越优先执行
    	  * 要优于事务的执行
    	  * 在启动类中加上了@EnableTransactionManagement(order = 10) 
    	  */
    	 return 1;
       }
    }

11、CdkeyMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.wocloud.arch.ssm.mapper.CdkeyMapper">
      <resultMap id="BaseResultMap" type="com.wocloud.arch.ssm.model.Cdkey">
        
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="cdkey" jdbcType="VARCHAR" property="cdkey" />
        <result column="order_code" jdbcType="VARCHAR" property="orderCode" />
        <result column="isusage" jdbcType="VARCHAR" property="isusage" />
        <result column="issend" jdbcType="SMALLINT" property="issend" />
        <result column="first_time" jdbcType="TIMESTAMP" property="firstTime" />
        <result column="last_time" jdbcType="TIMESTAMP" property="lastTime" />
        <result column="order_code_wo" jdbcType="VARCHAR" property="orderCodeWo" />
        <result column="flag1" jdbcType="VARCHAR" property="flag1" />
        <result column="flag2" jdbcType="VARCHAR" property="flag2" />
        <result column="flag3" jdbcType="INTEGER" property="flag3" />
        <result column="flag4" jdbcType="INTEGER" property="flag4" />
      </resultMap>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        
        delete from cdkey
        where id = #{id,jdbcType=BIGINT}
      </delete>
      <insert id="insert" parameterType="com.wocloud.arch.ssm.model.Cdkey">
        
        <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Long">
          SELECT LAST_INSERT_ID()
        </selectKey>
        insert into cdkey (id, cdkey, order_code, 
          isusage, issend, first_time, 
          last_time, order_code_wo, flag1, 
          flag2, flag3, flag4
          )
        values (#{id,jdbcType=BIGINT}, #{cdkey,jdbcType=VARCHAR}, #{orderCode,jdbcType=VARCHAR}, 
          #{isusage,jdbcType=VARCHAR}, #{issend,jdbcType=SMALLINT}, #{firstTime,jdbcType=TIMESTAMP}, 
          #{lastTime,jdbcType=TIMESTAMP}, #{orderCodeWo,jdbcType=VARCHAR}, #{flag1,jdbcType=VARCHAR}, 
          #{flag2,jdbcType=VARCHAR}, #{flag3,jdbcType=INTEGER}, #{flag4,jdbcType=INTEGER}
          )
      </insert>
      <update id="updateByPrimaryKey" parameterType="com.wocloud.arch.ssm.model.Cdkey">
        
        update cdkey
        set cdkey = #{cdkey,jdbcType=VARCHAR},
          order_code = #{orderCode,jdbcType=VARCHAR},
          isusage = #{isusage,jdbcType=VARCHAR},
          issend = #{issend,jdbcType=SMALLINT},
          first_time = #{firstTime,jdbcType=TIMESTAMP},
          last_time = #{lastTime,jdbcType=TIMESTAMP},
          order_code_wo = #{orderCodeWo,jdbcType=VARCHAR},
          flag1 = #{flag1,jdbcType=VARCHAR},
          flag2 = #{flag2,jdbcType=VARCHAR},
          flag3 = #{flag3,jdbcType=INTEGER},
          flag4 = #{flag4,jdbcType=INTEGER}
        where id = #{id,jdbcType=BIGINT}
      </update>
      <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        
        select id, cdkey, order_code, isusage, issend, first_time, last_time, order_code_wo, 
        flag1, flag2, flag3, flag4
        from cdkey
        where id = #{id,jdbcType=BIGINT}
      </select>
      <select id="selectAll" resultMap="BaseResultMap">
        
        select id, cdkey, order_code, isusage, issend, first_time, last_time, order_code_wo, 
        flag1, flag2, flag3, flag4
        from cdkey
      </select>
      <select id="selectCdkeyByIsUsage" parameterType="java.lang.String" resultMap="BaseResultMap">
        select id, cdkey, order_code, isusage, issend, first_time, last_time, order_code_wo,
        flag1, flag2, flag3, flag4
        from cdkey where 1=1
        <if test="_parameter!=null">
        and isusage = #{isusage,jdbcType=VARCHAR} limit 1
        </if>
        FOR UPDATE
      </select>
      <select id="selectCdkeyByOrderCode" parameterType="java.lang.String" resultMap="BaseResultMap">
        select id, cdkey, order_code, isusage, issend, first_time, last_time, order_code_wo, 
        flag1, flag2, flag3, flag4
        from cdkey where 1=1
        <if test="_parameter!=null">
        and order_code = #{orderCode,jdbcType=VARCHAR}
        </if>
      </select>
      
    </mapper>

12、CdkeyMapper.java

package com.wocloud.arch.ssm.mapper;
    
    import com.wocloud.arch.ssm.model.Cdkey;
    import java.util.List;
    
    import org.apache.ibatis.annotations.Mapper;
    @Mapper
    public interface CdkeyMapper {
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table cdkey
         *
         * @mbggenerated
         */
        int deleteByPrimaryKey(Long id);
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table cdkey
         *
         * @mbggenerated
         */
        int insert(Cdkey record);
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table cdkey
         *
         * @mbggenerated
         */
        Cdkey selectByPrimaryKey(Long id);
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table cdkey
         *
         * @mbggenerated
         */
        List<Cdkey> selectAll();
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table cdkey
         *
         * @mbggenerated
         */
        int updateByPrimaryKey(Cdkey record);
        /*
         * 查询可用兑换码
         */
        Cdkey selectCdkeyByIsUsage(String isUsage);
        
        /*
         * 由订单号查询兑换码是否已发送
         */
        Cdkey selectCdkeyByOrderCode(String orderCode);
    }

13、CdkeyService.java

/**
     * 
     */
    package com.wocloud.arch.ssm.service;
    
    import java.util.List;
    
    import com.wocloud.arch.ssm.model.Cdkey;
    
    /**
     * @author mazhen
     *
     */
    public interface CdkeyService {
    	/*
         * 插入Cdkey信息
         * @param Cdkey
         * @return 
         */
    	public int insertCdkey(Cdkey cdkey);
    	
    	/*
         * 查询Cdkey信息
         * @param cdkeyId
         * @return  Cdkey
         */
    	public Cdkey queryByCdkeyId(Long cdkeyId);
    	
    	/*
         * 删除Cdkey信息
         * @param cdkeyId
         * @return  
         */
    	public int deleteByCdkeyId(Long cdkeyId);
    	
    	/*
         * 更新Cdkey信息
         * @param Cdkey
         * @return  
         */
    	public int updateCdkey(Cdkey cdkey);
    	
    	/*
         * 查询所有Cdkey信息
         * @param 
         * @return  
         */
    	public List<Cdkey> queryAll();
    	
    	/*
         * 根据短信状态查询可用Cdkey兑换码
         * @param messageStatus
         * @return  Cdkey
         */
    	public Cdkey queryCdkeyByIsUsage(String isUsage);
    	
    	/*
         * 根据订单号查询Cdkey兑换码是否已存在
         * @param orderCode
         * @return  Cdkey
         */
    	public Cdkey queryCdkeyByOrderCode(String orderCode);
        
        /*
    	 * 兑换码是否使用，cdkey表中更新数据
    	 */
    	public String updateCdkeyIsusage(JSONObject jsonObject);
    }

14、CdkeyServiceImpl.java(使用getService()的原因:

[https://www.oschina.net/translate/comparative\_analysis\_between\_spring\_aop\_and\_aspectj][https_www.oschina.net_translate_comparative_analysis_between_spring_aop_and_aspectj])

/**
     * 
     */
    package com.wocloud.arch.ssm.service.impl;
    
    import java.util.List;
    
    import com.wocloud.arch.ssm.mybatis.ReadDataSource;
    import com.wocloud.arch.ssm.mybatis.WriteDataSource;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    
    import com.wocloud.arch.ssm.mapper.CdkeyMapper;
    import com.wocloud.arch.ssm.model.Cdkey;
    import com.wocloud.arch.ssm.service.CdkeyService;
    import com.wocloud.arch.ssm.utils.SpringContextUtil;
    
    
    import net.sf.json.JSONObject;
    
    
    /**
     * @author mazhen
     * 注：AOP ，内部方法之间互相调用时，如果是this.xxx()这形式，不会触发AOP拦截，可能会
     * 导致无法决定数据库是走写库还是读库
     * 方法：
     * 为了触发AOP的拦截，调用内部方法时，需要特殊处理下，看方法getService()
     */
    @Service
    public class CdkeyServiceImpl implements CdkeyService {
    	
    	private final static Logger logger = LoggerFactory.getLogger(CdkeyParameterServiceImpl.class);
    	
    	@Autowired
    	private CdkeyMapper cdkeyMapper;
    
    	@Override
    	@WriteDataSource(description="WRITE")
    	public int insertCdkey(Cdkey cdkey) {
    		return cdkeyMapper.insert(cdkey);
    	}
    	
    	@Override
    	@ReadDataSource(description="READ")
    	public Cdkey queryByCdkeyId(Long cdkeyId) {
    		return cdkeyMapper.selectByPrimaryKey(cdkeyId);
    	}
    	
    	@Override
    	@WriteDataSource(description="WRITE")
    	public int deleteByCdkeyId(Long cdkeyId) {
    		return cdkeyMapper.deleteByPrimaryKey(cdkeyId);
    	}
    
    	@Override
    	@WriteDataSource(description="WRITE")
    	public int updateCdkey(Cdkey cdkey) {
    		return cdkeyMapper.updateByPrimaryKey(cdkey);
    	}
    
    	@Override
    	@ReadDataSource(description="READ")
    	public List<Cdkey> queryAll() {
    		return cdkeyMapper.selectAll();
    	}
    
    	@Override
    	@ReadDataSource(description="READ")
    	public Cdkey queryCdkeyByIsUsage(String isUsage) {
    		return cdkeyMapper.selectCdkeyByIsUsage(isUsage);
    	}
    
    	@Override
    	@ReadDataSource(description="READ")
    	public Cdkey queryCdkeyByOrderCode(String orderCode) {
    		return cdkeyMapper.selectCdkeyByOrderCode(orderCode);
    	}
    
    	@Override
    	@Transactional
    	public String updateCdkeyIsusage(JSONObject jsonObject) {
    		String cdkeyStr = null;
    		
    		Cdkey cdkey = getService().queryCdkeyByIsUsage("0");
    	    if (null == cdkey) {
    	    	logger.info("兑换码已用完");
    	    	return cdkeyStr;
    	    }
    		
    		cdkey.setOrderCode(jsonObject.getString("orderCode"));
    		cdkey.setIsusage(jsonObject.getString("telephone"));
    		cdkey.setLastTime(RandomTool.getMillisecond());
    	
    		
    		try {
    			if (getService().updateCdkey(cdkey) > 0) {
    				cdkeyStr = cdkey.getCdkey();
    			}
    		} catch (Exception e) {
    			logger.info("异常信息："+e);
    		}
    		return cdkeyStr;
    	}
    	
    	private CdkeyServiceImpl getService(){
    		return SpringContextUtil.getBean(this.getClass());
    	}
    
    
    }

15、SpringContextUtil.java

package com.wocloud.arch.ssm.utils;
    
    import org.springframework.beans.BeansException;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;
    import org.springframework.stereotype.Component;
    
    @Component
    public class SpringContextUtil implements ApplicationContextAware{
    
    	private static ApplicationContext applicationContext = null;
    	
    	@Override
    	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
    		if(SpringContextUtil.applicationContext == null){
    			SpringContextUtil.applicationContext = applicationContext;
    		}
    		
    	}
    
    	public static ApplicationContext getApplicationContext() {
    		return applicationContext;
    	}
    
    	
    	public static Object getBean(String name){
    		return getApplicationContext().getBean(name);
    	}
    	
    	public static <T> T getBean(Class<T> clazz){
    		return getApplicationContext().getBean(clazz);
    	}
    	
    }

16、写一个Controller进行验证：

/**
     * 
     */
    package com.wocloud.arch.ssm.controller;
    
    import java.io.IOException;
    import java.security.DigestException;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    
    
    import com.wocloud.arch.ssm.model.CdkeyParameter;
    import com.wocloud.arch.ssm.responseResult.ResponseData;
    import com.wocloud.arch.ssm.responseResult.ResponseResult;
    import com.wocloud.arch.ssm.service.CdkeyParameterService;
    import com.wocloud.arch.ssm.service.CdkeyService;
    
    import net.sf.json.JSONObject;
    
    
    /**
     * @author mazhen
     * 兑换码请求接口
     */
    @RestController
    public class CdkeyController {
    	/**
    	 * 引入日志，注意都是"org.slf4j"包下
    	 */
    	private final static Logger logger = LoggerFactory.getLogger(CdkeyController.class);
      
        @Autowired
        private CdkeyParameterService cdkeyParameterService;
        @Autowired
        private CdkeyService cdkeyService;
        
    	/*
    	 * 兑换码post请求
    	 * @param HttpServletRequest request
    	 * @result ResponseResult
    	 */
    	@RequestMapping(value = "cdkeySender",method = RequestMethod.POST)
    	public ResponseData cdkeySender(HttpServletRequest request) {
    		logger.info("开始接收合作伙伴的参数");
    		Map<String, Object> map = new HashMap<>();
    		JSONObject jsonObjectData = null;
    		JSONObject jsonObject = null;
    		
    		try {
    			jsonObjectData = ParameterUtil.getParameters(request);
    		} catch (IOException e) {
    			e.printStackTrace();
    			logger.error("接口获取参数异常:"+e);
    			return responseDataFromContent(
    					"3",
    					"failure",
    					null,
    					"接口异常");
    		}
    
    		/*
    		 * 校验json包体完整性
    		 */
    		logger.info("从合作伙伴获取到的jsonObjectData："+jsonObjectData);
    		if (null == jsonObjectData || ("").equals(jsonObjectData) || !jsonObjectData.containsKey("data")) {
    			logger.error("接口获取参数失败");
    			return responseDataFromContent(
    					"4",
    					"failure",
    					null,
    					"接口获取参数失败");
    		}
    		
    		logger.info(jsonObjectData.getString("data"));
    		jsonObject = JSONObject.fromObject(jsonObjectData.getString("data"));
    		String telePhone = jsonObject.getString("telephone");
    		
    		
    		try {
    				return updateCdkey(jsonObject);
    		} catch (DigestException e) {
    			e.printStackTrace();
    			logger.error("接口签名算法异常:"+e);
    			return responseDataFromContent(
    					"5",
    					"failure",
    					null,
    					"接口签名算法异常");
    		}
    	}
    
    	/*
    	 * 处理Response数据
    	 */
    	private ResponseData responseDataFromContent(final String code, String message, String telePhone, String detail) {
    		ResponseData responseData = new ResponseData();
    		ResponseResult result = new ResponseResult();
    		result.setCode(code);
    		result.setMessage(message);
    		if (telePhone != null) {
    			result.setTelephone(telePhone);
    		}
    		result.setDetail(detail);
    		responseData.setData(result);
    		responseData.setResponseTime(RandomTool.getTimeStamp());
    		return responseData;
    	}
    	
    	
    	/*
    	 * 更新cdkey数据库
    	 */
    	public ResponseData updateCdkey(JSONObject jsonObject) {
            ResponseData responseData = null;
    			
    		String cdkeyStr = cdkeyService.updateCdkeyIsusage(jsonObject);
            
            if (cdkeyStr == null) {
    			logger.info("cdkey表更新失败,短信发送失败！");
    			responseData = responseDataFromContent(
    						"11",
    						"failure",
    						jsonObject.getString("telephone"),
    						"数据处理失败");
    	   } else {
               logger.info("cdkey表更新成功，短信发送成功！");
    		   responseData = responseDataFromContent(
    							"0",
    							"success",
    							jsonObject.getString("telephone"),
    							"请求成功")
           }
           return responseData;
    						
    	}
    
    }

17、ResponseResult.java

/**
     * 
     */
    package com.wocloud.arch.ssm.responseResult;
    
    /**
     * @author mazhen
     *
     */
    public class ResponseData {
    	
        private ResponseResult data;
        
        private String responseTime;
    
    	public ResponseResult getData() {
    		return data;
    	}
    
    	public void setData(ResponseResult data) {
    		this.data = data;
    	}
    
    	public String getResponseTime() {
    		return responseTime;
    	}
    
    	public void setResponseTime(String responseTime) {
    		this.responseTime = responseTime;
    	}
    }

18、ResponseData.java

/**
     * 
     */
    package com.wocloud.arch.ssm.responseResult;
    
    /**
     * @author mazhen
     *
     */
    public class ResponseResult {
        private String code;
    	
    	private String message;
    	
    	private String telephone;
    	
    	private String detail;
    
    	public String getCode() {
    		return code;
    	}
    
    	public void setCode(String code) {
    		this.code = code;
    	}
    
    	public String getMessage() {
    		return message;
    	}
    
    	public void setMessage(String message) {
    		this.message = message;
    	}
    		
    	
    	public String getTelephone() {
    		return telephone;
    	}
    
    	public void setTelephone(String telephone) {
    		this.telephone = telephone;
    	}
    
    	public String getDetail() {
    		return detail;
    	}
    
    	public void setDetail(String detail) {
    		this.detail = detail;
    	}
    
    	public ResponseResult(String code,String message,String telephone,String detail){
    		super();
    		this.code = code;
    		this.message = message;
    		this.telephone = telephone;
    		this.detail = detail;
    	}
    	public ResponseResult(){
    		
    	}
    }

19、ParameterUtil.java

/**
     * 
     */
    package com.wocloud.arch.ssm.utils.common;
    
    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import javax.servlet.http.HttpServletRequest;
    
    import net.sf.json.JSONObject;
    
    /**
     * @author mazhen
     * 获取post请求中的body内容
     */
    public class ParameterUtil {
    
        public static JSONObject getParameters(HttpServletRequest request) throws IOException {
            //从request中以"UTF-8"形式获取输入流，避免中文乱码
            BufferedReader br = new BufferedReader(new InputStreamReader(request.getInputStream(),"UTF-8"));
            StringBuffer sb = new StringBuffer("");
            String temp;
            //一行一行读取并放入到StringBuffer中
            while((temp = br.readLine()) != null){
                sb.append(temp);
            }
            br.close();
            String acceptjson = sb.toString();
            JSONObject jo = null;
            //把String转成JSON对象
            if (acceptjson != "") {
                jo = JSONObject.fromObject(acceptjson);
            }
            return jo;
        }
    
    }

最后，使用apache bench进行测试，事务的生效如图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70][]

代码参见：[https://github.com/mameng1992/SpringBoot2.0.3\_Mybatis\_Read\_Write][https_github.com_mameng1992_SpringBoot2.0.3_Mybatis_Read_Write]

参考文章：1、[https://blog.csdn.net/qq\_37061442/article/details/82350258][https_blog.csdn.net_qq_37061442_article_details_82350258]

2、[https://blog.csdn.net/Appleyk/article/details/79442006][https_blog.csdn.net_Appleyk_article_details_79442006]

[https_blog.csdn.net_ydyang1126_article_details_70174334]: https://blog.csdn.net/ydyang1126/article/details/70174334
[https_www.oschina.net_translate_comparative_analysis_between_spring_aop_and_aspectj]: https://www.oschina.net/translate/comparative_analysis_between_spring_aop_and_aspectj
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hbWVuZzE5ODg_size_16_color_FFFFFF_t_70]: /images/20220504/b26daca2045f47058fc160404bffafdc.png
[https_github.com_mameng1992_SpringBoot2.0.3_Mybatis_Read_Write]: https://github.com/mameng1992/SpringBoot2.0.3_Mybatis_Read_Write
[https_blog.csdn.net_qq_37061442_article_details_82350258]: https://blog.csdn.net/qq_37061442/article/details/82350258
[https_blog.csdn.net_Appleyk_article_details_79442006]: https://blog.csdn.net/Appleyk/article/details/79442006