MySQL/MyBatis | Using Dynamic Query

Yian Kim — Wed, 24 Nov 2021 15:48:58 +0000

🍞 Context

It is used to join and look up tables corresponding to a service called A. B and C services will also be added for commonization later. A, B and C services should all be commonized so that they can work in a single query.

Suppose that the table has all_hist, a_request, a_response, b_request, b_response, c_request, c_response that corresponds to each service.

🥫 Solution

If there is a variable that flags in the DTO, you can add a condition to check the variable in the SQL statement FROM clause. Suppose ReqDTO has a variable called type that acts as a flag. You can use <choose>, <when> in the From clause as shown below.

SELECT *
FROM all_hist ah
<choose>
    <when test="type != null and type == 'A_REQUEST'">
        , a_request areq, a_response ares
    </when>
</choose>

I'll compare it with the code before and after the modification.

Before

<select id="selectList" parameterType="ReqDTO" resultType="ResDTO">
    /* allHist.selectList */
    SELECT ah.id AS id,
        areq.name AS name,
        ares.ymdt AS ymdt
    FROM all_hist ah, a_request areq, a_response ares
    WHERE ah.id = areq.id
    AND areq.id = ares.id
    AND ah = #{id}
    AND ah.type = #{type}
    ORDER BY id DESC
</select>

After

<select id="selectList" parameterType="ReqDTO" resultType="ResDTO">
    /* allHist.selectList */
    SELECT ah.id AS id,
        areq.name AS name,
        ares.ymdt AS ymdt
    FROM all_hist ah
    <choose>
        <when test="type != null and type == 'A_REQUEST'">
            , a_request areq, a_response ares
        </when>
    </choose>
    WHERE ah.id = areq.id
    AND areq.id = ares.id
    AND ah = #{id}
    AND ah.type = #{type}
    ORDER BY id DESC
</select>

If B and C are added, it will be code as below.

SELECT * 
FROM all_hist ah
<choose>
    <when test="type != null and type == 'A_REQUEST'">
        , a_request areq, a_response ares
    </when>
    <when test="type != null and type == 'B_REQUEST'">
        , b_request areq, b_response ares
    </when>
    <when test="type != null and type == 'B_REQUEST'">
        , c_request areq, c_response ares
    </when>
</choose>

🍮 Retrospective

I've seen the use of Dynamic Query in the WHERE section, but I've rarely seen it in the FROM section, so I've been lost. I didn't understand until I heard the explanation from my colleague. I didn't know the keyword Dynamic Query because it was unfamiliar and I haven't used it very well. At first, I thought about whether I should write the DAO corresponding to A, B and C three times, but I found out again. I'll review the Dynamic Query later.

🍊 Reference

I'm not fluent in writing in English, but I'm trying because I want to do well. Feedback is always welcome.
만약 이 글을 한국어로 읽고 싶다면 MySQL/MyBatis | 동적 쿼리(Dynamic Query) 사용하기 글을 참고해 주세요.