springboot/mybaties/aop实现数据库读写分离

         数据库实现主从之后，相应的，在应用层面，我们也可以实现数据库的读写分离。

       原理是：依靠AbstractRoutingDataSource，基于特定的查找key路由到特定的数据源。它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。

       DataSource中存在一个getConnection()方法：该方法时提供一个Connection对象，简单来说就是我们配置的不同的DataSource。

public interface DataSource  extends CommonDataSource, Wrapper {/*** <p>Attempts to establish a connection with the data source that* this {@code DataSource} object represents.** @return  a connection to the data source* @exception SQLException if a database access error occurs* @throws java.sql.SQLTimeoutException  when the driver has determined that the* timeout value specified by the {@code setLoginTimeout} method* has been exceeded and has at least tried to cancel the* current database connection attempt*/Connection getConnection() throws SQLException;
}

       这个方法是由各个实现类实现的。在连接数据库之前会调用这个方法获取连接对象Connection。所以AbstractRoutingDataSource实现这个方法就根据路由策略来获取不同的DataSource的Vonnection。

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {//存储了所有的datasource信息private Map<Object, DataSource> resolvedDataSources;//默认的数据库private DataSource resolvedDefaultDataSource;...省略public Connection getConnection() throws SQLException {return this.determineTargetDataSource().getConnection();}public Connection getConnection(String username, String password) throws SQLException {return this.determineTargetDataSource().getConnection(username, password);}/*** 调用路由方法获取指定的DourceSource*/protected DataSource determineTargetDataSource() {Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");//获取 连接的dataSource的resolvedDataSources 的keyObject lookupKey = this.determineCurrentLookupKey();获取 连接的dataSourceDataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);if (dataSource == null && (this.lenientFallback || lookupKey == null)) {dataSource = this.resolvedDefaultDataSource;}if (dataSource == null) {throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");} else {return dataSource;}}
/*** 需要实现的路由方法，就是返回  resolvedDataSources 的key*/protected abstract Object determineCurrentLookupKey();
}

       首先准备好三个数据源配置：这里由于环境原因，所以使用同一个IP的三个不同数据库来演示

mysql.datasource.type=com.zaxxer.hikari.HikariDataSourcemysql.datasource.write.driver-class-name=com.mysql.jdbc.Driver
mysql.datasource.write.jdbc-url=jdbc:mysql://127.0.0.1:3306/zhongdemo?useSSL=false&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
mysql.datasource.write.username=root
mysql.datasource.write.password=zhong123456
mysql.datasource.write.minimum-idle=10
mysql.datasource.write.maximum-pool-size=20
mysql.datasource.write.idle-timeout=30000
mysql.datasource.write.max-lifetime=1800000
mysql.datasource.write.connection-timeout=30000
mysql.datasource.write.connection-test-query=SELECT 1
mysql.datasource.write.validation-timeout=3000mysql.datasource.read1.driver-class-name=com.mysql.jdbc.Driver
mysql.datasource.read1.jdbc-url=jdbc:mysql://127.0.0.1:3306/zhong_test_1?useSSL=false&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
mysql.datasource.read1.username=root
mysql.datasource.read1.password=zhong123456
mysql.datasource.read1.minimum-idle=10
mysql.datasource.read1.maximum-pool-size=20
mysql.datasource.read1.idle-timeout=30000
mysql.datasource.read1.max-lifetime=1800000
mysql.datasource.read1.connection-timeout=30000
mysql.datasource.read1.connection-test-query=SELECT 1
mysql.datasource.read1.validation-timeout=3000mysql.datasource.read2.driver-class-name=com.mysql.jdbc.Driver
mysql.datasource.read2.jdbc-url=jdbc:mysql://127.0.0.1:3306/zhong_test_2?useSSL=false&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
mysql.datasource.read2.username=root
mysql.datasource.read2.password=zhong123456
mysql.datasource.read2.minimum-idle=10
mysql.datasource.read2.maximum-pool-size=20
mysql.datasource.read2.idle-timeout=30000
mysql.datasource.read2.max-lifetime=1800000
mysql.datasource.read2.connection-timeout=30000
mysql.datasource.read2.connection-test-query=SELECT 1
mysql.datasource.read2.validation-timeout=3000

pom依赖：

 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>2.0.0</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.0</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId></dependency><dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId><version>2.7.9</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.11</version></dependency>

首先配置三个数据源信息并注入到容器中：

@Configuration
@PropertySource("classpath:mysql-pro.properties")
public class DataSourceConfig {@Value("${mysql.datasource.type}")private Class<? extends javax.sql.DataSource> dataSourceType;/*** 读数据源* @return*/@Bean(name ="writeDataSource")@ConfigurationProperties("mysql.datasource.write")public DataSource writeDataSource(){return DataSourceBuilder.create().type(dataSourceType).build();}/*** 读数据源  / 从数据源1* @return*/@Bean(name = "read1DataSource")@ConfigurationProperties("mysql.datasource.read1")public DataSource readDataSource(){return DataSourceBuilder.create().type(dataSourceType).build();}/*** 读数据源 / 从数据源2* @return*/@Bean(name = "read2DataSource")@ConfigurationProperties("mysql.datasource.read2")public DataSource read2DataSource(){return DataSourceBuilder.create().type(dataSourceType).build();}}

数据库枚举类： 

public enum DBTypeEnum {MASTER("write"),SLAVE1("read1"),SLAVE2("read2");private String type;DBTypeEnum(String type) {this.type = type;}public String getType() {return type;}public void setType(String type) {this.type = type;}
}

     接下来我们需要考虑怎么在每一个数据库访问之前切换到我们想用的数据库。采用的是Aop+ThreadLocal 来实现功能，aop切面编程可以实现在指定的方法之前执行指定方法。ThreadLocal提供线程内部对象访问，隔离开不同线程。

/*** 存储当前线程的使用的是读库 还是写库* 在在调用具体的  数据 到方法时  由切面变成传入。*/
public class DataSourceContextHolder {private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();/*** 切面方法调用，设置数据源* @param dbType 数据库类型*/public static void set(String dbType) {contextHolder.set(dbType);System.out.println("当前线程使用使用数据源："+ dbType);}/*** 路由方法调用，获取当前线程需要的数据源* @return*/public static String getReadOrWrite() {return contextHolder.get();}
}

       然后我们需要配置数据库连接的工厂SqlSessionFactory，并指定对应的DataSource为AbstractRoutingDataSource。

@Configuration
@PropertySource("classpath:mysql-pro.properties")
public class DataSourceConfig {@Value("${mysql.datasource.type}")private Class<? extends javax.sql.DataSource> dataSourceType;/*** 路由数据源-AbstractRoutingDataSource:数据库选择数据源时会根据路由的方法(determineCurrentLookupKey)获取指定的数据源* @param writeDataSource 写数据源* @param read1DataSource  读数据源1 / 从数据源1* @param read2DataSource  读数据源2 / 从数据源2* @return*/@Bean(name = "routingDataSource")public DataSource routingDataSource(DataSource writeDataSource,DataSource read1DataSource,DataSource read2DataSource){Map<Object, Object> targetDataSources = new HashMap<>();targetDataSources.put(DBTypeEnum.MASTER.getType(), writeDataSource);targetDataSources.put(DBTypeEnum.SLAVE1.getType(), read1DataSource);targetDataSources.put(DBTypeEnum.SLAVE2.getType(), read2DataSource);AbstractRoutingDataSource myRoutingDataSource = abstractRoutingDataSource(2);//设置默认的路由myRoutingDataSource.setDefaultTargetDataSource(writeDataSource);//其他路由目的数据源myRoutingDataSource.setTargetDataSources(targetDataSources);return myRoutingDataSource;}/*** 数据源session 工厂* @return* @throws Exception*/@Beanpublic SqlSessionFactory sqlSessionFactory(DataSource routingDataSource) throws Exception {SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(routingDataSource);//配置mapp路径sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/**/*.xml"));return sqlSessionFactoryBean.getObject();}/*** 事务管理* @param routingDataSource* @return*/@Beanpublic PlatformTransactionManager platformTransactionManager(DataSource routingDataSource) {return new DataSourceTransactionManager(routingDataSource);}/*** 获取 数据源路由的方法，这里会筛选出具体需要使用哪一个数据源* @param salveSize   读数据源 / 从数据源的数量。* @return*/private AbstractRoutingDataSource abstractRoutingDataSource(int salveSize){return new AbstractRoutingDataSource() {AtomicInteger connectTimes = new AtomicInteger(0);//实现路由，选择对应的数据源@Overrideprotected Object determineCurrentLookupKey() {String typeKey = DataSourceContextHolder.getReadOrWrite();if (typeKey.equals(DBTypeEnum.MASTER.getType())){System.out.println("使用数据库write.............");return DBTypeEnum.MASTER.getType();}//读库， 简单负载均衡int number = connectTimes.getAndAdd(1);int lookupKey = number % salveSize + 1;String name = "read" + lookupKey;System.out.println("number=" + number + " salveSize=" + salveSize + " lookupKey=" + lookupKey + " name=" + name);for(DBTypeEnum dbTypeEnum : DBTypeEnum.values()){if(name.equals(dbTypeEnum.getType())){System.out.println("使用数据库"+dbTypeEnum.getType());return dbTypeEnum.getType();}}System.out.println("默认使用数据库write.............");return DBTypeEnum.MASTER.getType();}};}
}

 接下来是实现mybaties查询数据库：

dao：

@Mapper
@Repository
public interface UserDao {List<UserDto> getUsers();int insertUser(@Param("userDto") UserDto userDto);
}

service:

@Service
public class UserService {@Autowiredprivate UserDao userDao;public List<UserDto> getUsers(){return userDao.getUsers();}public int insertUser(UserDto userDto){return userDao.insertUser(userDto);}
}

mapper: user.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zhong.spring.mysqldemo.dao.UserDao"><resultMap id="userMap" type="com.zhong.spring.mysqldemo.dto.UserDto"><id property="id" column="id"></id><result property="name" column="name"></result><result property="age" column="age"></result><result property="address" column="address"></result><result property="createTime" column="create_time"></result></resultMap><select id="getUsers" resultMap="userMap">SELECT * FROM demo_user LIMIT 5</select><insert id="insertUser" parameterType="com.zhong.spring.mysqldemo.dto.UserDto">INSERT INTO demo_user(id,name,age,address,create_time) values (#{userDto.id},#{userDto.name},#{userDto.age},#{userDto.address},#{userDto.createTime})</insert>
</mapper>

最后是aop：

/*** 切面，切换数据库*/
@Aspect
@Component
public class DataSourceAop {@Pointcut(" (execution(* com.zhong.spring.mysqldemo.dao..*.get*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.find*(..)))" +"|| execution(* com.zhong.spring.mysqldemo.dao..*.count*(..)))")public void readPointcut() {}@Pointcut(" execution(* com.zhong.spring.mysqldemo.dao..*.insert*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.add*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.update*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.edit*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.delete*(..)) " +"|| execution(* com.zhong.spring.mysqldemo.dao..*.remove*(..))")public void writePointcut() {}@Before("readPointcut()")public void read() {DataSourceContextHolder.set("read");}@Before("writePointcut()")public void write() {DataSourceContextHolder.set("write");}
}

到这里我们的工作基本上完成了。

接下来是测试：

由于我的启动类和测试项目不在一个moudel，分属不同的jar，所以启动类有所变化。但是@MapperScan("com.zhong.spring.mysqldemo.dao") // Dao扫描路径(必须)

@MapperScan("com.zhong.spring.mysqldemo.dao") // Dao扫描路径(必须)
@ComponentScan("com.zhong.spring")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class UsulDemoApplication {public static void main(String[] args) {SpringApplication.run(UsulDemoApplication.class, args);}
}

测试接口：

@RestController
@RequestMapping("/datasource")
public class DataSourceController {@AutowiredUserService userService;@GetMapping("/getUsers")BaseResult<List<UserDto>> getUsers(){return BaseResult.success(userService.getUsers());}@PostMapping("/insert")BaseResult<String> insertUser(@RequestBody UserDto userDto){userService.insertUser(userDto);return BaseResult.success("插入成功");}@GetMapping("/test")BaseResult<String> test(){return BaseResult.success("测试");}
}

在不同的数据库建相同的表，并插入不同的数据;

使用postman调用插入接口：

刷新数据库：

 调用查询方法：

 可以看到时成功了的。再看看打印的日志：

 成功，结束。

提示，在springboot下配置多数据源时如果需要打印sql日志。可以配置：

logging:config: classpath:loback.xmllevel:#这个是dao的路径com.zhong.spring.mybatiesdemo.dao: DEBUGcom.zhong.spring.mysqldemo.dao: DEBUG

如果配置了这个也不打印，如果你使用的logsj。那么检查一下你的日志配置文件(例如上方配置的logback.xml)钟配置的日志级别是否是debug，或者对应的配置是否过滤了debug的日志。如果是info那么是不会打印的。

注意：

@Transactional 等涉及到的注解 标注的方法也会执行 determineCurrentLookupKey() ，但是并不会触发DataSourceAop ，所以不会设置数据源，会使用默认的数据源。这个会导致原有的分库测策略失败。