结合spring jdbc 实现分表分库的数据库访问构思

数据库的分库分表访问，原理上很简单。对于一条sql来说，就是确定表名称，对于操作来说，就是要确定数据源。因此，我要对数据源与表名进行分析。

在spring中对于单数据源的配置，非常简单，相信大家也都会配置。那么对于多数据源来说有两种方式：

1，静态数据源选择方式，只需要在dao中注入对应数据源。这种也没什么好说的，但是如果存在事物的话，需要注意，一旦在 service的方法中操作不同数据源的dao应该如何处理。

2，动态数据源选择方式。动态的方式一般会在程序中通过一定的条件来选择数据源。所以对于在spring中配置数据源就有了小小改变。目前我使用的方式是实现自己的一个数据源，这个数据源的特点就是有一个map，保存了真正需要配置的数据员，然后给每个数据源分配一个key

?

示例配置

Xml代码?

1. <bean?id="dataSource"?class="halo.dao.sql.HaloDataSourceWrapper">??
2. ????????<property?name="dataSourceMap">??
3. ????????????<map>??
4. ????????????????<entry?key="mysql_test0">??
5. ????????????????????<bean?class="com.mchange.v2.c3p0.ComboPooledDataSource">??
6. ????????????????????????....??
7. ????????????????????</bean>??
8. ????????????????</entry>??
9. ????????????????<entry?key="mysql_test1">??
10. ????????????????????<bean?class="com.mchange.v2.c3p0.ComboPooledDataSource">??
11. ????????????????????????.....??
12. ????????????????????</bean>??
13. ????????????????</entry>??
14. ????????????</map>??
15. ????????</property>??
16. ????</bean>??

通过这种配置方式，程序就有机会根据条件来选择相应的数据源。那么，在程序的什么位置进行数据源选择才合适呢。个人认为这属于数据访问层的职责，因此，决定数据源的选择问题交给dao来处理。对dao注入自定的数据源。然后在所有的dao的方法中，肯定会多一个参数，这个参数就为了选择数据源所使用。

示例代码

?

Java代码?

1. public?int?count(Object?key,?String?where,?Object[]?params)??

?

?

?现在选择数据源的条件有了，下面要做的就是如何根据条件选择数据源，这时，我们可以专门写一个类，来做数据源的选择以及真实表名的确定。

示例代码

?

?

Java代码?

1. /**?
2. ?*?数据库表的基本信息，包括数据库真是名称与表真是名称?
3. ?*??
4. ?*?@author?akwei?
5. ?*/??
6. public?class?PartitionTableInfo?{??
7. ??
8. ????/**?
9. ?????*?数据源key?
10. ?????*/??
11. ????private?String?dsKey;??
12. ??
13. ????/**?
14. ?????*?表真是名称?
15. ?????*/??
16. ????private?String?tableName;??
17. ??
18. ????/**?
19. ?????*?表的别名?
20. ?????*/??
21. ????private?String?aliasName;??
22. ??
23. ????public?PartitionTableInfo()?{??
24. ????}??
25. ??
26. ????public?PartitionTableInfo(String?dsKey,?String?tableName)?{??
27. ????????this.dsKey?=?dsKey;??
28. ????????this.tableName?=?tableName;??
29. ????}??
30. ??
31. ????public?String?getDsKey()?{??
32. ????????return?dsKey;??
33. ????}??
34. ??
35. ????/**?
36. ?????*?设置数据库真实key?
37. ?????*??
38. ?????*?@param?dsKey?
39. ?????*/??
40. ????public?void?setDsKey(String?dsKey)?{??
41. ????????this.dsKey?=?dsKey;??
42. ????}??
43. ??
44. ????public?String?getTableName()?{??
45. ????????return?tableName;??
46. ????}??
47. ??
48. ????/**?
49. ?????*?设置表真实名称?
50. ?????*??
51. ?????*?@param?tableName?
52. ?????*/??
53. ????public?void?setTableName(String?tableName)?{??
54. ????????this.tableName?=?tableName;??
55. ????}??
56. ??
57. ????/**?
58. ?????*?设置表别名?
59. ?????*??
60. ?????*?@param?aliasName?
61. ?????*/??
62. ????public?void?setAliasName(String?aliasName)?{??
63. ????????this.aliasName?=?aliasName;??
64. ????}??
65. ??
66. ????public?String?getAliasName()?{??
67. ????????return?aliasName;??
68. ????}??
69. }??

?

?

数据源表名分析的抽象类

示例代码

?

Java代码?

1. /**?
2. ?*?数据分区分析器，通过此分析器可以分析表所在的具体的数据库与数据表名称?
3. ?*??
4. ?*?@author?akwei?
5. ?*/??
6. public?abstract?class?DbPartitionHelper?{??
7. ??
8. ????/**?
9. ?????*?根据内容进行分析，创建表的分区信息?
10. ?????*??
11. ?????*?@param?tableLogicName?
12. ?????*????????????逻辑表名称，也将会成为表的别名?
13. ?????*?@param?ctxMap?
14. ?????*????????????上下文信息存储,用来存储分区关键值?
15. ?????*?@return?
16. ?????*/??
17. ????public?abstract?PartitionTableInfo?parse(String?tableLogicName,??
18. ????????????Map<String,?Object>?ctxMap);??
19. }??

?

?

数据源的选择实现类

示例代码

?

Java代码?

1. public?class?TestUserDbPartitionHelper?extends?DbPartitionHelper?{??
2. ??
3. ????@Override??
4. ????public?PartitionTableInfo?parse(String?tableLogicName,??
5. ????????????Map<String,?Object>?ctxMap)?{??
6. ????????//?取出在程序中传递的分表分库关键字??
7. ????????long?userid?=?(Long)?ctxMap.get("userid");??
8. ????????//?对关键字进行分析，最终要获得真实操作的数据源key,表名称??
9. ????????String?lastChar?=?this.get01(userid);??
10. ????????PartitionTableInfo?partitionTableInfo?=?new?PartitionTableInfo();??
11. ????????//?设置表的逻辑表名称，也是表的别名??
12. ????????partitionTableInfo.setAliasName(tableLogicName);??
13. ????????//?设置通过分析后获得的真实表名称??
14. ????????partitionTableInfo.setTableName("testuser"?+?lastChar);??
15. ????????//?设置通过分析后获得的真实数据源key(此key在配置数据源时指定)??
16. ????????partitionTableInfo.setDsKey("mysql_test"?+?lastChar);??
17. ????????return?partitionTableInfo;??
18. ????}??
19. }??

?

?

这样dao的方法就获得了真正的数据源key和真实的表名称

?

调用举例代码

?

Java代码?

1. //?例如：我们需要根据useri的值来作为获取数据源以及分表的条件??
2. ????????TestUserDbPartitionHelper?dbPartitionHelper?=?new?TestUserDbPartitionHelper();??
3. ????????Map<String,?Object>?ctxMap?=?new?HashMap<String,?Object>();??
4. ????????ctxMap.put("userid",?123);??
5. ????????PartitionTableInfo?info?=?dbPartitionHelper.parse("testuser",?ctxMap);??
6. ????????String?dsKey?=?info.getDsKey();//?数据源的key??
7. ????????String?realTableName?=?info.getTableName();//?表的真实名称??

?

?通过这种调用，我么获得了数据源的key以及表的真实名称

这样dao里面的方法就可以拼装sql了。

那么数据源的key如何使用呢？

由于我们对dao都注入了自定义的datasource，这个key我们需要在datasource中通过map.get(String name)获得真实的datasource，一个简单的方式就是我们吧数据源key放到threadlocal中，让datasource在获得connection的方法中调用

?

保存数据源key的代码示例

?

Java代码?

1. /**?
2. ?*?保存当前使用的数据库key?
3. ?*??
4. ?*?@author?akwei?
5. ?*/??
6. public?class?DataSourceStatus?{??
7. ??
8. ????private?static?final?ThreadLocal<String>?currentDsKey?=?new?ThreadLocal<String>();??
9. ??
10. ????public?static?void?setCurrentDsKey(String?dsKey)?{??
11. ????????currentDsKey.set(dsKey);??
12. ????}??
13. ??
14. ????public?static?String?getCurrentDsKey()?{??
15. ????????return?currentDsKey.get();??
16. ????}??
17. }??

?

?

自定义的datasource代码示例

?

?

Java代码?

1. /**?
2. ?*?DataSource的包装类?
3. ?*??
4. ?*?@author?akwei?
5. ?*/??
6. public?class?MyDataSourceWrapper?implements?DataSource?{??
7. ??
8. ????private?Map<String,?DataSource>?dataSourceMap;??
9. ??
10. ????private?PrintWriter?logWriter;??
11. ??
12. ????private?int?loginTimeout?=?3;??
13. ??
14. ????public?DataSource?getCurrentDataSource()?{??
15. ????????DataSource?ds?=?this.dataSourceMap.get(DataSourceStatus??
16. ????????????????.getCurrentDsKey());??
17. ????????if?(ds?==?null)?{??
18. ????????????throw?new?RuntimeException("no?datasource?[?"??
19. ????????????????????+?DataSourceStatus.getCurrentDsKey()?+?"?]");??
20. ????????}??
21. ????????return?ds;??
22. ????}??
23. ??
24. ????public?void?setDataSourceMap(Map<String,?DataSource>?dataSourceMap)?{??
25. ????????this.dataSourceMap?=?dataSourceMap;??
26. ????}??
27. ??
28. ????@Override??
29. ????public?Connection?getConnection()?throws?SQLException?{??
30. ????????return?this.getCurrentDataSource().getConnection();??
31. ????}??
32. ??
33. ????@Override??
34. ????public?Connection?getConnection(String?username,?String?password)??
35. ????????????throws?SQLException?{??
36. ????????throw?new?SQLException("only?support?getConnection()");??
37. ????}??
38. ??
39. ????@Override??
40. ????public?PrintWriter?getLogWriter()?throws?SQLException?{??
41. ????????return?this.logWriter;??
42. ????}??
43. ??
44. ????@Override??
45. ????public?int?getLoginTimeout()?throws?SQLException?{??
46. ????????return?this.loginTimeout;??
47. ????}??
48. ??
49. ????@Override??
50. ????public?void?setLogWriter(PrintWriter?out)?throws?SQLException?{??
51. ????????this.logWriter?=?out;??
52. ????}??
53. ??
54. ????@Override??
55. ????public?void?setLoginTimeout(int?seconds)?throws?SQLException?{??
56. ????????this.loginTimeout?=?seconds;??
57. ????}??
58. ??
59. ????@Override??
60. ????public?boolean?isWrapperFor(Class<?>?iface)?throws?SQLException?{??
61. ????????return?this.getCurrentDataSource().isWrapperFor(iface);??
62. ????}??
63. ??
64. ????@Override??
65. ????public?<T>?T?unwrap(Class<T>?iface)?throws?SQLException?{??
66. ????????return?this.getCurrentDataSource().unwrap(iface);??
67. ????}??
68. }??

?

到目前为止，我们就可以使用spring jdbcTemplate来进行分库分表的sql操作了。

在上述的示例代码中很多的部分可以，进行数据库路由的分析写了不少的代码，其实这些代码可以通过配置的方式来解决，不需要通过手写代码来解决。我的一个思路就是对于与数据表对应的一个实体class配置一个路由规则的标示和表的别名，然后写一段程序来对这个配置进行解析，来实现上面分库分表选择的功能

上述方法解决了分库分表功能，但是没有解决单库的事务问题。由于数据库的选择是在dao层决定，那么对于一个service的方法就无法获得数据库，并开启事务。为了解决这种情况，我们可以对connection进行改造，然后再对自定义的datasource再次改造。我们在使用spring数据库事务的使用，大多情况都是在service的方法上加上事务，这样对于这个方法里面的dao调用都具有了事务操作。这样就必须在service方法运行之前就决定数据源是什么。

其实spring的事务方法只需要一个数据源，并获得connection然后进行connection.setAutoCommit等操作。spring并不关心你的connection是什么，是哪个数据源的。所以我们就可以写一个与数据源没有直接关系的自定义connection，让他来沉承担选择数据源之前对connection的所有操作。

自定义数据源示例代码

接口

?

Java代码?

1. /**?
2. ?*?Connection代理,不产生实际的connection资源?
3. ?*??
4. ?*?@author?yuanwei?
5. ?*/??
6. public?interface?ConnectionProxy?extends?Connection?{??
7. ??
8. ????/**?
9. ?????*?获得当前使用的Connection?
10. ?????*??
11. ?????*?@return?
12. ?????*?@see?Connection?
13. ?????*/??
14. ????Connection?getCurrentConnection();??
15. }??

?

?实现

?

Java代码?

1. public?class?ConnectionProxyImpl?implements?ConnectionProxy?{??
2. ??
3. ????/**?
4. ?????*?保存了真正的Connection?
5. ?????*/??
6. ????private?final?Map<String,?Connection>?conMap?=?new?HashMap<String,?Connection>();??
7. ??
8. ????private?boolean?autoCommit;??
9. ??
10. ????private?int?transactionIsolation;??
11. ??
12. ????private?int?holdability;??
13. ??
14. ????private?boolean?readOnly;??
15. ??
16. ????/**?
17. ?????*?自定义的数据源?
18. ?????*/??
19. ????private?HkDataSourceWrapper?cloudDataSourceWrapper;??
20. ??
21. ????public?ConnectionProxyImpl(HkDataSourceWrapper?cloudDataSourceWrapper)??
22. ????????????throws?SQLException?{??
23. ????????this.cloudDataSourceWrapper?=?cloudDataSourceWrapper;??
24. ????????this.setAutoCommit(true);??
25. ????}??
26. ??
27. ????@Override??
28. ????public?void?clearWarnings()?throws?SQLException?{??
29. ????????this.getCurrentConnection().clearWarnings();??
30. ????}??
31. ??
32. ????@Override??
33. ????public?void?close()?throws?SQLException?{??
34. ????????Collection<Connection>?c?=?this.conMap.values();??
35. ????????for?(Connection?con?:?c)?{??
36. ????????????con.close();??
37. ????????}??
38. ????????DataSourceStatus.setCurrentDsKey(null);??
39. ????}??
40. ??
41. ????@Override??
42. ????public?void?commit()?throws?SQLException?{??
43. ????????Collection<Connection>?c?=?this.conMap.values();??
44. ????????for?(Connection?con?:?c)?{??
45. ????????????con.commit();??
46. ????????}??
47. ????}??
48. ??
49. ????@Override??
50. ????public?Statement?createStatement()?throws?SQLException?{??
51. ????????return?this.getCurrentConnection().createStatement();??
52. ????}??
53. ??
54. ????@Override??
55. ????public?Connection?getCurrentConnection()?{??
56. ????????String?name?=?DataSourceStatus.getCurrentDsKey();??
57. ????????Connection?con?=?this.conMap.get(name);??
58. ????????if?(con?==?null)?{??
59. ????????????try?{??
60. ????????????????con?=?this.cloudDataSourceWrapper.getCurrentDataSource()??
61. ????????????????????????.getConnection();??
62. ????????????????this.initCurrentConnection(con);??
63. ????????????????this.conMap.put(name,?con);??
64. ????????????}??
65. ????????????catch?(SQLException?e)?{??
66. ????????????????throw?new?RuntimeException(e);??
67. ????????????}??
68. ????????}??
69. ????????return?con;??
70. ????}??
71. ??
72. ????private?void?initCurrentConnection(Connection?con)?throws?SQLException?{??
73. ????????con.setAutoCommit(this.getAutoCommit());??
74. ????????if?(this.getTransactionIsolation()?!=?0)?{??
75. ????????????con.setTransactionIsolation(this.getTransactionIsolation());??
76. ????????}??
77. ????????con.setHoldability(this.getHoldability());??
78. ????????con.setReadOnly(this.isReadOnly());??
79. ????}??
80. ??
81. ????@Override??
82. ????public?Statement?createStatement(int?resultSetType,?int?resultSetConcurrency)??
83. ????????????throws?SQLException?{??
84. ????????return?this.getCurrentConnection().createStatement(resultSetType,??
85. ????????????????resultSetConcurrency);??
86. ????}??
87. ??
88. ????@Override??
89. ????public?Statement?createStatement(int?resultSetType,??
90. ????????????int?resultSetConcurrency,?int?resultSetHoldability)??
91. ????????????throws?SQLException?{??
92. ????????return?this.getCurrentConnection().createStatement(resultSetType,??
93. ????????????????resultSetConcurrency,?resultSetHoldability);??
94. ????}??
95. ??
96. ????@Override??
97. ????public?boolean?getAutoCommit()?throws?SQLException?{??
98. ????????return?this.autoCommit;??
99. ????}??
100. ??
101. ????@Override??
102. ????public?int?getHoldability()?throws?SQLException?{??
103. ????????return?this.holdability;??
104. ????}??
105. ??
106. ????@Override??
107. ????public?DatabaseMetaData?getMetaData()?throws?SQLException?{??
108. ????????return?this.getCurrentConnection().getMetaData();??
109. ????}??
110. ??
111. ????@Override??
112. ????public?int?getTransactionIsolation()?throws?SQLException?{??
113. ????????return?this.transactionIsolation;??
114. ????}??
115. ??
116. ????@Override??
117. ????public?Map<String,?Class<?>>?getTypeMap()?throws?SQLException?{??
118. ????????return?this.getCurrentConnection().getTypeMap();??
119. ????}??
120. ??
121. ????@Override??
122. ????public?SQLWarning?getWarnings()?throws?SQLException?{??
123. ????????return?this.getCurrentConnection().getWarnings();??
124. ????}??
125. ??
126. ????@Override??
127. ????public?boolean?isClosed()?throws?SQLException?{??
128. ????????return?this.getCurrentConnection().isClosed();??
129. ????}??
130. ??
131. ????@Override??
132. ????public?boolean?isReadOnly()?throws?SQLException?{??
133. ????????return?this.readOnly;??
134. ????}??
135. ??
136. ????@Override??
137. ????public?String?nativeSQL(String?sql)?throws?SQLException?{??
138. ????????return?this.getCurrentConnection().nativeSQL(sql);??
139. ????}??
140. ??
141. ????@Override??
142. ????public?CallableStatement?prepareCall(String?sql)?throws?SQLException?{??
143. ????????return?this.getCurrentConnection().prepareCall(sql);??
144. ????}??
145. ??
146. ????@Override??
147. ????public?CallableStatement?prepareCall(String?sql,?int?resultSetType,??
148. ????????????int?resultSetConcurrency)?throws?SQLException?{??
149. ????????return?this.getCurrentConnection().prepareCall(sql,?resultSetType,??
150. ????????????????resultSetConcurrency);??
151. ????}??
152. ??
153. ????@Override??
154. ????public?CallableStatement?prepareCall(String?sql,?int?resultSetType,??
155. ????????????int?resultSetConcurrency,?int?resultSetHoldability)??
156. ????????????throws?SQLException?{??
157. ????????return?this.getCurrentConnection().prepareCall(sql,?resultSetType,??
158. ????????????????resultSetConcurrency,?resultSetHoldability);??
159. ????}??
160. ??
161. ????@Override??
162. ????public?PreparedStatement?prepareStatement(String?sql)?throws?SQLException?{??
163. ????????return?this.getCurrentConnection().prepareStatement(sql);??
164. ????}??
165. ??
166. ????@Override??
167. ????public?PreparedStatement?prepareStatement(String?sql,?int?autoGeneratedKeys)??
168. ????????????throws?SQLException?{??
169. ????????return?this.getCurrentConnection().prepareStatement(sql,??
170. ????????????????autoGeneratedKeys);??
171. ????}??
172. ??
173. ????@Override??
174. ????public?PreparedStatement?prepareStatement(String?sql,?int[]?columnIndexes)??
175. ????????????throws?SQLException?{??
176. ????????return?this.getCurrentConnection().prepareStatement(sql,?columnIndexes);??
177. ????}??
178. ??
179. ????@Override??
180. ????public?PreparedStatement?prepareStatement(String?sql,?String[]?columnNames)??
181. ????????????throws?SQLException?{??
182. ????????return?this.getCurrentConnection().prepareStatement(sql,?columnNames);??
183. ????}??
184. ??
185. ????@Override??
186. ????public?PreparedStatement?prepareStatement(String?sql,?int?resultSetType,??
187. ????????????int?resultSetConcurrency)?throws?SQLException?{??
188. ????????return?this.getCurrentConnection().prepareStatement(sql,?resultSetType,??
189. ????????????????resultSetConcurrency);??
190. ????}??
191. ??
192. ????@Override??
193. ????public?PreparedStatement?prepareStatement(String?sql,?int?resultSetType,??
194. ????????????int?resultSetConcurrency,?int?resultSetHoldability)??
195. ????????????throws?SQLException?{??
196. ????????return?this.getCurrentConnection().prepareStatement(sql,?resultSetType,??
197. ????????????????resultSetConcurrency,?resultSetHoldability);??
198. ????}??
199. ??
200. ????@Override??
201. ????public?void?rollback()?throws?SQLException?{??
202. ????????Collection<Connection>?c?=?conMap.values();??
203. ????????for?(Connection?con?:?c)?{??
204. ????????????con.rollback();??
205. ????????}??
206. ????}??
207. ??
208. ????@Override??
209. ????public?void?setAutoCommit(boolean?autoCommit)?throws?SQLException?{??
210. ????????this.autoCommit?=?autoCommit;??
211. ????????Collection<Connection>?c?=?conMap.values();??
212. ????????for?(Connection?con?:?c)?{??
213. ????????????con.setAutoCommit(autoCommit);??
214. ????????}??
215. ????}??
216. ??
217. ????@Override??
218. ????public?void?setCatalog(String?catalog)?throws?SQLException?{??
219. ????????this.getCurrentConnection().setCatalog(catalog);??
220. ????}??
221. ??
222. ????@Override??
223. ????public?String?getCatalog()?throws?SQLException?{??
224. ????????return?this.getCurrentConnection().getCatalog();??
225. ????}??
226. ??
227. ????@Override??
228. ????public?void?setHoldability(int?holdability)?throws?SQLException?{??
229. ????????this.holdability?=?holdability;??
230. ????}??
231. ??
232. ????@Override??
233. ????public?void?setReadOnly(boolean?readOnly)?throws?SQLException?{??
234. ????????this.readOnly?=?readOnly;??
235. ????}??
236. ??
237. ????@Override??
238. ????public?void?setTransactionIsolation(int?level)?throws?SQLException?{??
239. ????????this.transactionIsolation?=?level;??
240. ????????Collection<Connection>?c?=?conMap.values();??
241. ????????for?(Connection?con?:?c)?{??
242. ????????????con.setTransactionIsolation(level);??
243. ????????}??
244. ????}??
245. ??
246. ????@Override??
247. ????public?void?setTypeMap(Map<String,?Class<?>>?map)?throws?SQLException?{??
248. ????????this.getCurrentConnection().setTypeMap(map);??
249. ????}??
250. ??
251. ????@Override??
252. ????public?void?releaseSavepoint(Savepoint?savepoint)?throws?SQLException?{??
253. ????????throw?new?SQLException("do?not?support?savepoint");??
254. ????}??
255. ??
256. ????@Override??
257. ????public?void?rollback(Savepoint?savepoint)?throws?SQLException?{??
258. ????????throw?new?SQLException("do?not?support?savepoint");??
259. ????}??
260. ??
261. ????@Override??
262. ????public?Savepoint?setSavepoint()?throws?SQLException?{??
263. ????????throw?new?SQLException("do?not?support?savepoint");??
264. ????}??
265. ??
266. ????@Override??
267. ????public?Savepoint?setSavepoint(String?name)?throws?SQLException?{??
268. ????????throw?new?SQLException("do?not?support?savepoint");??
269. ????}??
270. ??
271. ????@Override??
272. ????public?Array?createArrayOf(String?typeName,?Object[]?elements)??
273. ????????????throws?SQLException?{??
274. ????????return?this.getCurrentConnection().createArrayOf(typeName,?elements);??
275. ????}??
276. ??
277. ????@Override??
278. ????public?Blob?createBlob()?throws?SQLException?{??
279. ????????return?this.getCurrentConnection().createBlob();??
280. ????}??
281. ??
282. ????@Override??
283. ????public?Clob?createClob()?throws?SQLException?{??
284. ????????return?this.getCurrentConnection().createClob();??
285. ????}??
286. ??
287. ????@Override??
288. ????public?NClob?createNClob()?throws?SQLException?{??
289. ????????return?this.getCurrentConnection().createNClob();??
290. ????}??
291. ??
292. ????@Override??
293. ????public?SQLXML?createSQLXML()?throws?SQLException?{??
294. ????????return?this.getCurrentConnection().createSQLXML();??
295. ????}??
296. ??
297. ????@Override??
298. ????public?Struct?createStruct(String?typeName,?Object[]?attributes)??
299. ????????????throws?SQLException?{??
300. ????????return?this.getCurrentConnection().createStruct(typeName,?attributes);??
301. ????}??
302. ??
303. ????@Override??
304. ????public?Properties?getClientInfo()?throws?SQLException?{??
305. ????????return?this.getCurrentConnection().getClientInfo();??
306. ????}??
307. ??
308. ????@Override??
309. ????public?String?getClientInfo(String?name)?throws?SQLException?{??
310. ????????Connection?con?=?this.getCurrentConnection();??
311. ????????return?con.getClientInfo(name);??
312. ????}??
313. ??
314. ????@Override??
315. ????public?boolean?isValid(int?timeout)?throws?SQLException?{??
316. ????????return?this.getCurrentConnection().isValid(timeout);??
317. ????}??
318. ??
319. ????@Override??
320. ????public?void?setClientInfo(Properties?properties)??
321. ????????????throws?SQLClientInfoException?{??
322. ????????this.getCurrentConnection().setClientInfo(properties);??
323. ????}??
324. ??
325. ????@Override??
326. ????public?void?setClientInfo(String?name,?String?value)??
327. ????????????throws?SQLClientInfoException?{??
328. ????????this.getCurrentConnection().setClientInfo(name,?value);??
329. ????}??
330. ??
331. ????@Override??
332. ????public?boolean?isWrapperFor(Class<?>?iface)?throws?SQLException?{??
333. ????????return?this.getCurrentConnection().isWrapperFor(iface);??
334. ????}??
335. ??
336. ????@Override??
337. ????public?<T>?T?unwrap(Class<T>?iface)?throws?SQLException?{??
338. ????????return?this.getCurrentConnection().unwrap(iface);??
339. ????}??
340. }??

?

?

然后我们对自定义的datasource再次进行改造，新的datasource代码如下

?

?

Java代码?

1. /**?
2. ?*?DataSource的包装类?
3. ?*??
4. ?*?@author?akwei?
5. ?*/??
6. public?class?HkDataSourceWrapper?implements?DataSource,?InitializingBean?{??
7. ??
8. ????public?static?final?String?DEFAULT_DBKEY?=?"defaultdbkey";??
9. ??
10. ????private?Map<String,?DataSource>?dataSourceMap;??
11. ??
12. ????private?PrintWriter?logWriter;??
13. ??
14. ????private?int?loginTimeout?=?3;??
15. ??
16. ????private?boolean?debugConnection;??
17. ??
18. ????public?void?setDebugConnection(boolean?debugConnection)?{??
19. ????????this.debugConnection?=?debugConnection;??
20. ????}??
21. ??
22. ????public?boolean?isDebugConnection()?{??
23. ????????return?debugConnection;??
24. ????}??
25. ??
26. ????public?DataSource?getCurrentDataSource()?{??
27. ????????DataSource?ds?=?this.dataSourceMap.get(DataSourceStatus??
28. ????????????????.getCurrentDsKey());??
29. ????????if?(ds?==?null)?{??
30. ????????????throw?new?RuntimeException("no?datasource");??
31. ????????}??
32. ????????return?ds;??
33. ????}??
34. ??
35. ????public?void?setDataSourceMap(Map<String,?DataSource>?dataSourceMap)?{??
36. ????????this.dataSourceMap?=?dataSourceMap;??
37. ????}??
38. ??
39. ????@Override??
40. ????public?Connection?getConnection()?throws?SQLException?{??
41. ????????return?new?ConnectionProxyImpl(this);??
42. ????}??
43. ??
44. ????@Override??
45. ????public?Connection?getConnection(String?username,?String?password)??
46. ????????????throws?SQLException?{??
47. ????????throw?new?SQLException("only?support?getConnection()");??
48. ????}??
49. ??
50. ????@Override??
51. ????public?PrintWriter?getLogWriter()?throws?SQLException?{??
52. ????????return?this.logWriter;??
53. ????}??
54. ??
55. ????@Override??
56. ????public?int?getLoginTimeout()?throws?SQLException?{??
57. ????????return?this.loginTimeout;??
58. ????}??
59. ??
60. ????@Override??
61. ????public?void?setLogWriter(PrintWriter?out)?throws?SQLException?{??
62. ????????this.logWriter?=?out;??
63. ????}??
64. ??
65. ????@Override??
66. ????public?void?setLoginTimeout(int?seconds)?throws?SQLException?{??
67. ????????this.loginTimeout?=?seconds;??
68. ????}??
69. ??
70. ????@Override??
71. ????public?boolean?isWrapperFor(Class<?>?iface)?throws?SQLException?{??
72. ????????return?this.getCurrentDataSource().isWrapperFor(iface);??
73. ????}??
74. ??
75. ????@Override??
76. ????public?<T>?T?unwrap(Class<T>?iface)?throws?SQLException?{??
77. ????????return?this.getCurrentDataSource().unwrap(iface);??
78. ????}??
79. ??
80. ????@Override??
81. ????public?void?afterPropertiesSet()?throws?Exception?{??
82. ????????if?(this.dataSourceMap.size()?==?1)?{??
83. ????????????this.dataSourceMap.put(DEFAULT_DBKEY,?this.dataSourceMap.values()??
84. ????????????????????.iterator().next());??
85. ????????}??
86. ????}??
87. }??

?

?

其中最主要的部分就是

?

Java代码?

1. @Override??
2. ????public?Connection?getConnection()?throws?SQLException?{??
3. ????????return?new?ConnectionProxyImpl(this);??
4. ????}??

?

?

就是这部分返回了一个虚假的connection让spring进行事务开启等操作，那么既然spring进行了事务等设置，如何反应到真实的connection上呢，最住院哦的代码部分就是

?

Java代码?

1. private?void?initCurrentConnection(Connection?con)?throws?SQLException?{??
2. ????????con.setAutoCommit(this.getAutoCommit());??
3. ????????if?(this.getTransactionIsolation()?!=?0)?{??
4. ????????????con.setTransactionIsolation(this.getTransactionIsolation());??
5. ????????}??
6. ????????con.setHoldability(this.getHoldability());??
7. ????????con.setReadOnly(this.isReadOnly());??
8. ????}??

?

?

这部分代码会在获得真正的connection的时候进行对connection的初始化。这样就解决了事务问题。

1 楼 rain2005 2012-05-16 哈哈，原来原型在这么早就有啦，一直在找这方面的东西，就是自己没有想到，看到的一些东西都是基于服务器端proxy的，感觉还是太复杂了，简单实用的小框架才适合我们，容易维护。淘宝，阿里开源的那些还是太复杂了，不敢碰，呵呵