Spring + iBatis multi-library horizontal segmentation simple solution idea

1. The introduction

The author is doing an Internet “SNS like” application recently, the number of users in the application is huge (about 40 million), therefore, simple use of traditional single database storage is definitely not good.

Referring to the widely used database and table, and the use of DAL data access layer, the author decided to use the simplest data source routing method to solve the problem.

Strictly speaking, the current implementation is not a solution, only a simple implementation of a way of thinking, the author only spent 2 days to complete (1.5 days of reading materials and Spring/ IBatis source code). Here also just provides a train of thought reference for everybody to see an officer, leave a note to oneself incidentally

2. System design premise

Our system uses 16 database instances (currently distributed on 2 physical machines, which will be gradually moved to 16 physical machines in the future according to the increase of system load). The 16 libraries are simple hash assignments based on the user’s UserID. It’s worth noting that since we’ve done this horizontal shard design, we’ve taken into account the characteristics of the system requirements,

• 1. Frequent cross-library access does not occur.

• 2. The main business logic is centered around UserID and can be completed in a single library transaction.

  On the system, we use Spring and iBatis. Spring is responsible for transaction management AOP for the database and IOC between beans. The biggest reason for choosing iBatis is the performance optimization for Sql and the ease with which Sql table names can be replaced later if there is a sub-table requirement.

3. Design ideas

First, I want to explain the idea of the author, actually very simple, that is, “before each database operation, determine the currently selected database object” and then access the currently selected database as if accessing a single library.

Second, to select the database before each DB access, we need to clarify several questions: 1. When iBatis obtains the specific database Connection from the DataSource, 2. Whether iBatis caches the Connection, because in the case of multiple libraries, the Connection is cached, which means that the database link selection cannot be changed in time. 3. Since we use Spring to manage DB transactions, we must know whether Spring’s interception of DB Connction on or off affects the multi-datasource situation.

Fortunately, a look at the source code shows that both iBatis and Spring are controlled via the standard DataSource interface

Connection, which saves us a lot of trouble, just need to implement a DataSource that can support multiple databases, to achieve our goal.

4. Code and implementation

MultiDataSource implementation: MultiDataSource. Class

Java code

1. import java.io.PrintWriter;
2. import java.sql.Connection;
3. import java.sql.SQLException;
4. import java.util.ArrayList;
5. import java.util.Collection;
6. import java.util.HashMap;
7. import java.util.Map;
9. import javax.sql.DataSource;
11. import org.apache.log4j.Logger;
13. import com.xxx.sql.DataSourceRouter.RouterStrategy;
15. / * *
16. * Composite multiple Data Sources (Alpha)
17. * @author [email protected]
18. * Jul 15, 2010
19. * /
20. public class MultiDataSource implements DataSource {
22. static Logger logger = Logger.getLogger(MultiDataSource.class);
24. // The actual DataSource for the current thread
25. private ThreadLocal currentDataSourceHolder = new ThreadLocal();
26. // Use the DataSource of the key-value mapping
27. private Map<String , DataSource> mappedDataSources;
28. // Use a distributed DataSource that is horizontally shard
29. private ArrayList clusterDataSources;
31. public MultiDataSource(){
32. ​ mappedDataSources = new HashMap<String , DataSource>(4);
33. ​ clusterDataSources = new ArrayList(4);
34. }
36. / * *
37. * Database connection pool initialization
38. * This method is usually called when the Web application starts
39. * /
40. public void initialMultiDataSource(){
41. ​ for(DataSource ds : clusterDataSources){
42. if(ds ! = null){
43. ​ Connection conn = null;
44. ​ try {
45. ​ conn = ds.getConnection();
46. ​ } catch (SQLException e) {
47. ​ e.printStackTrace();
48. ​ } finally{
49. if(conn ! = null){
50. ​ try {
51. ​ conn.close();
52. ​ } catch (SQLException e) {
53. ​ e.printStackTrace();
54. ​ }
55. ​ conn = null;
56. ​ }
57. ​ }
58. ​ }
59. ​ }
60. ​ Collection dsCollection = mappedDataSources.values();
61. ​ for(DataSource ds : dsCollection){
62. if(ds ! = null){
63. ​ Connection conn = null;
64. ​ try {
65. ​ conn = ds.getConnection();
66. ​ } catch (SQLException e) {
67. ​ e.printStackTrace();
68. ​ } finally{
69. if(conn ! = null){
70. ​ try {
71. ​ conn.close();
72. ​ } catch (SQLException e) {
73. ​ e.printStackTrace();
74. ​ }
75. ​ conn = null;
76. ​ }
77. ​ }
78. ​ }
79. ​ }
80. }
81. / * *
82. * Gets the DataSource of the current thread binding
83. * @return
84. * /
85. public DataSource getCurrentDataSource() {
86. // If the routing policy exists and has been updated, the new DataSource is selected according to the routing algorithm
87. ​ RouterStrategy strategy = DataSourceRouter.currentRouterStrategy.get();
88. ​ if(strategy == null){
89. ​ throw new IllegalArgumentException(“DataSource RouterStrategy No found.”);
90. ​ }
91. if(strategy ! = null && strategy.isRefresh()){
92. ​ if(RouterStrategy.SRATEGY_TYPE_MAP.equals(strategy.getType())){
93. ​ this.choiceMappedDataSources(strategy.getKey());
94. ​
95. ​ }else if(RouterStrategy.SRATEGY_TYPE_CLUSTER.equals(strategy.getType())){
96. ​ this.routeClusterDataSources(strategy.getRouteFactor());
97. ​ }
98. ​ strategy.setRefresh(false);
99. ​ }
100. ​ return currentDataSourceHolder.get();
101. }
103. public Map<String, DataSource> getMappedDataSources() {
104. ​ return mappedDataSources;
105. }
107. public void setMappedDataSources(Map<String, DataSource> mappedDataSources) {
108. ​ this.mappedDataSources = mappedDataSources;
109. }
111. public ArrayList getClusterDataSources() {
112. ​ return clusterDataSources;
113. }
115. public void setClusterDataSources(ArrayList clusterDataSources) {
116. ​ this.clusterDataSources = clusterDataSources;
117. }
119. / * *
120. * Select the current data source with Key
121. * @param key
122. * /
123. public void choiceMappedDataSources(String key){
124. ​ DataSource ds = this.mappedDataSources.get(key);
125. ​ if(ds == null){
126. throw new IllegalStateException(“No Mapped DataSources Exist!” );
127. ​ }
128. ​ this.currentDataSourceHolder.set(ds);
129. }
131. / * *
132. * Route selection in the cluster data source using a modular algorithm
133. * @param routeFactor
134. * /
135. public void routeClusterDataSources(int routeFactor){
136. ​ int size = this.clusterDataSources.size();
137. ​ if(size == 0){
138. throw new IllegalStateException(“No Cluster DataSources Exist!” );
139. ​ }
140. ​ int choosen = routeFactor % size;
141. ​ DataSource ds = this.clusterDataSources.get(choosen);
142. ​ if(ds == null){
143. throw new IllegalStateException(“Choosen DataSources is null!” );
144. ​ }
145. ​ logger.debug(“Choosen DataSource No.” + choosen+ ” : ” + ds.toString());
146. ​ this.currentDataSourceHolder.set(ds);
147. }
149. /* (non-Javadoc)
150. * @see javax.sql.DataSource#getConnection()
151. * /
152. public Connection getConnection() throws SQLException {
153. if(getCurrentDataSource() ! = null){
154. ​ return getCurrentDataSource().getConnection();
155. ​ }
156. ​ return null;
157. }
159. /* (non-Javadoc)
160. * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
161. * /
162. public Connection getConnection(String username, String password)
163. ​ throws SQLException {
164. if(getCurrentDataSource() ! = null){
165. ​ return getCurrentDataSource().getConnection(username , password);
166. ​ }
167. ​ return null;
168. }
170. /* (non-Javadoc)
171. * @see javax.sql.CommonDataSource#getLogWriter()
172. * /
173. public PrintWriter getLogWriter() throws SQLException {
174. if(getCurrentDataSource() ! = null){
175. ​ return getCurrentDataSource().getLogWriter();
176. ​ }
177. ​ return null;
178. }
180. /* (non-Javadoc)
181. * @see javax.sql.CommonDataSource#getLoginTimeout()
182. * /
183. public int getLoginTimeout() throws SQLException {
184. if(getCurrentDataSource() ! = null){
185. ​ return getCurrentDataSource().getLoginTimeout();
186. ​ }
187. ​ return 0;
188. }
190. /* (non-Javadoc)
191. * @see javax.sql.CommonDataSource#setLogWriter(java.io.PrintWriter)
192. * /
193. public void setLogWriter(PrintWriter out) throws SQLException {
194. if(getCurrentDataSource() ! = null){
195. ​ getCurrentDataSource().setLogWriter(out);
196. ​ }
197. }
199. /* (non-Javadoc)
200. * @see javax.sql.CommonDataSource#setLoginTimeout(int)
201. * /
202. public void setLoginTimeout(int seconds) throws SQLException {
203. if(getCurrentDataSource() ! = null){
204. ​ getCurrentDataSource().setLoginTimeout(seconds);
205. ​ }
206. }
208. /* (non-Javadoc)
209. * The interface method since 1.6
210. * Not all DataSource implementations have this method
211. * @see java.sql.Wrapper#isWrapperFor(java.lang.Class)
212. * /
213. public boolean isWrapperFor(Class
     iface) throws SQLException {
214. ​
215. // if(getCurrentDataSource() ! = null){
216. // return getCurrentDataSource().isWrapperFor(iface);
217. // }
218. ​ return false;
219. }
221. /* (non-Javadoc)
222. * The interface method since 1.6
223. * Not all DataSource implementations have this method
224. * @see java.sql.Wrapper#unwrap(java.lang.Class)
225. * /
226. public T unwrap(Class iface) throws SQLException {
227. // if(getCurrentDataSource() ! = null){
228. // return getCurrentDataSource().unwrap(iface);
229. // }
230. ​ return null;
231. }
232. }

This class implements the standard DataSource interface, with the core getConnection() method overloading. The following is specific:

• 1. Instance variable clusterDataSources is an ArrayList of DataSource instances, which stores the DataSource instances of multiple databases. We use the IOC function of Spring to inject multiple DataSource instances into this list.
• MappedDataSources is a Map of DataSource instances. Like clusterDataSources, mappedDataSources is used to store DataSource instances of multiple databases. The difference is that mappedDataSources can directly obtain DataSource instances using key. We will also use Spring’s IOC functionality to inject multiple DataSource into the Map.
• CurrentDataSourceHolder is a ThreadLocal variable that holds the DataSource instances associated with the current thread. This is so that when accessing the same database multiple times in the same thread, there is no need to re-route.
• 4. When an external class calls the getConnection() method, the method selects the corresponding DataSource from clusterDataSources or mappedDataSources based on the routing rules of the context and returns the Connection.

(PS: You can design routing rules for a DataSource based on application scenarios. The author provides two simple ideas here. 1. According to HashCode, which can be UserId in the above example, carry out modular operation to locate the database. 2. Select the mapping DataSource from the map according to the key set in the context.

DataSourceRouter. Class:

1. / * *
2. * @author [email protected]
3. * Jul 15, 2010
4. * /
5. public class DataSourceRouter {
7. public static ThreadLocal currentRouterStrategy =
8. ​ new ThreadLocal();
10. / * *
11. * Set the routing policy for the MultiDataSource
12. * @param type
13. * @param key
14. * @param routeFactor
15. * /
16. public static void setRouterStrategy(String type , String key , int routeFactor){
17. ​ if(type == null){
18. ​ throw new IllegalArgumentException(“RouterStrategy Type must not be null”);
19. ​ }
20. ​ RouterStrategy rs = currentRouterStrategy.get();
21. ​ if(rs == null){
22. ​ rs = new RouterStrategy();
23. ​ currentRouterStrategy.set(rs);
24. ​ }
25. ​ rs.setType(type);
26. ​ rs.setKey(key);
27. ​ rs.setRouteFactor(routeFactor);
28. }
30. / * *
31. * Data source routing policy
32. * @author [email protected]
33. * Jul 15, 2010
34. * /
35. public static class RouterStrategy{
36. ​
37. ​ public static final String SRATEGY_TYPE_MAP = “MAP”;
38. ​ public static final String SRATEGY_TYPE_CLUSTER = “CLUSTER”;
39. / *
40. * Optional values “MAP”, “CLUSTER”
41. * MAP: Select DS from DataSourceMap based on key
42. * CLUSTER: CLUSTER is obtained by the algorithm based on the routeFactor parameter
43. * /
44. ​ private String type;
45. / *
46. * Key in “MAP” ROUTE
47. ​ *
48. * /
49. ​ private String key;
50. / *
51. * Parameter for “CLUSTER” ROUTE
52. * /
53. ​ private int routeFactor;
54. / *
55. * True indicates that the RouterStrategy has been updated
56. * False indicates no update
57. * /
58. ​ private boolean refresh;
59. ​
60. ​ public String getType() {
61. ​ return type;
62. ​ }
63. ​
64. ​ public void setType(String type) {
65. if(this.type ! = null && ! this.type.equals(type)){
66. ​ this.type = type;
67. ​ this.refresh = true;
68. }else if(this.type == null && type ! = null){
69. ​ this.type = type;
70. ​ this.refresh = true;
71. ​ }
72. ​ }
73. ​
74. ​ public String getKey() {
75. ​ return key;
76. ​ }
77. ​
78. ​ public void setKey(String key) {
79. if(this.key ! = null && ! this.key.equals(key)){
80. ​ this.key = key;
81. ​ this.refresh = true;
82. }else if(this.key == null && key ! = null){
83. ​ this.key = key;
84. ​ this.refresh = true;
85. ​ }
86. ​ }
87. ​
88. ​ public int getRouteFactor() {
89. ​ return routeFactor;
90. ​ }
91. ​
92. ​ public void setRouteFactor(int routeFactor) {
93. if(this.routeFactor ! = routeFactor){
94. ​ this.routeFactor = routeFactor;
95. ​ this.refresh = true;
96. ​ }
97. ​ }
98. ​
99. ​ public boolean isRefresh() {
100. ​ return refresh;
101. ​ }
102. ​
103. ​ public void setRefresh(boolean refresh) {
104. ​ this.refresh = refresh;
105. ​ }
106. }
108. }

5. Combine MultiDataSource with Spring and iBatis

After completing the above coding process, it is time to combine the MultiDataSource configuration with existing Spring and iBatis.

STEP 1. Configuring multiple data sources

Here I use C3P0 as the database connection pool, which is the same step as the standard Spring configuration, except that you now have multiple configurations instead of just one

Xml code

2. <bean id=”c3p0_dataSource_1″ class=”com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=”close”>
3. <property name=”driverClass”>
4. ​ ${jdbc.driverClass}
6. <property name=”jdbcUrl”>
7. ​ ${mysql.url_1}
8. ​
9. <property name=”user”>
10. ​ ${jdbc.username}
12. <property name=”password”>
13. ​ ${jdbc.password}
16. <property name=”minPoolSize”>
17. ​ ${c3p0.minPoolSize}
18. ​
20. ​ <property name=”maxPoolSize”>
21. ​ ${c3p0.maxPoolSize}
24. ​ <property name=”initialPoolSize”>
25. ​ ${c3p0.initialPoolSize}
28. ​ <property name=”idleConnectionTestPeriod”>
29. ​ ${c3p0.idleConnectionTestPeriod}
30. ​
34. <bean id=”c3p0_dataSource_2″ class=”com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=”close”>
35. <property name=”driverClass”>
36. ​ ${jdbc.driverClass}
38. <property name=”jdbcUrl”>
39. ​ ${mysql.url_2}
40. ​
41. <property name=”user”>
42. ​ ${jdbc.username}
44. <property name=”password”>
45. ​ ${jdbc.password}
48. <property name=”minPoolSize”>
49. ​ ${c3p0.minPoolSize}
50. ​
52. ​ <property name=”maxPoolSize”>
53. ​ ${c3p0.maxPoolSize}
56. ​ <property name=”initialPoolSize”>
57. ​ ${c3p0.initialPoolSize}
60. ​ <property name=”idleConnectionTestPeriod”>
61. ​ ${c3p0.idleConnectionTestPeriod}
62. ​
66. .

STEP 2. Inject multiple data sources into the MultiDataSource

Xml code

1. <bean id=”multiDataSource” class=”com.xxx.sql.MultiDataSource”>
2. <property name=”clusterDataSources”>
3. ​
4. ​ <ref bean=”c3p0_dataSource_1″ />
5. ​ <ref bean=”c3p0_dataSource_2″ />
6. ​ <ref bean=”c3p0_dataSource_3″ />
7. ​ <ref bean=”c3p0_dataSource_4″ />
8. ​ <ref bean=”c3p0_dataSource_5″ />
9. ​ <ref bean=”c3p0_dataSource_6″ />
10. ​ <ref bean=”c3p0_dataSource_7″ />
11. ​ <ref bean=”c3p0_dataSource_8″ />
12. ​
14. <property name=”mappedDataSources”>
15. ​
16. ​ <entry key=”system” value-ref=”c3p0_dataSource_system” />
17. ​

STEP 3. Use the MultiDataSource just as you would a standard DataSource

Xml code

2. <bean id=”sqlMapClient” class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”>
3. ​ <property name=”configLocation” value=”classpath:SqlMapConfig.xml”/>
4. <property name=”dataSource” ref=”multiDataSource”>
8. <bean id=”jdbc_TransactionManager” class=”org.springframework.jdbc.datasource.DataSourceTransactionManager”>
9. <property name=”dataSource” ref=”multiDataSource”>

At this point, our program can let Spring manage multi-library access, but note that database transactions are still limited to single-library scope (as mentioned earlier, there are no cross-library transactions in this scenario).

6.Java code usage examples

First of all, we only provide a simple example. In the example, we must manually call the API to determine the routing rules of the DataSource. In the actual application, you can encapsulate the routing rules according to your own business characteristics to achieve relatively transparent routing

Java code

1. public boolean addUserGameInfo(UserGameInfo userGameInfo){
2. //1. Route the data source according to usergameInfo. uid
3. DataSourceRouter.setRouterStrategy(
4. ​ RouterStrategy.SRATEGY_TYPE_CLUSTER ,
5. ​ null,
6. ​ userGameInfo.getUid());
8. //2. Database storage
9. try {
10. ​ userGameInfoDAO.insert(userGameInfo);
11. ​ return true;
12. } catch (SQLException e) {
13. ​ e.printStackTrace();
14. ​ logger.debug(“Insert UserGameInfo failed. ” + userGameInfo.toString());
15. }
16. return false;
17. }