sharding-jdbc测试

sharding-jdbc在目前微服务和各种saas软件等数据量日益加大的情况下变得使用的人多了起来。

sharding-jdbc读写分离测试

数据库实体

@Data
@Table(name = "city")
@Entity
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;
}

Jpa操作类

public interface CityRepository extends JpaRepository<City,Long> {
}

SpringBoot启动类

@SpringBootApplication
@EnableTransactionManagement
public class RunBoot {
    public static void main(String[] args) {
        SpringApplication.run(RunBoot.class,args);
    }
}

具体读写分离配置

spring.shardingsphere.props.sql.show=true
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/test1
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=351848327


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/test2
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=351848327

#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

测试类和方法

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestMasterSlave {
    @Resource
    CityRepository cityRepository;
    @Test
    public  void testMasterSlave(){
        City city = new City();
        city.setName("shanghai");
        city.setProvince("shanghai");
        cityRepository.save(city);
    }

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

分别执行两个方法可以看到日志,实际操作写的时候是在master库,操作读的时候是在slave库

sharding-jdbc读写分离强制某库执行操作

这种方法主要是针对那些数据量大,数据无法及时同步到从库,可以直接从主库读取 具体配置

spring.shardingsphere.props.sql.show=true
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/test1
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=351848327


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/test2
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=351848327

spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.zhao.hint.MyHint

具体Hint的规则

public class MyHint implements HintShardingAlgorithm<Long> {


    @Override
    public Collection<String> doSharding(Collection<String> targetNames, HintShardingValue<Long> hintShardingValue) {
        Collection<String> results = new ArrayList<>();
        for (String each :targetNames){
            for (Long value:hintShardingValue.getValues()){
                    if (each.endsWith(String.valueOf(value%2))){
                        results.add(each);
                    }
            }
        }
        return results;
    }
}

Hint测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestHint {
    @Resource
    CityRepository cityRepository;
    @Test
    public void testHint(){
        HintManager hintManager =HintManager.getInstance();
        hintManager.setDatabaseShardingValue(0L);
        List<City> list = cityRepository.findAll();
        System.out.println("主库表大小"+list.size());
        hintManager.setDatabaseShardingValue(1L);
        List<City> list1 = cityRepository.findAll();
        System.out.println("从库表大小"+list1.size());
    }
}

执行后可以看到两次查询分别从主库和从库中获取了数据

sharding-jdbc分库分表

分库分表中要处理的包括id生成,分表列,广播表,字表跟随父表进行分表等配置 测试试题类

@Data
@Table(name = "position")
@Entity
@ToString
public class Position implements Serializable {
   @Id
   @Column(name = "Id")
   @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;
}


@Data
@Table(name = "position_detail")
@Entity
public class PositionDetail implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private  Long id;
    @Column(name = "pid")
    private  Long pid;
    @Column(name = "description")
    private  String description;
}

@Data
@Table(name = "b_order")
@Entity
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;
}
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/test1
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=351848327


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://rlocalhost:3306/test2
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=351848327


#sharding
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.key-generator.column=id
#spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.position.key-generator.type=zhao-sharding-key

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

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

spring.shardingsphere.sharding.tables.position_detail.key-generator.type=zhao-sharding-key


#broadcast
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

自定义id生成器

@Slf4j
public class MyShardingId implements ShardingKeyGenerator {
    private SnowflakeShardingKeyGenerator shardingKeyGenerator = new SnowflakeShardingKeyGenerator();
    @Override
    public Comparable<?> generateKey() {
        log.info("执行了自定义的id生成器");

        return shardingKeyGenerator.generateKey();
    }

    @Override
    public String getType() {
        return "zhao-sharding-key";
    }

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

    @Override
    public void setProperties(Properties properties) {

    }
}

执行的测试方法

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestingShardingDatabase {
    @Resource
    PositionRepository positionRepository;
    @Resource
    PositionDetailRepository positionDetailRepository;
    @Resource
    CityRepository cityRepository;
    @Resource
    BorderRepository borderRepository;

    /**
     * 测试是否分库
     */
    @Test
    public void testAdd(){
        for (long i=1;i<=20;i++){
            Position position = new Position();
            //position.setId(i);
            position.setCity("shanghai");
            position.setName("zhao");
            position.setSalary("100.86");
            positionRepository.save(position);
        }
    }

    /**
     * 分库时主表和字表配置的规则一样,所以这里测试的是主表和字表在同库对应
     */
    @Test
    public void testAddDetail(){
        for (long i=1;i<=20;i++){
            Position position = new Position();
            //position.setId(i);
            position.setCity("shanghai");
            position.setName("zhao");
            position.setSalary("100.86");
            positionRepository.save(position);
            PositionDetail detail = new PositionDetail();
            detail.setPid(position.getId());
            detail.setDescription("This is message "+i);
            positionDetailRepository.save(detail);
        }
    }

    /**
     * 测试加载数据分库时走的那个库
     */
    @Test
    public void testLoadData(){
        Object object = positionRepository.findPositionLoadById(607972934107004929L);
        Object[] objects = (Object[]) object;
        System.out.println(objects[0]+" "+objects[1]);
    }

    /**
     * 广播表会写入多个库
     */
    @Test
    public void testBroadCast(){
        City city = new City();
        city.setName("shanghai");
        city.setProvince("shanghai");
        cityRepository.save(city);
    }

    /**
     * 测试分表时如何插入
     */
    @Test
    @Repeat(100)
    public void testShardingBorder(){
        Random random = new Random();
        int companyId = random.nextInt(10);
        Border border = new Border();
        border.setIsDel(false);
        border.setCompanyId(companyId);
        border.setCreateTime(new Date());
        border.setName("zhao");
        border.setWorkYear("2");
        borderRepository.save(border);


    }
}

以上操作依赖的pom文件为

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.2.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.2.5.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.2.5.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.0</version>
        </dependency>
        <!--<dependency>-->
            <!--<groupId>org.apache.shardingsphere</groupId>-->
            <!--<artifactId>sharding-transaction-xa-core</artifactId>-->
            <!--<version>4.1.0</version>-->
        <!--</dependency>-->

        <!--<dependency>-->
            <!--<groupId>org.apache.shardingsphere</groupId>-->
            <!--<artifactId>sharding-transaction-base-saga</artifactId>-->
        <!--</dependency>-->
    </dependencies>

以上就是sharding-jdbc的一些基础使用的介绍。本文github地址https://github.com/zhendiao/deme-code

文章分享自微信公众号:
微瞰技术

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:赵镇
原始发表时间:2021-06-15
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • Sharding-JDBC往事

    2015年夏天我们在北京静安中心12层当当架构部启动自研数据库中间件项目的时候,完全没想过3年多之后,这个项目会成为首个加入Apache基金会的分布式数据库中间...

    用户5829009
  • Sharding-JDBC教程:Spring Boot整合Sharding-JDBC实现读写分离

    个人博客纯净版:https://www.fangzhipeng.com/db/2019/06/26/shardingjdbc-master-slave.html

    方志朋
  • Sharding-JDBC的实践

    数据分片是指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或者表中以达到提升性能瓶颈以及可用性的效果。数据分片有效手段是对关系型数据库进行分库和分...

    码农飞哥
  • JeecgBoot集成Sharding-JDBC

    本次测试基于JeecgBoot 2.4.6,测试代码在Jeecg-boot-module-system中编写。

    JEECG
  • 聊聊sharding-jdbc的ShardingMasterSlaveRouter

    本文主要研究一下sharding-jdbc的ShardingMasterSlaveRouter

    code4it
  • 聊聊sharding-jdbc的AbstractDataSourceAdapter

    本文主要研究一下sharding-jdbc的AbstractDataSourceAdapter

    code4it
  • 聊聊sharding-jdbc的SingleXADataSource

    incubator-shardingsphere-4.0.0-RC1/sharding-transaction/sharding-transaction-2pc...

    code4it
  • 聊聊sharding-jdbc的RootInvokeHook

    incubator-shardingsphere-4.0.0-RC1/sharding-core/sharding-core-execute/src/main/...

    code4it
  • 聊聊sharding-jdbc的SQLExecutionHook

    incubator-shardingsphere-4.0.0-RC1/sharding-core/sharding-core-execute/src/main/...

    code4it
  • SpringBoot+Mybatis-Plus整合Sharding-JDBC实现单库分表及其相关测试

    小编最近一直在研究关于分库分表的东西,前几天docker安装了mycat实现了分库分表,但是都在说mycat的bug很多。很多人还是倾向于shardingsph...

    掉发的小王
  • 聊聊sharding-jdbc的WrapperAdapter

    jdk-12.jdk/Contents/Home/lib/src.zip!/java.sql/java/sql/Wrapper.java

    code4it
  • 聊聊sharding-jdbc的SingleXADataSource

    incubator-shardingsphere-4.0.0-RC1/sharding-transaction/sharding-transaction-2pc...

    code4it
  • 聊聊sharding-jdbc的XATransactionManager

    本文主要研究一下sharding-jdbc的XATransactionManager

    code4it
  • 聊聊sharding-jdbc的ShardingTransactionManager

    本文主要研究一下sharding-jdbc的ShardingTransactionManager

    code4it
  • 聊聊sharding-jdbc的ShardingMasterSlaveRouter

    本文主要研究一下sharding-jdbc的ShardingMasterSlaveRouter

    code4it
  • 聊聊sharding-jdbc的ShardingTracer

    incubator-shardingsphere-4.0.0-RC1/sharding-opentracing/src/main/java/org/apache...

    code4it
  • 聊聊sharding-jdbc的AbstractDataSourceAdapter

    本文主要研究一下sharding-jdbc的AbstractDataSourceAdapter

    code4it
  • 聊聊sharding-jdbc的MasterSlaveRouter

    incubator-shardingsphere-4.0.0-RC1/sharding-core/sharding-core-route/src/main/ja...

    code4it
  • 聊聊sharding-jdbc的XAConnectionWrapper

    本文主要研究一下sharding-jdbc的XAConnectionWrapper

    code4it

扫码关注腾讯云开发者

领取腾讯云代金券