色视频在线免费看,久久综合久久网,视频一区二区三区在线观看

在mysql中新建數據庫sharding_db，新增兩張結構一樣的表student_1和student_2。

CREATE TABLE `student_1` (
`ID`  bigint(20) NOT NULL ,
`NAME`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ,
`AGE`  int(11) NOT NULL ,
`GENDER`  varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ,
PRIMARY KEY (`ID`)
);

此處未指定主鍵自增，因為兩張表的id不能重復，所以只能從后端傳入id。

添加依賴

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Druid連接池 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.20</version>
</dependency>

<!-- Mysql驅動依賴 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>

<!-- MybatisPlus -->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.0.5</version>
</dependency>

<!-- Sharding-JDBC -->
<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  <version>4.0.0-RC1</version>
</dependency>

<!-- lombok -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-test</artifactId>
  <scope>test</scope>
</dependency>

<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <scope>test</scope>
</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>

編寫配置文件

spring.main.allow-bean-definition-overriding=true

# 配置Sharding-JDBC的分片策略
# 配置數據源，給數據源起名g1,g2...此處可配置多數據源
spring.shardingsphere.datasource.names=g1

# 配置數據源具體內容:連接池，驅動,地址，用戶名，密碼
# 由于上面配置數據源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password
spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.g1.url=jdbc:mysql://localhost:3306/sharding_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.g1.username=root
spring.shardingsphere.datasource.g1.password=123456

# 配置表的分布，表的策略
spring.shardingsphere.sharding.tables.student.actual-data-nodes=g1.student_$->{1..2}

# 指定student表 主鍵gid 生成策略為 SNOWFLAKE
spring.shardingsphere.sharding.tables.student.key-generator.column=id
spring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE

# 指定分片策略 約定id值是偶數添加到student_1表，如果id是奇數添加到student_2表
spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression=student_$->{id % 2 + 1}

# 打開sql輸出日志
spring.shardingsphere.props.sql.show=true

或者是yml格式

spring:
main:
  allow-bean-definition-overriding: true
shardingsphere:
  datasource:
    g1:
      driver-class-name: com.mysql.cj.jdbc.Driver
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/sharding_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
      username: root
    names: g1
  props:
    sql:
      show: true
  sharding:
    tables:
      student:
        actual-data-nodes: g1.student_$->{1..2}
        key-generator:
          column: id
          type: SNOWFLAKE
        table-strategy:
          inline:
            algorithm-expression: student_$->{id % 2 + 1}
            sharding-column: id

編寫實體類

@Data
public class Student {
  private Long id;
  private String name;
  private int age;
  private String gender;
}

編寫mapper接口

@Repository
public interface StudentMapper extends BaseMapper<Student> {

}

編寫測試類

@SpringBootTest
class ShardingJdbcDemoApplicationTests {

  @Autowired
  private StudentMapper studentMapper;

  @Test
  public void test01() {
      for (int i = 0; i < 10; i++) {
          Student student = new Student();
          student.setName("wuwl");
          student.setAge(27);
          student.setGender("男");
          studentMapper.insert(student);
      }
  }
}

執行測試

使用sharding-jdbc實現水平分表的示例代碼

執行成功，主鍵通過雪花算法在后端生成，傳入到數據庫中，根據奇偶性進行分表。

student_1表數據：

使用sharding-jdbc實現水平分表的示例代碼

student_2表數據：

使用sharding-jdbc實現水平分表的示例代碼

兩張表的數據分別有5條，但這只是因為雪花算法生成的id奇數偶數各5個，不是1:1的關系，需要注意。
主鍵生成后，根據策略插入到對應的表中，從打印出來的sql可以證明這一點。
通過mapper接口的selectById方法進行查詢時，會先根據主鍵策略判斷在哪個庫，再直接去那個庫根據主鍵查詢。而如果是通過其它條件查詢，或者是多個id的selectById方法查詢，又是如何的呢？

  @Test
  public void test03() {
      List<Long> list = new ArrayList<>();
      list.add(1362282042768609282l);
      list.add(1362282040277192705l);
      List<Student> studentList = studentMapper.selectBatchIds(list);
      System.out.println(studentList);
  }

取了兩張表的id進行查詢。

使用sharding-jdbc實現水平分表的示例代碼