saas模式下的spring多資料來源管理

標籤：saas   資料來源   spring   spring多資料來源管理   

開發saas程式時，要求每個公司使用一個資料庫，當使用spring時如何配置資料來源使得每個公司使用不同的資料庫連接？

1、將公司id存放到一個ThreadLocal變數中，每次請求時設定，使得每次訪問資料來源可以從ThreadLocal擷取當前請求所屬的公司id。

/** * 用來存放當前線程的資料 */public class ThreadHolder {//公司idprivate static ThreadLocal<String> localDid = new ThreadLocal<String>();public static void putSp(String sp) {localDid.set(sp);}public static String getSp() {if (localDid.get() == null)return "";elsereturn (String)localDid.get();}}

2、在每次請求時調用ThreadHolder.putSp方法設定公司id。
可以定義一個filter，在doFilter方法中調用即可達到每次請求設定的效果。至於用戶端請求的公司id參數放在哪裡，這個可以靈活設定，可以放在每個請求的request參數中，也可以放在cookie中


3、修改spring資料來源配置
原配置（一般使用c0p3資料原的配置）

<bean id="dataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="xxxx" /><property name="jdbcUrl" value="xxx" /><property name="user" value="user" /><property name="password" value="password" /><property name="minPoolSize" value="1" /><property name="maxPoolSize" value="800" /><property name="maxIdleTime" value="25000" /><property name="acquireIncrement" value="1" /><property name="maxStatements" value="0" /><property name="initialPoolSize" value="100" /><property name="idleConnectionTestPeriod" value="18000" /><property name="acquireRetryAttempts" value="10" /><property name="acquireRetryDelay" value="1000" /><property name="breakAfterAcquireFailure" value="false" /><property name="checkoutTimeout" value="10000" /><property name="testConnectionOnCheckout" value="false" /></bean>

修改成：

    <bean id="dataSource" class="com.sangfor.frame.multiclient.MultiClientDataSource">          <property name="dataSource">              <ref bean="dataSourceDefault" />           </property>      </bean>  <bean id="dataSourceDefault"class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="xxxx" /><property name="jdbcUrl" value="xxx" /><property name="user" value="user" /><property name="password" value="password" /><property name="minPoolSize" value="1" /><property name="maxPoolSize" value="800" /><property name="maxIdleTime" value="120" /><property name="acquireIncrement" value="1" /><property name="maxStatements" value="0" /><property name="initialPoolSize" value="100" /><property name="idleConnectionTestPeriod" value="65" /><property name="acquireRetryAttempts" value="10" /><property name="acquireRetryDelay" value="1000" /><property name="breakAfterAcquireFailure" value="false" /><property name="checkoutTimeout" value="10000" /></bean>

MultiClientDataSource:

public class MultiClientDataSource implements DataSource {public static final String DBNAME_PREFIX = "CLIENT_";private ComboPooledDataSource dataSource = null;private Map<String,DataSource> dsMap = new HashMap<String,DataSource>();private static Object lock=new Object(); public DataSource addDataSource(String did){try{synchronized (lock) {if (dsMap==null)dsMap = new HashMap<String,DataSource>();DataSource ds = dsMap.get(did);if (ds != null)return ds;ComboPooledDataSource newDs = getNewDataSource(did);dsMap.put(did, newDs);}}catch(Exception e){e.printStackTrace();}return dsMap.get(did);}public void removeDataSource(String did){if (dsMap==null)return;dsMap.remove(did);}private ComboPooledDataSource getNewDataSource(String did) throws Exception{ComboPooledDataSource ds = new ComboPooledDataSource();ds.setDriverClass("xxx");//driver classString jdbcUrl = "jdbc:mysql://127.0.0.1" //一般將ip配置在設定檔+":3306"+"/"+ DBNAME_PREFIX + did+ "?useUnicode=true&characterEncoding=utf-8";ds.setJdbcUrl(jdbcUrl);ds.setUser("user");ds.setPassword("password");ds.setMinPoolSize(1);ds.setMaxPoolSize(800);ds.setMaxIdleTime(2000);ds.setAcquireIncrement(1);ds.setMaxStatements(0);ds.setInitialPoolSize(100);ds.setIdleConnectionTestPeriod(1800);ds.setAcquireRetryAttempts(10);ds.setAcquireRetryDelay(1000);ds.setBreakAfterAcquireFailure(false);ds.setCheckoutTimeout(10000);ds.setPreferredTestQuery(" select FORMID from FLOW_FORM where 1 = 2");ds.setTestConnectionOnCheckout(false);return ds;}private void initDsMap()throws Exception{try{if (dsMap==null){dsMap = new HashMap<String,DataSource>();}}catch(Exception e){throw new Exception(e);}}public Connection getConnection() throws SQLException {return getDataSource().getConnection();}public Connection getConnection(String arg0, String arg1)throws SQLException {return getDataSource().getConnection(arg0, arg1);}public PrintWriter getLogWriter() throws SQLException {return getDataSource().getLogWriter();}public int getLoginTimeout() throws SQLException {return getDataSource().getLoginTimeout();}public void setLogWriter(PrintWriter arg0) throws SQLException {getDataSource().setLogWriter(arg0);}public void setLoginTimeout(int arg0) throws SQLException {getDataSource().setLoginTimeout(arg0);}public DataSource getDataSource(String dataSourceName)throws SQLException {try{if(dataSourceName==null||dataSourceName.equals("")){return this.dataSource;}else{DataSource ds = dsMap.get(dataSourceName);if (ds!=null)return ds;elsereturn null;}}catch(NoSuchBeanDefinitionException ex){throw new SQLException("There is not the dataSource <name:"+dataSourceName+"> in the applicationContext!");}}public void setDataSource(ComboPooledDataSource dataSource) {this.dataSource = dataSource;}public ComboPooledDataSource getDataSource()throws SQLException{String did = ThreadHolder.getSp();DataSource  ds = getDataSource(did);if (ds == null)return null;elsereturn (ComboPooledDataSource)ds;}public DataSource getDefaultDataSource()throws SQLException{return dataSource;}@Overridepublic boolean isWrapperFor(Class<?> arg0) throws SQLException {// TODO Auto-generated method stubreturn false;}@Overridepublic <T> T unwrap(Class<T> arg0) throws SQLException {// TODO Auto-generated method stubreturn null;}}

實現原理：
自己建立一個MultiClientDataSource類（實現DataSource介面）去代替原來注入dataSource的ComboPooledDataSource，MultiClientDataSource類的實現精華在於使用一個map,用公司id（did）作為key，value為connection,在getConnection方法中判斷是否在map中已經有該did的key，如果沒有就建立connection,並且加入到map中

saas模式下的spring多資料來源管理