shardingsphere core demo_import org.apache.shardingsphere.driver.api.yaml.y-CSDN博客

本文链接：https://blog.csdn.net/ltzx20080441/article/details/134890189

使用shardingshpere core 完成水平分表操作

依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>shardingCoreDemo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.4.1</mybatis-plus.version>
        <shardingsphere.version>5.0.0</shardingsphere.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

springboot配置

文件名:application.yml

放置于resource目录下

mybatis-plus:
  configuration:
    cache-enabled: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
  mapper-locations: classpath:mapper/*.xml

sharding规则

文件名 :sharding-databases-tables.yaml

放置于resource目录下

mode:
  type: Standalone
  repository:
    type: JDBC
dataSources:
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/shardingsphere0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true
    username: root
    password: root
rules:
  - !SHARDING
    tables:
      t_problem:
        actualDataNodes: ds_1.t_problem${0..2}
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: t-problem-table-inline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake
    shardingAlgorithms:
      t-problem-table-inline:
        type: INLINE
        props:
          algorithm-expression: t_problem${id % 3}
    keyGenerators:
      snowflake:
        type: SNOWFLAKE

创建数据源

文件:

cn.tedu.config.ShardingConfig

作用:

创建datasource以便于mybatis-plus使用

package cn.tedu.config;

import org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.ResourceUtils;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;

@Configuration
public class ShardingConfig {
    @Bean
    DataSource initDataSource() throws IOException, SQLException {
        File yamlFile = ResourceUtils.getFile("classpath:sharding-databases-tables.yaml");
        DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(yamlFile);
        return dataSource;
    }
}

测试

创建实体类

文件:

cn.tedu.pojo.Problem

作用:

作为测试插入数据库一条记录的实体类

package cn.tedu.pojo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "t_problem")
public class Problem {
    @TableId(value = "id")
    Long id;
    @TableField(value = "content")
    String content;
}

创建mapper

文件:

cn.tedu.mapper.ProblemMapper

作用:

使用mybatisplus提供的一系列操作数据库方法

package cn.tedu.mapper;

import cn.tedu.pojo.Problem;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface ProblemMapper extends BaseMapper<Problem> {
}

创建springboot启动类

文件:

cn.tedu.AppMain

作用:作为启动类在此Demo中仅仅起到加载spring环境的作用以供给与测试类使用

package cn.tedu;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("cn.tedu.mapper")
public class AppMain {
    public static void main(String[] args) {
        SpringApplication.run(AppMain.class,args);
    }
}

进行测试

运行test方法:按照sharding规则id为9取余3为0应该被分到t_problem0这张表里

package cn.tedu;

import cn.tedu.mapper.ProblemMapper;
import cn.tedu.pojo.Problem;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;

@SpringBootTest
public class TestDemo {
    @Autowired
    ProblemMapper problemMapper;
    @Autowired
    DataSource dataSource;
    @Test
    public void test(){
        System.out.println(dataSource.getClass().getName());
        Problem problem = new Problem();
        problem.setContent("hello world shardingsphere");
        problem.setId(9L);
        problemMapper.insert(problem);
    }
}

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE IF NOT EXISTS `shardingsphere0` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */;
USE `shardingsphere0`;

CREATE TABLE IF NOT EXISTS `t_problem0` (
  `id` bigint(20) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

DELETE FROM `t_problem0`;
/*!40000 ALTER TABLE `t_problem0` DISABLE KEYS */;
INSERT INTO `t_problem0` (`id`, `content`) VALUES
	(9, 'hello world shardingsphere');
/*!40000 ALTER TABLE `t_problem0` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `t_problem1` (
  `id` bigint(20) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

DELETE FROM `t_problem1`;
/*!40000 ALTER TABLE `t_problem1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_problem1` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `t_problem2` (
  `id` bigint(20) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

DELETE FROM `t_problem2`;
/*!40000 ALTER TABLE `t_problem2` DISABLE KEYS */;
INSERT INTO `t_problem2` (`id`, `content`) VALUES
	(8, 'hello world shardingsphere');
/*!40000 ALTER TABLE `t_problem2` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;