Spring boot integrates Apache shardingsphere 5.0 0 first acquaintance

Posted by 938660 on Tue, 14 Dec 2021 10:41:06 +0100

Spring boot integrates Apache shardingsphere 5.0 0 first acquaintance

1, Foreword

Official documents

Apache ShardingSphere is an ecosystem composed of a set of open source distributed database solutions. It consists of JDBC Proxy and Sidecar (under planning) are composed of products that can be deployed independently and support mixed deployment. They all provide standardized data level expansion, distributed transaction, distributed governance and other functions, and can be applied to various application scenarios such as Java isomorphism, heterogeneous language, cloud primitives and so on.


Apache ShardingSphere aims to make full and reasonable use of the computing and storage capacity of relational database in distributed scenarios, rather than realizing a new relational database. Relational database still occupies a huge market share today and is the cornerstone of the enterprise's core system. It is difficult to shake in the future. We pay more attention to providing increment on the original basis rather than subversion.


Apache ShardingSphere 5. Version x began to focus on pluggable architecture, and the functional components of the project can be flexibly expanded in a pluggable manner. At present, the functions of data fragmentation, read-write separation, data encryption, shadow database pressure test, and the support of SQL and protocols such as MySQL, PostgreSQL, SQLServer and Oracle are woven into the project through plug-ins. Developers can customize their own unique systems like building blocks. Apache shardingsphere has provided dozens of SPI s as the extension points of the system, which is still increasing.


ShardingSphere became the top project of Apache Software Foundation on April 16, 2020.

2, Integration with spring boot (sub database and sub table)

1. CREATE TABLE statement

CREATE TABLE `t_order0` (
                            `order_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order1` (
                            `order_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order2` (
                            `order_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `user_id` varchar(36) COLLATE utf8_bin NOT NULL,
                            `order_name` varchar(100) COLLATE utf8_bin NOT NULL,
                            PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.pom.xml

    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.4.1</mybatis-plus.version>
        <shardingsphere.version>5.0.0</shardingsphere.version>
    </properties>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
  • Pit avoidance Guide

It is not recommended to directly introduce Druid spring boot starter to avoid that the project cannot start normally due to the loading sequence of data sources.
org.apache.shardingsphere.spring.boot.ShardingSphereAutoConfiguration
com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

3.application.yml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true
        password: 123456
        type: com.zaxxer.hikari.HikariDataSource
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true
        password: 123456
        type: com.zaxxer.hikari.HikariDataSource
        username: root
      names: ds0,ds1
    rules:
      sharding:
        sharding-algorithms:
          custom-db-inline:
            props:
              algorithm-expression: ds$->{Math.abs(user_id.hashCode()%2)}
            type: INLINE
          custom-table-inline:
            props:
              algorithm-expression: t_order$->{Math.abs(order_id.hashCode()%3)}
            type: INLINE
        tables:
          t_order:
            actual-data-nodes: ds$->{0..1}.t_order$->{0..2}
            database-strategy:
              standard:
                sharding-algorithm-name: custom-db-inline
                sharding-column: user_id
            table-strategy:
              standard:
                sharding-algorithm-name: custom-table-inline
                sharding-column: order_id
mybatis-plus:
  global-config:
    db-config:
      id-type: assign_id
  • Pit avoidance Guide

Sharing algorithm name prohibits underscores_ (dashes are recommended). The reason is org.springframework.boot.context.properties.source.ConfigurationPropertyName#elementsOf(java.lang.CharSequence, boolean, int). Don't worry about it Loose binding Yes, all with dashes. - come on.


java.lang.Math.abs(int) when the parameter is Integer#MIN_VALUE, return Integer#MIN_VALUE is a negative value. Therefore, t_ Change order $- > {math. ABS (order_id.hashcode())% 3} to t_ order$->{Math.abs(order_id.hashCode()%3)}

4.CURD

  • OrderController.java
package com.example.demo.controller;

import com.example.demo.entity.Order;
import com.example.demo.service.OrderService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author ouruyi
 */
@RestController
@RequestMapping("/order")
public class OrderController {

    @Resource
    private OrderService orderService;

    @GetMapping("/{id}")
    public Order getById(@PathVariable String id) {
        return this.orderService.getById(id);
    }

    @GetMapping("")
    public List<Order> list() {
        return this.orderService.list();
    }

    @GetMapping("/count")
    public Integer count() {
        return this.orderService.count();
    }

    @GetMapping("/mock")
    public String mock() {
        for (int i = 0; i < 12; i++) {
            final Order order = new Order();
            final String index = String.valueOf(i + 1);
            order.setUserId(index);
            order.setOrderName(index);
            this.orderService.save(order);
        }
        return "SUCCESS";
    }

}

  • OrderService.java
package com.example.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.entity.Order;

/**
 * (Order)Table service interface
 *
 * @author 
 * @since 2021-12-14 10:53:05
 */
public interface OrderService extends IService<Order> {

}


  • OrderServiceImpl.java
package com.example.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.dao.OrderDao;
import com.example.demo.entity.Order;
import com.example.demo.service.OrderService;
import org.springframework.stereotype.Service;

/**
 * (Order)Table service implementation class
 *
 * @author 
 * @since 2021-12-14 10:53:06
 */
@Service("orderService")
public class OrderServiceImpl extends ServiceImpl<OrderDao, Order> implements OrderService {

}


  • OrderDao.java
package com.example.demo.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Order;

/**
 * (Order)Table database access layer
 *
 * @author 
 * @since 2021-12-14 10:53:00
 */
public interface OrderDao extends BaseMapper<Order> {

}


  • Order.java
package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import java.io.Serializable;

/**
 * (Order)Table entity class
 *
 * @author 
 * @since 2021-12-14 10:53:04
 */
@SuppressWarnings("serial")
@TableName("t_order")
public class Order extends Model<Order> {

    @TableId
    private String orderId;
    
    private String userId;
    
    private String orderName;


    public String getOrderId() {
        return orderId;
    }

    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    /**
     * Get primary key value
     *
     * @return Primary key value
     */
    @Override
    protected Serializable pkVal() {
        return this.orderId;
    }
    }


reference resources:
Rule configuration

Topics: Java shardingsphere