#58 Spring Boot MyBatis trim

 2019.08.02

 

 출처 : http://www.mybatis.org/mybatis-3/ko/dynamic-sql.html

MyBatis – 마이바티스 3 | 동적 SQL

 

 

 ex) MyBatis trim SELECT

<?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="kr.or.ksmart.ksmart_layout1.mapper.GoodsMapper">
    <select id="searchGoods" parameterType="String" resultType="kr.or.ksmart.ksmart_layout1.vo.Goods">
        SELECT 
              g.m_id AS memberId
            ,g_code AS goodsCode
            ,g_name AS goodsName
            ,g_price AS goodsPrice
            ,g_cate AS goodsCate
            ,g_color AS goodsColor
            ,g_size AS goodsSize
            ,g_date AS goodsDate
            ,g_desc AS goodsDesc
            ,m_level AS memberLevel
            ,m_name AS memberName
            ,m_email AS memberEmail 
        FROM tb_goods g INNER JOIN tb_member m ON g.m_id = m.m_id
        <!-- AND 혹은 OR이 앞에 오면 AND 혹은 OR을 지운다-->
        <trim prefix="WHERE" prefixOverrides="AND|OR "> 
            <if test="sv neq null and sv neq ''.toString()">
                AND ${sk} LIKE CONCAT('%',#{sv},'%')
            </if> 
               <if test="firstPrice neq null and firstPrice neq ''.toString()">
                   AND g_price >= CAST(#{firstPrice} AS DECIMAL)
               </if>
               <if test="lastPrice neq null and lastPrice neq ''.toString()">
                   AND g_price <![CDATA[<=]]> CAST(#{lastPrice} AS DECIMAL)
               </if>
          </trim>
    </select>
</mapper>

 

 WHERE 엘리먼트는 태그에 의해 컨텐츠가 리턴되면 단순히 “WHERE”만을 추가한다

 거기에 컨텐츠가 “AND”나 “OR”로 시작한다면 그 “AND”나 “OR”를 지워버린다

 

 

 ex) MyBatis trim UPDATE

<?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="kr.or.ksmart.ksmart_layout1.mapper.GoodsMapper">
    <update id="modifyGoods" parameterType="kr.or.ksmart.ksmart_layout1.vo.Goods">
        UPDATE tb_goods
            <!-- 끝에 콤마(,)를 지운다 -->
            <trim prefix="SET" suffixOverrides=",">
                g_date=CURDATE(),
                <if test="goodsName != null and goodsName != ''.toString()">g_name=#{goodsName},</if>
                <if test="goodsCate != null and goodsCate != ''.toString()">g_cate=#{goodsCate},</if>
                <if test="goodsPrice != null and goodsPrice != ''.toString()">g_price=#{goodsPrice},</if>
                <if test="goodsColor != null and goodsColor != ''.toString()">g_color=#{goodsColor},</if>
                <if test="goodsSize != null and goodsSize != ''.toString()">g_size=#{goodsSize},</if>
                <if test="goodsDesc != null and goodsDesc != ''.toString()">g_desc=#{goodsDesc}</if>
            </trim>
        WHERE g_code = #{goodsCode}
    </update>
</mapper>

 SET 엘리먼트는 UPDATE 하고자 하는 칼럼을 동적으로 포함시키기 위해 사용한다

 SET 엘리먼트는 동적으로 SET 키워드를 붙히고 필요없는 콤마(,)를 제거한다

 

 

 prefixOverrides : 접두사, 앞에 나온 컨텐츠를 제거

 suffixOverrides : 접미사, 뒤에 나온 컨텐츠를 제거