盐析白兔

这个屌丝很懒，什么也没留下！

热门标签

springboot集成ShardingSphere-jdbc_io.shardingsphere s

作者：盐析白兔 | 2024-03-08 15:25:33

踩

io.shardingsphere sharding-jdbc-spring-boot-s

ShardingSphere-jdbc版本：4.1.1
Mysql创建两个数据库

一、pom文件


<?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.7.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.shardingsphere</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingsphere</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-transaction-2pc-xa</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>4.1.1</version>
        </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>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.8</version>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
</project>

二、application.yml


server:
  port: 8091
spring:
  shardingsphere:
    datasource:
      names: ds-0,ds-1
      ds-0:
        url: jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: 123456
      ds-1:
        url: jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false&allowPublicKeyRetrieval=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: 123456
    sharding:
      tables:
        user:
          # 数据节点配置，采用Groovy表达式
          actual-data-nodes: ds-$->{0..1}.user
          database-strategy:
            standard:
              sharding-column: age
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: 11
              executor.size: 4
              max.connections.size.per.query: 1
              check.table.metadata.enabled: false
        message:
          actual-data-nodes: ds-$->{0..1}.message_$->{0..1}
          database-strategy:
            standard:
              sharding-column: contact_id
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          table-strategy:
            standard:
              sharding-column: msg_id
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          key-generator:
            column: msg_id
            type: SNOWFLAKE
            props:
              worker.id: 22
              executor.size: 4
              max.connections.size.per.query: 1
              check.table.metadata.enabled: false
    props:
      # 日志显示具体的SQL
      sql.show: true
  jta:
    atomikos:
      properties:
        max-actives: -1
        log-base-dir: ./logs/shardingsphere

yml文件会爆红，不影响项目启动。

分库分表规则：

1、读写分离

2、数据库垂直分片

3、水平分片：（1）数据库分片；（2）水平分表

yml文件中采用的是数据库分片。

三、业务代码

1、src/main/java/com/shardingsphere/algorithm

分片策略

1、标准分片策略

标准分片策略适用于单分片键，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。

其中 PreciseShardingAlgorithm 是必选的，用于处理 = 和 IN 的分片。RangeShardingAlgorithm 是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片，如果不配置RangeShardingAlgorithm，SQL中的条件等将按照全库路由处理。

2、复合分片策略

复合分片策略，同样支持对 SQL语句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片键，具体分配片细节完全由应用开发者实现。

3、行表达式分片策略

行表达式分片策略，支持对 SQL语句中的 = 和 IN 的分片操作，但只支持单分片键。这种策略通常用于简单的分片，不需要自定义分片算法，可以直接在配置文件中接着写规则。

t_order_$->{t_order_id % 4} 代表 t_order 对其字段 t_order_id取模，拆分成4张表，而表名分别是t_order_0 到 t_order_3。

4、Hint分片策略

Hint分片策略，对应上边的Hint分片算法，通过指定分片健而非从 SQL中提取分片健的方式进行分片的策略。

分片算法

1、精确分片算法

精确分片算法（PreciseShardingAlgorithm）用于单个字段作为分片键，SQL中有 = 与 IN 等条件的分片，需要在标准分片策略（StandardShardingStrategy ）下使用。

2、范围分片算法

范围分片算法（RangeShardingAlgorithm）用于单个字段作为分片键，SQL中有 BETWEEN AND、>、<、>=、<= 等条件的分片，需要在标准分片策略（StandardShardingStrategy ）下使用。

3、复合分片算法

复合分片算法（ComplexKeysShardingAlgorithm）用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。需要在复合分片策略（ComplexShardingStrategy ）下使用。

4、Hint分片算法

Hint分片算法（HintShardingAlgorithm）稍有不同，上边的算法中都是解析SQL 语句提取分片键，并设置分片策略进行分片。但有些时候并没有使用任何的分片键和分片策略，可还想将 SQL 路由到目标数据库和表，就需要通过手动干预指定SQL的目标数据库和表信息，这也叫强制路由。

参考：分片算法和分片策略


package com.shardingsphere.algorithm;
 
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
 
import java.util.Collection;
 
/**
 * 复合分片算法
 * 用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。需要在复合分片策略下使用
 */
public class DefaultComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
 
        // 多片键的精确值场景
        shardingValue.getColumnNameAndShardingValuesMap();
 
        // 多片键的范围值场景
        shardingValue.getColumnNameAndRangeValuesMap();
 
        // 根据以上两种情况来筛选数据节点： 数据源、物理表...
 
        return availableTargetNames;
    }
}


package com.shardingsphere.algorithm;
 
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
 
import java.util.Collection;
import java.util.LinkedHashSet;
 
/**
 * Hint分片算法
 * 指定的外部路由值来筛选目标库、表,由外部指定分片信息，让 SQL在指定的分库、分表中执行
 */
public class DefaultHintShardingAlgorithm implements HintShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>();
        for (String each : availableTargetNames) {
            for (Long value : shardingValue.getValues()) {
                if (each.endsWith(String.valueOf(value))) {
                    result.add(each);
                }
            }
        }
 
        return result;
    }
}


package com.shardingsphere.algorithm;
 
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
 
import java.util.Collection;
 
/**
 * 精确分片算法
 * 只支持对单个分片键（字段）为依据的分库分表，用于 SQL 含有 = 和 IN 的分片处理
 */
public class DefaultModuloPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String modulo = String.valueOf(shardingValue.getValue() % availableTargetNames.size());
 
        for (String targetName : availableTargetNames) {
            if (targetName.endsWith(modulo)) return targetName;
        }
 
        throw new UnsupportedOperationException();
    }
}


package com.shardingsphere.algorithm;
 
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
 
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.stream.Collectors;
 
/**
 * 范围分片算法
 * 标准分片策略只支持对单个分片键（字段）为依据的分库分表，
 * 范围分片:处理含有 >，>=, <=，<，=，IN 和BETWEEN AND操作符 的分片处理
 */
public class DefaultModuloRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
 
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        Long lower = shardingValue.getValueRange().lowerEndpoint();
        Long upper = shardingValue.getValueRange().upperEndpoint();
 
        Collection<String> result = new LinkedHashSet<>();
        for (Long i = lower; i <= upper; i++) {
            if (result.size() == availableTargetNames.size()) break;
            String modulo = String.valueOf(i % availableTargetNames.size());
            result.addAll(availableTargetNames.stream().filter(m -> m.endsWith(modulo)).collect(Collectors.toList()));
        }
 
        return result;
    }
}

2、src/main/java/com/shardingsphere/config


package com.shardingsphere.config;
 
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.shardingsphere.entity.Entity;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
import java.util.Calendar;
 
@Configuration
@ConditionalOnClass(value = {PaginationInterceptor.class})
public class MyBatisPlusConfig implements MetaObjectHandler {
 
 
    /**
     * 创建时间
     *
     * @param metaObject
     */
    @Override
    public void insertFill(MetaObject metaObject) {
        if (metaObject.getOriginalObject() instanceof Entity) {
            Entity entity = (Entity) metaObject.getOriginalObject();
            if (entity.getCreationTime() == null) {
                this.setFieldValByName(Entity.META_CREATED, Calendar.getInstance().getTime(), metaObject);
            }
        }
    }
 
    /**
     * 更新时间
     *
     * @param metaObject
     */
    @Override
    public void updateFill(MetaObject metaObject) {
        if (metaObject.getOriginalObject() instanceof Entity) {
            Entity entity = (Entity) metaObject.getOriginalObject();
            if (entity.getUpdateTime() == null) {
                this.setFieldValByName(Entity.META_UPDATE, Calendar.getInstance().getTime(), metaObject);
            }
        }
    }
 
    /**
     * 分页
     *
     * @return
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}


package com.shardingsphere.config;
 
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
 
import javax.sql.DataSource;
 
@Configuration
@EnableTransactionManagement
public class TransactionConfig {
    /**
     * 关联 datasource 到 spring 的 PlatformTransactionManager（没有直接使用 jdbc 原生事务）
     */
    @Bean
    public PlatformTransactionManager txManager(@Qualifier("shardingDataSource") final DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
 
    /**
     * 通过 jdbcTemplate 简化原生 sharding-jdbc SQL 的使用
     */
    @Bean
    public JdbcTemplate jdbcTemplate(@Qualifier("shardingDataSource") final DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
 
}

3、src/main/java/com/shardingsphere/controller


package com.shardingsphere.controller;
 
import com.shardingsphere.service.MessageService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
@RestController
@RequestMapping("/user")
public class UserController {
 
    @Autowired
    private MessageService messageService;
 
    @PostMapping("/add")
    public int add() {
        return messageService.add();
    }
 
    @PostMapping("/update")
    public int updateByName() {
        return messageService.updateByName();
    }
 
}

4、src/main/java/com/shardingsphere/entity


package com.shardingsphere.entity;
 
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
 
import java.util.Date;
 
public class Entity<T extends Entity<?>> extends Model<T> {
 
    public final static String META_CREATED = "creationTime";
    public final static String META_UPDATE = "updateTime";
 
    @TableField(fill = FieldFill.INSERT)
    private Date creationTime;
 
    @TableField(fill = FieldFill.UPDATE)
    private Date updateTime;
 
    public Date getCreationTime() {
        return creationTime;
    }
 
    public void setCreationTime(Date creationTime) {
        this.creationTime = creationTime;
    }
 
    public Date getUpdateTime() {
        return updateTime;
    }
 
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
}


package com.shardingsphere.entity;
 
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
 
import java.io.Serializable;
import java.util.Date;
 
@Data
@TableName("t_message")
public class Message implements Serializable {
    private static final long serialVersionUID = 2921554930924807501L;
 
    @TableId(type = IdType.ASSIGN_ID)
    private Long msgId;
 
    private Long contactId;
 
    private Long userId;
 
    private Boolean userTag;
 
    private Date recallTime;
 
    private String content;
 
    private String pushMsg;
 
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date creationTime;
}


package com.shardingsphere.entity;
 
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
 
import java.io.Serializable;
 
@Data
@TableName("t_user")
public class User extends Entity<User> implements Serializable {
 
    private static final long serialVersionUID = -94704944729049233L;
 
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    private Long age;
    private String sex;
    private String education;
 
}

5、src/main/java/com/shardingsphere/Mapper


package com.shardingsphere.Mapper;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingsphere.entity.Message;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
 
@Mapper
@Repository
public interface MessageMapper extends BaseMapper<Message> {
 
 
}


package com.shardingsphere.Mapper;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingsphere.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
 
@Mapper
@Repository
public interface UserMapper  extends BaseMapper<User> {
 
}

6、src/main/java/com/shardingsphere/service


package com.shardingsphere.service;
 
import com.baomidou.mybatisplus.extension.service.IService;
import com.shardingsphere.entity.Message;
 
public interface MessageService extends IService<Message> {
 
    int add();
 
    int updateByName();
}


package com.shardingsphere.service.impl;
 
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.Mapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shardingsphere.Mapper.MessageMapper;
import com.shardingsphere.Mapper.UserMapper;
import com.shardingsphere.entity.Message;
import com.shardingsphere.entity.User;
import com.shardingsphere.service.MessageService;
import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.Date;
 
@Service
public class MessageServiceImpl extends ServiceImpl<MessageMapper, Message> implements MessageService {
 
    @Autowired
    private UserMapper userMapper;
 
    @Autowired
    private MessageMapper messageMapper;
 
    @Transactional(rollbackFor = Exception.class)
    @ShardingTransactionType(TransactionType.XA)
    @Override
    public int add() {
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        Message message = new Message();
        int randomNum = (int) (Math.random() * 9000 + 1000);
        message.setMsgId(snowflake.nextId());
        message.setContactId(snowflake.nextId());
        message.setUserId(new Long(randomNum));
        message.setUserTag(Boolean.TRUE);
        message.setRecallTime(new Date());
        message.setContent("测试测试");
        message.setPushMsg("22225555");
        message.setCreationTime(new Date());
        messageMapper.insert(message);
 
        User user = new User();
        user.setName("李四");
        int randomNum2 = (int) (Math.random() * 9000 + 1000);
        user.setAge(new Long(randomNum2));
        user.setSex("2");
        user.setEducation("1");
        int insert = userMapper.insert(user);
//        if (insert > 0) {
//            //抛出异常 事务回滚
//            int a = 10 / 0;
//            return 1;
//        }
        return 0;
    }
 
    @Transactional(rollbackFor = Exception.class)
    @ShardingTransactionType(TransactionType.XA)
    @Override
    public int updateByName() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.lambda().eq(User::getName,"李四");
        User user = userMapper.selectOne(wrapper);
        user.setName("李四大大");
        int update = userMapper.updateById(user);
        return update;
    }
}

四、数据库表


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint(0) NOT NULL,
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` bigint(0) NULL DEFAULT NULL,
  `sex` int(0) NOT NULL,
  `education` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `creation_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
SET FOREIGN_KEY_CHECKS = 1;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for t_message
-- ----------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message`  (
  `msg_id` bigint(0) NOT NULL,
  `contact_id` bigint(0) NULL DEFAULT NULL,
  `user_id` bigint(0) NULL DEFAULT NULL,
  `user_tag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `recall_time` datetime(0) NULL DEFAULT NULL,
  `content` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `push_msg` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `creation_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`msg_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
SET FOREIGN_KEY_CHECKS = 1;

五、测试

1、postman

2、插入数据库结果

3、更新数据库内容自己测试。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/210936