最近有一个项目需求,要求实现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属性的值");
网上有较多类似文章,仅供参考,以及自己的备份。