使用sharding-jdbc实现水平分表的示例代码

目录

• 在mysql中新建数据库sharding_db，新增两张结构一样的表student_1和student_2。
• 添加依赖
• 编写配置文件
• 编写实体类
• 编写mapper接口
• 编写测试类
• 执行测试

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`)
);

<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

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;
}

@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);
        }
    }
}

    @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);
    }