[Spring Mybatis] 중복 쿼리를 include, sql로 대체하기

속성이 많은 테이블의 정보를 조회해야 할 때, WHERE 조건만 다른 쿼리를 짤 때 등 중복되는 쿼리를 작성해야 할 때가 있습니다.

 

WHERE 조건을 제외한다면 다른 부분은 모두 중복되는 쿼리

 <select id="findByOwnerId">
        SELECT id,
               name, 
               delivery_type deliveryType, 
               signature_menu signatureMenu, 
               tel,
               zipcode, 
               address, 
               address_detail addressDetail, 
               biz_number bizNumber,
               info, 
               min_order_price minOrderPrice, 
               notice, 
               operating_time operatingTime,
               delivery_location deliveryLocation, 
               owner_id ownerId, 
               created_at createdAt,
               updated_at updatedAt, 
               status, 
               oreder_type orderType, 
               origin_info originInfo,
               work_condition workCondition
        FROM SHOP
        WHERE ownerId = #{ownerId}
    </select>
    
    <select id="findById">
        SELECT id,
               name, 
               delivery_type deliveryType, 
               signature_menu signatureMenu, 
               tel,
               zipcode, 
               address, 
               address_detail addressDetail, 
               biz_number bizNumber,
               info, 
               min_order_price minOrderPrice, 
               notice, 
               operating_time operatingTime,
               delivery_location deliveryLocation, 
               owner_id ownerId, 
               created_at createdAt,
               updated_at updatedAt, 
               status, 
               oreder_type orderType, 
               origin_info originInfo,
               work_condition workCondition
        FROM SHOP
        WHERE id = #{id}
    </select>

 

이 경우 중복되는 부분을 sql 태그로 뺀 후 빈 자리에 include 태그를 사용한다면 쉽게 중복 쿼리를 줄일 수 있습니다.

 

 

중복 쿼리를 따로 sql 태그로 뺀다

<sql id="selectShop">
         SELECT id,
               name, 
               delivery_type deliveryType, 
               signature_menu signatureMenu, 
               tel,
               zipcode, 
               address, 
               address_detail addressDetail, 
               biz_number bizNumber,
               info, 
               min_order_price minOrderPrice, 
               notice, 
               operating_time operatingTime,
               delivery_location deliveryLocation, 
               owner_id ownerId, 
               created_at createdAt,
               updated_at updatedAt, 
               status, 
               oreder_type orderType, 
               origin_info originInfo,
               work_condition workCondition
        FROM SHOP
    </sql>
    
    <select id="findByOwnerId">
        <include refid="selectOwner"></include>
        WHERE ownerId = #{ownerId}
    </select>
    <select id="findById">
        <include refid="selectOwner"></include>
        WHERE id = #{id}
    </select>