Lison <cundream@163.com>
, v1.0.0, 2019.10.13
SpringBoot系统搭建集成-011-Mybatis+MySQL读写分离
引言
关于MySQL读写主从实现,分两步:MySQL读写分离环境搭建–主从配置
第一步,需要现有主从的环境 可参照
第二步,利用已有的环境进行JavaEE的Web项目配置
读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行,至于谁来做选择数据库这件事儿,无非两个,要么中间件帮我们做,要么程序自己做。因此,一般来讲,读写分离有两种实现方式。第一种是依靠中间件(比如:MyCat),也就是说应用程序连接到中间件,中间件帮我们做SQL分离;第二种是应用程序自己去做分离。这里我们选择程序自己来做,主要是利用Spring提供的路由数据源,以及AOP。
然而,应用程序层面去做读写分离最大的弱点(不足之处)在于无法动态增加数据库节点,因为数据源配置都是写在配置中的,新增数据库意味着新加一个数据源,必然改配置,并重启应用。当然,好处就是相对简单。
配置
pom.xml 配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${org.apache.commons.version}</version>
</dependency>
application.yml 配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
master:
jdbc-url: jdbc:mysql://192.168.237.63:3306/springbootbuild?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
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
slave1:
jdbc-url: jdbc:mysql://192.168.237.63:3307/springbootbuild?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
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
slave2:
jdbc-url: jdbc:mysql://192.168.237.63:3307/springbootbuild?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
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
多数据源配置
首先,我们定义一个枚举来代表这三个数据源
public enum DBTypeEnum {
MASTER,SLAVE1,SLAVE2
}
接下来,通过ThreadLocal将数据源设置到每个线程上下文中
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);
System.out.println("切换到master");
}
public static void slave() {
// 轮询
int index = counter.getAndIncrement() % 2;
if (counter.get() > 9999) {
counter.set(-1);
}
if (index == 0) {
set(DBTypeEnum.SLAVE1);
System.out.println("切换到slave1");
}else {
set(DBTypeEnum.SLAVE2);
System.out.println("切换到slave2");
}
}
}
获取路由key
public class RoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.get();
}
}
MyBatis配置
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
@Resource(name = "routingDataSource")
private DataSource routingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(routingDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("mybatis/mapper/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(routingDataSource);
}
}
设置路由key
默认情况下,所有的查询都走从库,插入/修改/删除走主库。我们通过方法名来区分操作类型(CRUD)
@Aspect
@Component
public class DataSourceAop {
@Pointcut("!@annotation(com.github.cundream.springbootbuilding.common.annotation.Master) " +
"&& (execution(* com.github.cundream.springbootbuilding.service..*.select*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.get*(..)))")
public void readPointcut() {
}
@Pointcut("@annotation(com.github.cundream.springbootbuilding.common.annotation.Master) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.insert*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.add*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.update*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.edit*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.delete*(..)) " +
"|| execution(* com.github.cundream.springbootbuilding.service..*.remove*(..))")
public void writePointcut() {
}
@Before("readPointcut()")
public void read() {
DBContextHolder.slave();
}
@Before("writePointcut()")
public void write() {
DBContextHolder.master();
}
}
有一般情况就有特殊情况,特殊情况是某些情况下我们需要强制读主库,针对这种情况,我们定义一个主键,用该注解标注的就读主库
public @interface Master {
}
测试
@PutMapping("/addUserInfo")
public String addUserInfo(){
userService.addUserInfo();
return "";
}
@Override
public void addUserInfo() {
User user = new User();
user.setId(3);
user.setPassWord("123456");
user.setRealName("测试名字");
user.setUserName("用户名");
userMapper.addUserInfo(user);
}
执行查询和添加接口
结果如下
项目中已整合mybatis和jpa组合使用 ,并实现了它们各自的读写分离