[Spring] IN 조건문을 위한 foreach 사용하기 - myBatis, sql

Spring Boot / Oracle / Mybatis 차례

 

CASE1

Service.java

package project;

import java.util.ArrayList;
import java.util.List;

@Service
public class Service {
	
    public List<dataDTO> retrieveTasks(DataDTO dataDTO) {
    	// 파라미터 states는 "10,20,30" 형식으로 받는다.
    	String [] states = dataDTO.getStates().split(",");
        List<String> stateList = new ArrayList<String>();
        
        for (String state : states) {
        	stateList.add(state)
        }
        dataDTO.setStateList(stateList)
        return mappper.retrieveTasks(dataDTO)
    }
}

 

mapper.xml

<select id="retrieveTasks" parameterType="project.DataDTO" resultType="project.DataDTO">
	<![CDATA[
    	SELECT 
               ID
             , NAME
          FROM MY_TABLE
         WHERE 1=1
    ]]>
    <if test="myId != null and myId != ''">
           AND ID = #{myId, jdbcType=VARCHAR}
    </if>
    <if test="stateList != 0">
           AND STATE IN 
           <foreach collection="stateList" item="state" index="index" separator="," open="(" close=")">
           	#{state}
           </foreach>
    </if>
</select>

 

 

 

 

CASE2

ServiceImpl.java

package project;

import java.util.ArrayList;
import java.util.List;

@Service
public class Service {
	
    public List<dataDTO> retrieveTasks(DataDTO dataDTO) {
    	// 파라미터 states는 "10,20,30" 형식으로 받는다.
    	String states = (String) dataDTO.get("state");
        List<String> stateList = new ArrayList<String>();
        
        if (states != null) {
        	 String[] stateArr = ((String) dataDTO.get("state")).split(",");
             for (String state : stateArr) {
        	     stateList.add(state)
             }
        }
        dataDTO.set("stateList", stateList)
        return mappper.retrieveTasks(dataDTO)
    }
}

mapper.xml

<select id="retrieveTasks" parameterType="project.DataDTO" resultType="project.DataDTO">
	<![CDATA[
    	SELECT 
               ID
             , NAME
          FROM MY_TABLE
         WHERE 1=1
    ]]>
    <if test="myId != null and myId != ''">
           AND ID = #{myId, jdbcType=VARCHAR}
    </if>
    <if test="stateList.size != 0">
           AND STATE IN 
           <foreach collection="stateList" item="state" index="index" separator="," open="(" close=")">
           	    #{state}
           </foreach>
    </if>
</select>

 

 

 

 

 

 

 

Spring Boot / Oracle / Mybatis 차례