67,513
社区成员
发帖
与我相关
我的任务
分享
/**
* @author williams
* @descrice 多个登录用户可能需要同时切换数据源,所以这里需要写一个线程安全的ThreadLocal
* @more 用户切换数据源只要在程序中使用 DBContextHolder.setDBType("1") 即可完成数据源切换
*/
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();
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author williams
* @describe 实现动态数据源切换逻辑
*/
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 = "dataSource";
} else {
this.selectDataSource(Integer.valueOf(dataSourceName));
if (dataSourceName.equals("0"))
dataSourceName = "dataSource";
}
log.debug("--------> use datasource " + dataSourceName);
return dataSourceName;
}
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this._targetDataSources = targetDataSources;
super.setTargetDataSources(this._targetDataSources);
afterPropertiesSet();
}
public void addTargetDataSource(String key, BasicDataSource dataSource) {
this._targetDataSources.put(key, dataSource);
this.setTargetDataSources(this._targetDataSources);
}
public BasicDataSource 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);
dataSource.setTestWhileIdle(true);
return dataSource;
}
/**
* @param serverId
* @describe 数据源存在时不做处理,不存在时创建新的数据源链接,并将新数据链接添加至缓存
*/
public void selectDataSource(Integer serverId) {
Object sid = DBContextHolder.getDBType();
if ("0".equals(serverId + "")) {
DBContextHolder.setDBType("0");
return;
}
Object obj = this._targetDataSources.get(serverId);
if (obj != null && sid.equals(serverId + "")) {
return;
} else {
BasicDataSource dataSource = this.getDataSource(serverId);
if (null != dataSource)
this.setDataSource(serverId, dataSource);
}
}
/**
* @describe 查询serverId对应的数据源记录
* @param serverId
* @return
*/
public BasicDataSource getDataSource(Integer serverId) {
this.selectDataSource(0);
this.determineCurrentLookupKey();
Connection conn = null;
HashMap<String, Object> map = null;
try {
conn = this.getConnection();
PreparedStatement ps = conn
.prepareStatement("SELECT * FROM bas_datasource WHERE DBS_ID = ?");
ps.setInt(1, serverId);
ResultSet rs = ps.executeQuery();
map = new HashMap<String, Object>();
if (rs.next()) {
map.put("DBS_ID", rs.getInt("DBS_ID"));
map.put("DBS_DriverClassName", rs
.getString("DBS_DriverClassName"));
map.put("DBS_URL", rs.getString("DBS_URL"));
map.put("DBS_UserName", rs.getString("DBS_UserName"));
map.put("DBS_Password", rs.getString("DBS_Password"));
}
rs.close();
ps.close();
} catch (SQLException e) {
log.error(e);
} finally {
try {
conn.close();
} catch (SQLException e) {
log.error(e);
}
}
if (null != map) {
String driverClassName = map.get("DBS_DriverClassName").toString();
String url = map.get("DBS_URL").toString();
String userName = map.get("DBS_UserName").toString();
String password = map.get("DBS_Password").toString();
BasicDataSource dataSource = this.createDataSource(driverClassName,
url, userName, password);
return dataSource;
}
return null;
}
/**
* @param serverId
* @param dataSource
*/
public void setDataSource(Integer serverId, BasicDataSource dataSource) {
this.addTargetDataSource(serverId + "", dataSource);
DBContextHolder.setDBType(serverId + "");
}
}
[DEBUG]--------> use datasource 2 (DynamicDataSource.java:46):determineCurrentLookupKey[com.ehofy.base.db.DynamicDataSource]
[ERROR]获取用户列表时发生错误! (MasterAction.java:59):getStore[com.ehofy.action.global.MasterAction]
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'root'@'56.108.114.130' (using password: YES))
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.mybatis.spring.SqlSessionUtils.getSqlSession(SqlSessionUtils.java:117)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345)
at $Proxy12.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:190)
at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25)
at $Proxy29.selectList(Unknown Source)
at com.ehofy.db.service.MasterService.getMasters(MasterService.java:60)
at com.ehofy.controller.global.MasterController.getMasters(MasterController.java:23)
at com.ehofy.controller.global.MasterController$$FastClassByCGLIB$$37e7b273.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)
at com.ehofy.controller.global.MasterController$$EnhancerByCGLIB$$dcbd4599.getMasters(<generated>)
at com.ehofy.action.global.MasterAction.getStore(MasterAction.java:51)
......
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'root'@'56.108.114.130' (using password: YES))
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:148)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
... 46 more
Caused by: java.sql.SQLException: Access denied for user 'root'@'56.108.114.130' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3269)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670)
at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 51 more