spring boot学习7之mybatis+mysql读写分离(一写多读)+事务

骑猪看日落 2024-02-19 13:44 13阅读 0赞

当业务的访问量（数据库的查询）非常大时，为了降低数据库的压力，希望有多个数据库进行负载均衡，避免所有的查询都集中在一台数据库，造成数据库压力过大。mysql支持一主多从，即在写库的数据库发生变动时，会同步到所有从库，只是同步过程中，会有一定的延迟（除非业务中出现，立即写立即读，否则稍微的延迟是可以接收的）。

当数据库有主从之分了，那应用代码也应该读写分离了。那代码执行时，该如何决定选择哪个数据库呢。

方案一：

就像配置多个数据源那样（见博文[spring boot学习6之mybatis+PageHelper分页插件+jta多数据源事务整合][spring boot_6_mybatis_PageHelper_jta]），将dao都分别放到不通的包下，指明哪个包下dao接口或配置文件走哪个数据库，service层程序员决定走主库还是从库。

缺点：相同的dao接口和配置文件要复制多份到不同包路径下，不易维护和扩展。

方案二：

使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。

缺点：不支持事务。因为事务在service层开启时，就必须拿到数据源了。

方案三：

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务.

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

方案二和方案三的区别就是数据源的决定是方案dao还是service，所以本博文例子代码会都含有。

[源码下载，见github。][github]

项目结构

![20170603123759817][]

pom.xml

<parent>
    	    <groupId>org.springframework.boot</groupId>
    	    <artifactId>spring-boot-starter-parent</artifactId>
    	    <version>1.5.2.RELEASE</version>
    	</parent>   
      
    	<dependencies>
    	    <dependency>
    	        <groupId>org.springframework.boot</groupId>
    	        <artifactId>spring-boot-starter-web</artifactId>
    	    </dependency>
    	    
    		  <dependency>
       			 <groupId>org.mybatis.spring.boot</groupId>
        		 <artifactId>mybatis-spring-boot-starter</artifactId>
       			 <version>1.3.0</version>
    		</dependency> 
     
    		<dependency>
        			<groupId>mysql</groupId>
        			<artifactId>mysql-connector-java</artifactId>
    		</dependency>
    		<dependency>
        		<groupId>com.alibaba</groupId>
        		<artifactId>druid</artifactId>
        		<version>1.0.29</version>
    		</dependency>
    		
    		<dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
            </dependency>
            <dependency>
    	    <groupId>org.springframework</groupId>
    	    <artifactId>spring-beans</artifactId>
    	</dependency>
    		
    		
    		<dependency>
        		<groupId>com.github.pagehelper</groupId>
        		<artifactId>pagehelper</artifactId>
        		<version>4.1.6</version>
    		</dependency>
     
     
    		</dependencies>

本例子的数据库，都是在本地的mysql中建立3个库，test,test\_01,test\_02，例子是为了测试代码的读写分离，而是mysqld

application.yml

logging:
      config: classpath:logback.xml
      path: d:/logs
    server:
      port: 80
      session-timeout: 60
    
    
    mybatis:
         mapperLocations: classpath:/com/fei/springboot/dao/*.xml
         typeAliasesPackage: com.fei.springboot.dao    
         mapperScanPackage: com.fei.springboot.dao
         configLocation: classpath:/mybatis-config.xml
    
    mysql:
        datasource:
            readSize: 2  #读库个数
            type: com.alibaba.druid.pool.DruidDataSource
            mapperLocations: classpath:/com/fei/springboot/dao/*.xml
            configLocation: classpath:/mybatis-config.xml
            write:
               url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8
               username: root
               password: root
               driver-class-name: com.mysql.jdbc.Driver
               minIdle: 5
               maxActive: 100
               initialSize: 10
               maxWait: 60000
               timeBetweenEvictionRunsMillis: 60000
               minEvictableIdleTimeMillis: 300000
               validationQuery: select 'x'
               testWhileIdle: true
               testOnBorrow: false
               testOnReturn: false
               poolPreparedStatements: true
               maxPoolPreparedStatementPerConnectionSize: 50
               removeAbandoned: true
               filters: stat
            read01:
               url: jdbc:mysql://127.0.0.1:3306/test_01?useUnicode=true&characterEncoding=utf-8
               username: root
               password: root
               driver-class-name: com.mysql.jdbc.Driver
               minIdle: 5
               maxActive: 100
               initialSize: 10
               maxWait: 60000
               timeBetweenEvictionRunsMillis: 60000
               minEvictableIdleTimeMillis: 300000
               validationQuery: select 'x'
               testWhileIdle: true
               testOnBorrow: false
               testOnReturn: false
               poolPreparedStatements: true
               maxPoolPreparedStatementPerConnectionSize: 50
               removeAbandoned: true
               filters: stat
            read02:
               url: jdbc:mysql://127.0.0.1:3306/test_02?useUnicode=true&characterEncoding=utf-8
               username: root
               password: root
               driver-class-name: com.mysql.jdbc.Driver
               minIdle: 5
               maxActive: 100
               initialSize: 10
               maxWait: 60000
               timeBetweenEvictionRunsMillis: 60000
               minEvictableIdleTimeMillis: 300000
               validationQuery: select 'x'
               testWhileIdle: true
               testOnBorrow: false
               testOnReturn: false
               poolPreparedStatements: true
               maxPoolPreparedStatementPerConnectionSize: 50
               removeAbandoned: true
               filters: stat

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="cacheEnabled" value="true" />
    		
    		<setting name="lazyLoadingEnabled" value="true" />
    		        
             <setting name="aggressiveLazyLoading" value="true"/>        
             
    		<setting name="multipleResultSetsEnabled" value="true" />
    		
    		<setting name="useColumnLabel" value="true" />
    		
    		<setting name="useGeneratedKeys" value="false" />
    		
    		<setting name="autoMappingBehavior" value="PARTIAL" />
    		
    		<setting name="defaultExecutorType" value="SIMPLE" />
    		
    		<setting name="defaultStatementTimeout" value="25" />
    		
    		<setting name="defaultFetchSize" value="100" />
    		
    		<setting name="safeRowBoundsEnabled" value="false" />
    		
    		<setting name="mapUnderscoreToCamelCase" value="true" />
    		
    		<setting name="localCacheScope" value="SESSION" />
    		
    		<setting name="jdbcTypeForNull" value="NULL" />
    		<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
    	</settings>
    	
    </configuration>

读取配置多个数据源

DataSourceConfiguration.java

package com.fei.springboot.config.dbconfig;
     
    import javax.sql.DataSource;
     
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
     
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
     
    /**
     * 数据库源配置
     * @author Jfei
     *
     */
    @Configuration
    public class DataSourceConfiguration {
     
    	private static Logger log = LoggerFactory.getLogger(DataSourceConfiguration.class);
    	
    	@Value("${mysql.datasource.type}")
    	private Class<? extends DataSource> dataSourceType;
        
    	/**
    	 * 写库 数据源配置
    	 * @return
    	 */
    	@Bean(name = "writeDataSource")
        @Primary
        @ConfigurationProperties(prefix = "mysql.datasource.write")
        public DataSource writeDataSource() {
            log.info("-------------------- writeDataSource init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    	
    	/**
         * 有多少个从库就要配置多少个
         * @return
         */
        @Bean(name = "readDataSource01")
        @ConfigurationProperties(prefix = "mysql.datasource.read01")
        public DataSource readDataSourceOne() {
            log.info("-------------------- read01 DataSourceOne init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
     
        @Bean(name = "readDataSource02")
        @ConfigurationProperties(prefix = "mysql.datasource.read02")
        public DataSource readDataSourceTwo() {
            log.info("-------------------- read02 DataSourceTwo init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        
        
    }

数据库的sqlSessionFactorys、roundRobinDataSouceProxy、sqlSessionTemplate、annotationDrivenTransactionManager的设置

MybatisConfiguration.java

package com.fei.springboot.config.dbconfig;
     
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Properties;
    import java.util.concurrent.atomic.AtomicInteger;
     
    import javax.sql.DataSource;
     
    import org.apache.ibatis.plugin.Interceptor;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.DefaultResourceLoader;
    import org.springframework.core.io.Resource;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.transaction.PlatformTransactionManager;
     
    import com.fei.springboot.util.SpringContextUtil;
    import com.github.pagehelper.PageHelper;
     
    @Configuration
    @AutoConfigureAfter(DataSourceConfiguration.class)
    @MapperScan(basePackages="com.fei.springboot.dao")
    public class MybatisConfiguration {
     
    	private static Logger log = LoggerFactory.getLogger(MybatisConfiguration.class);
    	
    	@Value("${mysql.datasource.readSize}")
        private String readDataSourceSize;
     
    	//XxxMapper.xml文件所在路径
    	  @Value("${mysql.datasource.mapperLocations}")
          private String mapperLocations;
     
         //  加载全局的配置文件
          @Value("${mysql.datasource.configLocation}")
          private String configLocation;
          
    	@Autowired
    	@Qualifier("writeDataSource")
    	private DataSource writeDataSource;
    	@Autowired
    	@Qualifier("readDataSource01")
    	private DataSource readDataSource01;
    	@Autowired
    	@Qualifier("readDataSource02")
    	private DataSource readDataSource02;
    	
    	
        @Bean(name="sqlSessionFactory")
        public SqlSessionFactory sqlSessionFactorys() throws Exception {
            log.info("--------------------  sqlSessionFactory init ---------------------");
            try {
                SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
           //     sessionFactoryBean.setDataSource(roundRobinDataSouce);
                sessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
                
                // 读取配置 
                sessionFactoryBean.setTypeAliasesPackage("com.fei.springboot.domain");
                
                //设置mapper.xml文件所在位置 
                Resource[] resources = new PathMatchingResourcePatternResolver().getResources(mapperLocations);
                sessionFactoryBean.setMapperLocations(resources);
             //设置mybatis-config.xml配置文件位置
                sessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
     
                //添加分页插件、打印sql插件
                Interceptor[] plugins = new Interceptor[]{pageHelper(),new SqlPrintInterceptor()};
                sessionFactoryBean.setPlugins(plugins);
                
                return sessionFactoryBean.getObject();
            } catch (IOException e) {
                log.error("mybatis resolver mapper*xml is error",e);
                return null;
            } catch (Exception e) {
                log.error("mybatis sqlSessionFactoryBean create error",e);
                return null;
            }
        }
     
        /**
         * 分页插件
         * @return
         */
        @Bean
        public PageHelper pageHelper() {
            PageHelper pageHelper = new PageHelper();
            Properties p = new Properties();
            p.setProperty("offsetAsPageNum", "true");
            p.setProperty("rowBoundsWithCount", "true");
            p.setProperty("reasonable", "true");
            p.setProperty("returnPageInfo", "check");
            p.setProperty("params", "count=countSql");
            pageHelper.setProperties(p);
            return pageHelper;
        }
        /**
         * 把所有数据库都放在路由中
         * @return
         */
        @Bean(name="roundRobinDataSouceProxy")
        public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        	
        	Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
            //把所有数据库都放在targetDataSources中,注意key值要和determineCurrentLookupKey()中代码写的一至，
            //否则切换数据源时找不到正确的数据源
            targetDataSources.put(DataSourceType.write.getType(), writeDataSource);
            targetDataSources.put(DataSourceType.read.getType()+"1", readDataSource01);
            targetDataSources.put(DataSourceType.read.getType()+"2", readDataSource02);
        
            final int readSize = Integer.parseInt(readDataSourceSize);
       //     MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(readSize);
            
            //路由类，寻找对应的数据源
            AbstractRoutingDataSource proxy = new AbstractRoutingDataSource(){
            	 private AtomicInteger count = new AtomicInteger(0);
            	/**
                 * 这是AbstractRoutingDataSource类中的一个抽象方法，
                 * 而它的返回值是你所要用的数据源dataSource的key值，有了这个key值，
                 * targetDataSources就从中取出对应的DataSource，如果找不到，就用配置默认的数据源。
                 */
            	@Override
            	protected Object determineCurrentLookupKey() {
            		String typeKey = DataSourceContextHolder.getReadOrWrite();
            		
            		if(typeKey == null){
            		//	System.err.println("使用数据库write.............");
                    //    return DataSourceType.write.getType();
            			throw new NullPointerException("数据库路由时，决定使用哪个数据库源类型不能为空...");
            		}
            		
                    if (typeKey.equals(DataSourceType.write.getType())){
                    	System.err.println("使用数据库write.............");
                        return DataSourceType.write.getType();
                    }
                    	
                    //读库， 简单负载均衡
                    int number = count.getAndAdd(1);
                    int lookupKey = number % readSize;
                    System.err.println("使用数据库read-"+(lookupKey+1));
                    return DataSourceType.read.getType()+(lookupKey+1);
            	}
            };
            
            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"));
        }
        
    }

重点是roundRobinDataSouceProxy()方法，它把所有的数据库源交给AbstractRoutingDataSource类，并由它的determineCurrentLookupKey()进行决定数据源的选择，其中读库进行了简单的负载均衡（轮询）。

DataSourceType.java

package com.fei.springboot.config.dbconfig;
     
    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;
    	}
        
    }

DataSourceContextHolder.java

package com.fei.springboot.config.dbconfig;
     
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
     
    /**
     * 本地线程，数据源上下文
     * @author Jfei
     *
     */
    public class DataSourceContextHolder {
     
    	private static Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);
    	
    	//线程本地环境
    	private static final ThreadLocal<String> local = new ThreadLocal<String>();
     
        public static ThreadLocal<String> getLocal() {
            return local;
        }
     
        /**
         * 读库
         */
        public static void setRead() {
            local.set(DataSourceType.read.getType());
            log.info("数据库切换到读库...");
        }
     
        /**
         * 写库
         */
        public static void setWrite() {
            local.set(DataSourceType.write.getType());
            log.info("数据库切换到写库...");
        }
     
        public static String getReadOrWrite() {
            return local.get();
        }
        
        public static void clear(){
        	local.remove();
        }
    }

写库、读库的注解

package com.fei.springboot.annotation;
     
    import java.lang.annotation.Documented;
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Inherited;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
     
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    @Inherited
    @Documented
    public @interface ReadDataSource {
     
    }
    package com.fei.springboot.annotation;
     
    import java.lang.annotation.Documented;
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Inherited;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
     
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    @Inherited
    @Documented
    public @interface WriteDataSource {
     
    }

UserMapper.java

package com.fei.springboot.dao;
     
    import java.util.List;
     
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
     
    import com.fei.springboot.domain.User;
     
    @Mapper
    public interface UserMapper {
     
    	@Insert("insert sys_user(id,user_name) values(#{id},#{userName})")
    	void insert(User u);
    	
    	@Select("select id,user_name from sys_user where id=#{id} ")
    	User findById(@Param("id")String id);
    	
    	//注：方法名和要UserMapper.xml中的id一致
    	List<User> query(@Param("userName")String userName);
    	
    }

如果想在dao进行数据源的决定，在aop的拦截路径写明是dao

DataSourceAopInDao.java

package com.fei.springboot.aop;
     
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.stereotype.Component;
     
    import com.fei.springboot.config.dbconfig.DataSourceContextHolder;
    import com.fei.springboot.config.dbconfig.DataSourceType;
     
    /**
     * 在dao层决定数据源(注：如果用这方式，service层不能使用事务，否则出问题，因为打开事务打开时，就会觉得数据库源了）
     * @author Jfei
     *
     */
    //@Aspect
    //@Component
    public class DataSourceAopInDao {
     
    	private static Logger log = LoggerFactory.getLogger(DataSourceAopInDao.class);
    	
    	@Before("execution(* com.fei.springboot.dao..*.find*(..)) "
    			+ " or execution(* com.fei.springboot.dao..*.get*(..)) "
    			+ " or execution(* com.fei.springboot.dao..*.query*(..))")
        public void setReadDataSourceType() {
            DataSourceContextHolder.setRead();
        }
     
        @Before("execution(* com.fei.springboot.dao..*.insert*(..)) "
        		+ " or execution(* com.fei.springboot.dao..*.update*(..))"
        		+ " or execution(* com.fei.springboot.dao..*.add*(..))")
        public void setWriteDataSourceType() {
            DataSourceContextHolder.setWrite();
        }
        
        
    /*    @Before("execution(* com.fei.springboot.dao..*.*(..)) "
    			+ " and @annotation(com.fei.springboot.annotation.ReadDataSource) ")
    	public void setReadDataSourceType() {
    		//如果已经开启写事务了，那之后的所有读都从写库读
    		if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
    			DataSourceContextHolder.setRead();
    		}
    	    
    	}
    	
    	@Before("execution(* com.fei.springboot.dao..*.*(..)) "
    			+ " and @annotation(com.fei.springboot.annotation.WriteDataSource) ")
    	public void setWriteDataSourceType() {
    	    DataSourceContextHolder.setWrite();
    	}*/
    }

如果是service,必须实现Ordered,并且优先级优于事务的开启。

DataSourceAopInService.java

package com.fei.springboot.aop;
     
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.context.annotation.EnableAspectJAutoProxy;
    import org.springframework.core.PriorityOrdered;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
     
    import com.fei.springboot.config.dbconfig.DataSourceContextHolder;
    import com.fei.springboot.config.dbconfig.DataSourceType;
     
    /**
     * 在service层觉得数据源
     * 
     * 必须在事务AOP之前执行，所以实现Ordered,order的值越小，越先执行
     * 如果一旦开始切换到写库，则之后的读都会走写库
     * 
     * @author Jfei
     *
     */
    @Aspect
    @EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)
    @Component
    public class DataSourceAopInService implements PriorityOrdered{
     
    private static Logger log = LoggerFactory.getLogger(DataSourceAopInService.class);
    	
    /*	@Before("execution(* com.fei.springboot.service..*.find*(..)) "
    			+ " or execution(* com.fei.springboot.service..*.get*(..)) "
    			+ " or execution(* com.fei.springboot.service..*.query*(..))")
        public void setReadDataSourceType() {
    		//如果已经开启写事务了，那之后的所有读都从写库读
    		if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
    			DataSourceContextHolder.setRead();
    		}
            
        }
        @Before("execution(* com.fei.springboot.service..*.insert*(..)) "
        		+ " or execution(* com.fei.springboot.service..*.update*(..))"
        		+ " or execution(* com.fei.springboot.service..*.add*(..))")
        public void setWriteDataSourceType() {
            DataSourceContextHolder.setWrite();
        }*/
        
     
    	@Before("execution(* com.fei.springboot.service..*.*(..)) "
    			+ " and @annotation(com.fei.springboot.annotation.ReadDataSource) ")
    	public void setReadDataSourceType() {
    		//如果已经开启写事务了，那之后的所有读都从写库读
    		if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
    			DataSourceContextHolder.setRead();
    		}
    	    
    	}
    	
    	@Before("execution(* com.fei.springboot.service..*.*(..)) "
    			+ " and @annotation(com.fei.springboot.annotation.WriteDataSource) ")
    	public void setWriteDataSourceType() {
    	    DataSourceContextHolder.setWrite();
    	}
        
    	@Override
    	public int getOrder() {
    		/**
    		 * 值越小，越优先执行
    		 * 要优于事务的执行
    		 * 在启动类中加上了@EnableTransactionManagement(order = 10) 
    		 */
    		return 1;
    	}
     
    }

UserService.java

package com.fei.springboot.service;
     
    import org.springframework.aop.framework.AopContext;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.EnableAspectJAutoProxy;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Isolation;
    import org.springframework.transaction.annotation.Propagation;
    import org.springframework.transaction.annotation.Transactional;
     
    import com.fei.springboot.annotation.ReadDataSource;
    import com.fei.springboot.annotation.WriteDataSource;
    import com.fei.springboot.dao.UserMapper;
    import com.fei.springboot.domain.User;
    import com.fei.springboot.util.SpringContextUtil;
    import com.github.pagehelper.Page;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    /**
     * 如果需要事务，自行在方法上添加@Transactional
     * 如果方法有内部有数据库操作，则必须指定@WriteDataSource还是@ReadDataSource
     * 
     * 注：AOP ，内部方法之间互相调用时，如果是this.xxx()这形式，不会触发AOP拦截，可能会
     * 导致无法决定数据库是走写库还是读库
     * 方法：
     * 为了触发AOP的拦截，调用内部方法时，需要特殊处理下，看方法getService()
     * 
     * @author Jfei
     *
     */
    @Service
    public class UserService {
     
    	@Autowired
    	private UserMapper userMapper;
    	
    	@WriteDataSource
    	@Transactional(propagation=Propagation.REQUIRED,isolation=Isolation.DEFAULT,readOnly=false)
    	public void insertUser(User u){
    		this.userMapper.insert(u);
    	
    		//如果类上面没有@Transactional,方法上也没有，哪怕throw new RuntimeException,数据库也会成功插入数据
    	//	throw new RuntimeException("测试插入事务");
    	}
    	/**
    	 * 写事务里面调用读
    	 * @param u
    	 */
    	public void wirteAndRead(User u){
    		getService().insertUser(u);//这里走写库，那后面的读也都要走写库
    		//这是刚刚插入的
    		User uu = getService().findById(u.getId());
    		System.out.println("==读写混合测试中的读(刚刚插入的)====id="+u.getId()+",  user_name=" + uu.getUserName());
    		//为了测试,3个库中id=1的user_name是不一样的
    		User uuu = getService().findById("1");
    		System.out.println("==读写混合测试中的读====id=1,  user_name=" + uuu.getUserName());
    		
    	}
    	
    	public void readAndWirte(User u){
    		//为了测试,3个库中id=1的user_name是不一样的
    		User uu = getService(). findById("1");
    		System.out.println("==读写混合测试中的读====id=1,user_name=" + uu.getUserName());
    		getService().insertUser(u);
    		
    	}
    	
    	@ReadDataSource
    	public User findById(String id){
    		User u = this.userMapper.findById(id);
    		return u;
    	}
    	
    	
    	@ReadDataSource
    	public PageInfo<User> queryPage(String userName,int pageNum,int pageSize){
    		Page<User> page = PageHelper.startPage(pageNum, pageSize);
    		//PageHelper会自动拦截到下面这查询sql
    		this.userMapper.query(userName);
    		return page.toPageInfo();
    	}
    	
    	private UserService getService(){
    		// 采取这种方式的话，
    		//@EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)
    		//必须设置为true
    	/*	if(AopContext.currentProxy() != null){
    			return (UserService)AopContext.currentProxy();
    		}else{
    			return this;
    		}
    		*/
    		return SpringContextUtil.getBean(this.getClass());
    	}
    	
    }

开头的方案三中也说到了，如果aop拦截在service层，但是aop不会拦截类内部的调用，至于原因，看博文[http://www.oschina.net/translate/comparative\_analysis\_between\_spring\_aop\_and\_aspectj][http_www.oschina.net_translate_comparative_analysis_between_spring_aop_and_aspectj]  
所以UserService中增加了getService()方法进行处理。

写个controller进行简单测试

UserController.java

package com.fei.springboot.controller;
     
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
     
    import com.fei.springboot.domain.User;
    import com.fei.springboot.service.UserService;
    import com.github.pagehelper.PageInfo;
     
    @Controller
    @RequestMapping("/user")
    public class UserController {
     
    	
    	@Autowired
    	private UserService userService;
    	
    	@RequestMapping("/hello")
    	@ResponseBody
    	public String hello(){
    		return "hello";
    	}
    	/**
    	 * 测试插入
    	 * @return
    	 */
    	@RequestMapping("/add")
    	@ResponseBody
    	public String add(String id,String userName){
    		User u = new User();
    		u.setId(id);
    		u.setUserName(userName);
    		this.userService.insertUser(u);
    		return u.getId()+"    " + u.getUserName();
    	}
    	/**
    	 * 测试读
    	 * @param id
    	 * @return
    	 */
    	@RequestMapping("/get/{id}")
    	@ResponseBody
    	public String findById(@PathVariable("id") String id){
    		User u = this.userService.findById(id);
    		return u.getId()+"    " + u.getUserName();
    	}
    	/**
    	 * 测试写然后读
    	 * @param id
    	 * @param userName
    	 * @return
    	 */
    	@RequestMapping("/addAndRead")
    	@ResponseBody
    	public String addAndRead(String id,String userName){
    		User u = new User();
    		u.setId(id);
    		u.setUserName(userName);
    		this.userService.wirteAndRead(u);
    		return u.getId()+"    " + u.getUserName();
    	}
    	
    	/**
    	 * 测试读然后写
    	 * @param id
    	 * @param userName
    	 * @return
    	 */
    	@RequestMapping("/readAndAdd")
    	@ResponseBody
    	public String readAndWrite(String id,String userName){
    		User u = new User();
    		u.setId(id);
    		u.setUserName(userName);
    		this.userService.readAndWirte(u);
    		return u.getId()+"    " + u.getUserName();
    	}
    	
    	/**
    	 * 测试分页插件
    	 * @return
    	 */
    	@RequestMapping("/queryPage")
    	@ResponseBody
    	public String queryPage(){
    		PageInfo<User> page = this.userService.queryPage("tes", 1, 2);
    		StringBuilder sb = new StringBuilder();
    		sb.append("<br/>总页数=" + page.getPages());
    		sb.append("<br/>总记录数=" + page.getTotal()) ;
    		for(User u : page.getList()){
    			sb.append("<br/>" + u.getId() + "      " + u.getUserName());
    		}
    		System.out.println("分页查询....\n" + sb.toString());
    		return sb.toString();
    	}
    	
    }

http://127.0.0.1/user/get/1 时，获取到的结果要么是库test\_01要么是test\_02中来的，其他方法也可以测试均符合预期结果。这里就不一一贴结果了。其他的类源码也不一一贴出来了。想看的可以到github上看或下载。

[完整源码][github]

[spring boot_6_mybatis_PageHelper_jta]: http://blog.csdn.net/dream_broken/article/details/72691838
[github]: https://github.com/269941633/spring-boot-mybatis-mysql-write-read
[20170603123759817]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fc7c32bc090745b0b948635e5dfc5dd8.png
[http_www.oschina.net_translate_comparative_analysis_between_spring_aop_and_aspectj]: http://www.oschina.net/translate/comparative_analysis_between_spring_aop_and_aspectj