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数据源 */@Overrideprotected 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);}}}@Overridepublic 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()) {// DBString 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 {// JNDIString 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属性的值");

       

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