在大型应用中对数据进行切分,并且采用多个数据库实例进行管理,这样可以有效提高系统的水平伸缩性。而这样的方案就会不同于常见的单一数据实例的方案,这就要程序在运行时根据当时的请求及系统状态来动态的决定将数据存储在哪个数据库实例中,以及从哪个数据库提取数据,下面是具体的实现方案
1. 必要配置
1.1 依赖包引入
pom文件中引入相关支持
<!-- 与数据库操作相关的依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 支持@ConfigrationProperties注解 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!-- 使用数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.8</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
1.2 数据源配置
给数据源配置文件(datasource.properties)中定义多个数据源配置
jdbc.userdb.driverClassName = com.mysql.jdbc.Driver
jdbc.userdb.url = jdbc:mysql://demo.db:3306/userdb?zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8
jdbc.userdb.username = xxxx
jdbc.userdb.password = xxxx
jdbc.printdb.driverClassName = com.mysql.jdbc.Driver
jdbc.printdb.url = jdbc:mysql://demo.db:3306/printdb?zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8
jdbc.printdb.username = xxxx
jdbc.printdb.password = xxxx
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxActive=10
jdbc.maxWait=60000
jdbc.validationQuery=select 1 from dual
jdbc.testOnBorrow=false
jdbc.testOnReturn=false
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.minEvictableIdleTimeMillis=1200000
jdbc.removeAbandoned=false
jdbc.removeAbandonedTimeOut=1800
jdbc.logAbandoned=true
jdbc.poolPreparedStatements=true
jdbc.maxPoolPreparedStatementPerConnectionSize=20
jdbc.filters=stat,log4j
2. 数据源初始化
2.1 定义动态数据源
实现数据源切换的功能就是自定义一个类扩展AbstractRoutingDataSource抽象类,其实该相当于数据源DataSourcer的路由中介,可以实现在项目运行时根据相应key值切换到对应的数据源DataSource上。
自定义类扩展AbstractRoutingDataSource类时就是要重写determineCurrentLookupKey()方法来实现数据源切换功能。下面是自定义的扩展AbstractRoutingDataSource类的实现:
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
Object object = DatabaseContextHolder.getDatabaseType();
return object;
}
}
DatabaseContextHolder类如下,实现对数据源的操作功能:
public class DatabaseContextHolder {
private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<DatabaseType>();
public static void setDatabaseType(DatabaseType databaseType) {
contextHolder.set(databaseType);
}
public static DatabaseType getDatabaseType() {
return (DatabaseType) contextHolder.get();
}
public static void clearDatabaseType() {
contextHolder.remove();
}
}
新建数据源类型枚举 DatabaseType:
public enum DatabaseType {
USERDB, PRINTDB
}
2.2初始化动态数据源
新建一个基类AbstractBatisBaseConfig,并实现EnvironmentAware接口,通过读取数据源配置文件创建数据源实例DynamicDataSource,数据源实例用阿里的druid管理
public class AbstractBatisBaseConfig implements EnvironmentAware {
private static final Logger log = LoggerFactory.getLogger(AbstractBatisBaseConfig.class);
private Environment env;
@Override
public void setEnvironment(final Environment environment) {
this.env = environment;
}
@Bean
public DataSource userDs() throws Exception {
Properties props = new Properties();
props.put("driverClassName", env.getProperty("jdbc.usersb.driverClassName"));
props.put("url", env.getProperty("jdbc.userdb.url"));
props.put("username", env.getProperty("jdbc.userdb.username"));
props.put("password", env.getProperty("jdbc.userdb.password"));
this.setCommonJDBCProperties(props);
DruidDataSource ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(props);
// List<Filter> filters = new ArrayList<>();
// filters.add(wallFilter());
// ds.setProxyFilters(filters);
return ds;
}
/**
* private WallConfig wallConfig() { WallConfig wconfig = new WallConfig();
* wconfig.setMultiStatementAllow(true); return wconfig; }
*
* private WallFilter wallFilter() { WallFilter wfilter = new WallFilter();
* wfilter.setConfig(wallConfig()); return wfilter; }
**/
@Bean
public DataSource printDs() throws Exception {
Properties props = new Properties();
props.put("driverClassName", env.getProperty("jdbc.printdb.driverClassName"));
props.put("url", env.getProperty("jdbc.printdb.url"));
props.put("username", env.getProperty("jdbc.printdb.username"));
props.put("password", env.getProperty("jdbc.printdb.password"));
this.setCommonJDBCProperties(props);
DruidDataSource ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(props);
// List<Filter> filters = new ArrayList<>();
// filters.add(wallFilter());
// ds.setProxyFilters(filters);
return ds;
}
@Bean
@Primary
public DynamicDataSource dataSource(@Qualifier("userDs") DataSource userDs,
@Qualifier("printDs") DataSource printDs) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.USERDB, userDs);
targetDataSources.put(DatabaseType.PRINTDB, printDs);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
dataSource.setDefaultTargetDataSource(userDs);// 默认的datasource设置为myTestDbDataSource
return dataSource;
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean(DynamicDataSource ds) {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath:com/demo/**/mapper/*.xml"));
return bean.getObject();
} catch (Exception e) {
log.error("sqlSessionFactory创建失败!");
e.printStackTrace();
throw new RuntimeException();
}
}
@Bean
public DataSourceTransactionManager transationManager(DynamicDataSource ds) throws Exception {
return new DataSourceTransactionManager(ds);
}
private void setCommonJDBCProperties(Properties props) {
props.put("initialSize", env.getProperty("jdbc.initialSize"));
props.put("minIdle", env.getProperty("jdbc.minIdle"));
props.put("maxActive", env.getProperty("jdbc.maxActive"));
props.put("maxWait", env.getProperty("jdbc.maxWait"));
props.put("validationQuery", env.getProperty("jdbc.validationQuery"));
props.put("testOnBorrow", env.getProperty("jdbc.testOnBorrow"));
props.put("testOnReturn", env.getProperty("jdbc.testOnReturn"));
props.put("testWhileIdle", env.getProperty("jdbc.testWhileIdle"));
props.put("timeBetweenEvictionRunsMillis", env.getProperty("jdbc.timeBetweenEvictionRunsMillis"));
props.put("minEvictableIdleTimeMillis", env.getProperty("jdbc.minEvictableIdleTimeMillis"));
props.put("removeAbandoned", env.getProperty("jdbc.removeAbandoned"));
props.put("removeAbandonedTimeOut", env.getProperty("jdbc.removeAbandonedTimeOut"));
props.put("logAbandoned", env.getProperty("jdbc.logAbandoned"));
props.put("poolPreparedStatements", env.getProperty("jdbc.poolPreparedStatements"));
props.put("maxPoolPreparedStatementPerConnectionSize",
env.getProperty("jdbc.maxPoolPreparedStatementPerConnectionSize"));
props.put("filters", env.getProperty("jdbc.filters"));
}
新建MyBatisConfig并继承AbstractBatisBaseConfig类:
/**
* springboot集成mybatis的基本入口 1)创建数据源 2)创建SqlSessionFactory
*/
@Configuration
@PropertySource(value = { "classpath:config/datasource.properties",
"file:${spring.profiles.path}/datasource.properties" }, ignoreResourceNotFound = true)
@MapperScan(basePackages = "com.demo.**.mapper")
public class MyBatisConfig extends AbstractBatisBaseConfig {
}
至此,我们数据源就已经初始化好了
3. 切换数据源
3.1手工调用方法切换数据源
一般是在dao层操作数据库前进行切换的,只需在数据库操作前加上如下代码即可:
DatabaseContextHolder.setDatabaseType(DatabaseType.USERDB);
3.2自定义注解实现动态切换
还有一种操作性更强的切换方式,也就是通过AOP的方式,在dao层中需要切换数据源操作的方法或类上写上注解标签
自定义一个注解
/**
* 在方法上使用,用于指定使用哪个数据源
*/
@Target({ ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
public @interface MyDataSource {
DatabaseType type() default DatabaseType.USERDB;
}
新建一个切面类,
@Before和
@After
来定义数据源切换的切入点
@Aspect
@Component
@Order(1)
public class DataSourceApect {
private static final Logger log = LoggerFactory.getLogger(DataSourceApect.class);
@Before("execution(* com.xrq.demo.*.dao.*.*(..))")
public void setDataSourceKey(JoinPoint point) {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
MyDataSource ds = method.getDeclaredAnnotation(MyDataSource.class);
if (null == ds) {
if (log.isDebugEnabled()) {
log.debug("如获取不到MyDataSource类注解,则从目标类获取。");
}
try {
Class<?> targetClass = point.getTarget().getClass();
Method targetMethod = targetClass.getDeclaredMethod(method.getName(), method.getParameterTypes());
if (null != targetMethod) {
ds = targetMethod.getDeclaredAnnotation(MyDataSource.class);
}
} catch (Exception e) {
log.debug("获取目标类注解失败。", e);
}
}
if (null != ds && DatabaseType.PRINTDB.equals(ds.type())) {
log.debug("使用PRINTDB");
DatabaseContextHolder.setDatabaseType(DatabaseType.PRINTDB);
} else {
log.debug("使用USERDB");
DatabaseContextHolder.setDatabaseType(DatabaseType.USERDB);
}
}
@After("execution(* com.xrq.demo.*.dao.*.*(..))")
public void after(JoinPoint point) {
DatabaseContextHolder.clearDatabaseType();
}
}
上面的切面类中我们可以看到如果没找到数据源则设置了一个默认的数据源,定义好切面类后我们就可以用了,下面是在dao的方法上面添加注解指定操作的数据源
@Override
@MyDataSource(type = DatabaseType.PRINTDB)
public long getPrimaryByCode(String code) {
return printerMapper.getPrimaryByCode (code);
}