1、2 Sharding-JDBC入門使用2.1不使用Spring引入Maven依賴<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-core</artifactId><version>$sharding-sphere.version</version></dependency>基于Java編碼的規則配置Sharding-JDBC的分庫分表通過規則配置描述，以下例子是根據user_id取模分

2、庫, 且根據order_id取模分表的兩庫兩表的配置。/ 配置真實數據源Map<String, DataSource> dataSourceMap = new HashMap<>();/ 配置第一個數據源BasicDataSource dataSource1 = new BasicDataSource(); dataSource1.setDriverClassName("com.mysql.jdbc.Driver"); dataSource1.setUrl("jdbc:mysql:/localhost:3306/ds0"); da

3、taSource1.setUsername("root"); dataSource1.setPassword("");dataSourceMap.put("ds0", dataSource1);/ 配置第二個數據源BasicDataSource dataSource2 = new BasicDataSource(); dataSource2.setDriverClassName("com.mysql.jdbc.Driver"); dataSource2.setUrl("jdbc:mysql:/localh

4、ost:3306/ds1"); dataSource2.setUsername("root"); dataSource2.setPassword("");dataSourceMap.put("ds1", dataSource2);/ 配置Order表規則TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();orderTableRuleConfig.setLogicTable("t_order"); orderT

5、ableRuleConfig.setActualDataNodes("ds$0.1.t_order$0.1");/ 配置分庫 + 分表策略orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds$user_id % 2");orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrateg

6、yConfiguration("order_id", "t_order$order_id % 2");/ 配置分片規則ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);/ 省略配置order_item表規則./ ./ 獲取數據源對象DataSource dataSource = ShardingDataSourceF

7、actory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties();基于Yaml的規則配置或通過Yaml方式配置，與以上配置等價：dataSources:ds0: !mons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/ds0 username: rootpassword:ds1: !mons.dbcp.BasicDataSo

8、urce driverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/ds1 username: rootpassword:tables:t_order:actualDataNodes: ds$0.1.t_order$0.1 databaseStrategy:inline:shardingColumn: user_id algorithmInlineExpression: ds$user_id % 2tableStrategy: inline:shardingColumn: order_id algorithmIn

9、lineExpression: t_order$order_id % 2t_order_item:actualDataNodes: ds$0.1.t_order_item$0.1 databaseStrategy:inline:shardingColumn: user_id algorithmInlineExpression: ds$user_id % 2tableStrategy: inline:shardingColumn: order_idalgorithmInlineExpression: t_order_item$order_id % 2 DataSource dataSource

10、=YamlShardingDataSourceFactory.createDataSource(yamlFile);使用原生JDBC通過ShardingDataSourceFactory或者YamlShardingDataSourceFactory工廠和規則配置對象獲取ShardingDataSource，ShardingDataSource實現自JDBC的標準接口DataSource。然后可通過DataSource選擇使用原生JDBC開發，或者使用JPA, MyBatis等ORM工具。 以JDBC原生實現為例：DataSource dataSource = YamlShardingDataS

11、ourceFactory.createDataSource(yamlFile); String sql = "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=?" try (Connection conn = dataSource.getConnection();PreparedStatement preparedStatement = conn.prepareStatement(sql)preparedStatem

12、ent.setInt(1, 10);preparedStatement.setInt(2, 1001);try (ResultSet rs = preparedStatement.executeQuery() while(rs.next() System.out.println(rs.getInt(1); System.out.println(rs.getInt(2);2.2使用Spring引入Maven依賴<!- for spring boot -><dependency><groupId>io.shardingsphere</groupId>

13、<artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>$sharding-sphere.version</version></dependency><!- for spring namespace -><dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</arti

14、factId><version>$sharding-sphere.version</version></dependency>基于Spring boot的規則配置s=ds0,ds1sharding.jdbc.datasource.ds0.type=mons.dbcp2.BasicDataSource sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver sharding.jdbc.datasource.ds0.u

15、rl=jdbc:mysql:/localhost:3306/ds0 sharding.jdbc.datasource.ds0.username=root sharding.jdbc.datasource.ds0.password=sharding.jdbc.datasource.ds1.type=mons.dbcp2.BasicDataSource sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver sharding.jdbc.datasource.ds1.url=jdbc:mysql:/localhost:

16、3306/ds1 sharding.jdbc.datasource.ds1.username=root sharding.jdbc.datasource.ds1.password=sharding.jdbc.config.sharding.defau atabase-strategy.inline.sharding- column=user_idsharding.jdbc.config.sharding.defau atabase-strategy.inline.algorithm- expression=ds$->user_id % 2sharding.jdbc.config.shar

17、ding.tables.t_order.actual-data-nodes=ds$->0.1.t_order$->0.1 sharding.jdbc.config.sharding.tables.t_order.table- strategy.inline.sharding-column=order_id sharding.jdbc.config.sharding.tables.t_order.table- strategy.inline.algorithm-expression=t_order$->order_id % 2sharding.jdbc.config.shard

18、ing.tables.t_order_item.actual-data-nodes=ds$->0.1.t_order_item$->0.1 sharding.jdbc.config.sharding.tables.t_order_item.table- strategy.inline.sharding-column=order_id sharding.jdbc.config.sharding.tables.t_order_item.table- strategy.inline.algorithm-expression=t_order_item$->order_id % 2基于

19、Spring命名空間的規則配置<?xml version="1.0" encoding="UTF-8"?><beans xmlns=" xmlns:xsi="""xmlns:sharding=" xsi:schemaLocation="""><bean id="ds0" class="mons.dbcp.BasicDataSource" destroy-method="close">

20、;<property name="driverClassName" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql:/localhost:3306/ds0" /><property name="username" value="root" /><property name="password" value="&q

21、uot; /></bean><bean id="ds1" class="mons.dbcp.BasicDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql:/localhost:3306/ds1&q

22、uot; /><property name="username" value="root" /><property name="password" value="" /></bean><sharding:inline-strategy id="databaseStrategy" sharding- column="user_id" algorithm-expression="ds$->user_id % 2&qu

23、ot; /><sharding:inline-strategy id="orderTableStrategy" sharding- column="order_id" algorithm-expression="t_order$->order_id % 2" /><sharding:inline-strategy id="orderItemTableStrategy" sharding- column="order_id" algorithm-expression=

24、"t_order_item$->order_id % 2" /><sharding:data-source id="shardingDataSource"><sharding:sharding-rule data-source-names="ds0,ds1"><sharding:table-rules><sharding:table-rule logic-table="t_order" actual-data- nodes="ds$->0.1.t

25、_order$->0.1" database-strategy- ref="databaseStrategy" table-strategy-ref="orderTableStrategy" /><sharding:table-rule logic-table="t_order_item" actual- data-nodes="ds$->0.1.t_order_item$->0.1" database-strategy- ref="databaseStrategy

26、" table-strategy-ref="orderItemTableStrategy" /></sharding:table-rules></sharding:sharding-rule></sharding:data-source></beans>在Spring中使用DataSource直接通過注入的方式即可使用DataSource，或者將DataSource配置在JPA、Hibernate或MyBatis中使用。Resourceprivate DataSource dataSource;2.3 數據源配

27、置yaml格式dataSources:ds0: !mons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/ds0 username: rootpassword:ds1: !mons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql:/localhost:3306/ds1 username: rootpassword:props:sql.show: truespr

28、ing boot 配置s=ds0,ds1sharding.jdbc.datasource.ds0.type=mons.dbcp.BasicDataSource sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver sharding.jdbc.datasource.ds0.url=jdbc:mysql:/localhost:3306/ds0 sharding.jdbc.datasource.ds0.username=root sharding.jdbc.d

29、atasource.ds0.password=sharding.jdbc.datasource.ds1.type=mons.dbcp.BasicDataSource sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver sharding.jdbc.datasource.ds1.url=jdbc:mysql:/localhost:3306/ds1 sharding.jdbc.datasource.ds1.username=root sharding.jdbc.datasource.ds1.password=數據源

30、配置說明dataSources: # 配置數據源列表,必須是有效的jdbc配置,目前僅支持MySQL與PostgreSQL,另外通過一些未公開(代碼中可查,但可能會在未來有變化)的變量,可以配置來兼容其他支持JDBC的數 據庫,但由于沒有足夠的測試支持,可能會有嚴重的兼容性問題,配置時候要求至少有一個master_ds_0: # 數據源名稱,可以是合法的字符串,目前的校驗規則中,沒有強制性要求,只要是合法的yaml字符串即可,但如果要用于分庫分表配置,則需要有有意義的標志(在分庫分表配置中詳述),以下為目前公開的合法配置項目,不包含內部配置參數# 以下參數為必備參數url: jdbc:mysq

31、l:/:3306/demo_ds_slave_1?serverTimezone=UTC&useSSL=false # 這里的要求合法的jdbc連接串即可,目前尚未兼容MySQL 8.x,需要在maven編譯時候,升級MySQL JDBC版本到5.1.46或者47版本(不建議升級到JDBC的8.x系列版本,需要修改源代碼,并且無法通過很多測試case)username: root # MySQL用戶名password: password # MySQL用戶的明文# 以下參數為可選參數,給出示例為默認配置,主要用于連接池connectionTimeoutMilliseco

32、nds: 30000 #連接超時idleTimeoutMilliseconds: 60000 # 連接空閑時間設置maxLifetimeMilliseconds: 0 # 連接的最大持有時間,0為maxPoolSize: 50 # 連接池中最大維持的連接數量minPoolSize: 1 # 連接池的最小連接數量maintenanceIntervalMilliseconds: 30000 # 連接維護的時間間隔 atomikos框架需求2.4 屬性配置配置示例props:sql.show: true可配置屬性說明props:sql.show: #是否開啟SQL顯示，默認值: false acce

33、ptor.size: # accept連接的線程數量,默認為cpu核數2倍executor.size: #工作線程數量最大，默認值:max.connections.size.per.query: # 每個 可以打開的最大連接數量,默認為1 check.table.metadata.enabled: #是否在啟動時檢查分表元數據一致性，默認值: false proxy.frontend.flush.threshold: # proxy的服務時候,對于單個大 ,每多少個網絡包返回一次proxy.transaction.type: # 默認LOCAL,proxy的事務模型 LOCAL,XA,BASE

34、三個值LOCAL無分布式事務,XA則是采用atomikos實現的分布式事務 BASE目前尚未實現proxy.opentracing.enabled: # 是否啟用opentracingproxy.backend.use.nio: # 是否采用netty的NIO機制連接后端數據庫,默認False ,使用epoll機制proxy.backend.max.connections: # 使用NIO而非epoll的話,proxy 連接每個netty 客戶端 的最大連接數量(注意不是數據庫連接限制) 默認為8proxy.backend.connection.timeout.seconds: #使用nio而

35、非epoll的話,proxy 連接的超時時間,默認60s2.5 spring boot 示例maven 依賴 pom.xml<?xml version="1.0" encoding="UTF-8"?><project xmlns=" xmlns:xsi="xsi:schemaLocation=""""><mVersion>4.0.0</mVersion><parent><groupId>org.springframework

36、.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.1.3.RELEASE</version><relativePath/> <!- lookup parent from repository -></parent><groupId>com.study.mike</groupId><artifactId>sharding-jdbc-study</artifa

37、ctId><version>0.0.1-SNAPSHOT</version><name>sharding-jdbc-study</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>or

38、g.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.0.0</version></depend

39、ency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifa