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会报错。

	JdbcType	Oracle	MySql
JdbcType	BIGINT		BIGINT
JdbcType	BIT		BIT
JdbcType	BLOB	BLOB	BLOB
JdbcType	BOOLEAN
JdbcType	CHAR	CHAR	CHAR
JdbcType	CLOB	CLOB	修改为TEXT
JdbcType	DATE	DATE	DATE
JdbcType	DECIMAL	DECIMAL	DECIMAL
JdbcType	DOUBLE	NUMBER	DOUBLE
JdbcType	FLOAT	FLOAT	FLOAT
JdbcType	INTEGER	INTEGER	INTEGER
JdbcType	LONGVARCHAR	LONG VARCHAR
JdbcType	NULL
JdbcType	SMALLINT	SMALLINT	SMALLINT
JdbcType	TIME		TIME
JdbcType	TIMESTAMP	TIMESTAMP	TIMESTAMP/DATETIME
JdbcType	TINYINT		TINYINT
JdbcType	VARCHAR	VARCHAR	VARCHAR