Java 带有 IN 条件的 MyBatis 问题 <foreach with List inside a Map
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21374677/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MyBatis Issue with IN Condition <foreach with List inside a Map
提问by Shiv Gopal
I have to build a IN condition using MyBatis where have to pass a list of PARENT_VALUESto be obtained based on the foreach loop below....
我必须使用 MyBatis 构建一个 IN 条件，其中必须传递PARENT_VALUES基于下面的 foreach 循环获得的列表......
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);
Required SQL:
所需的 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:
MyBatis 映射器 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
采纳答案by Karthik Prasad
Your Select statement would like something like this
你的 Select 语句会像这样
<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>
回答by Phoenix
I agree with Karthik Prasad and if you remove CDATA and have sth like
我同意 Karthik Prasad 的观点，如果你删除 CDATA 并且有类似的东西
a_column>=6 AND b_column<10
you must do the XML escape just as below:
您必须按如下方式进行 XML 转义：
a_column >= 6 AND b_column < 10
