spring动态数据源

动态数据源切换（多租户）

1 需求

每个租户拥有独立的数据库，在应用中进行数据源动态选择

2 实现原理

继承接口AbstractRoutingDataSource实现多数据源选择，下面是用到的几个重要参数和方法

##目标数据源
private Map<Object, Object> targetDataSources;
##默认目标数据源
private Object defaultTargetDataSource;
##存储已加载的数据源
private Map<Object, DataSource> resolvedDataSources;
##已加载默认数据源
private DataSource resolvedDefaultDataSource;

##设置加载的目标数据源集合
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
	this.targetDataSources = targetDataSources;
}
##设置默认数据源
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
	this.defaultTargetDataSource = defaultTargetDataSource;
}
##重新加载数据源信息
public void afterPropertiesSet() {
	if (this.targetDataSources == null) {
		throw new IllegalArgumentException("Property 'targetDataSources' is required");
	}
	this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
	for (Map.Entry entry : this.targetDataSources.entrySet()) {
		Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
		DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
		this.resolvedDataSources.put(lookupKey, dataSource);
	}
	if (this.defaultTargetDataSource != null) {
		this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
	}
}
##选择目标数据源
protected DataSource determineTargetDataSource() {
		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
		Object lookupKey = determineCurrentLookupKey();
		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 + "]");
		}
		return dataSource;
}
##该方法返回需要使用的DataSource的key值，然后根据这个key从resolvedDataSources这个map里取出对应的DataSource
protected abstract Object determineCurrentLookupKey();

3 需要提前知道的几个知识点

• ThreadLocal的含义及几个方法

• 不通过controller访问数据库的方法

• DynamicDataSource不要采用new的方式使用，可能会导致添加数据源失败，没能真正添加成功

3.1 ThreadLocal的含义及几个方法

3.1.1 ThreadLocal 的作用？

ThreadLocal是解决线程安全问题一个很好的思路，它通过为每个线程提供一个独立的变量副本解决了变量并发访问的冲突问题。在很多情况下，ThreadLocal比直接使用synchronized同步机制解决线程安全问题更简单，更方便，且结果程序拥有更高的并发性。

3.1.2 ThreadLocal的应用场景？

在Java的多线程编程中，为保证多个线程对共享变量的安全访问，通常会使用synchronized来保证同一时刻只有一个线程对共享变量进行操作。这种情况下可以将类变量放到ThreadLocal类型的对象中，使变量在每个线程中都有独立拷贝，不会出现一个线程读取变量时而被另一个线程修改的现象。最常见的ThreadLocal使用场景为用来解决数据库连接、Session管理等。

3.1.3 ThreadLocal的内部方法

public T get() { }

public void set(T value) { }

public void remove() { }

3.2 不通过controller访问数据库的方法

提供ApplicationConext对象

<context:component-scan base-package="net.mopai">
		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
</context:component-scan>
##一定要去除懒加载，不然会导致加载顺序不对，导致无法在service,dao等类前获得applicationContext，从而报空指针异常（applicationContext.xml 与service,dao层放在同一个配置文件中）
<bean class="net.mopai.dynamicds.ApplicationContextHelper" lazy-init="false"/> 

package net.mopai.dynamicds;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

public class ApplicationContextHelper implements ApplicationContextAware {
	private static ApplicationContext applicationContext;
	
	@Override
	public void setApplicationContext(ApplicationContext context) throws BeansException {
		ApplicationContextHelper.applicationContext  = context;
	}
	
	public static Object getBean( String beanName ) {
        return applicationContext.getBean( beanName );
    }

	public static <T> T getBean(Class<T> clz) {
        return applicationContext.getBean(clz);
    }

}

访问数据库操作

DatasourceDao datasourceDao = ApplicationContextHelper.getBean(DatasourceDao.class);
Datasource datasource = datasourceDao.getDsByDefaultMerchant(mainMerchantId);

3.3 DynamicDataSource采用new的方式异常

采用注解的方式(new 的方式导致，新增加的数据源无法生效，具体原因请查看源码)

@Resource(name = "dynamicDataSource")
private DynamicDataSource dynamicDataSource;

4 实现案例

4.1 配置文件

applicationContext.xml配置动态据源

	<!-- ###########################配置多数据源begin##############################-->
	<!-- 配置多数据源 -->
	<!-- 数据源模板，动态增加数据源时需要用到，scope是prototype，非单例对象 -->
   <bean id="dataSourceTemplate" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" scope="prototype">
   		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="filters" value="stat" />
        <property name="initialSize" value="${dynamic_initialSize}" />
        <property name="maxActive" value="${dynamic_maxActive}" />
	    <property name="maxWait" value="${dynamic_maxWait}" />
	    <property name="minIdle" value="${dynamic_minIdle}" />
	    <property name="timeBetweenEvictionRunsMillis" value="${dynamic_timeBetweenEvictionRunsMillis}" />
	    <property name="minEvictableIdleTimeMillis" value="${dynamic_minEvictableIdleTimeMillis}" />
	    <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
	    <property name="testWhileIdle" value="${dynamic_testWhileIdle}" />
	    <property name="poolPreparedStatements" value="${dynamic_poolPreparedStatements}" />
	    <property name="maxOpenPreparedStatements" value="${dynamic_maxOpenPreparedStatements}" />
	    <property name="asyncInit" value="${dynamic_asyncInit}" />
    </bean>
    
     
    <bean id="defaultDS" parent="dataSourceTemplate">
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
    </bean>  
    

    <bean id="dynamicDataSource" class="net.mopai.dynamicds.DynamicDataSource" init-method="init">
        <property name="lenientFallback" value="false"/>
        <property name="targetDataSources">
            <map></map>
        </property>
        <!-- <property name="defaultDS" ref="defaultDataSource"></property>  --> 
         <property name="initConnectionsOnCreate" value="true"/>
    </bean>
    
    <!-- ###########################配置多数据源end##############################-->
    
 	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dynamicDataSource" />

配置aop切面

<bean id="dataSourceAspect" class="net.mopai.aspect.DataSourceAspect"></bean>
<aop:config>
   <aop:aspect id="dsAspect" ref="dataSourceAspect" order="1">
	  <aop:pointcut id="ptcut" expression="execution( * net.mopai.service..*.*(..))" />
	  <aop:around method="switchDataSource" pointcut-ref="ptcut"/>
   </aop:aspect>
</aop:config> 

4.2 继承AbstractRoutingDataSource

package net.mopai.dynamicds;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Repository;

import com.alibaba.druid.pool.DruidDataSource;

import net.mopai.dao.DatasourceDao;
import net.mopai.entity.Datasource;

@Repository("dynamicDataSource")
public class DynamicDataSource extends AbstractRoutingDataSource{
	private final Logger logger = LoggerFactory.getLogger(getClass());
	//存储已经加载的数据源
	public static Map<Object, Object> targetDataSources = new HashMap<Object,Object>();
	
	@Autowired
    ApplicationContext applicationContext;

    /**
     * 是否在创建数据源时，立即初始化连接
     */
    private boolean initConnectionsOnCreate = false;
    //该方法返回需要使用的DataSource的key值，然后根据这个key从resolvedDataSources这个map里取出对应的DataSource，如果找不到，则用默认的resolvedDefaultDataSource。
	@Override
	protected Object determineCurrentLookupKey() {
		if(targetDataSources.get(DataSourceRouter.getCurrentDataSourceKey()) == null){
			return DataSourceRouter.DEFAULT;
		}
		return DataSourceRouter.getCurrentDataSourceKey();
	}
	
	public void init() {
		DruidDataSource defaultTargetDataSource = applicationContext.getBean("defaultDS", DruidDataSource.class);
		Map<Object, Object> map =  new HashMap<Object,Object>();
		try {
			defaultTargetDataSource.init();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		map.put(DataSourceRouter.DEFAULT, defaultTargetDataSource);
		targetDataSources.put(DataSourceRouter.DEFAULT, defaultTargetDataSource);
		setTargetDataSources(map);
		setDefaultTargetDataSource(defaultTargetDataSource);
		afterPropertiesSet();
    }

    public void setInitConnectionsOnCreate(boolean initConnectionsOnCreate) {
        this.initConnectionsOnCreate = initConnectionsOnCreate;
    }
    
    
    /**
     * 动态加载数据源工厂
     * @param datasource 数据源
     * @return DruidDataSource
     * @throws SQLException 
     */
    public DruidDataSource dynamicDatasourceFactory(Datasource datasource){
    	 DruidDataSource dds = new DruidDataSource();
    	 dds.setDriverClassName(datasource.getDriverClassName());
    	 dds.setUrl(datasource.getUrl());
    	 dds.setUsername(datasource.getUsername());
    	 dds.setPassword(datasource.getPassword());
    	 dds.setInitialSize(datasource.getInitialSize());
    	 dds.setMaxActive(datasource.getMaxActive());
    	 dds.setMaxWait(datasource.getMaxWait());
    	 dds.setMinIdle(datasource.getMinIdle());
    	 dds.setTimeBetweenEvictionRunsMillis(datasource.getTimeBetweenEvictionRunsMillis());
    	 dds.setMinEvictableIdleTimeMillis(datasource.getMinEvictableIdleTimeMillis());
    	 dds.setTestWhileIdle(datasource.getTestWhileIdle().equals("Y") ? true : false);
    	 dds.setAsyncInit(datasource.getAsyncInit().equals("Y") ? true : false);
    	 return dds;
    }
    
    /**
     * 动态增加数据源
     * @param key
     * @param druidDataSource
     */
    public void dynamicAddDatasource(String key ,Datasource datasource){
    	try {
    		if(targetDataSources.get(key) == null){
    			DruidDataSource dds = dynamicDatasourceFactory(datasource);
    			dds.init();
    			targetDataSources.put(key, dds);
    			setTargetDataSources(targetDataSources);
    			//默认先执行配置文件中的targetDataSources属性的数据源，这里设置的数据源不会生效，必须调用afterPropertiesSet
    			afterPropertiesSet();
    		}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    }
    
    /**
     * 根据租户获取需要加载的数据源
     * @param defaultMerchant 租户
     * @return Datasource 需要加载的数据源
     */
    public Datasource getDsByDM(Long defaultMerchant){
    	DatasourceDao dao = ApplicationContextHelper.getBean(DatasourceDao.class);
    	Datasource datasource = dao.getDsByDefaultMerchant(defaultMerchant);
		return datasource;
    }
      
    /**
     * 当前数据库连接
     * @param source
     */
    public void testConnection(DruidDataSource source){
    	Connection connection = null;
    	try { 
    		Class.forName("com.mysql.jdbc.Driver");
    		logger.info("current datasource[url:" + source.getUrl() + ", username:" + source.getUsername() + ",password:" + source.getPassword() +"]");
    		connection = DriverManager.getConnection(
    				source.getUrl(),source.getUsername(),source.getPassword());
    		logger.info("current connection is: {}" , !connection.isClosed());
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }
}

4.3 数据源路由类DataSourceRouter

package net.mopai.dynamicds;

import java.util.function.Supplier;

public class DataSourceRouter {
	//默认数据源的key
    public final static String DEFAULT = "defaultDS";
      
    private final static ThreadLocal<String> threadLocal = ThreadLocal.withInitial(new Supplier<String>() {
        @Override
        public String get() {
            return DEFAULT;
        }
    });

    /**
     * 获取线程本地变量
     *
     * @return
     */
    public  static String getCurrentDataSourceKey() {
        return threadLocal.get();
    }

    /**
     * 设置动态数据库当前连接池
     * @param key
     */
   public static void  setDataSource(String key){
    	threadLocal.set(key);
    }
   
   public static void clearDataSourceKey() {
	   threadLocal.remove();
   }
}

4.4aop切面类DataSourceAspect

package net.mopai.aspect;


import javax.servlet.http.HttpServletRequest;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.aspectj.lang.ProceedingJoinPoint;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import net.mopai.Principal;
import net.mopai.dynamicds.DataSourceRouter;

/**
 * 动态选择数据源切面
 * @author sunny
 * 
 * 1： 小程序端 --> 获取租户ID --> 根据租户ID从mopai_merchant_ds路由表中获取数据源ID --> 动态加载数据源 -->设置租户当前线程应用数据源
 * 2： 商户端--> 用户名登录 --> 根据用户名从mopai_merchant_admin中获取sn,进而关联mopai_merchant拿到租户ID-->  
 * 			根据租户ID从mopai_merchant_ds路由表中获取数据源ID--> 动态加载数据源-->设置租户当前线程应用数据源
 * 3: 平台端 --> 根据需求选择数据源
 */

//@Aspect
//@Component
public class DataSourceAspect {
	protected static final Logger log = LoggerFactory.getLogger(DataSourceAspect.class);
	
	//@Around(value="execution( * net.mopai.service..*.*(..))")
    public Object switchDataSource(ProceedingJoinPoint jp) throws Throwable {
    	log.info("当前数据源：" + DataSourceRouter.getCurrentDataSourceKey());
    	//请求request项目启动时，为空，报错，异常处理；项目启动后：获取request,并获取一系列参数
    	if(RequestContextHolder.getRequestAttributes() == null){
			 Object obj = jp.proceed();
			 return obj;
		}
    	HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
    	String mainMerchantId = request.getHeader("mainMerchantId");
    	String appid = request.getHeader("appid");
    	 
    	//============================================小程序选库逻辑部分begin===============================================    	
    	String uri = request.getRequestURI();
    	if(appid!=null && !"".equals(appid)){
    		//小程序的第一个获取租户ID的请求不拦截,使用默认库
    		if(uri.contains("wx_openId")){
    			Object obj = jp.proceed();
    			return obj;
    		}
    		//当前请求线程根据租户ID，选择数据源
    		if(mainMerchantId != null && !"".equals(mainMerchantId)){
		    	log.info("切换数据源");
				DataSourceRouter.setDataSource(mainMerchantId);
				log.info("当前连接数据源：" + DataSourceRouter.getCurrentDataSourceKey());
			    log.info("执行dao操作 DataSource to [{}] in Method [{}]",	DataSourceRouter.getCurrentDataSourceKey(), jp.getSignature());
				Object obj = jp.proceed();
				DataSourceRouter.clearDataSourceKey();
				return obj;
			}   
    	}
    	Object obj = jp.proceed();
		return obj;
    }  
}
 

4.5 运行中添加数据源

<mvc:interceptors>
	<mvc:interceptor>
		<mvc:mapping path="/**" />			
		<bean id="changeDataSourceInterceptor" class="net.mopai.interceptor.ChangeDataSourceInterceptor" />
	</mvc:interceptor> 
</mvc:interceptors>

package net.mopai.interceptor;


import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

import com.alibaba.druid.pool.DruidDataSource;

import net.mopai.Principal;
import net.mopai.dao.DatasourceDao;
import net.mopai.dynamicds.ApplicationContextHelper;
import net.mopai.dynamicds.DataSourceRouter;
import net.mopai.dynamicds.DatasourceUtil;
import net.mopai.dynamicds.DynamicDataSource;
import net.mopai.entity.Datasource;


public class ChangeDataSourceInterceptor extends HandlerInterceptorAdapter{
	protected static final Logger log = LoggerFactory.getLogger(ChangeDataSourceInterceptor.class);
	@Resource(name = "dynamicDataSource")
	private DynamicDataSource dynamicDataSource;
	
	@Override
	public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
			throws Exception {
		String mainMerchantId = request.getHeader("mainMerchantId");
		Subject subject = SecurityUtils.getSubject();
		String uri = request.getRequestURI();
		log.info("请求连接URL:{}",uri);
		if(mainMerchantId != null && !"".equals(mainMerchantId)){
			log.info("小程序访问");
			return addDynamicDs(Long.valueOf(mainMerchantId));
		}    
		return true;
	}

	@Override
	public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler,
			ModelAndView modelAndView) throws Exception {
		//DataSourceRouter.clearDataSourceKey();
	}
	
	
	private boolean addDynamicDs(Long mainMerchantId){
		DatasourceDao datasourceDao = ApplicationContextHelper.getBean(DatasourceDao.class);
		Datasource datasource = datasourceDao.getDsByDefaultMerchant(mainMerchantId);
		if(datasource == null){
			return false;
		}
		dynamicDataSource.dynamicAddDatasource(mainMerchantId + "", datasource);
		return true;
	}
	
	
}

4.6 其他相关类

Datasource

package net.mopai.entity;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.hibernate.search.annotations.DocumentId;
import org.hibernate.validator.constraints.NotEmpty;

import com.fasterxml.jackson.annotation.JsonProperty;

/**
 * 数据源配置
 * @author sunny
 *
 */
@Entity
@Table(name = "mopai_datasource")
@SequenceGenerator(name = "sequenceGenerator", sequenceName = "mopai_datasource_sequence")
public class Datasource {
	
	private Long id;
	/**
	 * 连接驱动名字
	 */
	private String driverClassName;
	
	/**
	 * 数据库连接
	 */
	private String url;
	
	/**
	 * 用户名
	 */
	private String username;
	
	/**
	 * 密码
	 */
	private String password;
	
	/**
	 * 属性类型: stat(监控统计) log4j(日志) wall(防御sql注入)
	 */
	private String filters;
	
	/**
	 * 初始化时建立物理连接的个数
	 */
	private int initialSize;
	
	/**
	 * 最大连接池数量
	 */
	private int maxActive;
	
	/**
	 * 获取连接时最大等待时间，单位毫秒
	 */
	private long maxWait;
	
	/**
	 * 最小连接池数量
	 */
	private int minIdle;
	
	/**
	 * 检测连接的间隔时间
	 */
	private long timeBetweenEvictionRunsMillis;
	
	/**
	 * 连接保持空闲而不被驱逐的最小时间，单位毫秒
	 */
	private long minEvictableIdleTimeMillis;
	
	/**
	 * 申请连接的时候检测连接是否有效 Y:开启检查, N：关闭检查
	 */
	private String testWhileIdle;
	
	/**
	 * 默认开启 Y 关闭 N; 打开加快应用启动时间
	 */
	private String asyncInit;

	/**
	 * 创建时间
	 */
	private Date createDate;
	/**
	 * 修改时间
	 */
	private Date modifyDate;
	
	@JsonProperty
	@DocumentId
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO, generator = "sequenceGenerator")
	


	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}
	
	@NotEmpty
	@Column(name = "driver_class_name", nullable = false)
	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}
	
	
	@NotEmpty
	@Column(name = "url", nullable = false)
	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	@NotEmpty
	@Column(name = "username", nullable = false)
	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	@NotEmpty
	@Column(name = "password", nullable = false)
	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@NotEmpty
	@Column(name = "filters", nullable = false)
	public String getFilters() {
		return filters;
	}

	public void setFilters(String filters) {
		this.filters = filters;
	}

	@NotEmpty
	@Column(name = "initial_size", nullable = false)
	public int getInitialSize() {
		return initialSize;
	}

	public void setInitialSize(int initialSize) {
		this.initialSize = initialSize;
	}

	@NotEmpty
	@Column(name = "max_active", nullable = false)
	public int getMaxActive() {
		return maxActive;
	}

	public void setMaxActive(int maxActive) {
		this.maxActive = maxActive;
	}

	@NotEmpty
	@Column(name = "max_wait", nullable = false)
	public long getMaxWait() {
		return maxWait;
	}

	public void setMaxWait(long maxWait) {
		this.maxWait = maxWait;
	}

	@NotEmpty
	@Column(name = "min_idle", nullable = false)
	public int getMinIdle() {
		return minIdle;
	}

	public void setMinIdle(int minIdle) {
		this.minIdle = minIdle;
	}

	@NotEmpty
	@Column(name = "time_between_eviction_runs_millis", nullable = false)
	public long getTimeBetweenEvictionRunsMillis() {
		return timeBetweenEvictionRunsMillis;
	}

	public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
	}

	@NotEmpty
	@Column(name = "min_evictable_idle_time_millis", nullable = false)
	public long getMinEvictableIdleTimeMillis() {
		return minEvictableIdleTimeMillis;
	}

	public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
	}
	@NotEmpty
	@Column(name = "test_while_idle", nullable = false)
	public String getTestWhileIdle() {
		return testWhileIdle;
	}

	public void setTestWhileIdle(String testWhileIdle) {
		this.testWhileIdle = testWhileIdle;
	}
	@NotEmpty
	@Column(name = "async_init", nullable = false)
	public String getAsyncInit() {
		return asyncInit;
	}

	public void setAsyncInit(String asyncInit) {
		this.asyncInit = asyncInit;
	}
	@NotEmpty
	@Column(name = "create_date", nullable = false)
	public Date getCreateDate() {
		return createDate;
	}

	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	@NotEmpty
	@Column(name = "modify_date", nullable = false)
	public Date getModifyDate() {
		return modifyDate;
	}

	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
	
}

MerchantDs

package net.mopai.entity;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.hibernate.search.annotations.DocumentId;
import org.hibernate.validator.constraints.NotEmpty;

import com.fasterxml.jackson.annotation.JsonProperty;

@Entity
@Table(name = "mopai_merchant_ds")
@SequenceGenerator(name = "sequenceGenerator", sequenceName = "mopai_merchant_ds_sequence")
public class MerchantDs{

	private Long id;
	/**
	 * 租户id
	 */
	private Long defaultMerchant;
	
	/**
	 * 数据源id
	 */
	private Long datasource;
	
	/**
	 * 创建时间
	 */
	private Date createDate;
	
	/**
	 * 修改时间
	 */
	private Date modifyDate;
		
	@JsonProperty
	@DocumentId
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO, generator = "sequenceGenerator")
	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	@NotEmpty
	@Column(name = "default_merchant", nullable = false)
	public Long getDefaultMerchant() {
		return defaultMerchant;
	}

	public void setDefaultMerchant(Long defaultMerchant) {
		this.defaultMerchant = defaultMerchant;
	}

	@NotEmpty
	@Column(name = "datasource", nullable = false)
	public Long getDatasource() {
		return datasource;
	}

	public void setDatasource(Long datasource) {
		this.datasource = datasource;
	}

	@NotEmpty
	@Column(name = "create_date", nullable = false)
	public Date getCreateDate() {
		return createDate;
	}

	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}

	@NotEmpty
	@Column(name = "modify_date", nullable = false)
	public Date getModifyDate() {
		return modifyDate;
	}

	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
}

DatasourceDao

public interface DatasourceDao {	
	Datasource getDsByDefaultMerchant(Long defaultMerchant);	
}

DatasourceDaoImpl

@Repository("datasourceDaoImpl")
public class DatasourceDaoImpl extends BaseDaoImpl<Datasource, Long> implements DatasourceDao{

	@Override
	public Datasource getDsByDefaultMerchant(Long defaultMerchant) {
		if (null == defaultMerchant) {
			return null;
		}
		try {
			String jpql = "select ds "
							   + " from  Datasource ds, "
							   + " MerchantDs mds "
							   + " where ds.id = mds.datasource "
							   + " and mds.defaultMerchant = :defaultMerchant";
			return entityManager.createQuery(jpql, Datasource.class).setFlushMode(FlushModeType.COMMIT).setParameter("defaultMerchant", defaultMerchant).getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}

}

DatasourceService

public interface DatasourceService {	
	boolean getDsByDefaultMerchant(Long defaultMerchant);	
}

DatasourceServiceImpl

@Service("datasourceServiceImpl")
public class DatasourceServiceImpl implements DatasourceService{
	
	@Resource(name = "datasourceDaoImpl")
	private DatasourceDao datasourceDao;
	@Resource(name = "dynamicDataSource")
	private DynamicDataSource dynamicDataSource;
		
	public void setDatasourceDao(DatasourceDao datasourceDao) {
		this.datasourceDao = datasourceDao;
	}

	@Override
	public boolean getDsByDefaultMerchant(Long defaultMerchant) {	
		Datasource datasource = datasourceDao.getDsByDefaultMerchant(defaultMerchant);
		if(datasource == null){
			return false;
		}
		dynamicDataSource.dynamicAddDatasource(defaultMerchant + "", datasource);
		return true;
	}
}

DynamicDSController

@Controller
@RequestMapping("/common")
public class DynamicDSController {
		
	@Resource(name = "datasourceServiceImpl")
	private DatasourceService datasourceService;
	
	@ResponseBody
	@RequestMapping(value = "/dynamic/add/datasource")
	public boolean dynamicDsLoad(@RequestParam("mainMerchantId") String mainMerchantId){
		return datasourceService.getDsByDefaultMerchant(Long.valueOf(mainMerchantId));		
	}
}

4.7 数据库表信息

数据源路由表

CREATE TABLE `mopai_merchant_ds` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `default_merchant` bigint(20) NOT NULL COMMENT '租户ID',
  `datasource` bigint(20) NOT NULL COMMENT '数据源ID',
  `create_date` datetime NOT NULL COMMENT '创建日期',
  `modify_date` datetime NOT NULL COMMENT '修改日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='租户数据路由表';

数据源表

CREATE TABLE `mopai_datasource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `create_date` datetime NOT NULL COMMENT '创建日期',
  `modify_date` datetime NOT NULL COMMENT '修改日期',
  `driver_class_name` varchar(255) NOT NULL COMMENT '连接驱动名字',
  `url` varchar(256) NOT NULL COMMENT '数据库连接url',
  `username` varchar(20) NOT NULL COMMENT '用户名',
  `password` varchar(20) NOT NULL COMMENT '密码',
  `filters` varchar(8) NOT NULL COMMENT '属性类型: stat(监控统计) log4j(日志) wall(防御sql注入)',
  `initial_size` int(6) NOT NULL COMMENT '初始化时建立物理连接的个数',
  `max_active` int(6) NOT NULL COMMENT '最大连接池数量',
  `max_wait` bigint(20) NOT NULL COMMENT '获取连接时最大等待时间，单位毫秒',
  `min_idle` int(6) NOT NULL COMMENT '最小连接池数量',
  `time_between_eviction_runs_millis` bigint(20) NOT NULL COMMENT '检测连接的间隔时间',
  `min_evictable_idle_time_millis` bigint(20) NOT NULL COMMENT '连接保持空闲而不被驱逐的最小时间，单位毫秒',
  `test_while_idle` char(1) NOT NULL DEFAULT 'Y' COMMENT '申请连接的时候检测连接是否有效 Y:开启检查, N：关闭检查',
  `async_init` char(1) NOT NULL DEFAULT 'Y' COMMENT '默认开启 Y 关闭 N; 打开加快应用启动时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='数据源配置';