網(wǎng)易高級(jí)配套課件代碼sharding補(bǔ)充02入門(mén)使用

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

2、庫(kù), 且根據(jù)order_id取模分表的兩庫(kù)兩表的配置。/ 配置真實(shí)數(shù)據(jù)源Map<String, DataSource> dataSourceMap = new HashMap<>();/ 配置第一個(gè)數(shù)據(jù)源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);/ 配置第二個(gè)數(shù)據(jù)源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表規(guī)則TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();orderTableRuleConfig.setLogicTable("t_order"); orderT

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

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

7、actory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties();基于Yaml的規(guī)則配置或通過(guò)Yaml方式配置，與以上配置等價(jià)：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通過(guò)ShardingDataSourceFactory或者YamlShardingDataSourceFactory工廠和規(guī)則配置對(duì)象獲取ShardingDataSource，ShardingDataSource實(shí)現(xiàn)自JDBC的標(biāo)準(zhǔn)接口DataSource。然后可通過(guò)DataSource選擇使用原生JDBC開(kāi)發(fā)，或者使用JPA, MyBatis等ORM工具。 以JDBC原生實(shí)現(xiàn)為例：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的規(guī)則配置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命名空間的規(guī)則配置<?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直接通過(guò)注入的方式即可使用DataSource，或者將DataSource配置在JPA、Hibernate或MyBatis中使用。Resourceprivate DataSource dataSource;2.3 數(shù)據(jù)源配

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=數(shù)據(jù)源

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

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

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

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

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

35、非epoll的話,proxy 連接的超時(shí)時(shí)間,默認(rèn)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