Springboot + Mybatis-Plus + Sharding-JDBC

现在开源的分库分表方案中, 常用的就是MyCat和Sharding-JDBC, MyCat需要有专门的服务器, 通常要的配置也比较高, 公司之前就是专门部署了两台64G的MyCat服务器, 后面为了节省资源, 逐步切换成Sharding-JDBC

这里主要介绍Springboot, Mybatis-Plus, Sharding-JDBC的整合, 基于Springboot的yml配置文件配置分库分表策略, 使用snowflake雪花算法生成分布式主键, 分片策略是哈希取模

pom文件

<dependencies>
    <dependency>
        <groupId>de.codecentric</groupId>
        <artifactId>spring-boot-admin-starter-client</artifactId>
        <version>1.5.6</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-eureka</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-feign</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-hystrix</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-ribbon</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-zipkin</artifactId>
    </dependency>

    <dependency>
        <groupId>com.eric</groupId>
        <artifactId>common</artifactId>
        <version>1.0-SNAPSHOT</version>
    </dependency>

    <dependency>
        <groupId>com.eric</groupId>
        <artifactId>cache</artifactId>
        <version>1.0-SNAPSHOT</version>
    </dependency>

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

    <!--dozer依赖开始-->
    <dependency>
        <groupId>net.sf.dozer</groupId>
        <artifactId>dozer</artifactId>
        <version>5.5.1</version>
        <exclusions>
            <!--目前项目中使用的是apache自带的log4j-->
            <exclusion>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
            </exclusion>
            <!--<exclusion>
               <groupId>log4j</groupId>
               <artifactId>log4j</artifactId>
            </exclusion>-->
        </exclusions>
    </dependency>
    <dependency>
        <groupId>net.sf.dozer</groupId>
        <artifactId>dozer-spring</artifactId>
        <version>5.5.1</version>
    </dependency>
    <!--dozer依赖结束-->

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

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-tomcat</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-undertow</artifactId>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.41</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.0</version>
    </dependency>

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.6</version>
    </dependency>

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

    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>18.0</version>
    </dependency>

    <!-- mybatis的分页插件 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.1.7</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>provided</scope>
        <version>1.18.2</version>
    </dependency>

    <!--sharding-jdbc依赖开始-->
    <!-- for spring boot -->
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>

    <!-- for spring namespace -->
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>
    <!--sharding-jdbc依赖结束-->
</dependencies>

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

<build>
    <finalName>${project.artifactId}</finalName>
    <plugins>
        <!-- 资源文件拷贝插件 -->
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-resources-plugin</artifactId>
            <configuration>
                <encoding>UTF-8</encoding>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
                <encoding>UTF8</encoding>
            </configuration>
        </plugin>
    </plugins>
</build>

yml配置文件需要创建两个数据库实例ds0和ds1, 每个数据实例要创建ordermaster0和ordermaster1两个表

server:
  port: 12890
  undertow:
    io-threads: 16
    worker-threads: 256
    buffer-size: 1024
    buffers-per-region: 1024
    direct-buffers: true
    accesslog:
      dir: my-undertow
      enabled: true
      pattern: '%t %a "%r" %s (%D ms)'

# sharding-jdbc分库分表的配置
sharding:
  jdbc:
    datasource:
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: 123456
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: 123456
      names: ds0,ds1
    config:
      sharding:
        tables: # 分表策略
          order_master:
            table-strategy:
              inline:
                sharding-column: order_id
                algorithm-expression: order_master$->{order_id % 2}
            key-generator-column-name: order_id
            actual-data-nodes: ds$->{0..1}.order_master$->{0..1}
        default-database-strategy: # 分库策略
          inline:
            sharding-column: user_id
            algorithm-expression: ds$->{user_id % 2}

mybatis-plus:
  datasource: dataSource
  mapper-locations: classpath:/mapper/*.xml
  #实体扫描，多个package用逗号或者分号分隔
  typeAliasesPackage: com.eric.shardingjdbc.bean
  global-config:
    #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    id-type: 1
    #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
    field-strategy: 2
    #驼峰下划线转换
    db-column-underline: true
    #刷新mapper 调试神器
    refresh-mapper: true
    #数据库大写下划线转换
    #capital-mode: true
    #序列接口实现类配置
    #key-generator: com.baomidou.springboot.xxx
    #逻辑删除配置
    logic-delete-value: 0
    logic-not-delete-value: 1
    #自定义填充策略接口实现
    #meta-object-handler: com.baomidou.springboot.xxx
    #自定义SQL注入器
    #sql-injector: com.baomidou.springboot.xxx
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false

snowflake:
  datacenter: 1 # 数据中心的id
  bizType: sharding_order_id_ # 业务类型

bean对象

@Data
@Accessors(chain = true)
public class OrderMaster implements Serializable {
    /**
     * 订单ID
     */
    @TableId
    private Long orderId;

    /**
     * 订单编号
     */
    private String orderSn;

    /**
     * 下单人ID
     */
    private Long userId;

    private static final long serialVersionUID = 1L;

}

mapper(mapper.xml可以生成空的xml)

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.eric.shardingjdbc.bean.OrderMaster;

/**
 * @author wang.js on 2019/1/24.
 * @version 1.0
 */
public interface OrderMasterMapper extends BaseMapper<OrderMaster> {

}

service层

/**
 * 主订单
 *
 * @author wang.js on 2019/3/12.
 * @version 1.0
 */
public interface OrderMasterService {

    /**
     * 保存订单
     *
     * @param t 订单
     * @return boolean
     */
    boolean insert(OrderMaster t);

    /**
     * 查询订单
     * @param i
     * @return
     */
    OrderMaster findById(int i);
}


/**
 * @author wang.js on 2019/3/12.
 * @version 1.0
 */
@Service
public class OrderMasterServiceImpl extends ServiceImpl<OrderMasterMapper, OrderMaster> implements OrderMasterService {

    @Override
    public boolean insert(OrderMaster t) {
        return this.baseMapper.insert(t) > 0;
    }

    @Override
    public OrderMaster findById(int i) {
        return this.baseMapper.selectById(i);
    }

}

sql脚本(在ds0和ds1中分别执行)

DROP TABLE IF EXISTS `order_master0`;
CREATE TABLE `order_master0` (
  `order_id` varchar(40) DEFAULT NULL COMMENT '订单ID',
  `order_sn` varchar(40) DEFAULT NULL COMMENT '订单编号',
  `user_id` varchar(40) DEFAULT NULL COMMENT '下单人ID',
  `shipping_user` varchar(40) DEFAULT NULL COMMENT '收货人姓名',
  `payment_method` varchar(1) DEFAULT NULL COMMENT '支付方式',
  `order_money` int(11) DEFAULT NULL COMMENT '订单金额',
  `district_money` int(11) DEFAULT NULL COMMENT '优惠金额(不包含优惠券)',
  `shipping_money` int(11) DEFAULT NULL COMMENT '运费金额',
  `payment_money` int(11) DEFAULT NULL COMMENT '支付金额',
  `shipping_sn` varchar(40) DEFAULT NULL COMMENT '快递单号',
  `create_time` datetime DEFAULT NULL COMMENT '下单时间',
  `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
  `order_status` varchar(1) DEFAULT NULL COMMENT '订单状态',
  `order_point` int(11) DEFAULT NULL COMMENT '订单积分',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `coupon_discount` int(11) DEFAULT NULL COMMENT '优惠券金额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for order_master1
-- ----------------------------
DROP TABLE IF EXISTS `order_master1`;
CREATE TABLE `order_master1` (
  `order_id` varchar(40) DEFAULT NULL COMMENT '订单ID',
  `order_sn` varchar(40) DEFAULT NULL COMMENT '订单编号',
  `user_id` varchar(40) DEFAULT NULL COMMENT '下单人ID',
  `shipping_user` varchar(40) DEFAULT NULL COMMENT '收货人姓名',
  `payment_method` varchar(1) DEFAULT NULL COMMENT '支付方式',
  `order_money` int(11) DEFAULT NULL COMMENT '订单金额',
  `district_money` int(11) DEFAULT NULL COMMENT '优惠金额(不包含优惠券)',
  `shipping_money` int(11) DEFAULT NULL COMMENT '运费金额',
  `payment_money` int(11) DEFAULT NULL COMMENT '支付金额',
  `shipping_sn` varchar(40) DEFAULT NULL COMMENT '快递单号',
  `create_time` datetime DEFAULT NULL COMMENT '下单时间',
  `shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `receive_time` datetime DEFAULT NULL COMMENT '收货时间',
  `order_status` varchar(1) DEFAULT NULL COMMENT '订单状态',
  `order_point` int(11) DEFAULT NULL COMMENT '订单积分',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `coupon_discount` int(11) DEFAULT NULL COMMENT '优惠券金额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试类

@Test
public void t2() {
    Random random = new Random();
    for (long i = 1; i < 100L; i++) {
        OrderMaster t = new OrderMaster().setOrderId(defaultKeyGenerator.generateKey().longValue()).setUserId((long) (random.nextInt(100) + 1));
        orderMasterService.insert(t);
    }
}

ps: 如果并发度不高的情况下, defaultKeyGenerator.generateKey()生成的id很大几率会是偶数

项目已经上传到gitee和github

gitee: https://gitee.com/ericwo/second-kill

github: https://github.com/wangjisong1993/second-kill

posted on 2019-03-16 16:04 忘记送阅读(1747) 评论(0) 编辑收藏举报