目前项目上使用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 ©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);
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
