//sql中的in操作
// 1.传入map
Map m = new HashMap<String,Object>();
m.put("gidList", list);
m.put("gName", "002项目工程8543工程338");
List result = aProjectMapper.selectByListAndName(m);
//xml
<select id="selectByListAndName" parameterType="java.util.Map" resultType="java.util.Map">
select * from a_group where g_id in
<foreach collection="gidList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
or g_name = #{gName}
</select>
//1.批量 添加
int insertEngList(@Param("eList")List<TEng> eList);
//xml
<insert id="insertEngList" parameterType="java.util.List" >
INSERT INTO `jsy`.`t_eng` ( `p_id`,`e_id`,`e_name`,`e_content`,`e_num`)
VALUES
<foreach collection="eList" item="item" separator=",">
(#{item.pId,jdbcType=VARCHAR},
#{item.eId,jdbcType=VARCHAR},
#{item.eName,jdbcType=VARCHAR},
#{item.eContent,jdbcType=VARCHAR},
#{item.eNum,jdbcType=INTEGER})
</foreach>
</insert>
//2.批量 添加或更新 -- 这里使用唯一索引作为更新条件
#创建e_id唯一索引
#CREATE UNIQUE INDEX catename ON t_eng(e_id);
int insertOrUpdateEngList(@Param("eList")List<TEng> eList);
//xml
<insert id="insertOrUpdateEngList" parameterType="java.util.List" >
replace INTO `jsy`.`t_eng` ( `p_id`,`e_id`,`e_name`,`e_content`,`e_num`)
VALUES
<foreach collection="eList" item="item" separator=",">
(#{item.pId,jdbcType=VARCHAR},
#{item.eId,jdbcType=VARCHAR},
#{item.eName,jdbcType=VARCHAR},
#{item.eContent,jdbcType=VARCHAR},
#{item.eNum,jdbcType=INTEGER})
</foreach>
</insert>
// 3.批量删除
int deleteEngListByEids(@Param("eIds") String[] eIds);
//xml
<delete id="deleteEngListByEids">
delete from t_eng where e_id in
<foreach item="item" collection="eIds" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
// 4.批量更新 通过engId 更新
int updateEngList(@Param("eList") List<TEng> eList);
//xml
<update id="updateEngList" parameterType="java.util.List" >
<foreach collection="eList" item="item" index="index" open="" close="" separator=";">
UPDATE `jsy`.`t_eng`
<set>
`p_id` = #{item.pId}
,`e_id` = #{item.eId}
,`e_name` = #{item.eName}
,`e_content` = #{item.eContent}
,`e_num` = #{item.eNum}
</set>
WHERE `e_id` = #{item.eId}
</foreach>
</update>
//mybatis ORCLE 批量更新 **
<insert id="insertDdcclwzListToOrcle" parameterType="java.util.List" >
begin
<foreach collection="mList" item="item" index="index" separator=";" >
insert
into DDCCLWZJB ( <include refid="Base_Column_List" /> )
VALUES
(#{item.department,jdbcType=VARCHAR}, #{item.sapDocId,jdbcType=VARCHAR}, #{item.year,jdbcType=VARCHAR},
#{item.ygId,jdbcType=VARCHAR})
</foreach>
;end;
</insert>