A Guide to Sharding-JDBC: Quick Start for Linux Software Installation – Article 37

Background

Previously, there were no users for sharding. Today, I felt inspired to give it a try.

Background

Previously, there were no users for sharding. Today, I felt inspired to give it a try.

Preparation Work

1. Create Database and Tables

CREATE DATABASE `order_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `order_id` bigint(20) NOT NULL COMMENT 'Order ID', `price` decimal(10, 2) NOT NULL COMMENT 'Order Price', `user_id` bigint(20) NOT NULL COMMENT 'User ID who placed the order', `status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Order Status', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `t_order_2`; CREATE TABLE `t_order_2` ( `order_id` bigint(20) NOT NULL COMMENT 'Order ID', `price` decimal(10, 2) NOT NULL COMMENT 'Order Price', `user_id` bigint(20) NOT NULL COMMENT 'User ID who placed the order', `status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Order Status', PRIMARY KEY (`order_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2. Create Spring Boot Project

3. File Examples

3.1 POM File Example

<?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.1.RELEASE</version>        <relativePath/> <!-- lookup parent from repository -->    </parent>    <groupId>pers.wwz.study.shardingjdbc</groupId>    <artifactId>springboot-shardinghdbc</artifactId>    <version>0.0.1-SNAPSHOT</version>    <name>springboot-shardinghdbc</name>    <description>springboot-shardinghdbc</description>    <properties>        <java.version>1.8</java.version>    </properties>    <repositories>        <repository>            <id>central</id>            <url>https://maven.aliyun.com/repository/central</url>            <releases>                <enabled>true</enabled>            </releases>            <snapshots>                <enabled>true</enabled>            </snapshots>        </repository>        <repository>            <id>pu</id>            <url>https://maven.aliyun.com/repository/public</url>            <releases>                <enabled>true</enabled>            </releases>            <snapshots>                <enabled>true</enabled>            </snapshots>        </repository>    </repositories>    <dependencies>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-devtools</artifactId>            <scope>runtime</scope>            <optional>true</optional>        </dependency>        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid</artifactId>            <version>1.1.20</version>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>8.0.33</version>        </dependency>        <dependency>            <groupId>org.apache.shardingsphere</groupId>            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>            <version>4.0.0-RC1</version>        </dependency>        <dependency>            <groupId>com.baomidou</groupId>            <artifactId>mybatis-plus-boot-starter</artifactId>            <version>3.0.5</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>        </dependency>        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>4.12</version>            <scope>test</scope>        </dependency>    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>                <configuration>                    <excludes>                        <exclude>                            <groupId>org.projectlombok</groupId>                            <artifactId>lombok</artifactId>                        </exclude>                    </excludes>                </configuration>            </plugin>        </plugins>    </build></project>

3.2 Configuration File Example

spring.application.name=springboot-shardinghdbc
spring.main.allow-bean-definition-overriding=true
# Sharding-JDBC Sharding Strategy
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
# Specify database distribution, table distribution in the database
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}
spring.shardingsphere.props.sql.show=true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug

3.3 Startup Class Example

package pers.wwz.study.shardingjdbc;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@MapperScan("pers.wwz.study.shardingjdbc.orm")
@SpringBootApplication
public class SpringbootShardinghdbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootShardinghdbcApplication.class, args);
    }
}

3.4 Mapper Example

package pers.wwz.study.shardingjdbc.orm;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
@Data
@TableName(value = "t_order")
public class Order {
    @TableId
    private Long orderId;
    private BigDecimal price;
    private Long userId;
    private String status;
}

3.5 Entity Class (PO) Example

package pers.wwz.study.shardingjdbc.orm;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Mapper
@Repository
public interface OrderMapper extends BaseMapper<Order> {}

3.6 Test Class Example

package pers.wwz.study.shardingjdbc;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.springframework.test.context.junit4.SpringRunner;
import pers.wwz.study.shardingjdbc.orm.Order;
import pers.wwz.study.shardingjdbc.orm.OrderMapper;
import java.math.BigDecimal;
// Both ways are acceptable
//@RunWith(SpringRunner.class)
//@ExtendWith(SpringExtension.class)
@SpringBootTest
class SpringbootShardinghdbcApplicationTests {
    @Autowired
    OrderMapper orderMapper;
    @Test
    void contextLoads() {
    }
    @Test
    public void addOrder(){
        for(int i=1;i<=10;i++) {
            Order order = new Order();
            order.setPrice(new BigDecimal(i*10+20));
            order.setUserId(100L);
            order.setStatus("SUCCESS");
            orderMapper.insert(order);
        }
    }
}

Testing

Directly call the test class

Just run the addOrder test method of SpringbootShardinghdbcApplicationTests

Since the table selection rule is based on the ID modulo 2 plus 1,

Odd IDs fall into the t_order_2 table

Even IDs fall into the t_order_1 table

After running the test method, check the database data directly.

————————————————

Copyright Statement: This article is an original work by the author, following the CC 4.0 BY-SA copyright agreement. Please include the original source link and this statement when reprinting.

Original link: https://blog.csdn.net/wangwenzhe222/article/details/136637506