Spring Boot + MyBatis 实现读写分离
MySQL主从复制配置
项目下载
一、技术栈
1、 Spring Boot:2.2.2.Release
< parent>
< groupId> org.springframework.boot</ groupId>
< artifactId> spring-boot-starter-parent</ artifactId>
< version> 2.2.2.RELEASE</ version>
< relativePath/>
</ parent>
2、MyBatis:2.1.1
< dependency>
< groupId> org.mybatis.spring.boot</ groupId>
< artifactId> mybatis-spring-boot-starter</ artifactId>
< version> 2.1.1</ version>
</ dependency>
3、 MySQL:5.7.x
4、MiniUI
二、yml配置文件
三、代码实现
1、添加枚举类,用于区分主、从库
package com. productmanage. enums;
public enum DBTypeEnum {
MASTER, SLAVE;
}
2、DBContextHolder,用于切换、获取当前使用的数据库
package com. productmanage. config;
import com. productmanage. enums. DBTypeEnum;
import lombok. extern. slf4j. Slf4j;
import java. util. concurrent. atomic. AtomicInteger;
@Slf4j
public class DBContextHolder {
private static final ThreadLocal< DBTypeEnum> contextHolder = new ThreadLocal < > ( ) ;
private static final AtomicInteger counter = new AtomicInteger ( - 1 ) ;
public static void set ( DBTypeEnum dbType) {
contextHolder. set ( dbType) ;
}
public static DBTypeEnum get ( ) {
return contextHolder. get ( ) ;
}
public static void master ( ) {
set ( DBTypeEnum. MASTER) ;
log. info ( "切换到master" ) ;
}
public static void slave ( ) {
set ( DBTypeEnum. SLAVE) ;
log. info ( "切换到slave" ) ;
}
}
3、数据源配置类:DataSourceConfig
package com. productmanage. config;
import com. productmanage. enums. DBTypeEnum;
import org. springframework. beans. factory. annotation. Qualifier;
import org. springframework. boot. context. properties. ConfigurationProperties;
import org. springframework. boot. jdbc. DataSourceBuilder;
import org. springframework. context. annotation. Bean;
import org. springframework. context. annotation. Configuration;
import org. springframework. context. annotation. Primary;
import javax. sql. DataSource;
import java. util. HashMap;
import java. util. Map;
@Configuration
public class DataSourceConfig {
@Primary
@Bean ( name = "masterDataSource" )
@Qualifier ( "masterDataSource" )
@ConfigurationProperties ( "spring.datasource.master" )
public DataSource masterDataSource ( ) {
return DataSourceBuilder. create ( ) . build ( ) ;
}
@Bean ( name = "slaveDataSource" )
@Qualifier ( "slaveDataSource" )
@ConfigurationProperties ( "spring.datasource.slave" )
public DataSource slaveDataSource ( ) {
return DataSourceBuilder. create ( ) . build ( ) ;
}
@Bean
public DataSource myRouteDataSource ( @Qualifier ( "masterDataSource" ) DataSource masterDataSource,
@Qualifier ( "slaveDataSource" ) DataSource slaveDataSource) {
Map< Object, Object> targetDataSource = new HashMap < > ( ) ;
targetDataSource. put ( DBTypeEnum. MASTER, masterDataSource) ;
targetDataSource. put ( DBTypeEnum. SLAVE, slaveDataSource) ;
MyRouteDataSource myRouteDataSource = new MyRouteDataSource ( ) ;
myRouteDataSource. setDefaultTargetDataSource ( masterDataSource) ;
myRouteDataSource. setTargetDataSources ( targetDataSource) ;
return myRouteDataSource;
}
}
4、路由数据源:MyRouteDataSource
package com. productmanage. config;
import org. springframework. jdbc. datasource. lookup. AbstractRoutingDataSource;
import org. springframework. lang. Nullable;
public class MyRouteDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey ( ) {
return DBContextHolder. get ( ) ;
}
}
5、自定义主、从注解
(1)Master.java
package com. productmanage. annotation;
public @interface Master {
}
(2)Slave.java
package com. productmanage. annotation;
public @interface Slave {
}
6、AOP切面,@Before,在方法调用前切换数据源
package com. productmanage. aspect;
import com. productmanage. config. DBContextHolder;
import org. aspectj. lang. annotation. Aspect;
import org. aspectj. lang. annotation. Before;
import org. aspectj. lang. annotation. Pointcut;
import org. springframework. stereotype. Component;
@Aspect
@Component
public class DataSourceAspect {
@Pointcut ( "!@annotation(com.productmanage.annotation.Slave) " +
"&& (execution(* com.productmanage.service..*.select*(..)) " +
"|| execution(* com.productmanage.service..*.find*(..)))" )
public void readPointCut ( ) { }
@Pointcut ( "@annotation(com.productmanage.annotation.Master) " +
"|| execution(* com.productmanage.service..*.insert*(..)) " +
"|| execution(* com.productmanage.service..*.add*(..)) " +
"|| execution(* com.productmanage.service..*.update*(..)) " +
"|| execution(* com..productmanage.service..*.edit*(..)) " +
"|| execution(* com.productmanage.service..*.delete*(..)) " +
"|| execution(* com.productmanage.service..*.remove*(..))" )
public void writePointCut ( ) { }
@Before ( "readPointCut()" )
public void read ( ) {
DBContextHolder. slave ( ) ;
}
@Before ( "writePointCut()" )
public void write ( ) {
DBContextHolder. master ( ) ;
}
}
四、FAQ
1、多数据源配置启动报错
file [ H: \XZKUI\GoodGoodStudyDayDayUp\CODE\productmanagement\target\classes\com\productmanage\mapper\UserMapper. class ] required a single bean, but 3 were found:
- masterDataSource: defined by method 'masterDataSource' in class path resource [ com/ productmanage/ config/ DataSourceConfig. class ]
- slaveDataSource: defined by method 'slaveDataSource' in class path resource [ com/ productmanage/ config/ DataSourceConfig. class ]
- myRouteDataSource: defined by method 'myRouteDataSource' in class path resource [ com/ productmanage/ config/ DataSourceConfig. class ]
Action:
Consider marking one of the beans as @Primary , updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed
报错分析:很明显,Bean重复了,翻译一下Action:考虑将其中一个bean标记为@Primary,更新使用者以接受多个bean,或者使用@Qualifier来标识应该被消费的bean 解决:在数据源配置类(DataSourceConfig)中,给每个数据源添加一个唯一的名称,并在主库中添加@Primary注解,表示默认使用该数据库
2、项目运行时,从库被关闭,主库正常
3、项目运行时,从库被关闭,主库正常,此时更改主库数据
从库重新启动后,会读取主库中的binlog二进制文件,并将读取的数据保存到从库的relay-log日志文件中,并解析成sql语句逐一执行