spring实现动态维护数据源（动态增加或动态删除）：报错
最近在做spring3+mybatis3整合，遇上个比较难解决的问题，特此发帖向广大技术牛求助，废话不表，直接说问题。网上搜索了很多技术文章，都 是实现动态切换数据源、或者是动态配置多数据源的，这里先声明一下，如果还是提供这种解决方案的牛们就不要回帖了，很浪费时间的。
最终效果希望能够实现成为系统配置中维护中心库的数据源，其他库通过中心库统一维护，并可以实现这些数据源的动态新增、删除、动态切换。希望有相关经验的大牛能帮忙结贴。
以下是问题描述：
系统有一个默认的datasource（这个数据源是通过spring进行配置的），在这个datasource中维护了一张datasource表，用来维护其他的数据源（DynamicDataSource），表结构如下：
动态数据源DynamicDataSource在spring的applicationContext.xml配置文件中定义如下：
线程安全的ThreadLocal:DBContextHolder.java
Java code
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
/**
* @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();
}
}
|
实现动态数据源切换逻辑
Java code
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
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 + "");
}
}
|
在应用场景做数据源动态加载并切换的时候报错：
Plain Text code
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
[DEBUG]--------> <span style="color: #FF0000;">use datasource 2</span> (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:<span style="color: #FF0000;"> Cannot create PoolableConnectionFactory (Access denied for user 'root'@'56.108.114.130' (using password: YES))</span>
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 <span style="color: #FF0000;">com.mysql.jdbc
|
展开
收起
1
条回答
写回答
写回答
-
请问你的问题解决了吗？我目前也有一样的需求######解决了 经验证不是程序问题 要切换的目标库信息错了 交流加我QQ6598977######想探讨的加QQ群 315309006
2020-06-14 09:53:12赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
