ShardingSphere--03--SpringBoot整合案例

最新推荐文章于 2025-06-06 11:09:46 发布

知行合一。。。

最新推荐文章于 2025-06-06 11:09:46 发布

阅读量501

点赞数 4

CC 4.0 BY-SA版权

分类专栏： Spring基础知识--SSM 文章标签： spring boot java 后端

本文链接：https://blog.csdn.net/weixin_48052161/article/details/145288442

Spring基础知识--SSM 专栏收录该内容

55 篇文章

订阅专栏

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

SpringBoot整合案例

SpringBoot整合案例

1.建表

在这里插入图片描述

CREATE TABLE `t_audience_customer_0` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_1` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_2` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_3` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_4` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_5` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_6` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

CREATE TABLE `t_audience_customer_7` (
  `audid` BIGINT(20) NOT NULL COMMENT '客户档案索引ID',
  `customer_number` VARCHAR(255) DEFAULT NULL COMMENT '核心客户号',
  `cnoOrigin` JSON DEFAULT NULL COMMENT '客户号',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '常用手机号',
  `pmmsPhone` JSON DEFAULT NULL COMMENT 'PMMS报名手机号',
  `deviceId` JSON DEFAULT NULL COMMENT '设备号',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0-未删除 1-已删除',
  `bakchar1` VARCHAR(500) DEFAULT NULL COMMENT '备用字段1',
  `bakchar2` VARCHAR(500) DEFAULT NULL COMMENT '备用字段2',
  PRIMARY KEY (`audid`),
  KEY `idx_updatetime` (`update_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='客户号表' ;

2.nacos 配置ShardingSphere 规则

spring:
  shardingsphere:
    props:
      sql:
        show: false

    datasource:
      names: ds0

      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.1:3306/z_db_audience?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: xxx
        password: xxx
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
          filters: stat,wall,log4j
          stat-view-servlet:
            url-pattern: /druid/*
            login-username: root
            login-password: root
            enabled: true
            allow: 0.0.0.0

        initialSize: 200
        minIdle: 150
        maxActive: 200
        maxWait: 30000
        timeBetweenEvictionRunsMillis: 30000
        minEvictableIdleTimeMillis: 180000
        maxEvictableIdleTimeMillis: 27000000
        validationQueryTimeout: 5000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: false
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: false
        maxPoolPreparedStatementPerConnectionSize: 20
        removeAbandoned: true
        removeAbandonedTimeout: 3000
        logAbandoned: true

    sharding:
      default-database-strategy:
        inline:
          algorithm-expression: ds0
          sharding-column: audid

      binding-tables: t_audience,t_audience_customer
      #broadcast-tables: t_group,t_tag

      tables:
        t_audience:
          #actual-data-nodes: ds$->{0..1}.t_audience$->{0..1}
          actual-data-nodes: ds0.t_audience_$->{0..63}
          key-generator:
            column: audid
            type: CUSTOM_SNOWFLAKE
            props:
              worker:
                id: 1
          table-strategy:
            inline:
              algorithm-expression: t_audience_$->{audid % 64}
              sharding-column: audid

        
        t_audience_customer:
          actual-data-nodes: ds0.t_audience_customer_$->{0..7}
          table-strategy:
            inline:
              algorithm-expression: t_audience_customer_$->{audid % 8}
              sharding-column: audid

3.java 依赖

 <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- shardingsphere-jdbc-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
        </dependency>
        <!-- mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>

4.sql

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace=".mapper.AudienceCustomerMapper">

    <sql id="baseColumns">
        audid,customer_number,cnoOrigin,phone,pmmsPhone,deviceId,create_time,update_time,deleted,bakchar1,bakchar2
    </sql>


    <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO t_audience_customer
        (audid,customer_number,cnoOrigin,phone,pmmsPhone,deviceId,deleted,bakchar1,bakchar2)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
             #{item.audid},
             #{item.customerNumber},
             #{item.cnoOrigin},
             #{item.phone},
             #{item.pmmsPhone},
             #{item.deviceId},
             0,
             '',
             ''
            )
        </foreach>
        ON DUPLICATE KEY UPDATE update_time = VALUES(update_time),deleted=0
    </insert>

    <select id="queryList" resultType="com.tuzhanai.audience.server.model.AudienceCustomerModel">
        SELECT
        <include refid="baseColumns"/>
        FROM t_audience_customer
        WHERE deleted = 0 AND  update_time &lt;= #{yesterdayTime}
    </select>

   
    <delete id="deleteByAudId">
        DELETE FROM t_audience_customer WHERE audid = #{audid}
    </delete>
</mapper>

Model

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_audience_customer")
public class AudienceCustomerModel {


    /**
     * 客户档案ID
     */
    private Long audid;

    /**
     * 核心客户号
     */
    private String customerNumber;

    /**
     * 客户号
     */
    private String cnoOrigin;

    /**
     * 常用手机号
     */
    private String phone;

    /**
     * PMMS报名手机号
     */
    private String pmmsPhone;

    /**
     * 设备号
     */
    private String deviceId;

    /**
     * 创建时间
     */
    private Date createTime;

    /**
     * 更新时间
     */
    private Date updateTime;


    /**
     * 0-未删除 1-已删除
     */
    private Integer deleted;

    /**
     * 备用字段1
     */
    private String bakchar1;

    /**
     * 备用字段2
     */
    private String bakchar2;


}

5.java 代码

@Repository
public interface AudienceCustomerMapper extends BaseMapper<AudienceCustomerModel> {

    /**
     * 批量插入
     *
     * @param list 详细信息
     */
    void insertBatch(List<AudienceCustomerModel> list);

    List<AudienceCustomerModel> queryList(@Param("yesterdayTime") String yesterdayTime


}

    @Test
    public void test1() throws IOException {
        System.out.println("==========start==============");
         List<AudienceCustomerModel> list = new ArrayList<>();
        for (int i = 0; i <30 ; i++) {
             AudienceCustomerModel customerModel = new AudienceCustomerModel();
             customerModel.setAudid((long) i);
             customerModel.setCustomerNumber(String.valueOf(100000+i));
             Map<Object, Object> baseCnoMap = new HashMap<>();
             baseCnoMap.put("source","BANK_LOGIN");
             baseCnoMap.put("cno","0000"+i);
             customerModel.setCnoOrigin(JSONObject.toJSONString(baseCnoMap));
             customerModel.setPhone("1800000"+i);
             customerModel.setPmmsPhone(JSONObject.toJSONString(""));
             customerModel.setDeviceId(JSONObject.toJSONString(""));
             list.add(customerModel);
        }

        audienceCustomerMapper.insertBatch(list);
        
        String yesterdayTime = LocalDate.now().plusDays(1).format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        List<AudienceCustomerModel> modelList = audienceCustomerMapper.queryList(yesterdayTime);
     
        System.out.println("==========end==============");
    }

在这里插入图片描述