MyBatis单一和批量操作数据库SQL

1. 单条插入

<insert id="insert" parameterType="Model">
    insert into T_A (UID, PROJECT_ID, USERPROJ_RELATE_ID,
      PROJECT_STATUS, type,PERMISSION_FLAG)
    values (#{uid,jdbcType=VARCHAR}, #{projectId,jdbcType=VARCHAR}, #{userprojRelateId,jdbcType=VARCHAR},
      #{projectStatus,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR}, #{permissionFlag,jdbcType=VARCHAR})
  </insert>

2. 批量插入


  <insert id="insertRelateList" parameterType="java.util.List" >
    insert into T_A(UID, PROJECT_ID, USERPROJ_RELATE_ID,
      PROJECT_STATUS, type,PERMISSION_FLAG)
    values 
    <foreach  collection="list" item="item" index="index" separator=",">
    (#{item.uid,jdbcType=VARCHAR}, #{item.projectId,jdbcType=VARCHAR}, #{item.userprojRelateId,jdbcType=VARCHAR},
      #{item.projectStatus,jdbcType=VARCHAR}, #{item.type,jdbcType=VARCHAR}, #{item.permissionFlag,jdbcType=VARCHAR})
      </foreach>
  </insert>


3.单条删除

<delete id="delete" parameterType="Model">
   delete from T_A where uid=#{uid} and project_id=#{projectId}
  </delete>


4.批量删除

<delete id="deleteRelateList" parameterType="java.util.List">
   delete from T_A where project_id in
   <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
             #{item.projectId}
   </foreach>
  </delete>


5.单条修改

<update id="update" parameterType="Model">
          update  T_A 
          set PERMISSION_FLAG = #{permissionFlag,jdbcType=VARCHAR}
           where UID = #{uid,jdbcType=VARCHAR}
    </foreach>
  </update>


6.批量修改


6.1（MySQL写法） 要加红字部分

jdbc_url=jdbc:mysql://127.0.0.1:1334/testt?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

<update id="updateRelateList" parameterType="java.util.List">
    <foreach item="item" index="index" collection="list" open="" separator=";" close="">
          update  T_A 
           <set>
             PERMISSION_FLAG = #{item.permissionFlag,jdbcType=VARCHAR}
           </set>
           where UID = #{item.uid,jdbcType=VARCHAR}
    </foreach>
  </update>


6.2（Oracle写法）

<update id="updateRelateList" parameterType="java.util.List">
    <foreach item="item" index="index" collection="list" open="begin" separator=";" close="end;">
          update  T_A 
           <set>
             PERMISSION_FLAG = #{item.permissionFlag,jdbcType=VARCHAR}
           </set>
           where UID = #{item.uid,jdbcType=VARCHAR}
    </foreach>
  </update>