1 Sharding-Jdbc
分表分库
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 SpringBoot
整合 Sharding-Jdbc
分为两种方式
数据库表结构
创建 test
数据库:
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;
2.1 方式一:原生配置方式,自己需要实现接口
分库算法
类需要实现SingleKeyDatabaseShardingAlgorithm<T>
接口分表算法
类需要实现SingleKeyTableShardingAlgorithm<T>
接口
Ⅰ 依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.snow</groupId>
<artifactId>springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot</name>
<description>Demo project for Spring Boot</description>
<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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.23</version>
</dependency>
<!-- 引入sharding-jdbc依赖 -->
<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>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Ⅱ 启动项
package com.snow;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow
* @Description : 启动项
* @date :2020/4/8 22:25
*/
@SpringBootApplication
@EnableJpaRepositories("com.snow.repository")
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
}
Ⅲ application.yml
spring:
application:
name: app-snow-mysql
jdbc:
db0:
className: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.153.101:3306/%s?characterEncoding=utf-8&serverTimezone=GMT
username: root
password: 123456
jpa:
database: mysql
show-sql: true
hibernate:
## 自己建表
ddl-auto: none
Ⅳ 实体类
package com.snow.entity;
import lombok.Data;
import lombok.ToString;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.entity
* @Description : User 实体类
* @date :2020/4/8 23:13
*/
@Data
@ToString
@Entity
@Table(name = "t_order")
public class Order {
/**
* 订单ID
*/
@Id
private Long orderId;
/**
* 用户ID
*/
private Long userId;
}
Ⅴ OrderRepository
package com.snow.repository;
import com.snow.entity.Order;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.repository
* @Description : OrderRepository
* @date :2020/4/9 21:10
*/
public interface OrderRepository extends CrudRepository<Order, Long> {
@Query(value = "SELECT order_id, user_id FROM t_order WHERE order_id IN (?1);", nativeQuery = true)
public List<Order> findExpiredOrderState(List<String> bpIds);
@Query(value = "SELECT order_id, user_id FROM t_order WHERE user_id=:userId ", nativeQuery = true)
public List<Order> findByUserId(@Param("userId") Long userId);
}
Ⅵ OrderController
package com.snow.controller;
import com.snow.entity.Order;
import com.snow.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;
import java.util.Optional;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.controller
* @Description : OrderController
* @date :2020/4/8 22:25
*/
@RestController
public class OrderController {
@Autowired
private OrderRepository orderRepository;
// 查询所有的订单信息
@RequestMapping("/getOrderAll")
public List<Order> getOrderAll() {
List<Order> listOrder = (List<Order>) orderRepository.findAll();
System.out.println("总数:" + listOrder.size());
return listOrder;
}
@RequestMapping("/findIdByOrder")
public Optional<Order> findIdByOrder(Long id) {
return orderRepository.findById(id);
}
@RequestMapping("/findIdByUserId")
public List<Order> findIdByUserId(Long userId) {
return orderRepository.findByUserId(userId);
}
// 使用in条件查询
@RequestMapping("/inOrder")
public List<Order> 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(Order order) {
for (int i = 0; i < 100; i++) {
Order order1 = new Order();
order1.setOrderId((long) i);
order1.setUserId((long) i);
// 根据userid进行分片存放
orderRepository.save(order1);
}
return "success";
}
}
① 单库分表算法
分表算法类需要实现 SingleKeyTableShardingAlgorithm<T>
接口
Ⅰ DataSourceConfig
package com.snow.config;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
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;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.config
* @Description : 数据源相关配置信息
* @date :2020/4/9 21:46
*/
@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;
// 配置自动增长的策略
@Bean
public IdGenerator getIdGenerator() {
return new CommonSelfIdGenerator();
}
// 创建数据源
@Bean
public DataSource getDataSource() {
return buildDataSource();
}
private DataSource buildDataSource() {
// 1.设置分库映射
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
dataSourceMap.put("ds_0", createDataSource("ds_0"));
// 设置默认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;
}
}
Ⅱ TableShardingAlgorithm
package com.snow.config;
import java.util.Collection;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.config
* @Description : 分表策略
* @date :2020/4/9 21:54
*/
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
// sql 中关键字 匹配符为 =的时候,表的路由函数
@Override
public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
return tableName;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
return null;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
return null;
}
}
测试
启动项目,浏览器访问:http://127.0.0.1:8080/inserOrder
浏览器访问:http://127.0.0.1:8080/getOrderAll
② 多库分表算法
数据库环境准备
CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
新建两个数据库,下面均创建 t_order
表:
Ⅰ 创建多库分表配置文件
package com.snow.config;
import java.util.Collection;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.config
* @Description : 多库分表策略配置
* @date :2020/4/9 22:25
*/
public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
// 考虑表的数据存放在那个库里面
@Override
public String doEqualSharding(Collection<String> databases, ShardingValue<Long> shardingValue) {
for (String databaseName : databases) {
if (databaseName.endsWith(shardingValue.getValue() % 2 + "")) {
return databaseName;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
return null;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
ShardingValue<Long> shardingValue) {
return null;
}
}
Ⅱ 修改 DataSourceConfig
package com.snow.config;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
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.database.DatabaseShardingStrategy;
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;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.config
* @Description : 数据源相关配置信息
* @date :2020/4/9 21:46
*/
@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;
// 配置自动增长的策略
@Bean
public IdGenerator getIdGenerator() {
return new CommonSelfIdGenerator();
}
// 创建数据源
@Bean
public 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()))
.databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DatabaseShardingAlgorithm()))
.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;
}
}
测试
启动项目,浏览器访问:http://127.0.0.1:8080/inserOrder
浏览器访问:http://127.0.0.1:8080/getOrderAll
2.2 方式二:SpringBoot
集成
Ⅰ 依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.snow</groupId>
<artifactId>springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot</name>
<description>Demo project for Spring Boot</description>
<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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<!-- 引入spring-boot整合sharding-jdbc依赖 -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0.M3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Ⅱ 配置文件
###springjpa配置
spring:
jpa:
show-sql: true
hibernate:
ddl-auto: none
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
sharding:
jdbc:
####ds1
datasource:
names: ds1
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.153.101:3306/ds_1?characterEncoding=utf-8
username: root
password: 123456
#### 分片配置
config:
sharding:
tables:
####t_order表分表策略
t_order:
table-strategy:
inline:
#### 根据userid 进行分片
sharding-column: user_id
#### ds_1.t_order_0 ds_1.t_order_1
algorithm-expression: ds_1.t_order_$->{user_id % 2}
###分表的总数 0到1 t_order_0 t_order_1
actual-data-nodes: ds1.t_order_$->{0..1}
props:
sql:
### 开启分片日志
show: true
Ⅲ 启动项
package com.snow;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow
* @Description : 启动项
* @date :2020/4/8 22:25
*/
@SpringBootApplication
@EnableJpaRepositories("com.snow.repository")
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
}
Ⅳ 实体类
package com.snow.entity;
import lombok.Data;
import lombok.ToString;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.entity
* @Description : User 实体类
* @date :2020/4/8 23:13
*/
@Data
@ToString
@Entity
@Table(name = "t_order")
public class Order {
/**
* 订单ID
*/
@Id
private Long orderId;
/**
* 用户ID
*/
private Long userId;
}
Ⅴ OrderRepository
package com.snow.repository;
import com.snow.entity.Order;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.repository
* @Description : OrderRepository
* @date :2020/4/9 21:10
*/
public interface OrderRepository extends CrudRepository<Order, Long> {
@Query(value = "SELECT order_id, user_id FROM t_order WHERE order_id IN (?1);", nativeQuery = true)
public List<Order> findExpiredOrderState(List<String> bpIds);
@Query(value = "SELECT order_id, user_id FROM t_order WHERE user_id=:userId ", nativeQuery = true)
public List<Order> findByUserId(@Param("userId") Long userId);
}
Ⅵ OrderController
package com.snow.controller;
import com.snow.entity.Order;
import com.snow.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;
import java.util.Optional;
/**
* @author :yang-windows
* @Title : springboot
* @Package :com.snow.controller
* @Description : OrderController
* @date :2020/4/8 22:25
*/
@RestController
public class OrderController {
@Autowired
private OrderRepository orderRepository;
// 查询所有的订单信息
@RequestMapping("/getOrderAll")
public List<Order> getOrderAll() {
List<Order> listOrder = (List<Order>) orderRepository.findAll();
System.out.println("总数:" + listOrder.size());
return listOrder;
}
@RequestMapping("/findIdByOrder")
public Optional<Order> findIdByOrder(Long id) {
return orderRepository.findById(id);
}
@RequestMapping("/findIdByUserId")
public List<Order> findIdByUserId(Long userId) {
return orderRepository.findByUserId(userId);
}
// 使用in条件查询
@RequestMapping("/inOrder")
public List<Order> 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(Order order) {
for (int i = 0; i < 100; i++) {
Order order1 = new Order();
order1.setOrderId((long) i);
order1.setUserId((long) i);
// 根据userid进行分片存放
orderRepository.save(order1);
}
return "success";
}
}
3 Sharding-Jdbc
日志分析与原理图
Sharding-JDBC
中的路由结果是通过分片字段和分片方法来确定的,如果查询条件中有id
字段的情况还好,查询将会落到某个具体的分片;- 如果查询没有分片的字段,会向所有的 db 或者是表都会查询一遍,让后封装结果级给客户端。
Sharding-Jdbc
和 MyCat
查询原理大致相同