foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。
批量插入数据、批量更新数据。
foreach元素的属性主要有 collection,item,index,open,close,separator。
collection:指定要遍历的集合
list类型的参数会特殊处理封装在map中,map的key就叫list
item:将当前遍历出的元素赋值给指定的变量
index:索引。遍历list的时候是index就是索引,item就是当前值
open:遍历出所有结果拼接一个开始的字符(表示该语句以什么开始)
close:遍历出所有结果拼接一个结束的字符(表示以什么结束)
separator:每个元素之间的分隔符
在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况 下,该属性的值是不一样的,主要有一下3种情况:
1. 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
2. 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3. 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了
示例代码:
String answerStr = "[{\"questionNo\":1,\"answer\":\"A\",\"otherContent\":\"\",\"uid\":1},{\"questionNo\":2,\"answer\":\"D\",\"otherContent\":\"其他内容信息\",\"uid\":1}]";
//list模式
List<AnswerEvent> answerEventList = JSON.parseArray(answerStr, AnswerEvent.class);
//批量添加
answerMapper.batchInsertList(answerEventList);
//批量更新
answerMapper.batchUpdateList(answerEventList);
//array模式
AnswerEvent[] answerEventArr = answerEventList.toArray(new AnswerEvent[answerEventList.size()]);
//Object[] answerEventArr = answerEventList.toArray();
answerMapper.batchInsertArray(answerEventArr);
//map模式I
List<Map<String, Object>> paramList = new ArrayList<>();
for (AnswerEvent answerEvent : answerEventList) {
Map<String, Object> params = new HashMap<>();
params.put("questionNo", answerEvent.getQuestionNo());
params.put("answer", answerEvent.getAnswer());
params.put("otherContent", answerEvent.getOtherContent());
params.put("uid", answerEvent.getUid());
paramList.add(params);
}
//批量添加
answerMapper.batchInsertMap(paramList);
//批量更新
answerMapper.batchUpdateAnswer(paramList);
//map模式II
List<Long> uidList = answerEventList.stream().map(AnswerEvent::getUid).collect(Collectors.toList());
long[] uidArr = uidList.stream().mapToLong(Long::valueOf).toArray();
Map<String, Object> params = new HashMap<>();
params.put("uidArr", uidArr);
params.put("otherContent", "测试信息");
answerMapper.updateAnswer(params);
JmAnswerMapper相关代码:
/**
* 批量插入
* list模式
*
* @param answerEventList 集合
* @return int
*/
@Insert("<script> INSERT INTO `jm_answer` " +
"(`uid`, `question_no`, `answer`, `other_content`)" +
" VALUES " +
"<foreach collection='list' item='ans' index='index' separator=','>" +
"(#{ans.uid}, #{ans.questionNo}, #{ans.answer}, #{ans.otherContent})" +
"</foreach></script>")
int batchInsertList(List<AnswerEvent> answerEventList);
/**
* 批量插入
* array模式
*
* @param answerEventArr 数组
* @return int
*/
@Insert("<script> INSERT INTO `jm_answer` " +
"(`uid`, `question_no`, `answer`, `other_content`)" +
" VALUES " +
"<foreach collection='array' item='ans' index='index' separator=','>" +
"(#{ans.uid}, #{ans.questionNo}, #{ans.answer}, #{ans.otherContent})" +
"</foreach></script>")
//int batchInsertArray(Object[] answerEventArr);
int batchInsertArray(AnswerEvent[] answerEventArr);
/**
* 批量插入
* map模式
*
* @param list map集合
* @return int
*/
int batchInsertMap(List<Map<String, Object>> list);
/**
* 批量更新
* list模式
*
* @param answerEventList 集合
* @return int
*/
@Update("<script><foreach collection='list' item='ans' index='index' open='' close='' separator=';'>" +
" UPDATE `jm_answer` SET question_no =#{ans.questionNo}, answer=#{ans.answer}, other_content=#{ans.otherContent}" +
" WHERE uid = #{ans.uid}" +
"</foreach></script>")
int batchUpdateList(List<AnswerEvent> answerEventList);
/**
* 批量更新
* map模式
*
* @param list map集合
* @return int
*/
int batchUpdateAnswer(List<Map<String, Object>> list);
/**
* map模式
*
* @param params map
* @return int
*/
int updateAnswer(Map<String, Object> params);
JmAnswerMapper.xml相关代码:
<insert id="batchInsertMap" parameterType="java.util.Map">
<foreach collection="list" item="item" separator=";">
INSERT INTO `jm_answer` (`uid`, `question_no`, `answer`, `other_content`) VALUES (#{ans.uid}, #{ans.questionNo}, #{ans.answer}, #{ans.otherContent})
</foreach>
</insert>
<update id="batchUpdateAnswer" parameterType="java.util.Map">
<foreach collection="list" item="item" separator=";">
UPDATE `jm_answer` SET answer=#{ans.answer}, other_content=#{ans.otherContent} WHERE uid = #{ans.uid} AND question_no =#{ans.questionNo}
</foreach>
</update>
<update id="updateAnswer" parameterType="java.util.Map">
UPDATE `jm_answer` SET other_content =#{otherContent} WHERE uid IN
<foreach collection="uidArr" item="uid" open="(" close=")" separator=",">
#{uid}
</foreach>
</update>