总之一句话,传递多个参数,要么用Map,要么用Bean
Mapper接口定义
int batchInsert2(@Param("st1")String st1,@Param("st2")String st2,@Param("st3")String st3, @Param("directions") List<Direction> directions);
int batchInsert1(@Param("st")Station st, @Param("directions") List<Direction> directions);
int batchInsert(@Param("st")Station st);
Bean定义
public class Station{
private String stationId;
private List<Direction> directions;
XML实现
<insert id="batchInsert2">
insert into DIRECTION(ID,STATION_ID,DIRECTION_ID,DIRECTION_NAME,DIRECTION_DESC)
select CONCAT(#{st1,jdbcType=VARCHAR},DIRECTION_ID) ID, #{st2,jdbcType=VARCHAR} STATION_ID, DIRECTION_ID,CODEDESC DIRECTION_NAME,CODEDESC DIRECTION_DESC from(
<foreach collection="directions" item="item" index="index" separator=" union " >
select #{item.directionId,jdbcType=VARCHAR} DIRECTION_ID
</foreach>
)a,t_sys_code b
where a.DIRECTION_ID = b.CODE
and a.DIRECTION_ID not in(select DIRECTION_ID from DIRECTION where STATION_ID = #{st3,jdbcType=VARCHAR})
</insert>
<insert id="batchInsert1">
insert into DIRECTION(ID,STATION_ID,DIRECTION_ID,DIRECTION_NAME,DIRECTION_DESC)
select CONCAT(#{st.stationId,jdbcType=VARCHAR},DIRECTION_ID) ID, #{st.stationId,jdbcType=VARCHAR} STATION_ID, DIRECTION_ID,CODEDESC DIRECTION_NAME,CODEDESC DIRECTION_DESC from(
<foreach collection="directions" item="item" index="index" separator=" union " >
select #{item.directionId,jdbcType=VARCHAR} DIRECTION_ID
</foreach>
)a,t_sys_code b
where a.DIRECTION_ID = b.CODE
and a.DIRECTION_ID not in(select DIRECTION_ID from DIRECTION where STATION_ID = #{st.stationId,jdbcType=VARCHAR})
</insert>
<insert id="batchInsert">
insert into DIRECTION(ID,STATION_ID,DIRECTION_ID,DIRECTION_NAME,DIRECTION_DESC)
select CONCAT(#{st.stationId,jdbcType=VARCHAR},DIRECTION_ID) ID, #{st.stationId,jdbcType=VARCHAR} STATION_ID, DIRECTION_ID,CODEDESC DIRECTION_NAME,CODEDESC DIRECTION_DESC from(
<foreach collection="st.directions" item="item" index="index" separator=" union " >
select #{item.directionId,jdbcType=VARCHAR} DIRECTION_ID
</foreach>
)a,t_sys_code b
where a.DIRECTION_ID = b.CODE
and a.DIRECTION_ID not in(select DIRECTION_ID from DIRECTION where STATION_ID = #{st.stationId,jdbcType=VARCHAR})
</insert>
最初使用的方法1,batchInsert2的定义,那时候基本上是一个字符串变量,一个数组变量。这次SQL中是三次用到StationID,此时需要在参数中定义三次StationID的字符串变量
于是方法2,尝试使用Station类型的变量,在SQL中需要使用#{参数名.属性名}
进而方法2中,List变量是使用 “参数名.属性名” 的方式来引用
------------------------------------------------------------------------------------------------------
看看之间的定义,另做参考
<!-- 写入可以访问资源的所有权限
int insertMulti(@Param("menuId")int menuId, @Param("authorities")List<Integer> authorities);
-->
<insert id="insertMulti">
insert into auth_menu (menu_id,authority_id)
values
<foreach collection="authorities" item="authId" index="index" separator="," >
(#{authId,jdbcType=INTEGER},#{menuId})
</foreach>
</insert>