mycat分库分表+springcloud微服务小案例实现

别团等shy哥发育

发布于 2023-02-25 14:57:28

3340

发布于 2023-02-25 14:57:28

文章被收录于专栏：全栈开发那些事

文章目录

1. MyCat综合案例

1.1 案例概述

1.1.1 案例介绍

本案例将模拟电商项目中的商品管理、订单管理、基础信息管理、日志管理模块，对整个系统中的数据表进行分片操作，将根据不同的业务需求，采用不同的分片方式。项目结构

资源链接：https://download.csdn.net/download/qq_43753724/33479257

1.1.2 系统架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-30sB8VQH-1634717644136)(assets/image-20200201153127417.png)]

本案例涉及到的模块：

1). 商品微服务

2). 订单微服务

3). 日志微服务

1.1.3 技术选型

SpringBoot
SpringCloud
SpringMVC
Mybatis
SpringDataRedis
MySQL
Redis
Lombok

1.2 案例需求

1). 商品管理

A. 添加商品

B. 查询商品

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zGTFsYt7-1634717644143)(assets/image-20200201194027874.png)]

2). 订单管理

A. 下订单

B. 查询订单

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2cda1FN9-1634717644145)(assets/image-20200201194121792.png)]

3). 日志管理

A. 日志记录

B. 日志查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-97Z8pw81-1634717644148)(assets/image-20200201194159102.png)]

1.3 案例环境搭建

1.3.1 数据库

1). 省份表 tb_provinces

Field	Type	Comment
provinceid	varchar(20)	省份ID
province	varchar(50)	省份名称

2). 市表 tb_cities

Field	Type	Comment
cityid	varchar(20)	城市ID
city	varchar(50)	城市名称
provinceid	varchar(20)	省份ID

3). 区县表 tb_areas

Field	Type	Comment
areaid	varchar(20)	区域ID
area	varchar(50)	区域名称
cityid	varchar(20)	城市ID

4). 商品分类表 tb_category

Field	Type	Comment
id	int(20)	分类ID
name	varchar(50)	分类名称
goods_num	int(11)	商品数量
is_show	char(1)	是否显示
is_menu	char(1)	是否导航
seq	int(11)	排序
parent_id	int(20)	上级ID

5). 品牌表 tb_brand

Field	Type	Comment
id	int(11)	品牌id
name	varchar(100)	品牌名称
image	varchar(1000)	品牌图片地址
letter	char(1)	品牌的首字母
seq	int(11)	排序

6). 商品SPU表 tb_spu

Field	Type	Comment
id	varchar(20)	主键
sn	varchar(60)	货号
name	varchar(100)	SPU名
caption	varchar(100)	副标题
brand_id	int(11)	品牌ID
category1_id	int(20)	一级分类
category2_id	int(10)	二级分类
category3_id	int(10)	三级分类
template_id	int(20)	模板ID
freight_id	int(11)	运费模板id
image	varchar(200)	图片
images	varchar(2000)	图片列表
sale_service	varchar(50)	售后服务
introduction	text	介绍
spec_items	varchar(3000)	规格列表
para_items	varchar(3000)	参数列表
sale_num	int(11)	销量
comment_num	int(11)	评论数
is_marketable	char(1)	是否上架
is_enable_spec	char(1)	是否启用规格
is_delete	char(1)	是否删除
status	char(1)	审核状态

7). 商品SKU表 tb_sku

Field	Type	Comment
id	varchar(20)	商品id
sn	varchar(100)	商品条码
name	varchar(200)	SKU名称
price	int(20)	价格（分）
num	int(10)	库存数量
alert_num	int(11)	库存预警数量
image	varchar(200)	商品图片
images	varchar(2000)	商品图片列表
weight	int(11)	重量（克）
create_time	datetime	创建时间
update_time	datetime	更新时间
spu_id	varchar(20)	SPUID
category_id	int(10)	类目ID
category_name	varchar(200)	类目名称
brand_name	varchar(100)	品牌名称
spec	varchar(200)	规格
sale_num	int(11)	销量
comment_num	int(11)	评论数
status	char(1)	商品状态 1-正常，2-下架，3-删除
version	int(255)

8). 订单表 tb_order

Field	Type	Comment
id	varchar(200)	订单id
total_num	int(11)	数量合计
total_money	int(11)	金额合计
pre_money	int(11)	优惠金额
post_fee	int(11)	邮费
pay_money	int(11)	实付金额
pay_type	varchar(1)	支付类型，1、在线支付、0 货到付款
create_time	datetime	订单创建时间
update_time	datetime	订单更新时间
pay_time	datetime	付款时间
consign_time	datetime	发货时间
end_time	datetime	交易完成时间
close_time	datetime	交易关闭时间
shipping_name	varchar(20)	物流名称
shipping_code	varchar(20)	物流单号
username	varchar(50)	用户名称
buyer_message	varchar(1000)	买家留言
buyer_rate	char(1)	是否评价
receiver_contact	varchar(50)	收货人
receiver_mobile	varchar(12)	收货人手机
receiver_province	varchar(200)	收货人省份
receiver_city	varchar(200)	收货人市
receiver_area	varchar(200)	收货人区/县
receiver_address	varchar(200)	收货人具体街道地址
source_type	char(1)	订单来源：1:web，2：app，3：微信公众号，4：微信小程序 5 H5手机页面
transaction_id	varchar(30)	交易流水号
order_status	char(1)	订单状态
pay_status	char(1)	支付状态 0:未支付 1:已支付
consign_status	char(1)	发货状态 0:未发货 1:已发货 2:已送达
is_delete	char(1)	是否删除

9). 订单明细表 tb_order_item

Field	Type	Comment
id	varchar(200)	ID
category_id1	int(11)	1级分类
category_id2	int(11)	2级分类
category_id3	int(11)	3级分类
spu_id	varchar(200)	SPU_ID
sku_id	varchar(200)	SKU_ID
order_id	varchar(200)	订单ID
name	varchar(200)	商品名称
price	int(20)	单价
num	int(10)	数量
money	int(20)	总金额
pay_money	int(11)	实付金额
image	varchar(200)	图片地址
weight	int(11)	重量
post_fee	int(11)	运费
is_return	char(1)	是否退货

10). 订单日志表 tb_order_log

Field	Type	Comment
id	varchar(20)	ID
operater	varchar(50)	操作员
operate_time	datetime	操作时间
order_id	bigint(20)	订单ID
order_status	char(1)	订单状态
pay_status	char(1)	付款状态
consign_status	char(1)	发货状态
remarks	varchar(100)	备注

11). 操作日志表 tb_operatelog

Field	Type	Comment
id	bigint(20)	ID
model_name	varchar(200)	模块名
model_value	varchar(200)	模块值
return_value	varchar(200)	返回值
return_class	varchar(200)	返回值类型
operate_user	varchar(20)	操作用户
operate_time	varchar(20)	操作时间
param_and_value	varchar(500)	请求参数名及参数值
operate_class	varchar(200)	操作类
operate_method	varchar(200)	操作方法
cost_time	bigint(20)	执行方法耗时, 单位 ms

12). 字典表 tb_dictionary

Field	Type	Comment
id	int(11)	主键ID , 自增
codeid	int(11)	码表ID
codetype	varchar(2)	码值类型
codename	varchar(50)	名称
codevalue	varchar(50)	码值
description	varchar(100)	描述
createtime	datetime	创建时间
updatetime	datetime	修改时间
createuser	int(11)	创建人
updateuser	int(11)	修改人

1.3.2 工程预览

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cF8X23hw-1634717644151)(assets/image-20200201201704741.png)]

spring-boot-starter-parent
    |- v_parent	--------------------> 父工程, 统一管理依赖版本
        |- v_common ----------------> 通用工程, 存放通用的工具类及组件
        |- v_model -----------------> 实体类
        |- v_eureka ----------------> 注册中心
        |- v_feign_api -------------> feign远程调用的客户端接口
        |- v_gateway ---------------> 网关工程
        |- v_manage_web ------------> 模拟前端工程
        |- v_service_goods ---------> 商品微服务
        |- v_service_log -----------> 日志微服务
        |- v_service_order ---------> 订单微服务

1.3.3 工程层级关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qHipGVpJ-1634717644154)(assets/image-20200205010155489.png)]

1.3.4 父工程搭建

工程名: v_parent

pom.xml

<!-- springBoot项目需要集成自父工程 -->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.4.RELEASE</version>
</parent>

<properties>
    <skipTests>true</skipTests>
</properties>

<!--依赖包-->
<dependencies>
    <!--测试包-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-dependencies</artifactId>
            <version>Greenwich.SR1</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>

        <!--MySQL数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!--mybatis分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>
    </dependencies>
</dependencyManagement>

1.3.5 基础工程搭建

1). v_model

该基础工程中存放的是与数据库对应的实体类 ;

A. pom.xml

<dependencies>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

B. 导入实体类

2). v_common

该基础工程中存放的是通用的组件及工具类 , 比如分页实体类, 结果实体类, 状态码等

直接导入资料中提供的基础组件和工具类 ;

3). v_feign_api

该工程中, 主要存放的是Feign远程调用的客户端接口;

pom.xml

<dependencies>
    <!--web起步依赖-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- Feign起步依赖 -->
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-openfeign</artifactId>
    </dependency>

    <dependency>
        <groupId>cn.itcast</groupId>
        <artifactId>v_common</artifactId>
        <version>1.0-SNAPSHOT</version>
    </dependency>

    <dependency>
        <groupId>cn.itcast</groupId>
        <artifactId>v_model</artifactId>
        <version>1.0-SNAPSHOT</version>
    </dependency>

</dependencies>

1.3.6 Eureka Server搭建

1). pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-netflix-eureka-server</artifactId>
    </dependency>
</dependencies>

2). 引导类

@SpringBootApplication
@EnableEurekaServer
public class EurekaApplication {
    public static void main(String[] args) {
        SpringApplication.run(EurekaApplication.class,args);
    }
}

3). application.yml

spring:
  application:
    name: eureka
server:
  port: 8161
eureka:
  client:
    register-with-eureka: false #是否将自己注册到eureka中
    fetch-registry: false #是否从eureka中获取信息
    service-url:
      defaultZone: http://127.0.0.1:${server.port}/eureka/
  server:
    enable-self-preservation: true

1.3.7 GateWay 网关搭建

1). pom.xml

<!--网关依赖-->
<dependencies>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-gateway</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
    </dependency>
</dependencies>

2). 引导类

@SpringBootApplication
@EnableEurekaClient
public class GateWayApplication {
    public static void main(String[] args) {
        SpringApplication.run(GateWayApplication.class,args);
    }
}

3). application.yml

server:
  port: 8001
eureka:
  client:
    service-url:
      defaultZone: http://127.0.0.1:8161/eureka
  instance:
    prefer-ip-address: true
spring:
  application:
    name: gateway
  cloud:
    gateway:
      routes:
        - id: v_goods_route
          uri: lb://goods
          predicates:
            - Path=/goods/**
          filters:
            - StripPrefix=1

        - id: v_order_route
          uri: lb://order
          predicates:
            - Path=/order/**
          filters:
            - StripPrefix=1

4). Cors配置类

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.reactive.CorsWebFilter;
import org.springframework.web.cors.reactive.UrlBasedCorsConfigurationSource;
import org.springframework.web.util.pattern.PathPatternParser;

@Configuration
public class CorsConfig {

    @Bean
    public CorsWebFilter corsFilter(){
        UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource(new PathPatternParser());
        source.registerCorsConfiguration("/**", buildConfig());
        return new CorsWebFilter(source);
    }

    private CorsConfiguration buildConfig(){
        CorsConfiguration corsConfiguration = new CorsConfiguration();
		//在生产环境上最好指定域名，以免产生跨域安全问题
        corsConfiguration.addAllowedOrigin("*");
        corsConfiguration.addAllowedHeader("*");
        corsConfiguration.addAllowedMethod("*");
        return corsConfiguration;
    }
}

1.4 功能开发

1.4.1 商品管理模块

需求 :

1). 根据ID查询商品SPU信息;

2). 根据条件查询商品SPU列表;

3). 根据ID查询商品SKU信息;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uvxG3SKn-1634717644157)(assets/image-20200216225916691.png)]

概念:

1). SPU = Standard Product Unit （标准产品单位）

概念 : SPU 是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。通俗点讲，属性值、特性相同的货品就可以称为一个 SPU

例如：华为P30 就是一个 SPU

2). SKU=stock keeping unit( 库存量单位)

SKU 即库存进出计量的单位，可以是以件、盒、托盘等为单位。 SKU 是物理上不可分割的最小存货单元。在使用时要根据不同业态，不同管理模式来处理。在服装、鞋类商品中使用最多最普遍。

例如：红色 64G 全网通的华为P30 就是一个 SKU

1.4.1.1 创建工程

pom.xml

 <dependencies>
        <!-- Eureka客户端依赖 -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>

        <!--MySQL数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--mybatis分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
        </dependency>

        <!--web起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- redis 使用-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <!-- fastJson依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
        </dependency>

        <!-- Feign依赖 -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_common</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_model</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_feign_api</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

    </dependencies>

application.yml

server:
  port: 9001
spring:
  application:
    name: goods
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/v_shop?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 2143
  main:
    allow-bean-definition-overriding: true #当遇到同样名字的时候，是否允许覆盖注册
eureka:
  client:
    fetch-registry: true
    register-with-eureka: true
    service-url:
      defaultZone: http://127.0.0.1:8161/eureka
  instance:
    prefer-ip-address: true

引导类

@SpringBootApplication
@EnableEurekaClient
@EnableFeignClients(basePackages = "cn.itcast.feign")
@MapperScan("cn.itcast.goods.mapper")
public class GoodsApplication {
    public static void main(String[] args) {
        SpringApplication.run(GoodsApplication.class,args);
    }
}

1.4.1.2 Mapper

1). mapper接口定义

public interface SpuMapper {

    public TbSpu findById(String spuId);

    public List<TbSpu> search(Map<String,Object> searchMap);

}

public interface SkuMapper  {
    //根据ID查询SKU
    public TbSku findById(String skuId);

}

2). mapper映射配置文件

SpuMapper.xml

<mapper namespace="cn.itcast.goods.mapper.SpuMapper" >

    <resultMap id="spuResultMap" type="cn.itcast.model.TbSpu">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <result column="sn" jdbcType="VARCHAR" property="sn" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="caption" jdbcType="VARCHAR" property="caption" />
        <result column="brand_id" jdbcType="INTEGER" property="brandId" />
        <result column="category1_id" jdbcType="INTEGER" property="category1Id" />
        <result column="category2_id" jdbcType="INTEGER" property="category2Id" />
        <result column="category3_id" jdbcType="INTEGER" property="category3Id" />
        <result column="template_id" jdbcType="INTEGER" property="templateId" />
        <result column="freight_id" jdbcType="INTEGER" property="freightId" />
        <result column="image" jdbcType="VARCHAR" property="image" />
        <result column="images" jdbcType="VARCHAR" property="images" />
        <result column="sale_service" jdbcType="VARCHAR" property="saleService" />
        <result column="spec_items" jdbcType="VARCHAR" property="specItems" />
        <result column="para_items" jdbcType="VARCHAR" property="paraItems" />
        <result column="sale_num" jdbcType="INTEGER" property="saleNum" />
        <result column="comment_num" jdbcType="INTEGER" property="commentNum" />
        <result column="is_marketable" jdbcType="CHAR" property="isMarketable" />
        <result column="is_enable_spec" jdbcType="CHAR" property="isEnableSpec" />
        <result column="is_delete" jdbcType="CHAR" property="isDelete" />
        <result column="status" jdbcType="CHAR" property="status" />
    </resultMap>

    <select id="findById" parameterType="java.lang.String" resultMap="spuResultMap">
        select
            *
        from
            tb_spu
        where
            id = #{spuId}
    </select>

    <select id="search" resultMap="spuResultMap">
        select * from tb_spu
        <where>
            <if test="name != null and name != ''">
                and name like '%${name}%'
            </if>
            <if test="caption != null and caption != ''" >
                and caption like '%${caption}%'
            </if>
            <if test="brandId != null">
                and brand_id = #{brandId}
            </if>
            <if test="status != null and status != ''">
                and status = #{status}
            </if>
        </where>
    </select>

</mapper>

SkuMapper.xml

<mapper namespace="cn.itcast.goods.mapper.SkuMapper" >

    <resultMap id="skuResultMap" type="cn.itcast.model.TbSku">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <result column="sn" jdbcType="VARCHAR" property="sn" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="price" jdbcType="INTEGER" property="price" />
        <result column="num" jdbcType="INTEGER" property="num" />
        <result column="alert_num" jdbcType="INTEGER" property="alertNum" />
        <result column="image" jdbcType="VARCHAR" property="image" />
        <result column="images" jdbcType="VARCHAR" property="images" />
        <result column="weight" jdbcType="INTEGER" property="weight" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="spu_id" jdbcType="VARCHAR" property="spuId" />
        <result column="category_id" jdbcType="INTEGER" property="categoryId" />
        <result column="category_name" jdbcType="VARCHAR" property="categoryName" />
        <result column="brand_name" jdbcType="VARCHAR" property="brandName" />
        <result column="spec" jdbcType="VARCHAR" property="spec" />
        <result column="sale_num" jdbcType="INTEGER" property="saleNum" />
        <result column="comment_num" jdbcType="INTEGER" property="commentNum" />
        <result column="status" jdbcType="CHAR" property="status" />
        <result column="version" jdbcType="INTEGER" property="version" />
    </resultMap>

    <select id="findById" parameterType="java.lang.String" resultMap="skuResultMap">
        select * from tb_sku where id = #{skuId}
    </select>
</mapper>

1.4.1.3 Service

1). 接口定义

public interface SkuService {
    /**
     * 根据ID查询SKU
     * @param skuId
     * @return
     */
    public TbSku findById(String skuId);
}

public interface SpuService {
    /**
     * 根据ID查询
     * @param id
     * @return
     */
    TbSpu findById(String id);
    /***
     * 多条件分页查询
     * @param searchMap
     * @param page
     * @param size
     * @return
     */
    Page<TbSpu> findPage(Map<String, Object> searchMap, int page, int size);
}

2).接口实现

@Service
public class SkuServiceImpl implements SkuService {
    @Autowired
    private SkuMapper skuMapper;
    @Autowired
    private RedisTemplate redisTemplate;
    
    @Override
    public TbSku findById(String skuId) {
        return skuMapper.findById(skuId);
    }
    
}

@Service
public class SpuServiceImpl implements SpuService {

    @Autowired
    private SpuMapper spuMapper;

    /**
     * 根据ID查询
     * @param id
     * @return
     */
    @Override
    public TbSpu findById(String id){
        return  spuMapper.findById(id);
    }

    /**
     * 条件+分页查询
     * @param searchMap 查询条件
     * @param page 页码
     * @param size 页大小
     * @return 分页结果
     */
    @Override
    public Page<TbSpu> findPage(Map<String,Object> searchMap, int page, int size){
        PageHelper.startPage(page,size);
        return (Page<TbSpu>) spuMapper.search(searchMap);
    }

}

1.4.1.4 Controller

@RestController
@CrossOrigin(origins = "*")
@RequestMapping("/sku")
public class SkuController {
    @Autowired
    private SkuService skuService;
    /***
     * 根据ID查询数据
     * @param id
     * @return
     */
    @GetMapping("/{id}")
    public Result<TbSku> findById(@PathVariable("id") String id){
        TbSku sku = skuService.findById(id);
        return new Result(true,StatusCode.OK,"查询成功",sku);
    }
}

@RestController
@CrossOrigin(origins = "*")
@RequestMapping("/spu")
public class SpuController {

    @Autowired
    private SpuService spuService;

    /***
     * 根据ID查询数据
     * @param id
     * @return
     */
    @GetMapping("/{id}")
    public Result<TbSpu> findById(@PathVariable("id") String id){
        TbSpu spu = spuService.findById(id);
        return new Result(true,StatusCode.OK,"查询成功",spu);
    }
    
    /***
     * 分页搜索实现
     * @param searchMap
     * @param page
     * @param size
     * @return
     */
    @PostMapping(value = "/search/{page}/{size}" )
    public Result<TbSpu> findPage(@RequestBody Map searchMap, @PathVariable  Integer page, @PathVariable  Integer size){
        com.github.pagehelper.Page<TbSpu> pageList = spuService.findPage(searchMap, page, size);
        PageResult pageResult=new PageResult<TbSpu>(pageList.getTotal(),pageList.getResult());
        return new Result<TbSpu>(true,StatusCode.OK,"查询成功",pageResult);
    }
}

1.4.2 订单模块

需求:

1). 下单业务分析

2). 根据条件分页查询订单

表结构:

tb_order , tb_order_item , tb_order_log

1.4.2.1 创建工程

1).pom.xml

 <dependencies
     <dependency>
         <groupId>cn.itcast</groupId>
         <artifactId>v_common</artifactId>
         <version>1.0-SNAPSHOT</version>
     </dependency>

     <dependency>
         <groupId>cn.itcast</groupId>
         <artifactId>v_model</artifactId>
         <version>1.0-SNAPSHOT</version>
     </dependency>

     <dependency>
         <groupId>cn.itcast</groupId>
         <artifactId>v_feign_api</artifactId>
         <version>1.0-SNAPSHOT</version>
     </dependency>

     <!-- Eureka客户端依赖 -->
     <dependency>
         <groupId>org.springframework.cloud</groupId>
         <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
     </dependency>
	
	<!-- springboot - Mybatis 起步依赖 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>

     <!--MySQL数据库驱动-->
     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
     </dependency>

     <!--mybatis分页插件-->
     <dependency>
         <groupId>com.github.pagehelper</groupId>
         <artifactId>pagehelper-spring-boot-starter</artifactId>
     </dependency>

     <!--web起步依赖-->
     <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
     </dependency>

     <!-- redis 使用-->
     <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-redis</artifactId>
     </dependency>

     <!-- fastJson依赖 -->
     <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>fastjson</artifactId>
     </dependency>

     <!-- Feign依赖 -->
     <dependency>
         <groupId>org.springframework.cloud</groupId>
         <artifactId>spring-cloud-starter-openfeign</artifactId>
     </dependency>

</dependencies>

2). application.yml

server:
  port: 9002
spring:
  application:
    name: order
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/v_shop?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 2143
  main:
    allow-bean-definition-overriding: true #当遇到同样名字的时候，是否允许覆盖注册
eureka:
  client:
    fetch-registry: true
    register-with-eureka: true
    service-url:
      defaultZone: http://127.0.0.1:8161/eureka
  instance:
    prefer-ip-address: true
feign:
  client:
    config:
      default:   #配置全局的feign的调用超时时间  如果 有指定的服务配置 默认的配置不会生效
        connectTimeout: 60000 # 指定的是 消费者 连接服务提供者的连接超时时间 是否能连接  单位是毫秒
        readTimeout: 20000  # 指定的是调用服务提供者的 服务 的超时时间（）  单位是毫秒

3). 引导类

@SpringBootApplication
@EnableEurekaClient
@MapperScan(basePackages = "cn.itcast.order.mapper")
public class OrderApplication {
    public static void main(String[] args) {
        SpringApplication.run(OrderApplication.class,args);
    }
}

1.4.2.2 下单业务分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gc5IkM0z-1634717644160)(assets/image-20200217102815624.png)]

1.4.2.3 查询订单

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TCrAM4dj-1634717644164)(assets/image-20200217141007643.png)]

1.4.2.3.1 Mapper

1). mapper接口

public interface OrderMapper  {
    public List<TbOrder> search(Map<String,Object> searchMap);
}

2). mapper映射配置文件

OrderMapper.xml

<mapper namespace="cn.itcast.order.mapper.OrderMapper" >
    <resultMap id="orderResultMap" type="cn.itcast.model.TbOrder">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <result column="total_num" jdbcType="INTEGER" property="totalNum" />
        <result column="total_money" jdbcType="INTEGER" property="totalMoney" />
        <result column="pre_money" jdbcType="INTEGER" property="preMoney" />
        <result column="post_fee" jdbcType="INTEGER" property="postFee" />
        <result column="pay_money" jdbcType="INTEGER" property="payMoney" />
        <result column="pay_type" jdbcType="VARCHAR" property="payType" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="pay_time" jdbcType="TIMESTAMP" property="payTime" />
        <result column="consign_time" jdbcType="TIMESTAMP" property="consignTime" />
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime" />
        <result column="close_time" jdbcType="TIMESTAMP" property="closeTime" />
        <result column="shipping_name" jdbcType="VARCHAR" property="shippingName" />
        <result column="shipping_code" jdbcType="VARCHAR" property="shippingCode" />
        <result column="username" jdbcType="VARCHAR" property="username" />
        <result column="buyer_message" jdbcType="VARCHAR" property="buyerMessage" />
        <result column="buyer_rate" jdbcType="CHAR" property="buyerRate" />
        <result column="receiver_contact" jdbcType="VARCHAR" property="receiverContact" />
        <result column="receiver_mobile" jdbcType="VARCHAR" property="receiverMobile" />
        <result column="receiver_province" jdbcType="VARCHAR" property="receiverProvince" />
        <result column="receiver_city" jdbcType="VARCHAR" property="receiverCity" />
        <result column="receiver_area" jdbcType="VARCHAR" property="receiverArea" />
        <result column="receiver_address" jdbcType="VARCHAR" property="receiverAddress" />
        <result column="source_type" jdbcType="CHAR" property="sourceType" />
        <result column="transaction_id" jdbcType="VARCHAR" property="transactionId" />
        <result column="order_status" jdbcType="CHAR" property="orderStatus" />
        <result column="pay_status" jdbcType="CHAR" property="payStatus" />
        <result column="consign_status" jdbcType="CHAR" property="consignStatus" />
        <result column="is_delete" jdbcType="CHAR" property="isDelete" />
    </resultMap>


    <select id="search" resultType="cn.itcast.model.TbOrder">
        SELECT
            o.id ,
            o.`create_time` createTime,
            o.username ,
            o.`total_money` totalMoney,
            o.`total_num` totalNum,
            o.`pay_type` payType,
            o.`pay_status` payStatus,

            p.`province` receiverProvince
        FROM
            tb_order o , tb_provinces p
        WHERE
            o.receiver_province = p.provinceid
        <if test="orderId != null and orderId != ''">
            and o.id = #{orderId}
        </if>

        <if test="payType != null and payType != ''">
            and o.pay_type = #{payType}
        </if>

        <if test="username != null and username != ''">
            and o.username = #{username}
        </if>

        <if test="payStatus != null and payStatus != ''">
            and o.order_status = #{payStatus}
        </if>
    </select>
</mapper>

1.4.2.3.2 Service

service接口

public interface OrderService {

    /***
     * 新增
     * @param order
     */
    void add(TbOrder order);

    /***
     * 多条件分页查询
     * @param searchMap
     * @param page
     * @param size
     * @return
     */
    Page<TbOrder> findPage(Map<String, Object> searchMap, int page, int size);

}

service实现

@Service
public class OrderServiceImpl implements OrderService {
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private OrderItemMapper orderItemMapper;
    @Autowired
    private IdWorker idWorker;

    /**
     * 增加
     * @param order
     */
    @Override
    public void add(TbOrder order){
        //1.获取购物车的相关数据(redis)
        
        //2.统计计算:总金额,总数量
        
        //3.填充订单数据并保存到tb_order

        //4.填充订单项数据并保存到tb_order_item

        //5.记录订单日志
        
        //6.扣减库存并增加销量

        //7.删除购物车数据(redis)

      
    }
    
    
    /**
     * 条件+分页查询
     * @param searchMap 查询条件
     * @param page 页码
     * @param size 页大小
     * @return 分页结果
     */
    @Override
    public Page<TbOrder> findPage(Map<String,Object> searchMap, int page, int size){
        PageHelper.startPage(page,size);
        return (Page<TbOrder>)orderMapper.search(searchMap);
    }
}

1.4.2.3.3 Controller

@RestController
@CrossOrigin(value = {"*"})
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderService orderService;


    @PostMapping
    @OperateLog
    public Result add(@RequestBody TbOrder order){
        //获取登录人名称
        orderService.add(order);
        return new Result(true,StatusCode.OK,"提交成功");
    }


    /***
     * 分页搜索实现
     * @param searchMap
     * @param page
     * @param size
     * @return
     */
    @PostMapping(value = "/search/{page}/{size}" )
    @OperateLog
    public Result findPage(@RequestBody Map searchMap, @PathVariable  Integer page, @PathVariable  Integer size){
        Page<TbOrder> pageList = orderService.findPage(searchMap, page, size);
        PageResult pageResult=new PageResult(pageList.getTotal(),pageList.getResult());
        return new Result(true,StatusCode.OK,"查询成功",pageResult);
    }
}

1.4.3 日志模块

表结构:

tb_operatelog

需求:

1). 记录日志

2). 查询日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CkUsbUrh-1634717644166)(assets/image-20200205224758890.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CSUFmIXu-1634717644168)(assets/image-20200218031150324.png)]

1.4.3.1 创建工程

1). pom.xml

    <dependencies>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_common</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_model</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>

        <dependency>
            <groupId>cn.itcast</groupId>
            <artifactId>v_feign_api</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>


        <!-- Eureka客户端依赖 -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

        <!--MySQL数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--mybatis分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
        </dependency>

        <!--web起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- fastJson依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
        </dependency>

        <!-- Feign依赖 -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>

    </dependencies>

2). application.yml

server:
  port: 9003
spring:
  application:
    name: log
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/v_shop?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 2143
  main:
    allow-bean-definition-overriding: true #当遇到同样名字的时候，是否允许覆盖注册
eureka:
  client:
    fetch-registry: true
    register-with-eureka: true
    service-url:
      defaultZone: http://127.0.0.1:8161/eureka
  instance:
    prefer-ip-address: true

3). 引导类

@SpringBootApplication
@EnableEurekaClient
@MapperScan(basePackages = "cn.itcast.log.mapper")
public class LogApplication {
    public static void main(String[] args) {
        SpringApplication.run(LogApplication.class,args);
    }
   
    @Bean
    public IdWorker idworker(){
        return new IdWorker(0,0);
    }
}

分布式ID生成(雪花算法)

snowflake是 Twitter 开源的分布式ID生成算法，结果是一个long型的ID。其核心思想是：使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 ID），最后还有一个符号位，永远是0 ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8EDAZxln-1634717644171)(assets/image-20200218010603410.png)]

使用方式:

IdWorker idWorker=new IdWorker(1,1);//0-31 , 0-31

for(int i=0;i<10000;i++){
	long id = idWorker.nextId();
	System.out.println(id);
}

1.4.3.2 Mapper

mapper接口

public interface OperateLogMapper {

    public void insert(TbOperatelog operationLog);

    public List<TbOperatelog> search(Map searchMap);

}

OperateLogMapper.xml

<mapper namespace="cn.itcast.log.mapper.OperateLogMapper" >

    <resultMap id="operateLogResultMap" type="cn.itcast.model.TbOperatelog">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="model_name" jdbcType="VARCHAR" property="modelName" />
        <result column="model_value" jdbcType="VARCHAR" property="modelValue" />
        <result column="return_value" jdbcType="VARCHAR" property="returnValue" />
        <result column="return_class" jdbcType="VARCHAR" property="returnClass" />
        <result column="operate_user" jdbcType="VARCHAR" property="operateUser" />
        <result column="operate_time" jdbcType="VARCHAR" property="operateTime" />
        <result column="param_and_value" jdbcType="VARCHAR" property="paramAndValue" />
        <result column="operate_class" jdbcType="VARCHAR" property="operateClass" />
        <result column="operate_method" jdbcType="VARCHAR" property="operateMethod" />
        <result column="cost_time" jdbcType="BIGINT" property="costTime" />
    </resultMap>


    <insert id="insert" parameterType="cn.itcast.model.TbOperatelog">
    insert into tb_operatelog (id, model_name, model_value, 
      return_value, return_class, operate_user, 
      operate_time, param_and_value, operate_class, 
      operate_method, cost_time)
    values (#{id}, #{modelName}, #{modelValue}, 
      #{returnValue}, #{returnClass}, #{operateUser}, 
      #{operateTime}, #{paramAndValue}, #{operateClass}, 
      #{operateMethod}, #{costTime})
  </insert>


    <select id="search" resultMap="operateLogResultMap">
        select * from tb_operatelog
        <where>
            <if test="operateUser != null and operateUser != ''">
                and operate_user = #{operateUser}
            </if>
            <if test="operateMethod != null and operateMethod != ''">
                and operate_method = #{operateMethod}
            </if>
            <if test="returnClass != null and returnClass != ''">
                and return_class = #{returnClass}
            </if>
            <if test="costTime != null and costTime != '' ">
                and cost_time = #{costTime}
            </if>
        </where>
    </select>

</mapper>

1.4.3.3 Service

接口

public interface OperateLogService {
    public void insert(TbOperatelog operationLog);

    public Page<TbOperatelog> findPage(Map searchMap, Integer pageNum , Integer pageSize);
}

实现

@Service
@Transactional
public class OperateLogServiceImpl implements OperateLogService {


    @Autowired
    private OperateLogMapper operateLogMapper;

    public void insert(TbOperatelog operationLog){
        long id = idworker.nextId();
        operationLog.setId(id);
        operateLogMapper.insert(operationLog);
    }

    public Page<TbOperatelog> findPage(Map searchMap, Integer pageNum , Integer pageSize){
        System.out.println(searchMap);
	
        PageHelper.startPage(pageNum,pageSize);
        List<TbOperatelog> list = operateLogMapper.search(searchMap);

        return (Page<TbOperatelog>) list;
    }

}

1.4.3.4 Controller

@RestController
@RequestMapping("/operateLog")
public class OperateLogController {

    @Autowired
    private OperateLogService operateLogService;

    @RequestMapping("/search/{page}/{size}")
    public Result findList(@RequestBody Map dataMap, @PathVariable Integer page, @PathVariable  Integer size){

        Page<TbOperatelog> pageList = operateLogService.findPage(dataMap, page, size);
        PageResult pageResult=new PageResult(pageList.getTotal(),pageList.getResult());

        return new Result(true, StatusCode.OK,"查询成功",pageResult);
    }


    @RequestMapping("/add")
    public Result add(@RequestBody TbOperatelog operatelog){
        operateLogService.insert(operatelog);
        return new Result(true,StatusCode.OK,"添加成功");
    }

}

1.4.3.5 AOP记录日志

在需要记录操作日志的微服务中, 引入AOP记录日志的类 :

1). 自定义注解

@Inherited
@Documented
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface OperateLog {
}

2). AOP通知类

@Component
@Aspect
public class OperateAdvice {

	@Autowired
	private OperateLogFeign operateLogFeign;

	@Around("execution(* cn.itcast.goods.controller.*.*(..)) && @annotation(operateLog)")
	public Object insertLogAround(ProceedingJoinPoint pjp , OperateLog operateLog) throws Throwable{
		System.out.println(" *********************************** 记录日志 [start]  ****************************** ");

		TbOperatelog op = new TbOperatelog();

		DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		op.setOperateTime(sdf.format(new Date()));
		op.setOperateUser("10000");
		
		op.setOperateClass(pjp.getTarget().getClass().getName());
		op.setOperateMethod(pjp.getSignature().getName());

		String paramAndValue = "";
		Object[] args = pjp.getArgs();
		if(args != null){
			for (Object arg : args) {
				if(arg instanceof String || arg instanceof Integer || arg instanceof Long){
					paramAndValue += arg +",";
				}else{
					paramAndValue += JSON.toJSONString(arg)+",";
				}
			}
			op.setParamAndValue(paramAndValue);
		}

		long start_time = System.currentTimeMillis();

		//放行
		Object object = pjp.proceed();

		long end_time = System.currentTimeMillis();
		op.setCostTime(end_time - start_time);

		if(object != null){
			op.setReturnClass(object.getClass().getName());
			op.setReturnValue(object.toString());
		}else{
			op.setReturnClass("java.lang.Object");
			op.setParamAndValue("void");
		}

		operateLogFeign.add(op);

		System.out.println(" *********************************** 记录日志 [end]  ****************************** ");

		return object;
	}
}

1.5 MyCat分片

当前数据库的情况 :

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VyWutMkW-1634717644172)(assets/image-20200209111951501.png)]

由于当前项目是一个电商项目，项目上线后，随着项目的运营，业务系统的数据库中的数据与日俱增，特别是订单、日志等数据，如果数据量过大，这个时候就需要考虑通过MyCat分库分表。

1.5.1 分片分析

1). 垂直拆分

数据量过大，需要考虑扩容，可以通过MyCat来实现数据库表的垂直拆分，将同一块业务的数据库表拆分到同一个数据库服务中。拆分方式如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZdwkJ7Uh-1634717644173)(assets/image-20200209110344132.png)]

2). 全局表

按照上述的方式进行表结构的拆分，可以解决扩容的问题，但是存在另一个问题：由于省、市、区县、数据字典表，在订单及商品等模块中都需要用到，还会涉及到多表连接查询，那么这个时候涉及到跨库的join操作，可以使用全局表来解决。结构图如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J8i1IP6T-1634717644175)(assets/image-20200209111118652.png)]

3). 水平拆分

即使我们在上述的方案中使用垂直拆分，将系统中的表结构拆分到了三个数据库服务器中，但是对于当前这个比较繁忙的业务系统来说，每天都会产生大量的用户操作日志，长年累月，这张表的数据在单台服务器中已经存储不下了，这个时候，我们就可以使用MyCat的水平拆分来解决这个问题。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pqh1Ajp1-1634717644176)(assets/image-20200209171203841.png)]

1.5.2 服务器配置

名称	IP	端口	用户名/密码
MyCat-Server	192.168.192.157	8066	root/123456
MySQL-1	192.168.192.158	3306	root/itcast
MySQL-2	192.168.192.159	3306	root/itcast
MySQL-3	192.168.192.160	3306	root/itcast
MySQL-4	192.168.192.161	3306	root/itcast

1.5.3 schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	
	<schema name="V_SHOP" checkSQLschema="false" sqlMaxLimit="100">
       
	   <table name="tb_areas" dataNode="dn1,dn2,dn3,dn4" primaryKey="areaid" type="global"/>
	   <table name="tb_provinces" dataNode="dn1,dn2,dn3,dn4" primaryKey="provinceid" type="global"/>
	   <table name="tb_cities" dataNode="dn1,dn2,dn3,dn4" primaryKey="cityid"  type="global"/>
	   <table name="tb_dictionary" dataNode="dn1,dn2,dn3,dn4" primaryKey="id"  type="global"/>
		
		
		<table name="tb_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_category" dataNode="dn1" primaryKey="id" />
		<table name="tb_sku" dataNode="dn1" primaryKey="id" />
		<table name="tb_spu" dataNode="dn1" primaryKey="id" />
		
        
		<table name="tb_order" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_log" dataNode="dn2" primaryKey="id" />
		
        
		<table name="tb_operatelog" dataNode="dn3,dn4" primaryKey="id" rule="log-sharding-by-murmur"/>
	</schema>
    
	
	<dataNode name="dn1" dataHost="host1" database="v_goods" />
	<dataNode name="dn2" dataHost="host2" database="v_order" />
	<dataNode name="dn3" dataHost="host3" database="v_log" />
	<dataNode name="dn4" dataHost="host4" database="v_log" />
    
	
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="192.168.159.34:3306" user="root" password="123456">	</writeHost>
	</dataHost>	
    
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM2" url="192.168.159.35:3306" user="root" password="123456"></writeHost>
	</dataHost>	
    
    <dataHost name="host3" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM3" url="192.168.159.22:3306" user="root" password="123456"></writeHost>
	</dataHost>	
	
	<dataHost name="host4" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM4" url="192.168.159.37:3306" user="root" password="123456"></writeHost>
	</dataHost>	
</mycat:schema>

1.5.4 分片配置

1). 配置Mycat的schema.xml

2). 配置rule.xml

<tableRule name="log-sharding-by-murmur">
    <rule>
        <columns>id</columns>
        <algorithm>log-murmur</algorithm>
    </rule>
</tableRule>

<function name="log-murmur" class="io.mycat.route.function.PartitionByMurmurHash">
    <property name="seed">0</property>
    <property name="count">2</property>
    <property name="virtualBucketTimes">160</property>
</function>

3). 配置server.xml

<user name="root" defaultAccount="true">
    <property name="password">GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==</property>
    <property name="schemas">V_SHOP</property>
    <property name="readOnly">false</property>
    <property name="benchmark">1000</property>
    <property name="usingDecrypt">1</property>

    <!-- 表级 DML 权限设置

  <privileges check="true">
   <schema name="ITCAST" dml="1111" >
    <table name="TB_TEST" dml="1110"></table>
   </schema>
  </privileges>	
  -->		
</user>

4). 在各个MySQL数据库实例中创建数据库

MySQL-1 : v_goods
MySQL-2 : v_order
MySQL-3 : v_log
MySQL-4 : v_log

5). 导出本地的SQL脚本 , 在MyCat中执行SQL脚本 , 创建数据表 ,并导入数据

6). 连接测试

1.5.5 微服务连接MyCat

  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.192.157:8066/V_SHOP?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456