MyBatis常用标签

Mybatis常用SQL标签

<select> 查询标签
<insert>新增标签
<update>修改标签
<delete>删除标签
<sql id=""></sql>标签（主要是用来写一些通用的sql语句的，在其他标签中可以使用<include refid=""></include>来引入标签）
<parameterMap type="" id=""></parameterMap>参数集合
<resultMap type="" id=""></resultMap>结果集
<cache></cache>
<cache-ref namespace=""/> 这两个标签都是缓存标签

查询标签

<select id="queryGiftById" parameterType="TblGiftDto"
	resultType="TblGift">
   <![CDATA[SELECT]]>
	<include refid="giftColumn" />
      <![CDATA[FROM tbl_gift]]>
	<where>
		<if test="giftId != null and giftId != ''">
              <![CDATA[AND _ID=#{giftId}]]>
		</if>
	</where>
</select>

新增标签

<insert id="insertGroup" parameterType="GroupInfo">
    <![CDATA[
    INSERT INTO group_info (userid,liveuserid,groupid,nickname,headpic)
    VALUES (#{userid},#{liveuserid},#{groupid},#{nickname},#{headpic})
    ]]>
</insert>

修改标签

<update id="updateFollownum" parameterType="tblAnchorFollow">
	<![CDATA[UPDATE live_data SET _follownum = IFNULL(_follownum, 0) + #{addValue}]]>
	<where>
		<!-- 沒有条件时不允许更新 -->
		<if test="anchorId == null or anchorId == ''">
			<![CDATA[1 <> 1]]>
		</if>
		<if test="anchorId != null and anchorId != ''">
			<![CDATA[AND userid = #{anchorId}]]>
		</if>
	</where>
</update>

删除标签

<delete id="deleteGroup">
 		<![CDATA[DELETE FROM group_info ]]>
 		<where>
 			<if test="userid != null and userid != ''">
              <![CDATA[AND userid=#{userid}]]>
      </if>
      <if test="liveuserid != null and liveuserid != ''">
          <![CDATA[AND liveuserid=#{liveuserid}]]>
      </if>
 		</where>
 </delete>
  

resultMap标签

<resultMap type="TeacherEntity" id="teacherResultMap">  
    <id property="teacherID" column="TEACHER_ID" />  
    <result property="teacherName" column="TEACHER_NAME" />  
    <result property="teacherSex" column="TEACHER_SEX" />  
    <result property="teacherBirthday" column="TEACHER_BIRTHDAY"/>  
    <result property="workDate" column="WORK_DATE"/>  
    <result property="professional" column="PROFESSIONAL"/>  
</resultMap>  

<select id="getTeacher" parameterType="String"  resultMap="teacherResultMap">  
    SELECT *  
      FROM TEACHER_TBL TT  
     WHERE TT.TEACHER_ID = #{teacherID}  
</select>

foreach标签（批量插入）

<!-- 批量插入群组机器人成员 -->
<insert id="addGroupInfoBatch" useGeneratedKeys="true" parameterType="java.util.List">
	INSERT INTO group_info (userid,liveuserid,groupid,nickname,headpic)
	VALUES
	<foreach collection="list" item="info" index="index"
		separator=",">
		(#{info.userid},#{info.liveuserid},#{info.groupid},#{info.nickname},#{info.headpic})
	</foreach>
</insert>  

MySQL查询指定表的所有列名

<!-- MySQL查询指定表的所有列名-->
<select id="queryCOLUMNNAME" parameterType="String" resultType="String">  
       <![CDATA[
      SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns 
      WHERE table_name=#{tableName,jdbcType=VARCHAR}
      ]]>
</select>