mybatis-xml增删改查

• 单个查询

  <select id="selectById" resultType="io.zjh.modules.sys.entity.SysMenuEntity">
  select * from sys_menu t where t.menu_id = #{menuId}
 </select>

• 批量查询(resultType填返回列表中的类型就行)

 <select id="findMenuName" resultType="java.lang.String" parameterType="java.util.List">
    select menu_name
    from menu
    where menu_id in
  <foreach collection="list" item="valueList" open="(" close=")" separator=",">
    #{valueList}
  </foreach>
</select>

• 单个插入

<insert id="insert" parameterType="io.zjh.modules.sys.entity.SysMenuEntity">
  insert into sys_menu(menu_id,parent_id,name,url,perms,type,icon,order_num)
  values (
   #{menuId},
   #{parentId},
   #{name},
   #{url},
   #{perms},
   #{type},
   #{icon},
   #{orderNum}
  )
 </insert>

• 批量插入(注意括号不是写在open和close)

 <insert id="insertBatch" parameterType="java.util.List">
  insert into sys_role_menu(role_id,menu_id)
  values
  <foreach collection="list" item="item"  index="index" separator=",">
   (
   #{item.roleId},
   #{item.menuId}
   )
  </foreach>
 </insert>

• 单个更新

 <update id="update" parameterType="io.zjh.modules.sys.entity.SysUserEntity">
  update sys_user
  <set>
   password = #{newPassword}
  </set>
  where user_id = #{userId} and password = #{password}
 </update>

• 批量更新

<update id="updateRoles" parameterType="java.util.List">
  update role
  set enabled = '0'
  where role_id in <foreach collection="list" item="roleIds" index="index" open="(" separator="," close=")"> 
    #{roleIds} 
  </foreach>
</update>

• 单个删除

<delete id="deleteByMenuId">
  delete from sys_role_menu where menu_id = #{menuId}
</delete>

• 批量删除

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

传入list和array比较

• 如果传入的是单参数且参数类型是一个List的时候，parameterType为java.util.List。collection属性值为list
  int deleteByLogic(List list);
• 如果传入的是单参数且参数类型是一个array数组的时候， 参数类型为parameterType="int" 集合 collection的属性值为array 。
  int deleteByLogic(int[] array);

<foreach item="item" collection="array" open="(" separator="," close=")">
            #{item}
</foreach>

jdbcType

• 有时候插入更新时需要制定这个类型，是因为传入了空值，无法判断它的类型,mybatis会报错。