Sharding JDBC系列实战

最新推荐文章于 2024-05-28 10:10:21 发布

杜莱

最新推荐文章于 2024-05-28 10:10:21 发布

阅读量294

点赞数

分类专栏：数据库文章标签： sharding mysql jdbc

本文链接：https://blog.csdn.net/LaneDu/article/details/119007262

版权

数据库专栏收录该内容

17 篇文章 1 订阅

订阅专栏

理论参考：MySQL高级框架实战理论笔记
GitHub 代码：lane-mysql-42

文章目录

项目创建与初步实现

创建父项目 mysql-example 只是为了管理依赖

创建 module sharding-jdbc-example

SQL 语句

分别创建两个 db 为 lane1 和 lane2 ，分别执行下面的 sql


CREATE TABLE `position` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `salary` varchar(50) DEFAULT NULL,
  `city` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `position_detail` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `pid` bigint(11) NOT NULL DEFAULT '0',
  `description` text DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

引入依赖

父模块

<?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>com.galaxy</groupId>
    <artifactId>mysql-example</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>sharding-jdbc-example</module>
    </modules>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
        <shardingsphere.version>4.1.0</shardingsphere.version>
        <springboot.version>2.2.9.RELEASE</springboot.version>

    </properties>
    <dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${springboot.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
    </dependencies>
    </dependencyManagement>
    <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.1</version>
                    <configuration>
                        <source>11</source>
                        <target>11</target>
                        <testSource>11</testSource>
                        <testTarget>11</testTarget>
                    </configuration>
                </plugin>
            </plugins>
    </build>
</project>

子模块

<?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">
    <parent>
        <artifactId>mysql-example</artifactId>
        <groupId>com.galaxy</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharding-jdbc-example</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>

        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>
    </dependencies>

</project>

配置文件

application.properties

spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true

application-sharding-database.properties

#datasource
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#sharding-database
#分片策略inline 分片键id 表达式id/2=ds0、ds1
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
#id 生成策略
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE

Java 代码

entity

package com.galaxy.entity;

import javax.persistence.*;
import java.io.Serializable;

/**
 * @author lane
 * @date 2021年07月20日 下午7:12
 */

@Entity
@Table(name="position")
public class Position implements Serializable {

    @Id
    @Column(name = "id")
 //使用sharding jdbc指定主键生成，不指定则是数据库默认auto_increment
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "salary")
    private String salary;

    @Column(name = "city")
    private String city;

    public long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

repository

package com.galaxy.repository;

import com.galaxy.entity.Position;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * @author lane
 * @date 2021年07月20日 下午7:36
 */
public interface PositionRepository  extends JpaRepository<Position,Long> {


}

启动类

package com.galaxy;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @author lane
 * @date 2021年07月20日 下午7:31
 */
@SpringBootApplication
public class RunBootApplication {

    /*public static void main(String[] args) {
        SpringApplication.run(RunBootApplication.class,args);
    }*/
}

测试类

package dao;

import com.galaxy.RunBootApplication;
import com.galaxy.entity.Position;
import com.galaxy.repository.PositionRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * @author lane
 * @date 2021年07月20日 下午7:31
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBootApplication.class)
public class TestShardingDatabase {
    @Autowired
    private PositionRepository positionRepository;

    @Test
    public void test0(){
        for (int i=1;i<20;i++){
            Position position =new Position();
         //   position.setId(i);
            position.setCity("beijing");
            position.setName("zhangsan"+i);
            position.setSalary("20000");

            positionRepository.save(position);
        }



    }

}

测试结果

自定义主键生成策略

添加 Java 代码实现类

package com.galaxy.id;
import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;
import java.util.Properties;

/**
 * 仍然选择雪花片算法
 * @author lane
 * @date 2021年07月21日 上午10:41
 */

public class MyLaneId implements ShardingKeyGenerator {

    private  SnowflakeShardingKeyGenerator snow = new SnowflakeShardingKeyGenerator();

    @Override
    public Comparable<?> generateKey() {
        System.out.println("------执行了自定义主键生成器-------");
        return snow.generateKey();
    }

    @Override
    public String getType() {
        return "LANEKEY";
    }

    @Override
    public Properties getProperties() {
        return null;
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

添加实现的 spi 内容

在 resources 下新建 META-INF 文件夹及其子文件夹 service，之后添加文件名称为 org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

内容为 com.galaxy.id.MyLaneId 的文件

添加配置信息

#id
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY

执行结果

绑定表同库生成

对于 position 表和 position_detail 表有绑定关系，我们希望的是 db1 生成的 position 和 positionDetail 实现一一对应关系而不是出现 db1 的 position 和 db2 的 positionDetail 出现对应关系，这样不利于我们进行关联查询，下面是具体实现同库关联表一一绑定生成

Java 代码

positionDetail

package com.galaxy.entity;

import javax.persistence.*;
import java.io.Serializable;

/**
 * @author lane
 * @date 2021年07月21日 上午11:12
 */
@Entity
@Table(name = "position_detail")
public class PositionDetail implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "pid")
    private long pid;

    @Column(name = "description")
    private String description;

    public long getId() {
        return id;
    }

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

    public long getPid() {
        return pid;
    }

    public void setPid(long pid) {
        this.pid = pid;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}

repository

package com.galaxy.repository;

import com.galaxy.entity.PositionDetail;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author lane
 * @date 2021年07月21日 上午11:13
 */
public interface PositionDetailRepository extends JpaRepository<PositionDetail,Long> {
}

package com.galaxy.repository;

import com.galaxy.entity.Position;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * @author lane
 * @date 2021年07月20日 下午7:36
 */
public interface PositionRepository  extends JpaRepository<Position,Long> {

    @Query(nativeQuery = true,value = "" +
            "select p.id,p.name,p.salary,p.city,pd.description from position p join position_detail pd on(p.id=pd.pid) where p.id=:id")
    public Object findPositionsById(@Param("id") long id);

}

配置信息


#sharding-database
#分片策略inline 分片键id 表达式id/2=ds0、ds1
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}

spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{id % 2}

#id
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY

spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE

测试代码

  @Test
    public void testAdd2(){
        for (int i=1;i<=20;i++){
            Position position = new Position();
            position.setName("lisi"+i);
            position.setSalary("1000000");
            position.setCity("shanghai");
            positionRepository.save(position);

            PositionDetail positionDetail = new PositionDetail();
            positionDetail.setPid(position.getId());
            positionDetail.setDescription("this is a message "+i);
            positionDetailRepository.save(positionDetail);
        }
    }

    @Test
    public void testLoad(){
        Object object = positionRepository.findPositionsById(624565543113850880L);
        Object[] position = (Object[])object;
        System.out.println(position[0]+" "+position[1]+" "+position[2]+" "+position[3]+" "+position[4]);
    }

测试结果

广播表实现

广播表是要求每个库里面的表结构和数据完全一样，一般是配置信息或者固定信息，每次操作都对每个库中的表做同样的操作

SQL 建表语句

CREATE TABLE `city` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `province` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Java 代码

city

package com.galaxy.entity;

import javax.persistence.*;
import java.io.Serializable;

/**
 * @author lane
 * @date 2021年07月21日 上午11:37
 */
@Entity
@Table(name = "city")
public class City implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "province")
    private String province;

    public long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }
}

repository

package com.galaxy.repository;

import com.galaxy.entity.City;
import com.galaxy.entity.PositionDetail;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author lane
 * @date 2021年07月21日 上午11:13
 */
public interface CityRepository extends JpaRepository<City,Long> {
}

test

  @Test
    public void testBroadCast(){
        City city = new City();
        city.setName("beijing");
        city.setProvince("beijing");
        cityRepository.save(city);
    }

配置信息

#BoardCast
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

执行结果

分库分表实现

对于公司来说会有一个订单表 BOrder，将其以公司 ID 进行分库，以 order ID 进行分表实现数据均匀存储

SQL 创建

在库 lane1 创建表 b_order0 和表 b_order1

在库 lane2 创建表 b_order0 和表 b_order1


CREATE TABLE `b_order`(
    `id`              bigint(20)   NOT NULL AUTO_INCREMENT,
    `is_del`          bit(1)       NOT NULL DEFAULT 0 COMMENT '是否被删除',
    `company_id`      int(11)      NOT NULL COMMENT '公司ID',
    `position_id`     bigint(11)      NOT NULL COMMENT '职位ID',
    `user_id`         int(11)      NOT NULL COMMENT '用户id',
    `publish_user_id` int(11)      NOT NULL COMMENT '职位发布者id',
    `resume_type`     int(2)       NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
    `status`          varchar(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
    `create_time`     datetime     NOT NULL COMMENT '创建时间',
    `operate_time`    datetime     NOT NULL COMMENT '操作时间',
    `work_year`       varchar(100)          DEFAULT NULL COMMENT '工作年限',
    `name`            varchar(256)          DEFAULT NULL COMMENT '投递简历人名字',
    `position_name`   varchar(256)          DEFAULT NULL COMMENT '职位名称',
    `resume_id`        int(10)               DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
    PRIMARY KEY (`id`),
    KEY `index_createTime` (`create_time`),
    KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
    KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
    KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Java 实现

BOrder

package com.galaxy.entity;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
/**
 * @author lane
 * @date 2021年07月21日 下午12:12
 */

@Entity
//注意是逻辑表名
@Table(name = "b_order")
public class BOrder implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "is_del")
    private Boolean isDel;

    @Column(name = "company_id")
    private Integer companyId;

    @Column(name = "position_id")
    private long positionId;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "publish_user_id")
    private Integer publishUserId;

    @Column(name = "resume_type")
    private Integer resumeType;

    @Column(name = "status")
    private String status;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "operate_time")
    private Date operateTime;

    @Column(name = "work_year")
    private String workYear;

    @Column(name = "name")
    private String name;

    @Column(name = "position_name")
    private String positionName;

    @Column(name = "resume_id")
    private Integer resumeId;

    public long getId() {
        return id;
    }

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

    public Boolean getDel() {
        return isDel;
    }

    public void setDel(Boolean del) {
        isDel = del;
    }

    public Integer getCompanyId() {
        return companyId;
    }

    public void setCompanyId(Integer companyId) {
        this.companyId = companyId;
    }

    public long getPositionId() {
        return positionId;
    }

    public void setPositionId(long positionId) {
        this.positionId = positionId;
    }

    public Integer getUserId() {
        return userId;
    }

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

    public Integer getPublishUserId() {
        return publishUserId;
    }

    public void setPublishUserId(Integer publishUserId) {
        this.publishUserId = publishUserId;
    }

    public Integer getResumeType() {
        return resumeType;
    }

    public void setResumeType(Integer resumeType) {
        this.resumeType = resumeType;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getOperateTime() {
        return operateTime;
    }

    public void setOperateTime(Date operateTime) {
        this.operateTime = operateTime;
    }

    public String getWorkYear() {
        return workYear;
    }

    public void setWorkYear(String workYear) {
        this.workYear = workYear;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPositionName() {
        return positionName;
    }

    public void setPositionName(String positionName) {
        this.positionName = positionName;
    }

    public Integer getResumeId() {
        return resumeId;
    }

    public void setResumeId(Integer resumeId) {
        this.resumeId = resumeId;
    }
}

BOrderRepository

package com.galaxy.repository;

import com.galaxy.entity.BOrder;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author lane
 * @date 2021年07月21日 下午12:12
 */
public interface BOrderRepository extends JpaRepository<BOrder,Long> {
}

配置信息


#sharding-database-table
#spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
#spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
#spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
#spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
#spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
#spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

测试代码

   @Autowired
    BOrderRepository orderRepository;
    @Test
    @Repeat(100)
    public void testShardingBOrder(){
        Random random = new Random();
        int companyId = random.nextInt(100);
        BOrder order = new BOrder();
        order.setDel(false);
        order.setCompanyId(companyId);
        order.setPositionId(3242342);
        order.setUserId(2222);
        order.setPublishUserId(1111);
        order.setResumeType(1);
        order.setStatus("AUTO");
        order.setCreateTime(new Date());
        order.setOperateTime(new Date());
        order.setWorkYear("2");
        order.setName("lg");
        order.setPositionName("Java");
        order.setResumeId(23233);
        orderRepository.save(order);
    }

测试结果

不同数据库按照公司 id 进行分库，可以看到 lane1(ds0)库的公司 ID 全是偶数，lane2(ds1)库的公司 ID 全是奇数

对于同一个库拆分的不同表数据则是按照 ID 进行分片，可以看到下图中 b_order0 的 ID 全是偶数，而 b_order1 的 ID 全是奇数

读写分离实现

正常读写分离一般是一台主库负责数据的写入，两台从库负责数据的读取，足以满足多数情况下了

当然了 shardingJDBC 无法实现数据的同步，仍要自己去配置数据库的同步，可以参考文章{{MySQL 安装及主从异步、半同步、同步实现}}

这次按照 city 表进行操作。lane1 库作为主库写入数据，lane2 库作为从库实现数据的读取

配置信息

application.properties

spring.profiles.active=master-slave
spring.shardingsphere.props.sql.show=true

application-master-slave.properties

#datasource
spring.shardingsphere.datasource.names=master,slave0

spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/lane1
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=root

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/lane2
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root

#master-slave
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN

spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

测试代码

package dao;

import com.galaxy.RunBootApplication;
import com.galaxy.entity.City;
import com.galaxy.repository.CityRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

/**
 * @author lane
 * @date 2021年07月21日 下午3:35
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBootApplication.class)
public class TestMasterSlave {

    @Autowired
   private  CityRepository cityRepository;

    @Test
    public void testAdd(){
        City city = new City();
        city.setName("shanghai1");
        city.setProvince("shanghai1");
        cityRepository.save(city);
    }

    @Test
    public void testFind(){
        List<City> list = cityRepository.findAll();
        list.forEach(city->{
            System.out.println(city.getId()+" "+city.getName()+" "+city.getProvince());
        });
    }

}

测试结果

执行添加之后，只在主库添加了一条数据

执行查询则是只在从库查询到了一条数据，成功实现了读写分离

代码指定路由 Hint 实现

仍然采用 city 表来实现，在代码中指定路由到具体某一个数据库

Java 代码

package com.galaxy.hint;

import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

import java.util.ArrayList;
import java.util.Collection;

/**
 * @author lane
 * @date 2021年07月21日 下午4:50
 */
public class MyHintShardingAlgorithm implements HintShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(
            Collection<String> availableTargetNames,
            HintShardingValue<Long> shardingValue) {
        Collection<String> result = new ArrayList<>();
        //each是数据源ds0和ds1
        for (String each : availableTargetNames){
            for (Long value : shardingValue.getValues()){
                //value是自己传入的值，根据传入的值来匹配数据源
                if(each.endsWith(String.valueOf(value % 2))){
                    result.add(each);
                }
            }
        }
        return result;
    }
}

配置文件

application.properties


spring.profiles.active=hint-database
spring.shardingsphere.props.sql.show=true

application-hint-database.properties

#datasource
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root
#hint
spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.galaxy.hint.MyHintShardingAlgorithm

测试代码

package dao;

import com.galaxy.RunBootApplication;
import com.galaxy.entity.City;
import com.galaxy.repository.CityRepository;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

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

/**
 * @author lane
 * @date 2021年07月21日 下午4:52
 */

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBootApplication.class)
public class TestHintAlgorithm {

    @Resource
    private CityRepository cityRepository;

    @Test
    public void test1(){
        HintManager hintManager = HintManager.getInstance();
        //强制路由到ds${xx%2}既是 ds2%2 = ds0
        hintManager.setDatabaseShardingValue(2L);
        List<City> list = cityRepository.findAll();
        list.forEach(city->{
            System.out.println(city.getId()+" "+city.getName()+" "+city.getProvince());
        });
    }

}

测试结果

查询的是 ds0 也就是 lane1 库里的 City 数据两条

数据脱敏(加解密)实现

通过根据逻辑列在数据库中添加明文列（非必须）和密文列（必须）实现数据加密存储和数据解密查询效果

新建用户表实现密码的脱敏

创建 SQL

CREATE TABLE `c_user` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `pwd_plain` varchar(256) DEFAULT NULL,
  `pwd_cipher` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Java 代码实现

CUser

package com.galaxy.entity;

import javax.persistence.*;
import java.io.Serializable;

/**
 * @author lane
 * @date 2021年07月21日 下午6:33
 */
@Entity
@Table(name = "c_user")
public class CUser implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "pwd")//逻辑列名
    private String pwd;

    public long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
}

Repository

package com.galaxy.repository;

import com.galaxy.entity.CUser;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

/**
 * @author lane
 * @date 2021年07月21日 下午6:35
 */
public interface CUserRepository extends JpaRepository<CUser,Long> {

    List<CUser> findByPwd(String pwd);
}

配置文件

application.properties


spring.profiles.active=encryptor
spring.shardingsphere.props.sql.show=true

application-encryptor.properties

#datasource
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#sharding-database
#分片策略inline 分片键id 表达式id/2=ds0、ds1
spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.algorithm-expression=ds$->{id % 2}

#id
spring.shardingsphere.sharding.tables.c_user.key-generator.column=id
spring.shardingsphere.sharding.tables.c_user.key-generator.type=SNOWFLAKE
#encrypt
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.plain-column=pwd_plain
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.cipher-column=pwd_cipher

spring.shardingsphere.encrypt.encryptors.la_pwd.type=aes
spring.shardingsphere.encrypt.encryptors.la_pwd.props.aes.key.value=1234

spring.shardingsphere.encrypt.tables.c_user.columns.pwd.encryptor=la_pwd
#是否使用密文查询 默认true,false是指明文查询
spring.shardingsphere.props.query.with.cipher.column=true

测试类

package dao;

import com.galaxy.RunBootApplication;
import com.galaxy.entity.CUser;
import com.galaxy.repository.CUserRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Repeat;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Random;

/**
 * @author lane
 * @date 2021年07月21日 下午6:41
 */
@SpringBootTest(classes = RunBootApplication.class)
@RunWith(SpringRunner.class)
public class TestEncryptor {

    @Autowired
    private CUserRepository userRepository;

    @Test
    @Repeat(20)
    public void testAdd(){
        CUser user = new CUser();
       int i =  new Random().nextInt(20);
        user.setName("tiger"+ i);
        user.setPwd("abc"+i);
        userRepository.save(user);
    }

    @Test
    public void testFind(){
        List<CUser> list = userRepository.findByPwd("abc");
        list.forEach(cUser -> {
            System.out.println(cUser.getId()+" "+cUser.getName()+" "+cUser.getPwd());
        });
    }

  
}

测试结果

可以看到存储两列分别是明文密码和密文密码

不过在分库雪花片算法生成主键这方面有问题，依然按照默认的数据库 auto_increment 的生成策略

执行查询语句

实现分布式事务 XA

XA 是强一致性，Saga 是最终一致性，2PC，3PC 都是分布式事务规范，需要具体实现如 Seata

本地实现按照 lane1 position 和 lane2 的 position_detail 来实现

依赖添加

父 pom.xml

  <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>

子 pom.xml 添加

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-transaction-xa-core</artifactId>
        </dependency>

配置修改

application.properties

spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true

application-sharding-database.properties

注意这次指定了分别为 lane1 和 lane2 并没有按 id 分片

#datasource
spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#sharding-database
#分片策略inline 分片键id 表达式id/2=ds0、ds1
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{0}

spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{1}

#id
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY

spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE

#BoardCast
#spring.shardingsphere.sharding.broadcast-tables=city
#spring.shardingsphere.sharding.tables.city.key-generator.column=id
#spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE


#sharding-database-table
#spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
#spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
#spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
#spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
#spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
#spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

代码实现

启动类

package com.galaxy;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * @author lane
 * @date 2021年07月20日 下午7:31
 */
@SpringBootApplication
@EnableTransactionManagement
public class RunBootApplication {

    /*public static void main(String[] args) {
        SpringApplication.run(RunBootApplication.class,args);
    }*/
}

测试类


/**
 * @author lane
 * @date 2021年07月22日 下午4:43
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBootApplication.class)
public class TestShardingTransaction {

    @Resource
    private PositionRepository positionRepository;

    @Resource
    private PositionDetailRepository positionDetailRepository;

    @Test
//    @Transactional
//    @ShardingTransactionType(TransactionType.XA)
    public void test1(){
//        TransactionTypeHolder.set(TransactionType.XA);
//        for (int i=1;i<=5;i++){
        int i=1;
            Position position = new Position();
            position.setName("root"+i);
            position.setSalary("1000000");
            position.setCity("beijing");
            positionRepository.save(position);
            if (i==2){
                throw new RuntimeException("人为制造异常");
            }

            PositionDetail positionDetail = new PositionDetail();
            positionDetail.setPid(position.getId());
            positionDetail.setDescription("this is a root "+i);
            positionDetailRepository.save(positionDetail);

    }
}

正常情况的结果，两个不同的库都添加了一条数据

修改测试代码 i=2 后如下，此时会出现异常，一个库的 position 执行成功，另一个库 position_detail 执行失败



/**
 * @author lane
 * @date 2021年07月22日 下午4:43
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBootApplication.class)
public class TestShardingTransaction {

    @Resource
    private PositionRepository positionRepository;

    @Resource
    private PositionDetailRepository positionDetailRepository;

    @Test
    @Transactional
    //@ShardingTransactionType(TransactionType.XA)
    public void test1(){
   //      TransactionTypeHolder.set(TransactionType.XA);
  //        for (int i=1;i<=5;i++){
        int i=2;
            Position position = new Position();
            position.setName("root"+i);
            position.setSalary("1000000");
            position.setCity("beijing");
            positionRepository.save(position);
            if (i==2){
                throw new RuntimeException("人为制造异常");
            }

            PositionDetail positionDetail = new PositionDetail();
            positionDetail.setPid(position.getId());
            positionDetail.setDescription("this is a root "+i);
            positionDetailRepository.save(positionDetail);

    }
}

添加异常后的结果

删除刚才的数据，再次修改添加如下代码

 TransactionTypeHolder.set(TransactionType.XA);

再次测试，可以看到成功回滚了

实际上搞得我有点尴尬 😅，因为理论上来说，添加注解也可以实现异常回滚的，然而事实上并没有，只有在代码中添加才成功实现了异常回滚。

杜莱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Sharding JDBC系列实战

项目创建与初步实现创建父项目 mysql-example 只是为了管理依赖创建 module sharding-jdbc-exampleSQL 语句分别创建两个 db 为 lane1 和 lane2 ，分别执行下面的 sqlCREATE TABLE `position` ( `Id` bigint(11) NOT NULL AUTO_INCREMENT, `name` varchar(256) DEFAULT NULL, `salary` varchar(50) DEFAULT NU
复制链接

扫一扫