Sharding-Jdbc（3）：Sharding-Jdbc分表

1 分表分库

LogicTable

数据分片的逻辑表，对于水平拆分的数据库(表)，同一类表的总称。

订单信息表拆分为2张表,分别是t_order_0、t_order_1，他们的逻辑表名为t_order。

ActualTable

在分片的数据库中真实存在的物理表。即上个示例中的t_order_0、t_order_1。

DataNode

数据分片的最小单元。由数据源名称和数据表组成，例：test_msg0.t_order_0。配置时默认各个分片数据库的表结构均相同，直接配置逻辑表和真实表对应关系即可。

ShardingColumn

分片字段。用于将数据库(表)水平拆分的关键字段。SQL中如果无分片字段，将执行全路由，性能较差。Sharding-JDBC支持多分片字段。

ShardingAlgorithm

分片算法。Sharding-JDBC通过分片算法将数据分片，支持通过等号、BETWEEN和IN分片。分片算法目前需要业务方开发者自行实现，可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法，如range，hash和tag等。

2 自定义分片模式

2.1 数据库表结构

创建ds_0数据库，新建表如下：

CREATE TABLE `t_order_0` (`order_id` bigint(20) NOT NULL,`user_id` bigint(20) NOT NULL,PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;CREATE TABLE `t_order_1` (`order_id` bigint(20) NOT NULL,`user_id` bigint(20) NOT NULL,PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3 新建maven项目

    <parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.0.3.RELEASE</version><relativePath /> <!-- lookup parent from repository --></parent><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><java.version>1.8</java.version></properties><dependencies><!-- jpa --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- 引入shardingjdbc依赖信息 --><dependency><groupId>io.shardingjdbc</groupId><artifactId>sharding-jdbc-core</artifactId><version>2.0.3</version></dependency><dependency><groupId>com.dangdang</groupId><artifactId>sharding-jdbc-self-id-generator</artifactId><version>1.4.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.0.12</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build>

4 application配置

###数据库访问连接
spring:jdbc:db0:password: 123456className: com.mysql.jdbc.Driverurl: jdbc:mysql://192.168.222.157:3306/%s?characterEncoding=utf-8username: rootjpa:database: mysqlshow-sql: truehibernate:## 自己建表ddl-auto: noneapplication:name: sharding-jdbc-first

5 配置分表算法

5.1 配置数据库连接

package com.example.demo.config;import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;// 数据源相关配置信息
@Configuration
public class DataSourceConfig {@Value("${spring.jdbc.db0.className}")private String className;@Value("${spring.jdbc.db0.url}")private String url;@Value("${spring.jdbc.db0.username}")private String username;@Value("${spring.jdbc.db0.password}")private String password;@Beanpublic IdGenerator getIdGenerator() {return new CommonSelfIdGenerator();}@Beanpublic DataSource getDataSource() {return buildDataSource();}private DataSource buildDataSource() {// 1.设置分库映射Map<String, DataSource> dataSourceMap = new HashMap<>(2);dataSourceMap.put("ds_0", createDataSource("ds_0"));// dataSourceMap.put("ds_1", createDataSource("ds_1"));// 设置默认db为ds_0，也就是为那些没有配置分库分表策略的指定的默认库// 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，// 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");// 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1")).dataSourceRule(rule).build();// 3.具体的分库分表策略ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule).tableRules(Arrays.asList(orderTableRule))// 根据userid分片字段.tableShardingStrategy(new TableShardingStrategy("user_id", new TableShardingAlgorithm())).build();// 创建数据源DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);return dataSource;}private DataSource createDataSource(String dataSourceName) {// 使用druid连接数据库DruidDataSource druidDataSource = new DruidDataSource();druidDataSource.setDriverClassName(className);druidDataSource.setUrl(String.format(url, dataSourceName));druidDataSource.setUsername(username);druidDataSource.setPassword(password);return druidDataSource;}
}

5.2 配置分表策略

分表算法类需要实现SingleKeyTableShardingAlgorithm<T>接口

package com.example.demo.config;import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;import java.util.Collection;// 表分片算法
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {// sql 中关键字 匹配符为 =的时候，表的路由函数public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {for (String tableName : availableTargetNames) {if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {return tableName;}}throw new IllegalArgumentException();}@Overridepublic Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {return null;}@Overridepublic Collection<String> doBetweenSharding(Collection<String> availableTargetNames,ShardingValue<Long> shardingValue) {return null;}}

6 新建实体类

package com.example.demo.entity;import lombok.Data;import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {@Idprivate Long orderId;private Long userId;}

7 OrderRepository

package com.example.demo.repository;import com.example.demo.entity.OrderEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;import java.util.List;public interface OrderRepository extends CrudRepository<OrderEntity, Long> {@Query(value = "SELECT order_id ,user_id  FROM t_order  where order_id in (?1);", nativeQuery = true)public List<OrderEntity> findExpiredOrderState(List<String> bpIds);
}

8 OrderController

package com.example.demo.controller;import com.example.demo.entity.OrderEntity;
import com.example.demo.repository.OrderRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.ArrayList;
import java.util.List;@RestController
public class OrderController {@Autowiredprivate OrderRepository orderRepository;// 查询所有的订单信息@RequestMapping("/getOrderAll")public List<OrderEntity> getOrderAll() {return (List<OrderEntity>) orderRepository.findAll();}// 使用in条件查询@RequestMapping("/inOrder")public List<OrderEntity> inOrder() {List<String> ids = new ArrayList<>();ids.add("2");ids.add("3");ids.add("4");ids.add("5");return orderRepository.findExpiredOrderState(ids);}// 增加@RequestMapping("/inserOrder")public String inserOrder(OrderEntity orderEntity) {for (int i = 0; i < 10; i++) {OrderEntity order = new OrderEntity();order.setOrderId((long) i);order.setUserId((long) i);orderRepository.save(order);}return "success";}}

9 新建启动类

package com.example.demo;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;@SpringBootApplication
@EnableJpaRepositories(basePackages = "com.example.demo.repository")
public class AppSharding {public static void main(String[] args) {SpringApplication.run(AppSharding.class, args);}
}

10 启动项目

访问http://localhost:8080/inserOrder

查看数据库

分表测试成功