MyBatis问题，IN条件&for;带有地图内列表的foreach [英] MyBatis Issue with IN Condition <foreach with List inside a Map

问题描述

我必须使用MyBatis建立一个IN条件，其中必须根据下面的foreach循环传递要获取的PARENT_VALUES列表....

I have to build a IN condition using MyBatis where have to pass a list of PARENT_VALUES to be obtained based on the foreach loop below....

我尝试但无法解决此问题.我不确定

传递的值是:

I tried but unable to resolve this. I am not sure if

Values Passed are:

Map input = new HashMap();
input.put("somedata");
List<String> inConditionList = new ArrayList<String>();
inConditionList.add("P1");
inConditionList.add("P2");
input.put(inConditionList);
sqlSessionTemplate.selectList("getNameAgeDetails", input);

必需的SQL:

 SELECT P.NAME, P.AGE
   FROM PERSON_DETAILS P
   WHERE SOMECOLUMN is NULL AND DATA IN
   (SELECT DATA FROM PARENT_TABLE WHERE PARENT_VALUE IN ("P1, "P2"))
 ORDER BY P.NAME
  FETCH FIRST 10 ROW ONLY

MyBatis Mapper SQL:

<select id="getNameAgeDetails" parameterType="map" resultMap="someResultMap">
    <![CDATA[
        SELECT P.NAME, P.AGE
        FROM PERSON_DETAILS P
        WHERE
         SOMECOLUMN is NULL
        AND DATA IN
          (SELECT DATA
          FROM PARENT_TABLE
          WHERE PARENT_VALUE IN 
         <FOREACH item="item"  index="index" collection="list" separator="," open="(" close=")"> 
                ${item}
            </FOREACH>  
          )
          ORDER BY P.NAME
          FETCH 
            FIRST 10 ROW ONLY 
    ]]>
  </select>

以下是我尝试运行单元测试案例时遇到的错误:

Below is the Error I am getting when I try to run my Unit TestCases:

### The error occurred while setting parameters
### SQL: SELECT P.NAME, P.AGE
            FROM PERSON_DETAILS P
            WHERE
             SOMECOLUMN is NULL
            AND DATA IN
              (SELECT DATA
              FROM PARENT_TABLE
              WHERE PARENT_VALUE IN <FOREACH item="item"  index="index" collection="list" separator="," open="(" close=")">                      ?              </FOREACH>             
### Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=PARENT_VALUE IN 
            <foreach it;TION
          WHERE;<space>, DRIVER=3.63.75
; bad SQL grammar []; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=PARENT_VALUE IN 

推荐答案

您的Select语句想要这样的东西

Your Select statement would like something like this

<select id="getNameAgeDetails" parameterType="map" resultMap="someResultMap">
        SELECT P.NAME, P.AGE
        FROM PERSON_DETAILS P
        WHERE
         SOMECOLUMN is NULL
        AND DATA IN
          (SELECT DATA
          FROM PARENT_TABLE
          WHERE PARENT_VALUE IN 
         <FOREACH item="item"  index="index" collection="list" separator="," open="(" close=")"> 
                ${item}
            </FOREACH>  
          )
          ORDER BY P.NAME
          FETCH 
            FIRST 10 ROW ONLY 
  </select>

这篇关于MyBatis问题，IN条件&for;带有地图内列表的foreach的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！