第5章：ShardingJDBC项目实战-优快云博客

项目初始化

首先这个项目我们选择的是JDK11，我们需要配置一下maven依赖

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.guslegend</groupId>
  <artifactId>shardingJdbc</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>shardingJdbc</name>
  <url>http://maven.apache.org</url>

  <properties>
      <java.version>11</java.version>
      <maven.compiler.source>11</maven.compiler.source>
      <maven.compiler.target>11</maven.compiler.target>
      <spring.boot.version>2.5.5</spring.boot.version>
      <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
      <lombok.version>1.18.16</lombok.version>
      <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
      <junit.version>4.12</junit.version>
      <druid.version>1.1.16</druid.version>
      <!--跳过单元测试-->
      <skipTests>true</skipTests>
    </properties>


  <dependencies>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>${spring.boot.version}</version>
    </dependency>

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

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <version>${spring.boot.version}</version>
      <scope>test</scope>
    </dependency>


    <!--mybatis plus和springboot整合-->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>${mybatisplus.boot.starter.version}</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.27</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>${lombok.version}</version>
      <!--<scope>provided</scope>-->
    </dependency>

    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>${sharding-jdbc.version}</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>${junit.version}</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <version>${spring.boot.version}</version>
        <configuration>
          <fork>true</fork>
          <addResources>true</addResources>
        </configuration>
      </plugin>

    </plugins>
  </build>
</project>

配置启动类

@MapperScan("com.guslegend.mapper")
@EnableTransactionManagement
@SpringBootApplication
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }
}

编写配置文件

spring.application.name=shardingJdbc
server.port=8080

订单数据库库表建立

首先我们要将订单数据库分为（shop_order_0，shop_order_1）两个库，（product_order_0，product_order_1）两个表。

CREATE TABLE `product_order_0` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) DEFAULT NULL COMMENT '订单唯一标识',
  `state` varchar(11) DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单',
  `create_time` datetime DEFAULT NULL COMMENT '订单生成时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) DEFAULT NULL COMMENT '昵称',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("product_order")
public class ProductOrderDO {

    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;
}

mapper层

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

常规数据源配置和订单水平分表实战

新增配置文件

spring.application.name=shardingJdbc
server.port=8080

# 打印sql语句
spring.shardingsphere.props.sql.show =  true

# 数据源 ds0
spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3308/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..9}
# 分表策略
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

单元测试和控制台SQL介绍

单元测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShardingJdbcApplication.class)
@Slf4j
public class DBTest {

    @Autowired
    private ProductOrderMapper productOrderMapper;

    @Test
    public void test(){

        for (int i = 0; i < 10; i++){
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setNickname("guslegend "+i);
            productOrderDO.setState("PAY");
            productOrderDO.setPayAmount(100.0);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
            productOrderDO.setUserId(Long.valueOf( i));
            productOrderMapper.insert(productOrderDO);
        }
    }

}