mysql配合mycat读写分离实现动态数据源

本文承接MYCAT安装和配置使用（实现mysql读写分离）

在实现数据库读写分离的情况下实现代码读写分离动态选择数据源

前文中 mysql3009是主库 可以写入操作 而mysql3008只能进行读取操作

本文利用 AbstractRoutingDatasource实现业务代码中动态的选择读取或写入操作的数据源

pom.xml

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<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.2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.0.23</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

1创建读写数据源

配置application.yml

spring:
  datasource:
    update: 
      jdbc-url: jdbc:mysql://192.168.43.66:8066/mycat_testdb
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: root
    select: 
      jdbc-url: jdbc:mysql://192.168.43.66:8066/mycat_testdb
      driver-class-name: com.mysql.jdbc.Driver
      username: user
      password: user
    type: com.alibaba.druid.pool.DruidDataSource

其中 jdbc-url为mycat配置的虚拟数据库

用户root有写入权限  user为只读权限 详细参照mycat的server.xml文件配置

DatasourceConfig

@Configuration
public class DatasourceConfig {
	
	@Bean
	@ConfigurationProperties(prefix="spring.datasource.select")
	public DataSource selectDataSource(){
		return DataSourceBuilder.create().build();
	}
	
	@Bean
	@ConfigurationProperties(prefix="spring.datasource.update")
	public DataSource updateDataSource(){
		return DataSourceBuilder.create().build();
	}
}

DataSourceContextHolder 用于获取当前线程数据源并设置

@Component
public class DataSourceContextHolder {
	
	private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

	// 设置数据源类型
	public static void setDbType(String dbType) {
		contextHolder.set(dbType);
	}

	public static String getDbType() {
		return contextHolder.get();
	}

	public static void clearDbType() {
		contextHolder.remove();
	}

}

2将数据源注册到AbstractRoutingDataSource

@Primary
@Component
public class DynamicDatasource extends AbstractRoutingDataSource{
	@Autowired
	@Qualifier("selectDataSource")
	private DataSource selectDataSource;
	@Autowired
	@Qualifier("updateDataSource")
	private DataSource updateDataSource;
	
	@Override
	protected Object determineCurrentLookupKey() {
		String dbType = DataSourceContextHolder.getDbType();
		System.out.println("当前数据源类型是："+dbType);
		return dbType;
	}
	
	/**
	 * 配置数据源信息
	 */
	@Override
	public void afterPropertiesSet() {
		Map<Object, Object> map = new HashMap<>();
		map.put("selectDataSource", selectDataSource);
		map.put("updateDataSource", updateDataSource);
		setTargetDataSources(map);
		setDefaultTargetDataSource(updateDataSource);
		super.afterPropertiesSet();
	}

}

注意 注入读写数据源时要使用@qualifier注解 指定注入数据源 不然会报错 同时类上要加上@primary 首选加载此类

3AOP拦截业务逻辑方法，通过方法名前缀判断是读还是写操作

@Aspect
@Component
public class DataSourceAop {
	
	@Pointcut("execution(* com.xuxu.service.*.*(..))")
	public void cutPoint(){
		
	}
	
	@Before("cutPoint()")
	public void process(JoinPoint joinPoint){
		String methodName = joinPoint.getSignature().getName();
		if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")
				|| methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")) {
			DataSourceContextHolder.setDbType("selectDataSource");
		} else {
			// 切换dataSource
			DataSourceContextHolder.setDbType("updateDataSource");
		}

	}
}

 

测试 

mapper

@Mapper
public interface UserMapper {
	
	@Insert("insert into test (id) values(#{id})")
	public int insert(@Param("id") Integer id);
	
	@Select("select * from test")
	public List<Integer> getAll();
	
	@Update("update test set id=#{id} where id=#{targetId}")
	public int update(@Param("targetId") Integer targetId,@Param("id") Integer id);
}

service

@Service
public class TestDatasourceService {
	@Autowired
	private UserMapper userMapper;
	
	public int insert(Integer id){
		return userMapper.insert(id);
	}
	
	public List<Integer> getAll(){
		return userMapper.getAll();
	}
	
	public int update(Integer targetId,Integer id){
		return userMapper.update(targetId, id);
	}
}

controller

@RestController
public class TestDatasourceController {
	@Autowired
	private TestDatasourceService testService;
	
	@RequestMapping("/insert/{id}")
	public int insert(@PathVariable Integer id){
		return testService.insert(id);
	}
	@RequestMapping("/get")
	public List<Integer> getAll(){
		return testService.getAll();
	}
	@RequestMapping("/update")
	public int update(Integer targetId,Integer id){
		return testService.update(targetId, id);
	}
}

实现动态数据源