网上关于动态数据源配置的博文一搜一大堆,都是拿来主义,往往把需要的人弄得不是太明白,也没有个具体的好用的简单的demo例子供人参考,本篇,我也是拿来主义,,我拿来核心的core,进行demo案列整理,我只挑重要的部分讲,demo会在最后提供GitHub下载
(
博主 2018年3月16日14:26:47
注: 这种多数据源的动态切换确实可以解决数据的主从分库操作,但是却有一个致命的BUG,那就是事务不但失效而且无法实现
一致性,因为涉及到跨库,因此我们必须另想办法来实现事务的ACID原则,下一篇,我会讲解如何利用atomikos来实现分布式事务的管理和应用。
Atomikos 是一个为Java平台提供增值服务的并且开源类事务管理器
)
一、项目目录结构图
二、多数据源SQL结构设计如下(简单主从关系)
sql脚本最后附上
三、多数据源注册(拿来主义)
DynamicDataSourceRegister.java
-
package com.appleyk.config;
-
-
import java.sql.ResultSet;
-
import java.sql.SQLException;
-
import java.util.HashMap;
-
import java.util.List;
-
import java.util.Map;
-
-
import javax.sql.DataSource;
-
-
import org.mybatis.spring.annotation.MapperScan;
-
import org.slf4j.Logger;
-
import org.slf4j.LoggerFactory;
-
import org.springframework.beans.MutablePropertyValues;
-
import org.springframework.beans.PropertyValues;
-
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
-
import org.springframework.beans.factory.support.GenericBeanDefinition;
-
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
-
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
-
import org.springframework.boot.bind.RelaxedDataBinder;
-
import org.springframework.boot.bind.RelaxedPropertyResolver;
-
import org.springframework.boot.context.properties.EnableConfigurationProperties;
-
import org.springframework.context.EnvironmentAware;
-
import org.springframework.context.annotation.Bean;
-
import org.springframework.context.annotation.Configuration;
-
import org.springframework.context.annotation.ImportBeanDefinitionRegistrar;
-
import org.springframework.core.convert.ConversionService;
-
import org.springframework.core.convert.support.DefaultConversionService;
-
import org.springframework.core.env.Environment;
-
import org.springframework.core.type.AnnotationMetadata;
-
import org.springframework.jdbc.core.JdbcTemplate;
-
import org.springframework.jdbc.core.RowMapper;
-
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
-
import org.springframework.transaction.PlatformTransactionManager;
-
import org.springframework.transaction.annotation.EnableTransactionManagement;
-
-
import com.appleyk.datasource.DynamicDataSource;
-
import com.appleyk.datasource.DynamicDataSourceContextHolder;
-
import com.appleyk.pojo.DataSourceInfo;
-
-
/**
-
*
-
* 功能描述:动态数据源注册 启动动态数据源请在启动类中(如Start)
-
* 添加 @Import(DynamicDataSourceRegister.class)
-
*/
-
@Configuration
-
@EnableTransactionManagement
-
@EnableConfigurationProperties(DataSourceProperties.class)
-
@MapperScan(
"com.appleyk")
-
public
class DynamicDataSourceRegister implements ImportBeanDefinitionRegistrar, EnvironmentAware {
-
-
private
static
final Logger logger = LoggerFactory.getLogger(DynamicDataSourceRegister.class);
-
-
private ConversionService conversionService =
new DefaultConversionService();
-
private PropertyValues dataSourcePropertyValues;
-
-
// 如配置文件中未指定数据源类型,使用该默认值
-
private
static
final Object DATASOURCE_TYPE_DEFAULT =
"org.apache.tomcat.jdbc.pool.DataSource";
-
-
// 数据源
-
private DataSource defaultDataSource;
-
private Map<String, DataSource> customDataSources =
new HashMap<>();
-
-
@Override
-
public void registerBeanDefinitions(AnnotationMetadata importingClassMetadata, BeanDefinitionRegistry registry) {
-
Map<Object, Object> targetDataSources =
new HashMap<Object, Object>();
-
// 将主数据源添加到更多数据源中
-
targetDataSources.put(
"dataSource", defaultDataSource);
-
DynamicDataSourceContextHolder.dataSourceIds.add(
"dataSource");
-
-
// 添加更多数据源
-
targetDataSources.putAll(customDataSources);
-
for (String key : customDataSources.keySet()) {
-
DynamicDataSourceContextHolder.dataSourceIds.add(key);
-
}
-
-
// 创建DynamicDataSource
-
GenericBeanDefinition beanDefinition =
new GenericBeanDefinition();
-
beanDefinition.setBeanClass(DynamicDataSource.class);
-
beanDefinition.setSynthetic(
true);
-
MutablePropertyValues mpv = beanDefinition.getPropertyValues();
-
mpv.addPropertyValue(
"defaultTargetDataSource", defaultDataSource);
-
mpv.addPropertyValue(
"targetDataSources", targetDataSources);
-
registry.registerBeanDefinition(
"dataSource", beanDefinition);
-
-
System.err.println(
"动态数据源注册成功,从数据源个数 == " + customDataSources.size());
-
}
-
-
/**
-
* 创建DataSource
-
*
-
* @param type
-
* @param driverClassName
-
* @param url
-
* @param username
-
* @param password
-
* @return
-
*/
-
@SuppressWarnings(
"unchecked")
-
public DataSource buildDataSource(Map<String, Object> dsMap) {
-
try {
-
Object type = dsMap.get(
"type");
-
-
if (type ==
null)
-
type = DATASOURCE_TYPE_DEFAULT;
// 默认DataSource
-
-
Class<? extends DataSource> dataSourceType;
-
dataSourceType = (Class<? extends DataSource>) Class.forName((String) type);
-
-
String driverClassName = dsMap.get(
"driver-class-name").toString();
-
String url = dsMap.get(
"url").toString();
-
String username = dsMap.get(
"username").toString();
-
String password = dsMap.get(
"password").toString();
-
-
DataSourceBuilder factory = DataSourceBuilder.create().driverClassName(driverClassName).url(url)
-
.username(username).password(password).type(dataSourceType);
-
-
return factory.build();
-
}
catch (ClassNotFoundException e) {
-
e.printStackTrace();
-
}
-
return
null;
-
}
-
-
/**
-
* 加载多数据源配置
-
*/
-
@Override
-
public void setEnvironment(Environment env) {
-
initDefaultDataSource(env);
-
initCustomDataSources(env);
-
}
-
-
/**
-
* 初始化主数据源
-
*
-
*/
-
private void initDefaultDataSource(Environment env) {
-
-
// 读取主数据源
-
RelaxedPropertyResolver propertyResolver =
new RelaxedPropertyResolver(env,
"spring.datasource.");
-
Map<String, Object> dsMap =
new HashMap<>();
-
dsMap.put(
"type", propertyResolver.getProperty(
"type"));
-
dsMap.put(
"driver-class-name", propertyResolver.getProperty(
"driver-class-name"));
-
dsMap.put(
"url", propertyResolver.getProperty(
"url"));
-
dsMap.put(
"username", propertyResolver.getProperty(
"username"));
-
dsMap.put(
"password", propertyResolver.getProperty(
"password"));
-
-
defaultDataSource = buildDataSource(dsMap);
-
-
dataBinder(defaultDataSource, env);
-
-
}
-
-
/**
-
* 为DataSource绑定更多数据
-
*
-
* @param dataSource
-
* @param env
-
*/
-
private void dataBinder(DataSource dataSource, Environment env) {
-
RelaxedDataBinder dataBinder =
new RelaxedDataBinder(dataSource);
-
dataBinder.setConversionService(conversionService);
-
dataBinder.setIgnoreNestedProperties(
false);
// false
-
dataBinder.setIgnoreInvalidFields(
false);
// false
-
dataBinder.setIgnoreUnknownFields(
true);
// true
-
-
if (dataSourcePropertyValues ==
null) {
-
Map<String, Object> rpr =
new RelaxedPropertyResolver(env,
"spring.datasource").getSubProperties(
".");
-
Map<String, Object> values =
new HashMap<>(rpr);
-
// 排除已经设置的属性
-
values.remove(
"type");
-
values.remove(
"driver-class-name");
-
values.remove(
"url");
-
values.remove(
"username");
-
values.remove(
"password");
-
dataSourcePropertyValues =
new MutablePropertyValues(values);
-
}
-
dataBinder.bind(dataSourcePropertyValues);
-
}
-
-
/**
-
* 初始化更多数据源
-
*
-
*/
-
private void initCustomDataSources(Environment env) {
-
// 读取库表中datasource获取更多数据源
-
-
Map<String, Map<String, Object>> customInfo = getCustomDataSourceInfo();
-
for (String key : customInfo.keySet()) {
-
Map<String, Object> dsMap = customInfo.get(key);
-
DataSource ds = buildDataSource(dsMap);
-
try {
-
// 判断一下 数据源是否连接成功
-
ds.getConnection();
-
}
catch (SQLException e) {
-
e.printStackTrace();
-
}
-
-
customDataSources.put(key, ds);
-
dataBinder(ds, env);
-
}
-
}
-
-
private Map<String, Map<String, Object>> getCustomDataSourceInfo() {
-
Map<String, Map<String, Object>> customMap =
new HashMap<>();
-
// 从主库的slave表中,读取出从库slave的连接信息
-
String sql =
"select url,username,password from slave";
-
JdbcTemplate jdbcTemplate =
new JdbcTemplate(defaultDataSource);
-
List<DataSourceInfo> infos = jdbcTemplate.query(sql,
new RowMapper<DataSourceInfo>() {
-
@Override
-
public DataSourceInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
-
DataSourceInfo info =
new DataSourceInfo();
-
info.setType(
"com.alibaba.druid.pool.DruidDataSource");
-
info.setDriverClassName(
"com.mysql.jdbc.Driver");
-
info.setUrl(rs.getString(
"url"));
-
info.setPassWord(rs.getString(
"password"));
-
info.setUserName(rs.getString(
"username"));
-
// 从库名称:slave -- 对应后面的数据源注解里面的name属性
-
// 这个地方可以表slave加个字段,字段名ds,值存数据源的名称
-
info.setDsName(
"slave");
-
return info;
-
}
-
});
-
for (DataSourceInfo info : infos) {
-
Map<String, Object> dsMap =
new HashMap<>();
-
dsMap.put(
"type", info.getType());
-
dsMap.put(
"driver-class-name", info.getDriverClassName());
-
dsMap.put(
"url", info.getUrl());
-
dsMap.put(
"username", info.getUserName());
-
dsMap.put(
"password", info.getPassWord());
-
customMap.put(info.getDsName(), dsMap);
-
}
-
return customMap;
-
}
-
-
@Bean
-
public PlatformTransactionManager masterTransactionManager() {
-
System.err.println(
"masterTransactionManager=========配置主数据库的事务");
-
return
new DataSourceTransactionManager(defaultDataSource);
-
}
-
-
@Bean
-
public PlatformTransactionManager slaveTransactionManager() {
-
System.err.println(
"slaveTransactionManager=========配置从数据库的事务");
-
return
new DataSourceTransactionManager(customDataSources.get(
"slave"));
-
}
-
-
}
注意,数据源注册成功后,如果不手动配置各自的事务,会导致后面的数据源表面虽切换成功,但是在默认的同事务下,主从业务执行的时候仍然使用的是默认的主库数据源,也就是会造成“数据源切换失效”(事务一旦开启,Connection就不能再改变)
因此,我们需要在Spring-Boot里,根据数据源的名称向spring容器中注入各自的事务(Bean),当然name我们是知道的
四、AOP实现数据源的动态切换(拿来主义)
-
package com.appleyk.aop;
-
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.slf4j.Logger;
-
import org.slf4j.LoggerFactory;
-
import org.springframework.stereotype.Component;
-
-
import com.appleyk.annotation.DataSource;
-
import com.appleyk.datasource.DynamicDataSourceContextHolder;
-
-
@Aspect
-
@Component
-
public
class DynamicDataSourceAspect {
-
private
static
final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
-
-
@Before(
"@annotation(ds)")
-
public void changeDataSource(JoinPoint point, DataSource ds) throws Throwable {
-
String dsId = ds.name();
-
System.err.println(dsId);
-
if (!DynamicDataSourceContextHolder.containsDataSource(dsId)) {
-
System.err.println(
"数据源[{"+ds.name()+
"}]不存在,使用默认数据源 >"+point.getSignature());
-
}
else {
-
System.err.println(
"Use DataSource : "+ds.name()+
">"+point.getSignature() );
-
DynamicDataSourceContextHolder.setDataSourceType(ds.name());
-
}
-
}
-
-
@After(
"@annotation(ds)")
-
public void restoreDataSource(JoinPoint point, DataSource ds) {
-
System.err.println(
"Revert DataSource : "+ds.name()+
" > "+point.getSignature());
-
DynamicDataSourceContextHolder.clearDataSourceType();
-
-
}
-
}
五、Service层实现不同业务的多数据源切换(使用注解)
(3)二者事务虽然不一样,但可以根据各自的业务执行状态进行整个object对象存储的事务控制,比如,先入主库,如果主库insert成功,才向下执行从库的对象存储,如果主库事务回滚,我们可以手动抛出异常,并终止从库的数据操作;
同理,从库的service操作也可以进行相应的事物控制(事物没有进行过多的验证,可自行解决)
六、Controller层提供restful风格的API接口
ObjectController.java
-
package com.appleyk.controller;
-
-
import org.springframework.beans.factory.annotation.Autowired;
-
import org.springframework.web.bind.annotation.PostMapping;
-
import org.springframework.web.bind.annotation.RequestBody;
-
import org.springframework.web.bind.annotation.RequestMapping;
-
import org.springframework.web.bind.annotation.RestController;
-
-
import com.appleyk.entity.A;
-
import com.appleyk.result.ResponseMessage;
-
import com.appleyk.result.ResponseResult;
-
import com.appleyk.service.ObjectService;
-
-
@RestController
-
@RequestMapping(
"/rest/v1.0.1/object")
-
public
class ObjectController {
-
-
@Autowired
-
private ObjectService objService;
-
-
@PostMapping(
"/save")
-
public ResponseResult SaveObject(@RequestBody A a) throws Exception {
-
-
if (objService.Save(a)) {
-
return
new ResponseResult(ResponseMessage.OK);
-
}
-
return
new ResponseResult(ResponseMessage.INTERNAL_SERVER_ERROR);
-
}
-
}
注意:master的a表和slave的b表结构一样,只是名称不一样
object对象 分 --- A实体对象对应主库master的a表,B实体(A实体对象构造而来)对象对应从库slave的b表
master -- a表
slave -- b表
七、API接口测试(实现object对象的分库存储)
{
"name": "appleyk",
"sex": "F",
"age":27
}
2.Spring-Boot启动
3.接口测试
a表
b表
控制台数据源切换信息打印
八、项目GitHub地址
<script>
(function(){
function setArticleH(btnReadmore,posi){
var winH = $(window).height();
var articleBox = $("div.article_content");
var artH = articleBox.height();
if(artH > winH*posi){
articleBox.css({
'height':winH*posi+'px',
'overflow':'hidden'
})
btnReadmore.click(function(){
if(typeof window.localStorage === "object" && typeof window.csdn.anonymousUserLimit === "object"){
if(!window.csdn.anonymousUserLimit.judgment()){
window.csdn.anonymousUserLimit.Jumplogin();
return false;
}else if(!currentUserName){
window.csdn.anonymousUserLimit.updata();
}
}
articleBox.removeAttr("style");
$(this).parent().remove();
})
}else{
btnReadmore.parent().remove();
}
}
var btnReadmore = $("#btn-readmore");
if(btnReadmore.length>0){
if(currentUserName){
setArticleH(btnReadmore,3);
}else{
setArticleH(btnReadmore,1.2);
}
}
})()
</script>
</article>