MariaDB数据库中测试数据的生成

现在的项目需求是测试MariaDB的单表的复杂SQL的查询性能, 所以建表, 测试性能:

新建一个springboot项目, 添加依赖:

<?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.4.12</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.talkdesk</groupId>
	<artifactId>mariadb-generate-date</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>mariadb-generate-date</name>
	<description>mariadb-generate-date</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!--<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.4</version>
		</dependency>-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.3.4</version>
		</dependency>

		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.apifan.common</groupId>
			<artifactId>common-random</artifactId>
			<version>1.0.9</version>
		</dependency>
		<dependency>
			<groupId>com.github.jsonzou</groupId>
			<artifactId>jmockdata</artifactId>
			<version>4.3.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

2.添加CaseEvents的实体类:

package com.talkdesk.entity;

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

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * @description case_events
 * @author zhengkai.blog.csdn.net
 * @date 2021-10-25
 */
@Builder
@Data
@TableName("case_events")
public class CaseEvents implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    /**
    * id
    */
    private String id;

    /**
    * event_type
    */
    private String eventType;

    /**
    * case_id
    */
    private String caseId;

    /**
    * category
    */
    private String category;

    /**
    * sub_category
    */
    private String subCategory;

    /**
    * form_id
    */
    private String formId;

    /**
    * old_form_id
    */
    private String oldFormId;

    /**
    * owner_id
    */
    private String ownerId;

    /**
    * old_owner_id
    */
    private String oldOwnerId;

    /**
    * owner_name
    */
    private String ownerName;

    /**
    * old_owner_name
    */
    private String oldOwnerName;

    /**
    * channel_source
    */
    private String channelSource;

    /**
    * tenant_id
    */
    private String tenantId;

    /**
    * brand_id
    */
    private String brandId;

    /**
    * group_id
    */
    private String groupId;

    /**
    * old_group_id
    */
    private String oldGroupId;

    /**
    * group_name
    */
    private String groupName;

    /**
    * old_group_name
    */
    private String oldGroupName;

    /**
    * status
    */
    private String status;

    /**
    * old_status
    */
    private String oldStatus;

    /**
    * priority
    */
    private String priority;

    /**
    * old_priority
    */
    private String oldPriority;

    /**
    * requester_id
    */
    private String requesterId;

    /**
    * requester_name
    */
    private String requesterName;

    /**
    * submitter_id
    */
    private String submitterId;

    /**
    * submitter_name
    */
    private String submitterName;

    /**
    * create_at
    */
    private LocalDateTime createAt;

    /**
    * occurred_at
    */
    private LocalDateTime occurredAt;

    /**
    * mask
    */
    private String mask;

    /**
    * solve_reason
    */
    private String solveReason;

    /**
    * close_reason
    */
    private String closeReason;

    /**
    * assign_reason
    */
    private String assignReason;

    /**
    * satisfaction
    */
    private String satisfaction;


}

3.创建生成数据的service:

package com.talkdesk.service;

import com.apifan.common.random.source.DateTimeSource;
import com.apifan.common.random.source.NumberSource;
import com.talkdesk.config.Constant;
import com.talkdesk.entity.CaseEvents;
import com.talkdesk.mapper.CaseEventsMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author ke.zhang
 * @version 1.0
 * @description: TODO
 * @date 2021/10/25 10:06
 */
@Service
public class EventService {

    @Autowired
    private CaseEventsMapper caseEventsMapper;

    public void generateEventData(int num) {
        if (num < 1) {
            return;
        }
        Constant.tenantIds().parallelStream().forEach(e -> oneTenantSaveData(num, e));
    }

    private void oneTenantSaveData(int num, String tenantId) {
        List<CaseEvents> events = new ArrayList<>();
        for (int i = 0; i < num; i++) {
            CaseEvents caseEvents = getEventObject(tenantId);
            events.add(caseEvents);
            if (events.size() > Constant.NUM) {
                caseEventsMapper.insertBatch(events);
                events.clear();
            }
        }
        if (events.size() > 0) {
            caseEventsMapper.insertBatch(events);
        }
    }


    private CaseEvents getEventObject(String tenantId) {
        String id1 = UUID.randomUUID().toString().replace("-", "");
        String id2 = UUID.randomUUID().toString().replace("-", "");
        CaseEvents build = CaseEvents.builder()
                .id(id1)
                .caseId(id1.substring(0, 19))
                .eventType("case_created")
                .category("00" + NumberSource.getInstance().randomInt(1, 4))
                .subCategory("00" + NumberSource.getInstance().randomInt(1, 4))
                .formId(id1.substring(0, 19))
                .oldFormId(id1.substring(0, 19))
                .ownerId(id2)
                .oldOwnerId(id2)
                .ownerName("")
                .oldOwnerName("")
                .channelSource("00" + NumberSource.getInstance().randomInt(1, 3))
                .tenantId(tenantId)
                .brandId("0")
                .groupId("management-2")
                .oldGroupId("management-2")
                .groupName("management-2")
                .oldGroupName("management-2")
                .status("00" + NumberSource.getInstance().randomInt(1, 7))
                .oldStatus("00" + NumberSource.getInstance().randomInt(1, 7))
                .priority("00" + NumberSource.getInstance().randomInt(1, 5))
                .oldPriority("00" + NumberSource.getInstance().randomInt(1, 5))
                .requesterId("001")
                .requesterName("001")
                .submitterId("002")
                .submitterName("002")
                .createAt(DateTimeSource.getInstance().randomPastTime(30))
                .occurredAt(DateTimeSource.getInstance().randomPastTime(30))
                .mask("001")
                .solveReason("001")
                .closeReason("001")
                .assignReason("001")
                .satisfaction("001")
                .build();
        return build;
    }


}

4.创建Mapper

package com.talkdesk.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.talkdesk.entity.CaseEvents;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CaseEventsMapper extends BaseMapper<CaseEvents> {


    void insertBatch(@Param("events") List<CaseEvents> events);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.talkdesk.mapper.CaseEventsMapper">

    <insert id="insertBatch">
    INSERT INTO case_events values
        <foreach collection="events" item="event" separator=",">
            (#{event.id},#{event.eventType},#{event.caseId},#{event.category},#{event.subCategory},#{event.formId},#{event.oldFormId},#{event.ownerId},#{event.oldOwnerId},
            #{event.ownerName},#{event.oldOwnerName},#{event.channelSource},#{event.tenantId},#{event.brandId},#{event.groupId},#{event.oldGroupId},#{event.groupName},#{event.oldGroupName},
            #{event.status},#{event.oldStatus},#{event.priority},#{event.oldPriority},#{event.requesterId},#{event.requesterName},#{event.submitterId},#{event.submitterName},#{event.createAt},
            #{event.occurredAt},#{event.mask},#{event.solveReason},#{event.closeReason},#{event.assignReason},#{event.satisfaction})
        </foreach>
    </insert>


</mapper>

5.设置常量:

package com.talkdesk.config;

import java.util.ArrayList;
import java.util.List;

/**
 * @author ke.zhang
 * @version 1.0
 * @description: TODO
 * @date 2021/10/27 16:22
 */
public class Constant {

    private static List<String> tenantIds = new ArrayList<>();


    public static final int NUM = 1500;

    public static List<String> tenantIds() {
        if (tenantIds.size() < 1) {
            for (int i = 0; i < 100; i++) {
                tenantIds.add("60f797a5b1a6b0e450dbe" + i);
            }
        }
        return tenantIds;
    }

}

写一个切面, 记录方法的执行时间

package com.talkdesk.config;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * @author ke.zhang
 * @version 1.0
 * @date 2021/10/25 16:44
 */
@Slf4j
@Aspect
@Component
public class ExecuteTimeAspect {


    /**
     * Pointcut 切入点
     * 匹配cn.controller包下面的所有方法
     */
    @Pointcut("execution(public * com.talkdesk.service.*.*(..))")
    public void execute() {
    }

    /**
     * 环绕通知
     */
    @Around(value = "execute()")
    public Object arround(ProceedingJoinPoint point) {
        long l1 = System.currentTimeMillis();
        Object o = null;
        try {
            o = point.proceed();
        } catch (Throwable e) {
            e.printStackTrace();
        }
        long l2 = System.currentTimeMillis();
        log.info("执行{}方法,所用的时间是:{}秒", point.getSignature().toString(), (l2 - l1) / 1000);
        return o;
    }

}

6.配置数据库信息:

spring:
  datasource:
    driver-class-name: org.mariadb.jdbc.Driver
    url: jdbc:mariadb://localhost:3306/test
    username: root
    password: 123456
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

和项目启动方法:

package com.talkdesk;

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

@SpringBootApplication
@MapperScan("com.talkdesk.mapper")
public class MariadbGenerateDateApplication {

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

}

7.写一个单元测试方法

package com.talkdesk.service;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

/**
 * @author ke.zhang
 * @version 1.0
 * @description:
 * @date 2021/10/25 13:44
 */
@SpringBootTest
class EventServiceTest {

    @Autowired
    EventService eventService;

    @Test
    void generateEventData() {
        eventService.generateEventData(50000);
    }
    
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值