记一次sharding-sphere

1.项目结构

2.jdk版本1.8， sharding-sphere版本 3.0.0.M3， mysql版本1.8， springboot版本1.5.18.RELEASE

3.直接上代码

pom.xml

<?xmlversion="1.0"?>

<project

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0http://maven.apache.org/xsd/maven-4.0.0.xsd"

xmlns="http://maven.apache.org/POM/4.0.0"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<modelVersion>4.0.0</modelVersion>

<parent>

<groupId>net.popo</groupId>

<artifactId>middleware</artifactId>

<version>1.0.0-SNAPSHOT</version>

</parent>

<artifactId>middleware-sharding-sphere</artifactId>

<name>middleware-sharding-sphere</name>

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

<properties>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

</properties>

<dependencies>

<dependency>

<artifactId>middleware-utils</artifactId>

<groupId>net.popo</groupId>

<version>1.0.0-SNAPSHOT</version>

</dependency>

<dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<dependency>

<groupId>io.shardingsphere</groupId>

<artifactId>sharding-jdbc-spring-boot-starter</artifactId>

</dependency>

</dependencies>

</project>

 

 

application-sharding.yml

sharding:

  jdbc:

    datasource:

      names:master,slave #数据源名称，多数据源以逗号分隔

      master:

        driver-class-name:com.mysql.cj.jdbc.Driver

        url:jdbc:mysql://10.2.98.14:3306/predb?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false

        username:xxxx

        password:xxxx

        type:com.alibaba.druid.pool.DruidDataSource

        filters:stat

        initial-size:1

        min-idle:1

        max-active:20

        max-wait:60000

        time-between-eviction-runs-millis:60000

        min-evictable-idle-time-millis:300000

        validation-query:SELECT 'x'

        test-while-idle:true

        test-on-borrow:false

        test-on-return:false

        pool-prepared-statements:false

        max-pool-prepared-statement-per-connection-size:20

      slave:

        driver-class-name:com.mysql.cj.jdbc.Driver

        url:jdbc:mysql://10.2.98.174:3306/predb?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false

        username:xxxx

        password:xxxx

        type:com.alibaba.druid.pool.DruidDataSource

        filters:stat

        initial-size:1

        min-idle:1

        max-active:20

        max-wait:60000

        time-between-eviction-runs-millis:60000

        min-evictable-idle-time-millis:300000

        validation-query:SELECT 'x'

        test-while-idle:true

        test-on-borrow:false

        test-on-return:false

        pool-prepared-statements:false

        max-pool-prepared-statement-per-connection-size:20

    config:

      sharding:

        props:

          sql:

            show:true

        tables:# 分表规则

          demo:# 要分的表

            actual-data-nodes:sharding_db.demo$->{0..2} # 哪个库的哪些表(子表)，分为3个表demo0，demo1，demo2

            key-generator-column-name:id # 主键

            table-strategy:# 分表策略

              inline:# 行表达式策略

                shardingColumn:mobile  # 用于分表的字段

                algorithmExpression:demo$->{mobile % 3} # 分表规则表达式，0-3-6-9落到demo0，1-4-7-10落到demo1，2-5-8落到demo2

        master-slave-rules:# 主从配置规则

          sharding_db:  # 所有数据库的集合

            master-data-source-name:master # 主

            slave-data-source-names:slave  # 从

 

 

application.yml

spring:

  profiles:

    include:sharding

mybatis:

  #mapper-locations: classpath*:mybatis/*.xml #用xml的形式必须要配置

  type-aliases-package:net.popo.*.po

  configuration:

    call-setters-on-nulls:true

 

 

Demo.java

packagenet.popo.demo.po;

publicclassDemo {

    privateLong id;

    privateString name;

    privateLong mobile;

    publicLong getId() {

        returnid;

    }

    publicvoidsetId(Long id) {

        this.id= id;

    }

    publicString getName() {

        returnname;

    }

    publicvoidsetName(String name) {

        this.name= name== null? null: name.trim();

    }

    publicLong getMobile() {

        returnmobile;

    }

    publicvoidsetMobile(Long mobile) {

        this.mobile= mobile;

    }

}

 

DemoMapper.java

package net.popo.demo.dao;

import net.popo.demo.po.Demo;

import org.apache.ibatis.annotations.Insert;

 

public interface DemoMapper {

    @Insert({

        "insert into demo (id, name, ",

        "mobile)",

        "values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, ",

        "#{mobile,jdbcType=BIGINT})"

    })

    int insert(Demo record);

}

 

TestServiceImpl.java

package net.popo.demo.service;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import net.popo.demo.dao.DemoMapper;

import net.popo.demo.po.Demo;

 

@Service

public class TestServiceImpl {

 

    @Autowired

    private DemoMapper testMapper;

    

    public int save(Demo test) {

        return testMapper.insert(test);

    }

}

 

 

TestController.java

package net.popo.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import net.popo.demo.po.Demo;

import net.popo.demo.service.TestServiceImpl;

import net.popo.util.SnowFlakeOrder;

@RestController

public class TestController {

    

    @Autowired

    private TestServiceImpl testServiceImpl;

    @RequestMapping("/test")

    public int test() {

        Demo test = new Demo();

        test.setId(SnowFlakeOrder.getUniqueId("133xxxxxxxx", 3)); // 根据手机号对3取模存到对应的表中

        test.setMobile(133xxxxxxxxl);

        System.out.println(test.getId());

        System.out.println("该条数据应该落在 ：" + test.getMobile() % 3 + " 的表中");

        test.setName("hoopopo");

        return testServiceImpl.save(test);

    }

}

 

 

4.源码地址 https://github.com/hoopopo/middleware