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组合使用 ，并实现了它们各自的读写分离

项目GitHub地址