Mastering MySQL CASE WHEN and MyBatis Dynamic Queries for Test Case Search

When implementing a test‑case search feature that must filter by many optional parameters such as environment, project, service, module, and interface, the author discovered that basic MySQL knowledge was insufficient and turned to advanced constructs.

MySQL CASE WHEN and FIND_IN_SET

The query relies on the CASE WHEN THEN ELSE END expression to translate nullable fields and on FIND_IN_SET to handle comma‑separated project IDs. These functions enable flexible conditional logic without writing separate SQL statements for each combination of parameters.

MyBatis Dynamic SQL with <where>

MyBatis provides a convenient way to assemble the WHERE clause dynamically. By wrapping conditions inside <where> and using <if test="...">, the framework automatically removes the leading AND and only includes predicates whose corresponding input values are non‑zero.

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

Asynchronous to Synchronous Conversion in Spring

To reduce latency when fetching test‑case attributes and related project information, the author uses two @Async methods that populate a shared TestCaseAttributeBean. A CountDownLatch with a timeout of 10 seconds synchronizes the results before returning the bean.

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

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

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

Supporting Bean and Application Configuration

The CaseSearchBean captures all possible search criteria, while TestCaseAttributeBean stores the detailed result. The Spring Boot application class must enable async processing, transaction management, and mapper scanning.

@EnableScheduling
@MapperScan("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);
    }
}

These snippets together demonstrate how to construct a flexible, multi‑parameter test‑case search using MySQL and MyBatis, and how to efficiently retrieve related data with asynchronous calls that are safely synchronized before responding to the client.