【mybatis-oracle】批量插入、批量删除以及xml文件大于号 小于号处理

批量插入

dao层

public interface MsgInfoMapper {
    int insertBatch(@Param("tableName")String tableName,@Param("list")List<MsgInfo> list);
}

mapper.xml
传入表名，使用${}拼接，不会预编译,使用#{}会导致预编译为占位符号“？”

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.mybatis.msg.dao.MsgInfoMapper" >
  <resultMap id="BaseResultMap" type="com.example.mybatis.msg.po.MsgInfo" >
    <id column="SEQ" property="seq" jdbcType="VARCHAR" />
    <result column="PARAMETER" property="parameter" jdbcType="VARCHAR" />
    <result column="SERVICE_NO" property="serviceNo" jdbcType="VARCHAR" />
    <result column="PHONE_NO" property="phoneNo" jdbcType="VARCHAR" />
    <result column="INSERT_TIME" property="insertTime" jdbcType="TIMESTAMP" />
  </resultMap>
  <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
    insert into ${tableName} (SEQ,PARAMETER, SERVICE_NO, PHONE_NO, INSERT_TIME)
    select t.* from(
    <foreach collection ="list" item="MsgInfo" separator ="union all">
     select 
      #{MsgInfo.seq,jdbcType=VARCHAR}, #{MsgInfo.parameter,jdbcType=VARCHAR}, 
      #{MsgInfo.serviceNo,jdbcType=VARCHAR}, #{MsgInfo.phoneNo,jdbcType=VARCHAR}, #{MsgInfo.insertTime,jdbcType=TIMESTAMP}
      from dual
    </foreach > 
    ) t
##   </insert>
</mapper>

mysql版本如下，oracle中错写成这种,报错为Sql命令为正常结束（认真比较mysql 与Oracle的版本差别）

<!--   <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
    insert into ${tableName} (SEQ, TEMPLATEID, PARAMETER, 
      SERVICE_NO, PHONE_NO, INSERT_TIME)
    values
    <foreach collection ="list" item="MsgInfo" separator =",">
      (#{MsgInfo.seq,jdbcType=VARCHAR}, #{MsgInfo.templateid,jdbcType=VARCHAR}, #{MsgInfo.parameter,jdbcType=VARCHAR}, 
      #{MsgInfo.serviceNo,jdbcType=VARCHAR}, #{MsgInfo.phoneNo,jdbcType=VARCHAR}, #{MsgInfo.insertTime,jdbcType=TIMESTAMP})
    </foreach > 
  </insert> 

批量删除：传入数组，实现批量删除

dao层

public interface PushInfoMapper {
int deleteByBatch(Long[] serv_os);
}

mapper.xml

<delete id="deleteByBatch"  parameterType="java.lang.Long">
        delete from TABLE_NAME
        where serial_no IN 
        <foreach collection="array" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

mapper.xml中的大于号，小于号导致异常

xml文件对> <等字符非常敏感，会导致xml文件解析异常

<select id="selectBylimit" resultMap="BaseResultMap" parameterType="java.lang.Integer">
   select * from  TABLE_NAME  where  <![CDATA[rownum< ${num}]]>
  </select>