学习sharding-jdbc 分库分表扩展框架-摩杜云开发者社区

前一段时间研究阿里的分库框架cobar-client，cobar-client是基于ibatis的SqlMapClientTemplate进行了一层薄薄的封装，分装成CobarSqlMapClientTemplate，在用户在CRUD的时候可以透明的进行操作，算是现在大多公司分库的一个成熟解决方案，不过现在面临的一些问题：

①不支持分表

②基于ibatis而且2013年后基本维护了，没有进行升级，所以大多公司都基于该思想进行了自己的重写

来看下当当开源的sharding-jdbc，官方网址：点击打开链接

先允许我盗一波图：

学习sharding-jdbc 分库分表扩展框架_bc

学习sharding-jdbc 分库分表扩展框架_java_02

好了，看了这么多的介绍，感觉还是很高大上的，注意点有：

①对JDBC API进行了原生态的分装，这是与cobar-client不一样的地方，这就是他可以支持多个第三方ORM框架的关键

②可支持=，BETWEEN，IN等操作，说明，JDBC返回结果后，sharding进行了合并操作，这里面肯定会有性能损耗

③支持分表，这也是cobar-client不支持的地方

好了，先简单的按照官方网址的demo实践一发：

先在MySQL中建2个库

学习sharding-jdbc 分库分表扩展框架_bc_03

分别在这2个库中运行：

1. CREATE TABLE IF NOT EXISTS `t_order_0` (  
2. INT NOT NULL,  
3. INT NOT NULL,  
4. PRIMARY KEY (`order_id`)  
5. );  
6. CREATE TABLE IF NOT EXISTS `t_order_item_0` (  
7. INT NOT NULL,  
8. INT NOT NULL,  
9. INT NOT NULL,  
10. PRIMARY KEY (`item_id`)  
11. );  
12. CREATE TABLE IF NOT EXISTS `t_order_1` (  
13. INT NOT NULL,  
14. INT NOT NULL,  
15. PRIMARY KEY (`order_id`)  
16. );  
17. CREATE TABLE IF NOT EXISTS `t_order_item_1` (  
18. INT NOT NULL,  
19. INT NOT NULL,  
20. INT NOT NULL,  
21. PRIMARY KEY (`item_id`)  
22. );

新建maven项目

学习sharding-jdbc 分库分表扩展框架_java_04

Maven依赖的pom.xml

1. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
2. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  
3. <modelVersion>4.0.0</modelVersion>  
4.   
5. <groupId>org.study</groupId>  
6. <artifactId>sharding-jdbc</artifactId>  
7. <version>0.0.1-SNAPSHOT</version>  
8. <packaging>jar</packaging>  
9.   
10. <name>sharding-jdbc</name>  
11. <url>http://maven.apache.org</url>  
12.   
13. <properties>  
14. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
15. <spring.version>3.2.5.RELEASE</spring.version>  
16. <mybatis.version>3.2.4</mybatis.version>  
17. </properties>  
18.   
19. <dependencies>  
20. <dependency>  
21. <groupId>junit</groupId>  
22. <artifactId>junit</artifactId>  
23. <version>4.10</version>  
24. </dependency>  
25. <dependency>  
26. <groupId>com.dangdang</groupId>  
27. <artifactId>sharding-jdbc-core</artifactId>  
28. <version>1.0.0</version>  
29. </dependency>  
30. <dependency>  
31. <groupId>org.springframework</groupId>  
32. <artifactId>spring-orm</artifactId>  
33. <version>${spring.version}</version>  
34. </dependency>  
35. <dependency>  
36. <groupId>commons-dbcp</groupId>  
37. <artifactId>commons-dbcp</artifactId>  
38. <version>1.4</version>  
39. </dependency>  
40. <dependency>  
41. <groupId>org.mybatis</groupId>  
42. <artifactId>mybatis-spring</artifactId>  
43. <version>1.2.2</version>  
44. </dependency>  
45. <dependency>  
46. <groupId>org.mybatis</groupId>  
47. <artifactId>mybatis</artifactId>  
48. <version>${mybatis.version}</version>  
49. </dependency>  
50.   
51. <dependency>  
52. <groupId>org.springframework</groupId>  
53. <artifactId>spring-expression</artifactId>  
54. <version>${spring.version}</version>  
55. </dependency>  
56. <dependency>  
57. <groupId>org.springframework</groupId>  
58. <artifactId>spring-aop</artifactId>  
59. <version>${spring.version}</version>  
60. </dependency>  
61. <dependency>  
62. <groupId>org.springframework</groupId>  
63. <artifactId>spring-beans</artifactId>  
64. <version>${spring.version}</version>  
65. </dependency>  
66. <dependency>  
67. <groupId>org.springframework</groupId>  
68. <artifactId>spring-context</artifactId>  
69. <version>${spring.version}</version>  
70. </dependency>  
71. <dependency>  
72. <groupId>org.springframework</groupId>  
73. <artifactId>spring-context-support</artifactId>  
74. <version>${spring.version}</version>  
75. </dependency>  
76. <dependency>  
77. <groupId>org.springframework</groupId>  
78. <artifactId>spring-test</artifactId>  
79. <version>${spring.version}</version>  
80. </dependency>  
81. <dependency>  
82. <groupId>org.springframework</groupId>  
83. <artifactId>spring-tx</artifactId>  
84. <version>${spring.version}</version>  
85. </dependency>  
86. <dependency>  
87. <groupId>mysql</groupId>  
88. <artifactId>mysql-connector-java</artifactId>  
89. <version>5.1.28</version>  
90. </dependency>  
91. <dependency>  
92. <groupId>log4j</groupId>  
93. <artifactId>log4j</artifactId>  
94. <version>1.2.16</version>  
95. </dependency>  
96. <dependency>  
97. <groupId>org.slf4j</groupId>  
98. <artifactId>slf4j-log4j12</artifactId>  
99. <version>1.7.5</version>  
100. </dependency>  
101. </dependencies>  
102. </project>

ShardingJdbc

ModuloDatabaseShardingAlgorithm

1. package com.study.base;  
2.   
3. import java.sql.Connection;  
4. import java.sql.PreparedStatement;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7. import java.util.Arrays;  
8. import java.util.HashMap;  
9. import java.util.Map;  
10.   
11. import javax.sql.DataSource;  
12.   
13. import org.apache.commons.dbcp.BasicDataSource;  
14.   
15. import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource;  
16. import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;  
17. import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;  
18. import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;  
19. import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;  
20. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;  
21. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;  
22.   
23. public class ShardingJdbc {  
24.       
25. public static void main(String[] args) throws SQLException {  
26.           
27. //数据源  
28. new HashMap<>(2);  
29. "sharding_0", createDataSource("sharding_0"));  
30. "sharding_1", createDataSource("sharding_1"));  
31.           
32. new DataSourceRule(dataSourceMap);  
33.           
34. //分表分库的表，第一个参数是逻辑表名，第二个是实际表名，第三个是实际库  
35. new TableRule("t_order", Arrays.asList("t_order_0", "t_order_1"), dataSourceRule);  
36. new TableRule("t_order_item", Arrays.asList("t_order_item_0", "t_order_item_1"), dataSourceRule);  
37.           
38.           
39.           
40. /**
41.          * DatabaseShardingStrategy 分库策略
42.          * 参数一：根据哪个字段分库
43.          * 参数二：分库路由函数
44.          * TableShardingStrategy 分表策略
45.          * 参数一：根据哪个字段分表
46.          * 参数二：分表路由函数
47.          * 
48.          */  
49. new ShardingRule(dataSourceRule, Arrays.asList(orderTableRule, orderItemTableRule),  
50. new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))),  
51. new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()),  
52. new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()));  
53.           
54.           
55. new ShardingDataSource(shardingRule);  
56. "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";  
57. try (  
58.                 Connection conn = dataSource.getConnection();  
59.                 PreparedStatement pstmt = conn.prepareStatement(sql)) {  
60. 1, 10);  
61. 2, 1001);  
62. try (ResultSet rs = pstmt.executeQuery()) {  
63. while(rs.next()) {  
64. 1));  
65. 2));  
66. 3));  
67.                 }  
68.             }  
69.         }  
70.     }  
71.   
72. /**
73.      * 创建数据源
74.      * @param dataSourceName
75.      * @return
76.      */  
77. private static DataSource createDataSource(String dataSourceName) {  
78. new BasicDataSource();  
79. class.getName());  
80. "jdbc:mysql://localhost:3306/%s", dataSourceName));  
81. "root");  
82. "");  
83. return result;  
84.     }  
85.   
86. }

1. package com.study.base;  
2.   
3. import java.util.Collection;  
4. import java.util.LinkedHashSet;  
5.   
6. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
7. import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;  
8. import com.google.common.collect.Range;  
9.   
10. /**
11.  * 
12.  * @author lyncc
13.  *
14.  */  
15. public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{  
16.   
17. @Override  
18. public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {  
19. for (String each : availableTargetNames) {  
20. if (each.endsWith(shardingValue.getValue() % 2 + "")) {  
21. return each;  
22.             }  
23.         }  
24. throw new IllegalArgumentException();  
25.     }  
26.   
27. @Override  
28. public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {  
29. new LinkedHashSet<>(availableTargetNames.size());  
30. for (Integer value : shardingValue.getValues()) {  
31. for (String tableName : availableTargetNames) {  
32. if (tableName.endsWith(value % 2 + "")) {  
33.                     result.add(tableName);  
34.                 }  
35.             }  
36.         }  
37. return result;  
38.     }  
39.   
40. @Override  
41. public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,  
42.             ShardingValue<Integer> shardingValue) {  
43. new LinkedHashSet<>(availableTargetNames.size());  
44.         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
45. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {  
46. for (String each : availableTargetNames) {  
47. if (each.endsWith(i % 2 + "")) {  
48.                     result.add(each);  
49.                 }  
50.             }  
51.         }  
52. return result;  
53.     }  
54.   
55. }

ModuloTableShardingAlgorithm.java

1. package com.study.base;  
2. import java.util.Collection;  
3. import java.util.LinkedHashSet;  
4.   
5. import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
6. import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;  
7. import com.google.common.collect.Range;  
8.   
9. public final class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {  
10.       
11. /**
12.     *  select * from t_order from t_order where order_id = 11 
13.     *          └── SELECT *  FROM t_order_1 WHERE order_id = 11
14.     *  select * from t_order from t_order where order_id = 44
15.     *          └── SELECT *  FROM t_order_0 WHERE order_id = 44
16.     */  
17. public String doEqualSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {  
18. for (String each : tableNames) {  
19. if (each.endsWith(shardingValue.getValue() % 2 + "")) {  
20. return each;  
21.             }  
22.         }  
23. throw new IllegalArgumentException();  
24.     }  
25.       
26. /**
27.     *  select * from t_order from t_order where order_id in (11,44)  
28.     *          ├── SELECT *  FROM t_order_0 WHERE order_id IN (11,44) 
29.     *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,44) 
30.     *  select * from t_order from t_order where order_id in (11,13,15)  
31.     *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,13,15)  
32.     *  select * from t_order from t_order where order_id in (22,24,26)  
33.     *          └──SELECT *  FROM t_order_0 WHERE order_id IN (22,24,26) 
34.     */  
35. public Collection<String> doInSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {  
36. new LinkedHashSet<>(tableNames.size());  
37. for (Integer value : shardingValue.getValues()) {  
38. for (String tableName : tableNames) {  
39. if (tableName.endsWith(value % 2 + "")) {  
40.                     result.add(tableName);  
41.                 }  
42.             }  
43.         }  
44. return result;  
45.     }  
46.       
47. /**
48.     *  select * from t_order from t_order where order_id between 10 and 20 
49.     *          ├── SELECT *  FROM t_order_0 WHERE order_id BETWEEN 10 AND 20 
50.     *          └── SELECT *  FROM t_order_1 WHERE order_id BETWEEN 10 AND 20 
51.     */  
52. public Collection<String> doBetweenSharding(final Collection<String> tableNames, final ShardingValue<Integer> shardingValue) {  
53. new LinkedHashSet<>(tableNames.size());  
54.         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
55. for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {  
56. for (String each : tableNames) {  
57. if (each.endsWith(i % 2 + "")) {  
58.                     result.add(each);  
59.                 }  
60.             }  
61.         }  
62. return result;  
63.     }  
64. }

log4j.xml

好了，按照官方教程说明：

1. <?xml version="1.0" encoding="UTF-8"?>      
2. <!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">    
3. <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">    
4. <!-- [控制台STDOUT] -->    
5. <appender name="console" class="org.apache.log4j.ConsoleAppender">    
6. <param name="encoding" value="GBK" />    
7. <param name="target" value="System.out" />    
8. <layout class="org.apache.log4j.PatternLayout">    
9. <param name="ConversionPattern" value="%-5p %c{2} - %m%n" />    
10. </layout>    
11. </appender>    
12.     
13. <!-- [公共Appender] -->    
14. <appender name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">    
15. <param name="File" value="${webapp.root}/logs/common-default.log" />    
16. <param name="Append" value="true" />    
17. <param name="encoding" value="GBK" />    
18. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
19. <layout class="org.apache.log4j.PatternLayout">    
20. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
21. </layout>    
22. </appender>    
23.     
24. <!-- [错误日志APPENDER] -->    
25. <appender name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">    
26. <param name="File" value="${webapp.root}/logs/common-error.log" />    
27. <param name="Append" value="true" />    
28. <param name="encoding" value="GBK" />    
29. <param name="threshold" value="error" />    
30. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
31. <layout class="org.apache.log4j.PatternLayout">    
32. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
33. </layout>    
34. </appender>    
35.     
36. <!-- [组件日志APPENDER] -->    
37. <appender name="COMPONENT-APPENDER"    
38. class="org.apache.log4j.DailyRollingFileAppender">    
39. <param name="File" value="${webapp.root}/logs/logistics-component.log" />    
40. <param name="Append" value="true" />    
41. <param name="encoding" value="GBK" />    
42. <param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />    
43. <layout class="org.apache.log4j.PatternLayout">    
44. <param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />    
45. </layout>    
46. </appender>    
47.     
48. <!-- [组件日志] -->    
49. <logger name="LOGISTICS-COMPONENT">    
50. <level value="${loggingLevel}" />    
51. <appender-ref ref="COMPONENT-APPENDER" />    
52. <appender-ref ref="ERROR-APPENDER" />    
53. </logger>    
54.     
55. <!-- Root Logger -->    
56. <root>    
57. <level value="${rootLevel}"></level>    
58. <appender-ref ref="DEFAULT-APPENDER" />    
59. <appender-ref ref="ERROR-APPENDER" />    
60. <appender-ref ref="console" />   
61. <appender-ref ref="COMPONENT-APPENDER" />   
62. </root>    
63. </log4j:configuration>

学习sharding-jdbc 分库分表扩展框架_bc_05

我们现在user_id是10，order_id是1001

我们应该在sharding0库中的t_order_1和t_order_item_1中新建数据：

1. INSERT INTO `t_order_1` VALUES ('1001', '10');  
2.   
3. INSERT INTO `t_order_item_1` VALUES ('4', '1001', '2');

好了，准备工作做完了，我们运行main函数，运行结果为：

学习sharding-jdbc 分库分表扩展框架_spring_06

好了，sharding-jdbc正常工作了