当业务的访问量(数据库的查询)非常大时,为了降低数据库的压力,希望有多个数据库进行负载均衡,避免所有的查询都集中在一台数据库,造成数据库压力过大。mysql支持一主多从,即在写库的数据库发生变动时,会同步到所有从库,只是同步过程中,会有一定的延迟(除非业务中出现,立即写立即读,否则稍微的延迟是可以接收的)。
当数据库有主从之分了,那应用代码也应该读写分离了。那代码执行时,该如何决定选择哪个数据库呢。
方案一:
就像配置多个数据源那样,将dao都分别放到不通的包下,指明哪个包下dao接口或配置文件走哪个数据库,service层程序员决定走主库还是从库。
缺点:相同的dao接口和配置文件要复制多份到不同包路径下,不易维护和扩展。
方案二:
使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
缺点:不支持事务。因为事务在service层开启时,就必须拿到数据源了。
方案三:
使用AbstractRoutingDataSource+aop+annotation在service层决定数据源,可以支持事务.
缺点:类内部方法通过this.xx()方式相互调用时,aop不会进行拦截,需进行特殊处理。
方案二和方案三的区别就是数据源的决定是方案dao还是service,所以本博文例子代码会都含有。
项目结构
pom.xml
[html]
1. <parent>
2. <groupId>org.springframework.boot</groupId>
3. <artifactId>spring-boot-starter-parent</artifactId>
4. <version>1.5.2.RELEASE</version>
5. </parent>
6.
7. <dependencies>
8. <dependency>
9. <groupId>org.springframework.boot</groupId>
10. <artifactId>spring-boot-starter-web</artifactId>
11. </dependency>
12.
13. <dependency>
14. <groupId>org.mybatis.spring.boot</groupId>
15. <artifactId>mybatis-spring-boot-starter</artifactId>
16. <version>1.3.0</version>
17. </dependency>
18.
19. <dependency>
20. <groupId>mysql</groupId>
21. <artifactId>mysql-connector-java</artifactId>
22. </dependency>
23. <dependency>
24. <groupId>com.alibaba</groupId>
25. <artifactId>druid</artifactId>
26. <version>1.0.29</version>
27. </dependency>
28.
29. <dependency>
30. <groupId>org.aspectj</groupId>
31. <artifactId>aspectjweaver</artifactId>
32. </dependency>
33. <dependency>
34. <groupId>org.springframework</groupId>
35. <artifactId>spring-beans</artifactId>
36. </dependency>
37.
38. <!-- 分页插件 -->
39. <dependency>
40. <groupId>com.github.pagehelper</groupId>
41. <artifactId>pagehelper</artifactId>
42. <version>4.1.6</version>
43. </dependency>
44.
45.
46. </dependencies>
本例子的数据库,都是在本地的mysql中建立3个库,test,test_01,test_02,例子是为了测试代码的读写分离,而是mysqld
application.yml
[plain]
1. logging:
2. config: classpath:logback.xml
3. path: d:/logs
4. server:
5. port: 80
6. session-timeout: 60
7.
8.
9. mybatis:
10. mapperLocations: classpath:/com/fei/springboot/dao/*.xml
11. typeAliasesPackage: com.fei.springboot.dao
12. mapperScanPackage: com.fei.springboot.dao
13. configLocation: classpath:/mybatis-config.xml
14.
15. mysql:
16. datasource:
17. readSize: 2 #读库个数
18. type: com.alibaba.druid.pool.DruidDataSource
19. mapperLocations: classpath:/com/fei/springboot/dao/*.xml
20. configLocation: classpath:/mybatis-config.xml
21. write:
22. url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8
23. username: root
24. password: root
25. driver-class-name: com.mysql.jdbc.Driver
26. minIdle: 5
27. maxActive: 100
28. initialSize: 10
29. maxWait: 60000
30. timeBetweenEvictionRunsMillis: 60000
31. minEvictableIdleTimeMillis: 300000
32. validationQuery: select 'x'
33. testWhileIdle: true
34. testOnBorrow: false
35. testOnReturn: false
36. poolPreparedStatements: true
37. maxPoolPreparedStatementPerConnectionSize: 50
38. removeAbandoned: true
39. filters: stat
40. read01:
41. url: jdbc:mysql://127.0.0.1:3306/test_01?useUnicode=true&characterEncoding=utf-8
42. username: root
43. password: root
44. driver-class-name: com.mysql.jdbc.Driver
45. minIdle: 5
46. maxActive: 100
47. initialSize: 10
48. maxWait: 60000
49. timeBetweenEvictionRunsMillis: 60000
50. minEvictableIdleTimeMillis: 300000
51. validationQuery: select 'x'
52. testWhileIdle: true
53. testOnBorrow: false
54. testOnReturn: false
55. poolPreparedStatements: true
56. maxPoolPreparedStatementPerConnectionSize: 50
57. removeAbandoned: true
58. filters: stat
59. read02:
60. url: jdbc:mysql://127.0.0.1:3306/test_02?useUnicode=true&characterEncoding=utf-8
61. username: root
62. password: root
63. driver-class-name: com.mysql.jdbc.Driver
64. minIdle: 5
65. maxActive: 100
66. initialSize: 10
67. maxWait: 60000
68. timeBetweenEvictionRunsMillis: 60000
69. minEvictableIdleTimeMillis: 300000
70. validationQuery: select 'x'
71. testWhileIdle: true
72. testOnBorrow: false
73. testOnReturn: false
74. poolPreparedStatements: true
75. maxPoolPreparedStatementPerConnectionSize: 50
76. removeAbandoned: true
77. filters: stat
mybatis-config.xml
[html]
1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE configuration
3. PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4. >
5. <configuration>
6. <settings>
7. <!-- 使全局的映射器启用或禁用缓存。 -->
8. <setting name="cacheEnabled" value="true" />
9. <!-- 全局启用或禁用延迟加载。当禁用时,所有关联对象都会即时加载。 -->
10. <setting name="lazyLoadingEnabled" value="true" />
11. <!-- 当启用时,有延迟加载属性的对象在被调用时将会完全加载任意属性。否则,每种属性将会按需要加载。 -->
12. <setting name="aggressiveLazyLoading" value="true"/>
13. <!-- 是否允许单条sql 返回多个数据集 (取决于驱动的兼容性) default:true -->
14. <setting name="multipleResultSetsEnabled" value="true" />
15. <!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true -->
16. <setting name="useColumnLabel" value="true" />
17. <!-- 允许JDBC 生成主键。需要驱动器支持。如果设为了true,这个设置将强制使用被生成的主键,有一些驱动器不兼容不过仍然可以执行。 default:false -->
18. <setting name="useGeneratedKeys" value="false" />
19. <!-- 指定 MyBatis 如何自动映射 数据基表的列 NONE:不隐射 PARTIAL:部分 FULL:全部 -->
20. <setting name="autoMappingBehavior" value="PARTIAL" />
21. <!-- 这是默认的执行类型 (SIMPLE: 简单; REUSE: 执行器可能重复使用prepared statements语句;BATCH: 执行器可以重复执行语句和批量更新) -->
22. <setting name="defaultExecutorType" value="SIMPLE" />
23.
24. <setting name="defaultStatementTimeout" value="25" />
25.
26. <setting name="defaultFetchSize" value="100" />
27.
28. <setting name="safeRowBoundsEnabled" value="false" />
29. <!-- 使用驼峰命名法转换字段。 -->
30. <setting name="mapUnderscoreToCamelCase" value="true" />
31. <!-- 设置本地缓存范围 session:就会有数据的共享 statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
32. <setting name="localCacheScope" value="SESSION" />
33. <!-- 默认为OTHER,为了解决oracle插入null报错的问题要设置为NULL -->
34. <setting name="jdbcTypeForNull" value="NULL" />
35. <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
36. </settings>
37.
38. </configuration>
读取配置多个数据源
DataSourceConfiguration.java
[java]
1. package com.fei.springboot.config.dbconfig;
2.
3. import javax.sql.DataSource;
4.
5. import org.slf4j.Logger;
6. import org.slf4j.LoggerFactory;
7. import org.springframework.beans.factory.annotation.Value;
8. import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
9. import org.springframework.boot.context.properties.ConfigurationProperties;
10. import org.springframework.boot.web.servlet.FilterRegistrationBean;
11. import org.springframework.boot.web.servlet.ServletRegistrationBean;
12. import org.springframework.context.annotation.Bean;
13. import org.springframework.context.annotation.Configuration;
14. import org.springframework.context.annotation.Primary;
15.
16. import com.alibaba.druid.support.http.StatViewServlet;
17. import com.alibaba.druid.support.http.WebStatFilter;
18.
19. /**
20. * 数据库源配置
21. * @author Jfei
22. *
23. */
24. @Configuration
25. public class DataSourceConfiguration {
26.
27. private static Logger log = LoggerFactory.getLogger(DataSourceConfiguration.class);
28.
29. @Value("${mysql.datasource.type}")
30. private Class<? extends DataSource> dataSourceType;
31.
32. /**
33. * 写库 数据源配置
34. * @return
35. */
36. @Bean(name = "writeDataSource")
37. @Primary
38. @ConfigurationProperties(prefix = "mysql.datasource.write")
39. public DataSource writeDataSource() {
40. "-------------------- writeDataSource init ---------------------");
41. return DataSourceBuilder.create().type(dataSourceType).build();
42. }
43.
44. /**
45. * 有多少个从库就要配置多少个
46. * @return
47. */
48. @Bean(name = "readDataSource01")
49. @ConfigurationProperties(prefix = "mysql.datasource.read01")
50. public DataSource readDataSourceOne() {
51. "-------------------- read01 DataSourceOne init ---------------------");
52. return DataSourceBuilder.create().type(dataSourceType).build();
53. }
54.
55. @Bean(name = "readDataSource02")
56. @ConfigurationProperties(prefix = "mysql.datasource.read02")
57. public DataSource readDataSourceTwo() {
58. "-------------------- read02 DataSourceTwo init ---------------------");
59. return DataSourceBuilder.create().type(dataSourceType).build();
60. }
61.
62.
63. }
数据库的sqlSessionFactorys、roundRobinDataSouceProxy、sqlSessionTemplate、annotationDrivenTransactionManager的设置
MybatisConfiguration.java
[java]
1. package com.fei.springboot.config.dbconfig;
2.
3. import java.io.IOException;
4. import java.util.HashMap;
5. import java.util.Map;
6. import java.util.Properties;
7. import java.util.concurrent.atomic.AtomicInteger;
8.
9. import javax.sql.DataSource;
10.
11. import org.apache.ibatis.plugin.Interceptor;
12. import org.apache.ibatis.session.SqlSessionFactory;
13. import org.mybatis.spring.SqlSessionFactoryBean;
14. import org.mybatis.spring.SqlSessionTemplate;
15. import org.mybatis.spring.annotation.MapperScan;
16. import org.slf4j.Logger;
17. import org.slf4j.LoggerFactory;
18. import org.springframework.beans.factory.annotation.Autowired;
19. import org.springframework.beans.factory.annotation.Qualifier;
20. import org.springframework.beans.factory.annotation.Value;
21. import org.springframework.boot.autoconfigure.AutoConfigureAfter;
22. import org.springframework.context.annotation.Bean;
23. import org.springframework.context.annotation.Configuration;
24. import org.springframework.core.io.DefaultResourceLoader;
25. import org.springframework.core.io.Resource;
26. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
27. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
28. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
29. import org.springframework.transaction.PlatformTransactionManager;
30.
31. import com.fei.springboot.util.SpringContextUtil;
32. import com.github.pagehelper.PageHelper;
33.
34. @Configuration
35. @AutoConfigureAfter(DataSourceConfiguration.class)
36. @MapperScan(basePackages="com.fei.springboot.dao")
37. public class MybatisConfiguration {
38.
39. private static Logger log = LoggerFactory.getLogger(MybatisConfiguration.class);
40.
41. @Value("${mysql.datasource.readSize}")
42. private String readDataSourceSize;
43.
44. //XxxMapper.xml文件所在路径
45. @Value("${mysql.datasource.mapperLocations}")
46. private String mapperLocations;
47.
48. // 加载全局的配置文件
49. @Value("${mysql.datasource.configLocation}")
50. private String configLocation;
51.
52. @Autowired
53. @Qualifier("writeDataSource")
54. private DataSource writeDataSource;
55. @Autowired
56. @Qualifier("readDataSource01")
57. private DataSource readDataSource01;
58. @Autowired
59. @Qualifier("readDataSource02")
60. private DataSource readDataSource02;
61.
62.
63. @Bean(name="sqlSessionFactory")
64. public SqlSessionFactory sqlSessionFactorys() throws Exception {
65. "-------------------- sqlSessionFactory init ---------------------");
66. try {
67. new SqlSessionFactoryBean();
68. // sessionFactoryBean.setDataSource(roundRobinDataSouce);
69. sessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
70.
71. // 读取配置
72. "com.fei.springboot.domain");
73.
74. //设置mapper.xml文件所在位置
75. new PathMatchingResourcePatternResolver().getResources(mapperLocations);
76. sessionFactoryBean.setMapperLocations(resources);
77. //设置mybatis-config.xml配置文件位置
78. new DefaultResourceLoader().getResource(configLocation));
79.
80. //添加分页插件、打印sql插件
81. new Interceptor[]{pageHelper(),new SqlPrintInterceptor()};
82. sessionFactoryBean.setPlugins(plugins);
83.
84. return sessionFactoryBean.getObject();
85. catch (IOException e) {
86. "mybatis resolver mapper*xml is error",e);
87. return null;
88. catch (Exception e) {
89. "mybatis sqlSessionFactoryBean create error",e);
90. return null;
91. }
92. }
93.
94. /**
95. * 分页插件
96. * @return
97. */
98. @Bean
99. public PageHelper pageHelper() {
100. new PageHelper();
101. new Properties();
102. "offsetAsPageNum", "true");
103. "rowBoundsWithCount", "true");
104. "reasonable", "true");
105. "returnPageInfo", "check");
106. "params", "count=countSql");
107. pageHelper.setProperties(p);
108. return pageHelper;
109. }
110. /**
111. * 把所有数据库都放在路由中
112. * @return
113. */
114. @Bean(name="roundRobinDataSouceProxy")
115. public AbstractRoutingDataSource roundRobinDataSouceProxy() {
116.
117. new HashMap<Object, Object>();
118. //把所有数据库都放在targetDataSources中,注意key值要和determineCurrentLookupKey()中代码写的一至,
119. //否则切换数据源时找不到正确的数据源
120. targetDataSources.put(DataSourceType.write.getType(), writeDataSource);
121. "1", readDataSource01);
122. "2", readDataSource02);
123.
124. final int readSize = Integer.parseInt(readDataSourceSize);
125. // MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(readSize);
126.
127. //路由类,寻找对应的数据源
128. new AbstractRoutingDataSource(){
129. private AtomicInteger count = new AtomicInteger(0);
130. /**
131. * 这是AbstractRoutingDataSource类中的一个抽象方法,
132. * 而它的返回值是你所要用的数据源dataSource的key值,有了这个key值,
133. * targetDataSources就从中取出对应的DataSource,如果找不到,就用配置默认的数据源。
134. */
135. @Override
136. protected Object determineCurrentLookupKey() {
137. String typeKey = DataSourceContextHolder.getReadOrWrite();
138.
139. if(typeKey == null){
140. // System.err.println("使用数据库write.............");
141. // return DataSourceType.write.getType();
142. throw new NullPointerException("数据库路由时,决定使用哪个数据库源类型不能为空...");
143. }
144.
145. if (typeKey.equals(DataSourceType.write.getType())){
146. "使用数据库write.............");
147. return DataSourceType.write.getType();
148. }
149.
150. //读库, 简单负载均衡
151. int number = count.getAndAdd(1);
152. int lookupKey = number % readSize;
153. "使用数据库read-"+(lookupKey+1));
154. return DataSourceType.read.getType()+(lookupKey+1);
155. }
156. };
157.
158. //默认库
159. proxy.setTargetDataSources(targetDataSources);
160. return proxy;
161. }
162.
163.
164. @Bean
165. public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
166. return new SqlSessionTemplate(sqlSessionFactory);
167. }
168.
169. //事务管理
170. @Bean
171. public PlatformTransactionManager annotationDrivenTransactionManager() {
172. return new DataSourceTransactionManager((DataSource)SpringContextUtil.getBean("roundRobinDataSouceProxy"));
173. }
174.
175. }
重点是roundRobinDataSouceProxy()方法,它把所有的数据库源交给AbstractRoutingDataSource类,并由它的determineCurrentLookupKey()进行决定数据源的选择,其中读库进行了简单的负载均衡(轮询)。
DataSourceType.java
[java]
1. package com.fei.springboot.config.dbconfig;
2.
3. public enum DataSourceType {
4.
5. "read", "从库"),
6. "write", "主库");
7.
8. private String type;
9.
10. private String name;
11.
12. DataSourceType(String type, String name) {
13. this.type = type;
14. this.name = name;
15. }
16.
17. public String getType() {
18. return type;
19. }
20.
21. public void setType(String type) {
22. this.type = type;
23. }
24.
25. public String getName() {
26. return name;
27. }
28.
29. public void setName(String name) {
30. this.name = name;
31. }
32.
33. }
DataSourceContextHolder.java
[java]
1. package com.fei.springboot.config.dbconfig;
2.
3. import org.slf4j.Logger;
4. import org.slf4j.LoggerFactory;
5.
6. /**
7. * 本地线程,数据源上下文
8. * @author Jfei
9. *
10. */
11. public class DataSourceContextHolder {
12.
13. private static Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);
14.
15. //线程本地环境
16. private static final ThreadLocal<String> local = new ThreadLocal<String>();
17.
18. public static ThreadLocal<String> getLocal() {
19. return local;
20. }
21.
22. /**
23. * 读库
24. */
25. public static void setRead() {
26. local.set(DataSourceType.read.getType());
27. "数据库切换到读库...");
28. }
29.
30. /**
31. * 写库
32. */
33. public static void setWrite() {
34. local.set(DataSourceType.write.getType());
35. "数据库切换到写库...");
36. }
37.
38. public static String getReadOrWrite() {
39. return local.get();
40. }
41.
42. public static void clear(){
43. local.remove();
44. }
45. }
写库、读库的注解
[java]
1. package com.fei.springboot.annotation;
2.
3. import java.lang.annotation.Documented;
4. import java.lang.annotation.ElementType;
5. import java.lang.annotation.Inherited;
6. import java.lang.annotation.Retention;
7. import java.lang.annotation.RetentionPolicy;
8. import java.lang.annotation.Target;
9.
10. @Target({ElementType.METHOD, ElementType.TYPE})
11. @Retention(RetentionPolicy.RUNTIME)
12. @Inherited
13. @Documented
14. public @interface ReadDataSource {
15.
16. }
[java]
1. package com.fei.springboot.annotation;
2.
3. import java.lang.annotation.Documented;
4. import java.lang.annotation.ElementType;
5. import java.lang.annotation.Inherited;
6. import java.lang.annotation.Retention;
7. import java.lang.annotation.RetentionPolicy;
8. import java.lang.annotation.Target;
9.
10. @Target({ElementType.METHOD, ElementType.TYPE})
11. @Retention(RetentionPolicy.RUNTIME)
12. @Inherited
13. @Documented
14. public @interface WriteDataSource {
15.
16. }
UserMapper.java
[java]
1. package com.fei.springboot.dao;
2.
3. import java.util.List;
4.
5. import org.apache.ibatis.annotations.Insert;
6. import org.apache.ibatis.annotations.Mapper;
7. import org.apache.ibatis.annotations.Param;
8. import org.apache.ibatis.annotations.Select;
9.
10. import com.fei.springboot.domain.User;
11.
12. @Mapper
13. public interface UserMapper {
14.
15. @Insert("insert sys_user(id,user_name) values(#{id},#{userName})")
16. void insert(User u);
17.
18. @Select("select id,user_name from sys_user where id=#{id} ")
19. @Param("id")String id);
20.
21. //注:方法名和要UserMapper.xml中的id一致
22. @Param("userName")String userName);
23.
24. }
如果想在dao进行数据源的决定,在aop的拦截路径写明是dao
DataSourceAopInDao.java
[java]
1. package com.fei.springboot.aop;
2.
3. import org.aspectj.lang.annotation.Aspect;
4. import org.aspectj.lang.annotation.Before;
5. import org.slf4j.Logger;
6. import org.slf4j.LoggerFactory;
7. import org.springframework.stereotype.Component;
8.
9. import com.fei.springboot.config.dbconfig.DataSourceContextHolder;
10. import com.fei.springboot.config.dbconfig.DataSourceType;
11.
12. /**
13. * 在dao层决定数据源(注:如果用这方式,service层不能使用事务,否则出问题,因为打开事务打开时,就会觉得数据库源了)
14. * @author Jfei
15. *
16. */
17. //@Aspect
18. //@Component
19. public class DataSourceAopInDao {
20.
21. private static Logger log = LoggerFactory.getLogger(DataSourceAopInDao.class);
22.
23. @Before("execution(* com.fei.springboot.dao..*.find*(..)) "
24. " or execution(* com.fei.springboot.dao..*.get*(..)) "
25. " or execution(* com.fei.springboot.dao..*.query*(..))")
26. public void setReadDataSourceType() {
27. DataSourceContextHolder.setRead();
28. }
29.
30. @Before("execution(* com.fei.springboot.dao..*.insert*(..)) "
31. " or execution(* com.fei.springboot.dao..*.update*(..))"
32. " or execution(* com.fei.springboot.dao..*.add*(..))")
33. public void setWriteDataSourceType() {
34. DataSourceContextHolder.setWrite();
35. }
36.
37.
38. /* @Before("execution(* com.fei.springboot.dao..*.*(..)) "
39. + " and @annotation(com.fei.springboot.annotation.ReadDataSource) ")
40. public void setReadDataSourceType() {
41. //如果已经开启写事务了,那之后的所有读都从写库读
42. if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
43. DataSourceContextHolder.setRead();
44. }
45.
46. }
47.
48. @Before("execution(* com.fei.springboot.dao..*.*(..)) "
49. + " and @annotation(com.fei.springboot.annotation.WriteDataSource) ")
50. public void setWriteDataSourceType() {
51. DataSourceContextHolder.setWrite();
52. }*/
53. }
如果是service,必须实现Ordered,并且优先级优于事务的开启。
DataSourceAopInService.java
[java]
1. package com.fei.springboot.aop;
2.
3. import org.aspectj.lang.annotation.Aspect;
4. import org.aspectj.lang.annotation.Before;
5. import org.slf4j.Logger;
6. import org.slf4j.LoggerFactory;
7. import org.springframework.context.annotation.EnableAspectJAutoProxy;
8. import org.springframework.core.PriorityOrdered;
9. import org.springframework.stereotype.Component;
10. import org.springframework.transaction.annotation.EnableTransactionManagement;
11.
12. import com.fei.springboot.config.dbconfig.DataSourceContextHolder;
13. import com.fei.springboot.config.dbconfig.DataSourceType;
14.
15. /**
16. * 在service层觉得数据源
17. *
18. * 必须在事务AOP之前执行,所以实现Ordered,order的值越小,越先执行
19. * 如果一旦开始切换到写库,则之后的读都会走写库
20. *
21. * @author Jfei
22. *
23. */
24. @Aspect
25. @EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)
26. @Component
27. public class DataSourceAopInService implements PriorityOrdered{
28.
29. private static Logger log = LoggerFactory.getLogger(DataSourceAopInService.class);
30.
31. /* @Before("execution(* com.fei.springboot.service..*.find*(..)) "
32. + " or execution(* com.fei.springboot.service..*.get*(..)) "
33. + " or execution(* com.fei.springboot.service..*.query*(..))")
34. public void setReadDataSourceType() {
35. //如果已经开启写事务了,那之后的所有读都从写库读
36. if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
37. DataSourceContextHolder.setRead();
38. }
39.
40. }
41.
42. @Before("execution(* com.fei.springboot.service..*.insert*(..)) "
43. + " or execution(* com.fei.springboot.service..*.update*(..))"
44. + " or execution(* com.fei.springboot.service..*.add*(..))")
45. public void setWriteDataSourceType() {
46. DataSourceContextHolder.setWrite();
47. }*/
48.
49.
50. @Before("execution(* com.fei.springboot.service..*.*(..)) "
51. " and @annotation(com.fei.springboot.annotation.ReadDataSource) ")
52. public void setReadDataSourceType() {
53. //如果已经开启写事务了,那之后的所有读都从写库读
54. if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){
55. DataSourceContextHolder.setRead();
56. }
57.
58. }
59.
60. @Before("execution(* com.fei.springboot.service..*.*(..)) "
61. " and @annotation(com.fei.springboot.annotation.WriteDataSource) ")
62. public void setWriteDataSourceType() {
63. DataSourceContextHolder.setWrite();
64. }
65.
66. @Override
67. public int getOrder() {
68. /**
69. * 值越小,越优先执行
70. * 要优于事务的执行
71. * 在启动类中加上了@EnableTransactionManagement(order = 10)
72. */
73. return 1;
74. }
75.
76. }
UserService.java
[java]
1. package com.fei.springboot.service;
2.
3. import org.springframework.aop.framework.AopContext;
4. import org.springframework.beans.factory.annotation.Autowired;
5. import org.springframework.context.annotation.EnableAspectJAutoProxy;
6. import org.springframework.stereotype.Service;
7. import org.springframework.transaction.annotation.Isolation;
8. import org.springframework.transaction.annotation.Propagation;
9. import org.springframework.transaction.annotation.Transactional;
10.
11. import com.fei.springboot.annotation.ReadDataSource;
12. import com.fei.springboot.annotation.WriteDataSource;
13. import com.fei.springboot.dao.UserMapper;
14. import com.fei.springboot.domain.User;
15. import com.fei.springboot.util.SpringContextUtil;
16. import com.github.pagehelper.Page;
17. import com.github.pagehelper.PageHelper;
18. import com.github.pagehelper.PageInfo;
19. /**
20. * 如果需要事务,自行在方法上添加@Transactional
21. * 如果方法有内部有数据库操作,则必须指定@WriteDataSource还是@ReadDataSource
22. *
23. * 注:AOP ,内部方法之间互相调用时,如果是this.xxx()这形式,不会触发AOP拦截,可能会
24. * 导致无法决定数据库是走写库还是读库
25. * 方法:
26. * 为了触发AOP的拦截,调用内部方法时,需要特殊处理下,看方法getService()
27. *
28. * @author Jfei
29. *
30. */
31. @Service
32. public class UserService {
33.
34. @Autowired
35. private UserMapper userMapper;
36.
37. @WriteDataSource
38. @Transactional(propagation=Propagation.REQUIRED,isolation=Isolation.DEFAULT,readOnly=false)
39. public void insertUser(User u){
40. this.userMapper.insert(u);
41.
42. //如果类上面没有@Transactional,方法上也没有,哪怕throw new RuntimeException,数据库也会成功插入数据
43. // throw new RuntimeException("测试插入事务");
44. }
45. /**
46. * 写事务里面调用读
47. * @param u
48. */
49. public void wirteAndRead(User u){
50. //这里走写库,那后面的读也都要走写库
51. //这是刚刚插入的
52. User uu = getService().findById(u.getId());
53. "==读写混合测试中的读(刚刚插入的)====id="+u.getId()+", user_name=" + uu.getUserName());
54. //为了测试,3个库中id=1的user_name是不一样的
55. "1");
56. "==读写混合测试中的读====id=1, user_name=" + uuu.getUserName());
57.
58. }
59.
60. public void readAndWirte(User u){
61. //为了测试,3个库中id=1的user_name是不一样的
62. "1");
63. "==读写混合测试中的读====id=1,user_name=" + uu.getUserName());
64. getService().insertUser(u);
65.
66. }
67.
68. @ReadDataSource
69. public User findById(String id){
70. this.userMapper.findById(id);
71. return u;
72. }
73.
74.
75. @ReadDataSource
76. public PageInfo<User> queryPage(String userName,int pageNum,int pageSize){
77. Page<User> page = PageHelper.startPage(pageNum, pageSize);
78. //PageHelper会自动拦截到下面这查询sql
79. this.userMapper.query(userName);
80. return page.toPageInfo();
81. }
82.
83. private UserService getService(){
84. // 采取这种方式的话,
85. //@EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)
86. //必须设置为true
87. /* if(AopContext.currentProxy() != null){
88. return (UserService)AopContext.currentProxy();
89. }else{
90. return this;
91. }
92. */
93. return SpringContextUtil.getBean(this.getClass());
94. }
95.
96. }
开头的方案三中也说到了,如果aop拦截在service层,但是aop不会拦截类内部的调用
所以UserService中增加了getService()方法进行处理。
写个controller进行简单测试
UserController.java
[java]
1. package com.fei.springboot.controller;
2.
3. import org.springframework.beans.factory.annotation.Autowired;
4. import org.springframework.stereotype.Controller;
5. import org.springframework.web.bind.annotation.PathVariable;
6. import org.springframework.web.bind.annotation.RequestMapping;
7. import org.springframework.web.bind.annotation.ResponseBody;
8.
9. import com.fei.springboot.domain.User;
10. import com.fei.springboot.service.UserService;
11. import com.github.pagehelper.PageInfo;
12.
13. @Controller
14. @RequestMapping("/user")
15. public class UserController {
16.
17.
18. @Autowired
19. private UserService userService;
20.
21. @RequestMapping("/hello")
22. @ResponseBody
23. public String hello(){
24. return "hello";
25. }
26. /**
27. * 测试插入
28. * @return
29. */
30. @RequestMapping("/add")
31. @ResponseBody
32. public String add(String id,String userName){
33. new User();
34. u.setId(id);
35. u.setUserName(userName);
36. this.userService.insertUser(u);
37. return u.getId()+" " + u.getUserName();
38. }
39. /**
40. * 测试读
41. * @param id
42. * @return
43. */
44. @RequestMapping("/get/{id}")
45. @ResponseBody
46. public String findById(@PathVariable("id") String id){
47. this.userService.findById(id);
48. return u.getId()+" " + u.getUserName();
49. }
50. /**
51. * 测试写然后读
52. * @param id
53. * @param userName
54. * @return
55. */
56. @RequestMapping("/addAndRead")
57. @ResponseBody
58. public String addAndRead(String id,String userName){
59. new User();
60. u.setId(id);
61. u.setUserName(userName);
62. this.userService.wirteAndRead(u);
63. return u.getId()+" " + u.getUserName();
64. }
65.
66. /**
67. * 测试读然后写
68. * @param id
69. * @param userName
70. * @return
71. */
72. @RequestMapping("/readAndAdd")
73. @ResponseBody
74. public String readAndWrite(String id,String userName){
75. new User();
76. u.setId(id);
77. u.setUserName(userName);
78. this.userService.readAndWirte(u);
79. return u.getId()+" " + u.getUserName();
80. }
81.
82. /**
83. * 测试分页插件
84. * @return
85. */
86. @RequestMapping("/queryPage")
87. @ResponseBody
88. public String queryPage(){
89. this.userService.queryPage("tes", 1, 2);
90. new StringBuilder();
91. "<br/>总页数=" + page.getPages());
92. "<br/>总记录数=" + page.getTotal()) ;
93. for(User u : page.getList()){
94. "<br/>" + u.getId() + " " + u.getUserName());
95. }
96. "分页查询....\n" + sb.toString());
97. return sb.toString();
98. }
99.
100. }
http://127.0.0.1/user/get/1 时,获取到的结果要么是库test_01要么是test_02中来的,其他方法也可以测试均符合预期结果。这里就不一一贴结果了。其他的类源码也不一一贴出来了。想看的可以到github上看或下载。