背景
最近做一项目，公司数据库用的主从结构，以前做项目都只是用的单数据库，网上扒了两天，终于搞定自认为最优雅的方式。
方案
网上扒了下，实现方式大致分为两种
将不同库操作分开放进不同的mapper，配置两个数据源
- 优点：配置简单，只用增加一个数据源即可。
- 缺点：复杂化了mapper，若做读写分离，对同一个dao，需要两个mapper。甚至，使用3库或者更过，则需要配置2套以上mapper。
配置动态数据源，使用aop进行动态切换，真正实现动态读写分离
- 优点：读写分离，轻松实现多库操作，配置好数据源后，真实使用时，只用在dao层方法添加注解即可实现指定数据源的操作；mapper只用一套。
- 缺点：配置较复杂。
实现
权衡了一下，选择了第二种方法，实现如下
数据源配置
- spring boot 配置
spring:
datasource:
db-master:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/bus_data?serverTimezone=UTC&useSSL=true
username: bus_admin
password: adminpass
db-read:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/bus_data_read?serverTimezone=UTC&useSSL=true
username: bus_admin
password: adminpass
- 数据源
package com.levy.mysql;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* Created by li_weia on 2017/7/6.
*/
@Configuration
public class DataSourceConfig {
@Bean(name = "dynamicSource")
public DynamicDataSource dataSource(@Qualifier("dbMaster")DataSource dbMaster,
@Qualifier("dbRead")DataSource dbRead) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(dbMaster);
// 配置多数据源
Map<Object, Object> dsMap = new HashMap<>(5);
dsMap.put("db-master", dbMaster);
dsMap.put("db-read", dbRead);
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.db-master")
public DataSource dbMaster() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.db-read")
public DataSource dbRead() {
return DataSourceBuilder.create().build();
}
}
SessionFactory配置
package com.levy.mysql;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
/**
* Created by li_weia on 2017/7/6.
*/
@Configuration
@MapperScan(basePackages = {"com.levy.dao.mapper"}, sqlSessionFactoryRef = "dynamicSqlSessionFactory")
public class DynamicDbConfig {
private static String MYBATIS_CONFIG = "mybatis/mybatis-config.xml";
/** * mybatis mapper resource 路径 */
private static String MAPPER_PATH = "mybatis/mapper/**.xml";
private final DataSource dynamicDS;
@Autowired
public DynamicDbConfig(DataSource dynamicSource) {
this.dynamicDS = dynamicSource;
}
@Bean
public SqlSessionFactory dynamicSqlSessionFactory() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setConfigLocation(new ClassPathResource(MYBATIS_CONFIG));
PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
String packageSearchPath = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + MAPPER_PATH;
factoryBean.setMapperLocations(pathMatchingResourcePatternResolver.getResources(packageSearchPath));
factoryBean.setDataSource(dynamicDS);
return factoryBean.getObject();
}
}
新建一个ContextHolder类持有当前线程数据源
package com.levy.mysql;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Created by li_weia on 2017/7/6.
*/
class DataSourceContextHolder {
private static final Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);
/**
* 默认数据源
*/
static final String DEFAULT_DS = "db-master";
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
// 设置数据源名
static void setDB(String dbType) {
log.debug("切换到{}数据源", dbType);
contextHolder.set(dbType);
}
// 获取数据源名
static String getDB() {
return contextHolder.get();
}
// 清除数据源名
static void clearDB() {
contextHolder.remove();
}
}
继承AbstractRoutingDataSource类，让spring知道使用动态数据源
package com.levy.mysql;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* Created by li_weia on 2017/7/6.
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
log.debug("数据源为{}", DataSourceContextHolder.getDB());
return DataSourceContextHolder.getDB();
}
}
创建annotation用于aop入口
package com.levy.mysql;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by li_weia on 2017/7/6.
*/
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface DBSource {
String value() default "db-master";
}
实现aop
package com.levy.mysql;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* Created by li_weia on 2017/7/6.
*/
@Aspect
@Component
public class DynamicDataSourceAspect {
@Before("@annotation(com.levy.mysql.DBSource)")
public void beforeSwitchDS(JoinPoint point){
//获得当前访问的class
Class<?> className = point.getTarget().getClass();
//获得访问的方法名
String methodName = point.getSignature().getName();
//得到方法的参数的类型
Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
String dataSource = DataSourceContextHolder.DEFAULT_DS;
try {
// 得到访问的方法对象
Method method = className.getMethod(methodName, argClass);
// 判断是否存在@DS注解
if (method.isAnnotationPresent(DBSource.class)) {
DBSource annotation = method.getAnnotation(DBSource.class);
// 取出注解中的数据源名
dataSource = annotation.value();
}
} catch (Exception e) {
e.printStackTrace();
}
// 切换数据源
DataSourceContextHolder.setDB(dataSource);
}
@After("@annotation(com.levy.mysql.DBSource)")
public void afterSwitchDS(JoinPoint point){
DataSourceContextHolder.clearDB();
}
}
程序入口SpringBootApplication注解需exclude掉DataSourceAutoConfiguration
package com.levy;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication(exclude = {
DataSourceAutoConfiguration.class
})
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
dao配置
package com.levy.dao;
import com.levy.dao.mapper.UserMapper;
import com.levy.entity.User;
import com.levy.mysql.DBSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* Created by li_weia on 2017/7/7.
*/
@Repository
public class UserDao {
private final UserMapper userMapper;
@Autowired(required = false)
public UserDao(UserMapper userMapper) {
this.userMapper = userMapper;
}
@DBSource("db-read")
public List<User> getAllSite(){
return userMapper.getAllSite();
}
@DBSource("db-master")
public User getUserById(int id){
return userMapper.getUserById(id);
}
}
全部代码可以看这里：demo
