Sharding-JDBC-Demo(水平分库水平分表)

一、环境构建

​ 1、创建一个springboot项目

​ 2、导入如下依赖

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cc</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-demo</name>
    <description>sharding-demo</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
            <plugins>

                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>

            </plugins>
        </pluginManagement>
    </build>

</project>

遇到一些问题：我的application.properties文件中的ShardingSphere出现问题：未使用的属性

通过网上搜索答案是：如果出现未使用的属性，通常是因为该属性在当前的配置中没有被使用到。
在这种情况下，可以尝试删除该属性或者检查是否有其他地方需要使用该属性。另外，如果您使用的是较旧版本的ShardingSphere，可能会出现某些属性不再被支持的情况，建议升级到最新版本。
升级到最新版后仍然无法解决。把配置文件转为 yml的格式就可以了

二、sharding-jdbc实现水平分表

1、创建sharding_sphere数据库
2、在数据库中创建两张表，orders_1和orders_2
3、分片规则：如果订单编号是偶数添加到orders_1,如果是奇数添加到orders_2
4、创建实体类

public class Orders {
    private Integer id;
    private Integer orderType;
    private Integer customerId;
    private Double amount;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getOrderType() {
        return orderType;
    }

    public void setOrderType(Integer orderType) {
        this.orderType = orderType;
    }

    public Integer getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Integer customerId) {
        this.customerId = customerId;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }
    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderType=" + orderType +
                ", customerId=" + customerId +
                ", amount=" + amount +
                '}';
    }
}

5、创建mapper类

@Repository
@Mapper
public interface OrdersMapper {
    @Insert("insert into orders(id,orderType,customerId,amount) values(#{id},#{orderType},#{customerId},#{amount})")
    public void insert(Orders orders);

    @Select("select * from orders where id = #{id}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "orderType",column = "orderType"),
            @Result(property = "customerId",column = "customerId"),
            @Result(property = "amount",column = "amount")
    })
    public Orders selectOne(Integer id);
}

6、创建配置文件

#整合mybatis
mybatis:
  type-aliases-package: com.cc.shardingdemo.mapper

spring:
  shardingsphere:
    datasource:
      names: ds1  #定义数据源ds1
      #配置数据源 ds1 的具体内容，
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_sphere?serverTimezone=UTC
        username: root
        password: 123456
    #指定 orders 表的分布情况，配置表在哪个数据库中，表名称是什么
    sharding:
      tables:
        orders:
          actual-data-nodes: ds1.orders_$->{1..2}
          key-generator:
            column: id       #指定orders表里主键id生成策略
            type: SNOWFLAKE  #雪花算法
          #指定分片策略。根据id的奇偶性来判断插入到哪个表
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: orders_${id%2+1}  #id为偶数则加入 orders_(0+1)也就是orders_1;
    #打开sql输出日志
    props:
      sql:
        show:true

7、创建测试类

@SpringBootTest
class ShardingDemoApplicationTests {

   @Test
   void contextLoads() {

   }

   @Autowired
   private OrdersMapper ordersMapper;
   @Test
   public void addOrders(){
       for (int i = 1; i <=10 ; i++) {
           Orders orders = new Orders();
           orders.setId(i);
           orders.setCustomerId(i);
           orders.setOrderType(i);
           orders.setAmount(1000.0*i);
           ordersMapper.insert(orders);
       }
   }
   @Test
   public void queryOrders(){
       Orders orders = ordersMapper.selectOne(1);
       System.out.println(orders);
   }
}

三、sharding-jdbc实现水平分库

1、创建不同名称的数据库：sharding_sphere_1,sharding_sphere_2
2、在两个数据库上创建相同的表orders_1,orders_2
3、分片规则，按照customer_id的奇偶性来进行分库，然后按照id的奇偶性进行分表
4、修改配置文件

#mapper文件扫描路径
mybatis:
  mapper-locations: classpath:/mapper/*.xml
spring:
  main:
    allow-bean-definition-overriding: true


#水平分库水平分表配置,两个数据库，每个数据库中两张表
  shardingsphere:
    # 数据源名称 db1 、db2
    datasource:
      names: db1,db2
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_sphere_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 123456
      db2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_sphere_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 123456

    # 配置数据库的分布，表的分布
    sharding:
      tables:
        orders:
          # db1:orders_1, orders_2; db2:orders_1 ,orders_2;
          actual-data-nodes: db$->{1..2}.orders_$->{1..2}
          # 指定user_info表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定数据库分片策略 约定orders 值是偶数添加到db1(sharding_sphere_1)中，奇数添加到db2中
          database-strategy:
            inline:
              sharding-column: customer_id
              algorithm-expression: db$->{customer_id%2+1}
            # 指定表分片策略
          table-strategy:
            inline:
              #约定id值是偶数添加到orders_1表，如果是奇数添加到orders_2表
              sharding-column: id
              algorithm-expression: orders_$->{id%2+1}
    props:
      sql:
        show: true
# 控制台日志配置
logging:
  level:
    root: info

5、修改mapper类
换一种写法：

<?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="com.cc.shardingdemo.mapper.OrdersMapper">

    <insert id="insert" parameterType="com.cc.shardingdemo.entity.Orders">
       insert into orders(id,orderType,customerId,amount)
        values(#{id},#{orderType},#{customerId},#{amount})
    </insert>

    <select id="selectUser" resultType="com.cc.shardingdemo.entity.Orders">
        select * from orders
    </select>
</mapper>

@Mapper
public interface OrdersMapper {
    public void insert(Orders orders);
    }

6、编写测试类

@Test
    public void addOrdersDB(){
        for (int i = 1; i <=20 ; i++) {
            Orders orders = new Orders();
            orders.setId(i);
            orders.setCustomerId(10+i);
            orders.setOrderType(i);
            orders.setAmount(10.0*i);
            ordersMapper.insert(orders);
        }
    }