1 分表分库

LogicTable

数据分片逻辑表,对于水平拆分数据库(表),同一类表的总称。

订单信息表拆分为2张表,分别是t_order_0、t_order_1,他们逻辑表名为t_order

ActualTable

分片数据库中真实存在物理表。即上个示例中的t_order_0、t_order_1。

DataNode

数据分片最小单元。由数据源名称数据表组成,例:test_msg0.t_order_0。配置默认各个分片数据库的表结构相同直接配置逻辑表和真实表对应关系即可

ShardingColumn

分片字段用于数据库(表)水平拆分的关键字段。SQL中如果无分片字段,将执行路由性能较差。Sharding-JDBC支持多分片字段

ShardingAlgorithm

分片算法。Sharding-JDBC通过分片算法将数据分片,支持通过等号、BETWEEN和IN分片。分片算法目前需要业务开发者自行实现,可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法,如rangehashtag等。

2 自定义分片模式

2.1 数据库结构

创建ds_0数据库新建如下

CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3 新建maven项目

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </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>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- 引入shardingjdbc依赖信息 -->
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>2.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-self-id-generator</artifactId>
            <version>1.4.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.12</version>
        </dependency>

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

4 application配置

###数据库访问连接
spring:
  jdbc:
    db0:
      password: 123456
      className: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.222.157:3306/%s?characterEncoding=utf-8
      username: root
  jpa:
    database: mysql
    show-sql: true
    hibernate:
      ## 自己建表
      ddl-auto: none
  application:
    name: sharding-jdbc-first

5 配置分表算法

5.1 配置数据库连接

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

// 数据源相关配置信息
@Configuration
public class DataSourceConfig {
    @Value("${spring.jdbc.db0.className}")
    private String className;
    @Value("${spring.jdbc.db0.url}")
    private String url;
    @Value("${spring.jdbc.db0.username}")
    private String username;
    @Value("${spring.jdbc.db0.password}")
    private String password;

    @Bean
    public IdGenerator getIdGenerator() {
        return new CommonSelfIdGenerator();
    }

    @Bean
    public DataSource getDataSource() {
        return buildDataSource();
    }

    private DataSource buildDataSource() {
        // 1.设置分库映射
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("ds_0", createDataSource("ds_0"));
        // dataSourceMap.put("ds_1", createDataSource("ds_1"));
        // 设置默认db为ds_0,也就是为那些没有配置分库分表策略指定默认库
        // 如果只有一个库,也就是不需要分库的话,map里只放一个映射就行了,只有一个库时不需要指定默认库,
        // 但2个及以上时必须指定默认库,否则那些没有配置策略的表将无法操作数据
        DataSourceRule rule = new DataSourceRule(dataSourceMap, "ds_0");
        // 2.设置分表映射,将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
        TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
                .dataSourceRule(rule).build();
        // 3.具体的分库分表策略
        ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
                .tableRules(Arrays.asList(orderTableRule))
                // 根据userid分片字段
                .tableShardingStrategy(new TableShardingStrategy("user_id", new TableShardingAlgorithm())).build();
        // 创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }

    private DataSource createDataSource(String dataSourceName) {
        // 使用druid连接数据库
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(className);
        druidDataSource.setUrl(String.format(url, dataSourceName));
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        return druidDataSource;
    }
}

5.2 配置分表策略

分表算法需要实现SingleKeyTableShardingAlgorithm<T>接口

package com.example.demo.config;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;

import java.util.Collection;

// 表分片算法
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

    // sql关键字 匹配符为 =的时候,表的路由函数
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {

        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
                                                ShardingValue<Long> shardingValue) {

        return null;
    }

}

6 新建实体类

package com.example.demo.entity;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {
    @Id
    private Long orderId;

    private Long userId;

}

7 OrderRepository

package com.example.demo.repository;

import com.example.demo.entity.OrderEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface OrderRepository extends CrudRepository<OrderEntity, Long> {
    @Query(value = "SELECT order_id ,user_id  FROM t_order  where order_id in (?1);", nativeQuery = true)
    public List<OrderEntity> findExpiredOrderState(List<String> bpIds);
}

8 OrderController

package com.example.demo.controller;

import com.example.demo.entity.OrderEntity;
import com.example.demo.repository.OrderRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

@RestController
public class OrderController {
    @Autowired
    private OrderRepository orderRepository;

    // 查询所有的订单信息
    @RequestMapping("/getOrderAll")
    public List<OrderEntity> getOrderAll() {
        return (List<OrderEntity>) orderRepository.findAll();
    }

    // 使用in条件查询
    @RequestMapping("/inOrder")
    public List<OrderEntity> inOrder() {
        List<String> ids = new ArrayList<>();
        ids.add("2");
        ids.add("3");
        ids.add("4");
        ids.add("5");
        return orderRepository.findExpiredOrderState(ids);

    }

    // 增加
    @RequestMapping("/inserOrder")
    public String inserOrder(OrderEntity orderEntity) {
        for (int i = 0; i < 10; i++) {
            OrderEntity order = new OrderEntity();
            order.setOrderId((long) i);
            order.setUserId((long) i);
            orderRepository.save(order);
        }
        return "success";
    }

}

9 新建启动

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@EnableJpaRepositories(basePackages = "com.example.demo.repository")
public class AppSharding {
    public static void main(String[] args) {
        SpringApplication.run(AppSharding.class, args);
    }
}

10 启动项目

访问http://localhost:8080/inserOrder

查看数据库

分表测试成功

原文地址:https://blog.csdn.net/u013938578/article/details/134765215

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任

如若转载,请注明出处:http://www.7code.cn/show_35388.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱suwngjj01@126.com进行投诉反馈,一经查实,立即删除

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注