๐ 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) ์ฌ์ฉํ๊ธฐ ๊ธ์ ์ฐธ๊ณ ํด ์ฃผ์ธ์.
Top comments (1)
Writing code like this improves efficiency by 100 times compared to directly using MyBatis: dev.to/troyzhxu/writing-code-like-...