Spring 的动态多数据源的配置

目前项目上使用spring的多数据源的架构，这里我简要的描述一下：目前系统需要在多个省下的多个地市使用，一方面为了避免系统升级一次每个地市都要升级，另一方面为了实现每个省的业务数据存放到自己的数据库里。所以将所有的用户信息和数据源的信息，以及他们之间的映射关系都存放到中心端的基础数据库。用户首先登陆的时候首先连接的是中心端的基础数据库，根据用户信息获取对应的数据源信息，重新建立连接，就可以实现数据源的动态切换了，值得注意的是用户登录后的数据源信息存放在浏览器端的Cookie中，这样能保证每次进行的数据库访问中，数据源信息是使用切换后的，而不是配置文件中的默认的数据源。

一、数据源信息表建表语句：

create table SYSDATASOURCE
(
  id           NUMBER not null,
  driver       VARCHAR2(50),
  url          VARCHAR2(50),
  ds_username  VARCHAR2(50),
  ds_password  VARCHAR2(50),
  yxbz         VARCHAR2(1) default '1' not null,
  sortnum      NUMBER default 0 not null,
  ip           VARCHAR2(30),
  portname     VARCHAR2(8),
  instancename VARCHAR2(50),
  maxactive    VARCHAR2(20),
  maxwait      VARCHAR2(10)
)
tablespace AA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 8K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column SYSDATASOURCE.id
  is '主键';
comment on column SYSDATASOURCE.driver
  is '数据库驱动';
comment on column SYSDATASOURCE.url
  is '数据库连接串';
comment on column SYSDATASOURCE.ds_username
  is '数据库用户名';
comment on column SYSDATASOURCE.ds_password
  is '数据库密码';
comment on column SYSDATASOURCE.yxbz
  is '有效标志';
comment on column SYSDATASOURCE.sortnum
  is '排序';
comment on column SYSDATASOURCE.ip
  is '数据库服务器的IP地址';
comment on column SYSDATASOURCE.portname
  is '端口号（例如：1521）';
comment on column SYSDATASOURCE.instancename
  is '实例名（例如：orcl）';
comment on column SYSDATASOURCE.maxactive
  is '最大连接数';
comment on column SYSDATASOURCE.maxwait
  is '最大等待毫秒数, 单位为 ms, 超过时间会出错误信息';
-- Create/Recreate primary, unique and foreign key constraints 
alter table SYSDATASOURCE
  add constraint PK_SYSDATASOURCE primary key (ID)
  using index 
  tablespace AA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

备注：用户和数据源的映射信息就存放在用户信息表中（ID,USERID,SYSDATASOURCEID）;

二、实体类

package com.winning.bi.domain.sys;

import java.io.Serializable;

/**   
 * @标题: SysDataSource.java 
 * @包名: com.winning.bi.domain.sys 
 * @描述: 数据源实体类
 * @作者: yanghao
 * @时间: Oct 28, 2015 12:46:12 PM 
*/
public class SysDataSource implements Serializable
{

	/**
	 * @Fields serialVersionUID : TODO
	 */
	private static final long serialVersionUID = 1L;

	private long id;
	private String driver;
	private String url;
	private String dsUsername;
	private String dsPassword;
	private String yxbz;
	private long sortNum;
	private String ip;
	private String portName;
	private long maxWait;//最大等待时间
	private int maxActive;//最大连接数
	public String getPortName() {
		return portName;
	}

	public void setPortName(String portName) {
		this.portName = portName;
	}

	private String instanceName;

	public long getId()
	{
		return id;
	}

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

	public String getDriver()
	{
		return driver;
	}

	public void setDriver(String driver)
	{
		this.driver = driver;
	}

	public String getUrl()
	{
		return url;
	}

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

	public String getDsUsername()
	{
		return dsUsername;
	}

	public void setDsUsername(String dsUsername)
	{
		this.dsUsername = dsUsername;
	}

	public String getDsPassword()
	{
		return dsPassword;
	}

	public void setDsPassword(String dsPassword)
	{
		this.dsPassword = dsPassword;
	}

	public String getYxbz() {
		return yxbz;
	}

	public void setYxbz(String yxbz) {
		this.yxbz = yxbz;
	}

	public long getSortNum() {
		return sortNum;
	}

	public void setSortNum(long sortNum) {
		this.sortNum = sortNum;
	}

	public String getIp() {
		return ip;
	}

	public void setIp(String ip) {
		this.ip = ip;
	}

	public String getInstanceName() {
		return instanceName;
	}

	public void setInstanceName(String instanceName) {
		this.instanceName = instanceName;
	}

	/**
	 * @return the maxWait
	 */
	public long getMaxWait() {
		return maxWait;
	}

	/**
	 * @param maxWait the maxWait to set
	 */
	public void setMaxWait(long maxWait) {
		this.maxWait = maxWait;
	}

	/**
	 * @return the maxActive
	 */
	public int getMaxActive() {
		return maxActive;
	}

	/**
	 * @param maxActive the maxActive to set
	 */
	public void setMaxActive(int maxActive) {
		this.maxActive = maxActive;
	}
	
}

三、如果要实现动态多数据源的切换需要注意的有两个地方，首先是配置文件：

	<!--值得注意的是，这里配置的是中心端的基础数据库-->

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
 		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />  
  	      <property name="url" value="jdbc:oracle:thin:@192.168.0.21:1521:orcl" />
 		<property name="username" value="yanghao"></property>  
 		<property name="password" value="yanghao"></property>
 		<property name="maxActive" value="50"></property>
 		<property name="validationQuery">   
			<value>SELECT 1 FROM DUAL</value>
		</property>
 	</bean>

	<!-- 配置动态数据源 -->
	<bean id="dynamicDataSource" class="com.winning.utils.DynamicDataSource">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
                <entry value-ref="dataSource" key="dataSource"></entry>
            </map> 
		</property>
		<property name="defaultTargetDataSource" ref="dataSource"></property>
	</bean>

四、重写spring的AbstractDataSource

    package com.winning.utils;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.servlet.ServletContext;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpSession;
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.InitializingBean;
    import org.springframework.jdbc.datasource.AbstractDataSource;
    import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
    import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
    import org.springframework.util.Assert;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    import com.winning.bi.domain.sys.SysDataSource;
    import com.winning.bi.domain.sys.SysUser;
    
    public class DynamicDataSource extends AbstractDataSource implements InitializingBean {
    	
    	private Map<Object, Object> targetDataSources;
    
    	private Object defaultTargetDataSource;
    
    	private boolean lenientFallback = true;
    
    	private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
    
    	private Map<Object, DataSource> resolvedDataSources;
    
    	private DataSource resolvedDefaultDataSource;
    	
    
    	public DataSource getResolvedDefaultDataSource()
    	{
    		return resolvedDefaultDataSource;
    	}
    
    	public void setResolvedDefaultDataSource(DataSource resolvedDefaultDataSource)
    	{
    		this.resolvedDefaultDataSource = resolvedDefaultDataSource;
    	}
    
    	public Map<Object, DataSource> getResolvedDataSources()
    	{
    		return resolvedDataSources;
    	}
    
    	public void setResolvedDataSources(Map<Object, DataSource> resolvedDataSources)
    	{
    		this.resolvedDataSources = resolvedDataSources;
    	}
    
    	public Object getDefaultTargetDataSource()
    	{
    		return defaultTargetDataSource;
    	}
    	
    	public Map<Object, Object> getTargetDataSources()
    	{
    		return targetDataSources;
    	}
    
    
    	/**
    	 * Specify the map of target DataSources, with the lookup key as key.
    	 * The mapped value can either be a corresponding {@link javax.sql.DataSource}
    	 * instance or a data source name String (to be resolved via a
    	 * {@link #setDataSourceLookup DataSourceLookup}).
    	 * <p>The key can be of arbitrary type; this class implements the
    	 * generic lookup process only. The concrete key representation will
    	 * be handled by {@link #resolveSpecifiedLookupKey(Object)} and
    	 * {@link #determineCurrentLookupKey()}.
    	 */
    	public void setTargetDataSources(Map<Object, Object> targetDataSources) {
    		this.targetDataSources = targetDataSources;
    	}
    
    	/**
    	 * Specify the default target DataSource, if any.
    	 * <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
    	 * instance or a data source name String (to be resolved via a
    	 * {@link #setDataSourceLookup DataSourceLookup}).
    	 * <p>This DataSource will be used as target if none of the keyed
    	 * {@link #setTargetDataSources targetDataSources} match the
    	 * {@link #determineCurrentLookupKey()} current lookup key.
    	 */
    	public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
    		this.defaultTargetDataSource = defaultTargetDataSource;
    	}
    
    	/**
    	 * Specify whether to apply a lenient fallback to the default DataSource
    	 * if no specific DataSource could be found for the current lookup key.
    	 * <p>Default is "true", accepting lookup keys without a corresponding entry
    	 * in the target DataSource map - simply falling back to the default DataSource
    	 * in that case.
    	 * <p>Switch this flag to "false" if you would prefer the fallback to only apply
    	 * if the lookup key was <code>null</code>. Lookup keys without a DataSource
    	 * entry will then lead to an IllegalStateException.
    	 * @see #setTargetDataSources
    	 * @see #setDefaultTargetDataSource
    	 * @see #determineCurrentLookupKey()
    	 */
    	public void setLenientFallback(boolean lenientFallback) {
    		this.lenientFallback = lenientFallback;
    	}
    
    	/**
    	 * Set the DataSourceLookup implementation to use for resolving data source
    	 * name Strings in the {@link #setTargetDataSources targetDataSources} map.
    	 * <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
    	 * of application server DataSources to be specified directly.
    	 */
    	public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
    		this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
    	}
    
    
    	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);
    		}
    	}
    
    	/**
    	 * Resolve the specified data source object into a DataSource instance.
    	 * <p>The default implementation handles DataSource instances and data source
    	 * names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}).
    	 * @param dataSource the data source value object as specified in the
    	 * {@link #setTargetDataSources targetDataSources} map
    	 * @return the resolved DataSource (never <code>null</code>)
    	 * @throws IllegalArgumentException in case of an unsupported value type
    	 */
    	public DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
    		if (dataSource instanceof DataSource) {
    			return (DataSource) dataSource;
    		}
    		else if (dataSource instanceof String) {
    			return this.dataSourceLookup.getDataSource((String) dataSource);
    		}
    		else {
    			throw new IllegalArgumentException(
    					"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
    		}
    	}
    
    
    	public Connection getConnection() throws SQLException {
    		 Connection concretion = determineTargetDataSource().getConnection();
    		 return concretion;
    	}
    
    	public Connection getConnection(String username, String password) throws SQLException {
    		return determineTargetDataSource().getConnection(username, password);
    	}
    
    	/**
    	 * Retrieve the current target DataSource. Determines the
    	 * {@link #determineCurrentLookupKey() current lookup key}, performs
    	 * a lookup in the {@link #setTargetDataSources targetDataSources} map,
    	 * falls back to the specified
    	 * {@link #setDefaultTargetDataSource default target DataSource} if necessary.
    	 * @see #determineCurrentLookupKey()
    	 */
    	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;
    	}
    
    
    	/**
    	 * Resolve the given lookup key object, as specified in the
    	 * {@link #setTargetDataSources targetDataSources} map, into
    	 * the actual lookup key to be used for matching with the
    	 * {@link #determineCurrentLookupKey() current lookup key}.
    	 * <p>The default implementation simply returns the given key as-is.
    	 * @param lookupKey the lookup key object as specified by the user
    	 * @return the lookup key as needed for matching
    	 */
    	public Object resolveSpecifiedLookupKey(Object lookupKey) {
    		return lookupKey;
    	}
    	
        
        /**   
         * @作者: yanghao 
         * @时间: Nov 4, 2015 10:27:43 AM 
         * @描述: TODO
         * @return
         * @备注:  
        */
        public static String getCustomerType() {
        	return HttpSessionUtil.getDataSourceId();
        }
        
        
        public static void clearCustomerType() {
        }
        
        protected Object determineCurrentLookupKey() {
        	return getCustomerType();
        }
    }

    /**
     * <p> @Project:  </p>
     * <p> @Package: com.winning.utils </p>
     * <p> @Company:   </p>
     * <p> Copyright &copy;2017 Tech-Winning. All rights reserved </p>
     * 
     * <p> @author YangHao </p>
     * <p> @version V3.0 </p>
     * <p> @Createdate Mar 28, 2017 4:53:07 PM </p>
     *
     * <p> @Modification  History 
     *     Date             Author        Version        Description
     *     -----------------------------------------------------------------------------------
     *     Mar 28, 2017                  YangHao       1.0            1.0 </p>
     * <p> Why & What is modified </p>
     */
    package com.winning.utils;
    
    import javax.servlet.http.Cookie;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    import flex.messaging.FlexContext;
    
    
    /**
     * 
     *
     *HttpSessionUtil.java 文件使用说明
     * 说明：XXXX<br/>
     *  
     * 公开方法：
     * <ul>
     *      <li>getDataSourceId： 获取数据源ID</li>
     *      <li>sendDataSourceIdToCookie：将数据源ID保存到Cookie中</li>
     * </ul>
     *
     * @version ver 4.0.0
     * @author Shanghai Kinstar Winning Software .co.ltd. pcdalao
     * @since 作成日期：May 14, 2017（XCH）<br/>
     *        改修日期：
     */
    public class HttpSessionUtil {
    
    	private static Logger logger = LoggerFactory.getLogger(HttpSessionUtil.class);
    
    	/**
    	 *  获取数据源ID,
    	 * @return 数据源ID
    	 * @author: XCH
    	 */
    	public static String getDataSourceId() {
    		HttpServletRequest req=getRequest();
    		if(req==null){
    			return null;
    		}
    		Cookie[] cookies = req.getCookies();
    		
    		if(cookies==null ||cookies.length == 0){
    			return null;
    		}
    		
    		for (Cookie eleCookie : cookies) {
    			if (Constant.COOKIDDBSID.equals(eleCookie.getName())) {
    				logger.debug("COOKIDDBSID:{}",new String[]{eleCookie.getValue()});
    				/**cookie 数据源ID，字符null  代表基础库，基础库为默认数据源，返回null  */
    				if(StringUtil.isEmptyOfStrict(eleCookie.getValue())){
    					return null;
    				}
    				return eleCookie.getValue();
    			}
    		}
    		return null;
    	}
    	
    	private static HttpServletRequest getRequest(){
    		if(RequestContextHolder.getRequestAttributes()==null){
    			return null;
    		}
    		return	((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
    	}
    	
    	
    	/**
    	 * <p> 保存DataSourceID 到Cookie 中 </p>
    	 * @param dataSourceID   待保存到浏览器Cookie的数据源ID
    	 * @author: XCH
    	 */
    	public static void sendDataSourceIdToCookie( String dataSourceID) {
    		HttpServletResponse response=FlexContext.getHttpResponse();
    		if(response!=null){
    			response.addCookie(new Cookie(Constant.COOKIDDBSID, dataSourceID));
    		}
    	}
    
    }

五、登录的时候，首先根据用户ID查找到该用户对应的数据源信息，调用以下方法并将数据源的ID传入，将数据源的信息存入cookie中，以后每次建立数据库连接，

就从cookie中获取。

	HttpSessionUtil.sendDataSourceIdToCookie(dataSourceId);