今天继续完成测试用例的相关功能，因为我们这个平台内容比较多，一个测试用例可能关联的属性也比较多，具不完全统计大概有：环境、项目、服务、模块、接口、用例级别、创建人和编辑人等。所以这块功能在写的时候都需要关联很多表去查询或者写入数据。不做不知道，原来自己之前掌握的MySQL语句的知识，太浅薄了。

今天主要用到了MySQL语法case when then else end、FIND_IN_SET函数和多种非必选条件和判断语句的练习（这个其实就是复杂一些）。中间还发现了mybatis其实替用户做了一些兼容性的处理，比如我在拼WHERE条件语句的时候，每个都是and condition，本来还担心要不要去掉第一个and，经过咨询前辈才知道，这块不要直接拼WHERE语句，可以用​​<where></where>​​这个​​mybatis​​语法即可完成，非常友好。今天学到的第二个就是异步查询转同步，在之前的文章已经讲过了，今天算是又实践了一下，写实际的业务跟练习区别还是挺大。

MySQL语法case when then else end实践

需求大概就是从很多非必传参数里面搜索库里面的用例，这些条件包括：环境、项目、服务、模块、接口等等，如下图：

我的开发日记（六）_mysql然后接口传参都是​​​int​​​和​​string​​​类型，​​0​​表示全部，各个选项状态不一。在处理搜索框的搜索类型时候，我提前做了判断，如果内容是个数字，那么我就认为是搜索ID，否则我认为是搜索用例名（模糊匹配），因此我引入了另外一个type作为记录。接收参数的类代码如下：

class CaseSearchBean extends AbstractBean {

    private static final long serialVersionUID = -869483426556;

    int id

    @Min(value = 1L)
    int uid

    int envId

    int apiId

    @Range(min = 0L, max = 2L, message = "是否本人参数错误")
    int isMyself

    @Range(min = 0L, max = 2L, message = "是否可用参数错误")
    int isUsed

    int serviceId

    int moduleId

    @Min(value = 1L)
    int pageNum

    @Range(min = 5L, max = 10L)
    int pageSize

    int projectId

    String testQuery

    /**
     * 处理搜索类型,1:用例名模糊搜索,2:用例id精确搜索
     */
    int type


    def init() {
        if (StringUtils.isEmpty(testQuery)) return
        if (SourceCode.isNumber(testQuery)) type = 2
        else type = 1
    }


}

由于业务不多，中间的​​service​​和​​mapper接口​​就不放了。

mybatis配置 

 <select id="searchCases" parameterType="com.okay.family.common.bean.testcase.request.CaseSearchBean"
            resultType="com.okay.family.common.bean.testcase.response.TestCaseListBean">
        select t.id id,CASE ISNULL(u.nick_name) WHEN 1 THEN "匿名用户" ELSE u.nick_name END user,t.name name,e.name envName,api.name apiName, t.uid = #{uid} isMyself,CASE
        t.available
        WHEN 1 THEN 1 ELSE 0 END isUsed from
        <include refid="table"/>
        t LEFT JOIN
        <include refid="case_available"/>
        a ON t.available = a.id
        LEFT JOIN
        <include refid="user_table"/>
        u on t.uid = u.id
        LEFT JOIN
        <include refid="api_info"/>
        api on t.apiId = api.id
        LEFT JOIN
        <include refid="env"/>
        e on t.envId = e.id
        <where>
            <if test="envId != 0">
                and envId = #{envId}
            </if>
            <if test="serviceId != 0">
                and serviceId = #{serviceId}
            </if>
            <if test="apiId != 0">
                and apiId = #{apiId}
            </if>
            <if test="isUsed != 0">
                and available = #{isUsed}
            </if>
            <if test="moduleId != 0">
                and moduleId = #{moduleId}
            </if>
            <if test="projectId != 0">
                AND FIND_IN_SET(#{projectId},projectList)
            </if>
            <choose>
                <when test="type == 1">
                    and t.name like concat("%",#{testQuery},"%")
                </when>
                <when test="type == 2">
                   and t.id = #{testQuery}
                </when>
            </choose>
            <choose>
                <when test="isUsed == 1">
                    and uid = #{uid}
                </when>
                <when test="isUsed == 2">
                    and uid != #{uid}
                </when>
            </choose>
        </where>
        order by t.create_time

    </select>

异步查询转同步

相关文章和测试方案请参考:

这个地方主要是因为用例关联的项目和版本比较多，返回格式要求是​​array<object>​​​格式的​​id+name​​形式对象，所以单独拿出来查了。业务实现类的相关代码如下：

@Override
    public TestCaseAttributeBean getAttributeById(int id) {
        TestCaseAttributeBean bean = new TestCaseAttributeBean();
        bean.setId(id);
        CountDownLatch countDownLatch = new CountDownLatch(2);
        getAttributeById(bean, countDownLatch);
        getCaseProjectRelation(bean, countDownLatch);
        try {
            countDownLatch.await(10, TimeUnit.SECONDS);
        } catch (InterruptedException e) {
            CaseException.fail("查询用例信息发生错误:" + e.getMessage());
        }
        return bean;
    }

    @Async
    @Override
    public void getAttributeById(TestCaseAttributeBean bean, CountDownLatch countDownLatch) {
        try {
            TestCaseAttributeBean attributeById = testCaseMapper.getAttributeById(bean.getId());
            bean.copyFrom(attributeById);
        } finally {
            countDownLatch.countDown();
        }
    }

    @Async
    @Override
    public void getCaseProjectRelation(TestCaseAttributeBean bean, CountDownLatch countDownLatch) {
        try {
            bean.setVersionList(testCaseMapper.getCaseProjectRelation(bean.getId()));
        } finally {
            countDownLatch.countDown();
        }
    }

bean代码

本来打算用AbstractBean类的​​copyFrom()​​方法的，发现不太好，只能重写了一个。

class TestCaseAttributeBean extends AbstractBean {

    private static final long serialVersionUID = -1595563071153477L;

    int id

    int apiId

    String apiName

    int envId

    String envName


    int moduleId

    String moduleName
    String name

    int serviceId

    String serviceName

    List<ProjectBean> versionList

/**
 * 用于异常查询结果赋值
 * @param bean
 */
    @Override
    def copyFrom(TestCaseAttributeBean bean) {
        this.id = bean.getId()
        this.apiName = bean.getApiName()
        this.envId = bean.getEnvId()
        this.envName = bean.getEnvName()
        this.moduleId = bean.getModuleId()
        this.moduleName = bean.getModuleName()
        this.name = bean.getName()
        this.serviceId = bean.getServiceId()
        this.serviceName = bean.getServiceName()
    }

}

当然还需要在启动类的增加注解，开启异步任务功能。

@EnableScheduling
@MapperScan(value = "com.okay.family.mapper")
@ServletComponentScan
@EnableAsync
@EnableTransactionManagement
@SpringBootApplication(exclude = {MongoAutoConfiguration.class, MongoDataAutoConfiguration.class})
public class FamilyApplication {

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


}

我的开发日记（六）_ide_02