Spring动态加载、编辑数据源

        最近有一个项目需求，要求实现SAAS服务，最终结果大致如上图，当然这只是其中的一部分。这个项目有一个很明确的需求：所有站点用的是一套代码，有一个主站，多个子站，所有子站的数据结构是一致的，A登录时访问子站一，B登录时访问子站二，子站一与子站二的数据分别位于不同的数据库中，数据内容互不影响。

        网上查了一些资料，总结并实践后得出此文。

        我项目中用的是Spring JPA Data，忽略大部分配置，主要配置如下所示：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
	xmlns:cache="http://www.springframework.org/schema/cache" xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xmlns:tool="http://www.springframework.org/schema/tool" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans  
     http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
     http://www.springframework.org/schema/tx  
     http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
     http://www.springframework.org/schema/aop  
     http://www.springframework.org/schema/aop/spring-aop.xsd  
     http://www.springframework.org/schema/jee  
     http://www.springframework.org/schema/jee/spring-jee.xsd  
     http://www.springframework.org/schema/context  
     http://www.springframework.org/schema/context/spring-context.xsd  
   	 http://www.springframework.org/schema/cache
	 http://www.springframework.org/schema/cache/spring-cache-3.1.xsd
	 http://www.springframework.org/schema/data/jpa
 	 http://www.springframework.org/schema/data/jpa/spring-jpa.xsd
     http://www.springframework.org/schema/util  
     http://www.springframework.org/schema/util/spring-util-3.1.xsd
     http://www.springframework.org/schema/jdbc 
     http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
     http://www.springframework.org/schema/tool  
     http://www.springframework.org/schema/tool/spring-tool.xsd"
	default-lazy-init="true" default-autowire="byName">

	<bean id="dataSource" class="${datasource.driver}"> 
		<property name="user" value="${datasource.username}" /> 
		<property name="password" value="${datasource.password}" /> 
		<property name="URL" value="${datasource.url}" /> 
	</bean>

	<bean id="dynamicDataSource" class="com.gsoft.induasso.datasource.DynamicDataSource">
		<property name="targetDataSources">
			<map>
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="dataSource" />
	</bean>

</beans>

        配置中引用了properties文件配置的值，具体如何实现不在此处描述。

        DynamicDataSource类的代码如下所示：

/**
 * 
 * @author Geloin
 * @date Jan 16, 2014 6:08:27 PM
 */
package com.gsoft.induasso.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;

import com.gsoft.crystal.framework.core.util.Utils;
import com.gsoft.induasso.constant.Constants;

/**
 * 
 * @author Geloin
 * @date Jan 16, 2014 6:08:27 PM
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	private Logger log = Logger.getLogger(this.getClass());
	private Map<Object, Object> _targetDataSources;

	/**
	 * @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
	 * @describe 数据源为空或者为0时，自动切换至默认数据源，即在配置文件中定义的dataSource数据源
	 */
	@Override
	protected Object determineCurrentLookupKey() {
		String dataSourceName = DbContextHolder.getDBType();
		if (dataSourceName == null) {
			dataSourceName = Constants.DEFAULT_DATA_SOURCE_NAME;
		} else {
			this.selectDataSource(dataSourceName);
		}
		log.debug("--------> use datasource " + dataSourceName);
		return dataSourceName;
	}

	/**
	 * 到数据库中查找名称为dataSourceName的数据源
	 * 
	 * @author Geloin
	 * @date Jan 20, 2014 12:15:41 PM
	 * @param dataSourceName
	 */
	private void selectDataSource(String dataSourceName) {
		Object sid = DbContextHolder.getDBType();
		if (Utils.isEmpty(dataSourceName)
				|| dataSourceName.trim().equals("dataSource")) {
			DbContextHolder.setDBType("dataSource");
			return;
		}
		Object obj = this._targetDataSources.get(dataSourceName);
		if (obj != null && sid.equals(dataSourceName)) {
			return;
		} else {
			DataSource dataSource = this.getDataSource(dataSourceName);
			if (null != dataSource) {
				this.setDataSource(dataSourceName, dataSource);
			}
		}
	}

	@Override
	public void setTargetDataSources(Map<Object, Object> targetDataSources) {
		this._targetDataSources = targetDataSources;
		super.setTargetDataSources(this._targetDataSources);
		afterPropertiesSet();
	}

	private void addTargetDataSource(String key, DataSource dataSource) {
		this._targetDataSources.put(key, dataSource);
		this.setTargetDataSources(this._targetDataSources);
	}

	private DataSource createDataSource(String driverClassName, String url,
			String username, String password) {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName(driverClassName);
		dataSource.setUrl(url);
		dataSource.setUsername(username);
		dataSource.setPassword(password);
		return dataSource;
	}

	/**
	 * 到数据库中查询名称为dataSourceName的数据源
	 * 
	 * @author Geloin
	 * @date Jan 20, 2014 12:18:12 PM
	 * @param dataSourceName
	 * @return
	 */
	private DataSource getDataSource(String dataSourceName) {
		this.selectDataSource(Constants.DEFAULT_DATA_SOURCE_NAME);
		this.determineCurrentLookupKey();
		Connection conn = null;
		try {
			conn = this.getConnection();
			StringBuilder builder = new StringBuilder();
			builder.append("SELECT C_NAME,C_TYPE,C_URL,C_USER_NAME,");
			builder.append("C_PASSWORD,C_JNDI_NAME,C_DRIVER_CLASS_NAME ");
			builder.append("FROM IA_DATA_SOURCE WHERE c_name = ?");

			PreparedStatement ps = conn.prepareStatement(builder.toString());
			ps.setString(1, dataSourceName);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {

				Integer type = rs.getInt("C_TYPE");
				if (Utils.isNotEmpty(type)
						&& type.intValue() == Constants.DataSourceType.DB
								.intValue()) {
					// DB
					String url = rs.getString("C_URL");
					String userName = rs.getString("C_USER_NAME");
					String password = rs.getString("C_PASSWORD");
					String driverClassName = rs
							.getString("C_DRIVER_CLASS_NAME");
					DataSource dataSource = this.createDataSource(
							driverClassName, url, userName, password);
					return dataSource;
				} else {
					// JNDI
					String jndiName = rs.getString("C_JNDI_NAME");

					JndiDataSourceLookup jndiLookUp = new JndiDataSourceLookup();
					DataSource dataSource = jndiLookUp.getDataSource(jndiName);
					return dataSource;
				}

			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			log.error(e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error(e);
			}
		}
		return null;
	}

	/**
	 * 将已存在的数据源存储到内存中
	 * 
	 * @author Geloin
	 * @date Jan 20, 2014 12:24:13 PM
	 * @param dataSourceName
	 * @param dataSource
	 */
	private void setDataSource(String dataSourceName, DataSource dataSource) {
		this.addTargetDataSource(dataSourceName, dataSource);
		DbContextHolder.setDBType(dataSourceName);
	}

}

        其中使用的DbContextHolder类的内容如下所示：

/**
 * 
 * @author Geloin
 * @date Jan 16, 2014 6:08:47 PM
 */
package com.gsoft.induasso.datasource;

/**
 * 
 * @author Geloin
 * @date Jan 16, 2014 6:08:47 PM
 */
public class DbContextHolder {

	private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

	public static void setDBType(String dbType) {
		contextHolder.set(dbType);
	}

	public static String getDBType() {
		return (String) contextHolder.get();
	}

	public static void clearDBType() {
		contextHolder.remove();
	}
}

        文中还涉及到一个Entity类，如下所示：

/**
 * 
 * @author Geloin
 * @date Jan 17, 2014 10:17:32 AM
 */
package com.gsoft.induasso.entity;

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 com.gsoft.induasso.constant.Constants;

/**
 * 
 * @author Geloin
 * @date Jan 17, 2014 10:17:32 AM
 */
@Entity
@Table(name = "IA_DATA_SOURCE")
public class GsoftDataSource {

	@Id
	@SequenceGenerator(name = "IA_DATA_SOURCE_SEQ", sequenceName = "IA_DATA_SOURCE_SEQ", allocationSize = 1)
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "IA_DATA_SOURCE_SEQ")
	private Long id;

	/**
	 * data source name
	 */
	@Column(name = "C_NAME", unique = true)
	private String name;

	/**
	 * data source type, default is database<br />
	 * {@link Constants.DataSourceType}
	 */
	@Column(name = "C_TYPE")
	private Integer type = Constants.DataSourceType.DB.intValue();

	/**
	 * 数据库类型，目前只支持MySql和Oracle<br />
	 * {@link Constants.DataType}
	 */
	@Column(name = "C_DATA_TYPE")
	private Integer dataType = Constants.DataType.ORACLE.intValue();

	@Column(name = "C_URL")
	private String url;

	@Column(name = "C_USER_NAME")
	private String userName;

	@Column(name = "C_PASSWORD")
	private String password;

	@Column(name = "C_JNDI_NAME")
	private String jndiName;

	@Column(name = "C_DRIVER_CLASS_NAME")
	private String driverClassName;

	public Long getId() {
		return id;
	}

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

	public Integer getType() {
		return type;
	}

	public void setType(Integer type) {
		this.type = type;
	}

	public String getUrl() {
		return url;
	}

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

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPassword() {
		return password;
	}

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

	public String getJndiName() {
		return jndiName;
	}

	public void setJndiName(String jndiName) {
		this.jndiName = jndiName;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDriverClassName() {
		return driverClassName;
	}

	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}

	public Integer getDataType() {
		return dataType;
	}

	public void setDataType(Integer dataType) {
		this.dataType = dataType;
	}

}

        常量类可忽略。

        在实际的场景中，IA_Data_Source表中保存了多条数据源记录（可以通过增删改查维护），在操作子站点数据时，只需要在每个Service方法中加上以下代码即可：

DbContextHolder.setDBType("IA_DATA_SOURCE中某个数据源的name属性的值");

       

        网上有较多类似文章，仅供参考，以及自己的备份。