java面试回忆总结之-----mybatis的增删改查（包括循环添加查询修改删除）

添加：

<insert id="insert" parameterType="com.brsy.lq365.model.entity.saas.task.SaPlanCourse">
  insert into kms_plan_course (ID, PLAN_ID, COURSE_ID
    )
  values (#{id,jdbcType=VARCHAR}, #{planId,jdbcType=VARCHAR}, #{courseId,jdbcType=VARCHAR}
    )
</insert>

<insert id="insertSelective" parameterType="com.brsy.lq365.model.entity.saas.task.SaPlanCourse">
  insert into kms_plan_course
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="id != null">
      ID,
    </if>
    <if test="planId != null">
      PLAN_ID,
    </if>
    <if test="courseId != null">
      COURSE_ID,
    </if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="id != null">
      #{id,jdbcType=VARCHAR},
    </if>
    <if test="planId != null">
      #{planId,jdbcType=VARCHAR},
    </if>
    <if test="courseId != null">
      #{courseId,jdbcType=VARCHAR},
    </if>
  </trim>
</insert>

循环添加：

public void batchInsertUsers(List<User> users);

<insert id="batchInsertUsers" parameterType="java.util.List">
  insert into mhc_user(userName,password) values
  <foreach collection="list" item="item" index="index" separator=",">
    (#{item.userName},#{item.password})
  </foreach>
</insert>

修改：

<update id="updateByPrimaryKey" parameterType="com.brsy.lq365.model.entity.saas.task.SaPlanUser">
  update kms_plan_user
  set PLAN_ID = #{planId,jdbcType=VARCHAR},
    USER_ID = #{userId,jdbcType=VARCHAR}
  where ID = #{id,jdbcType=VARCHAR}
</update>

<update id="updateByPrimaryKeyWithBLOBs" parameterType="com.brsy.lq365.model.entity.saas.task.SaPlan">
  update kms_plan
  set PLAN_NAME = #{planName,jdbcType=VARCHAR},
    PLAN_STARTDATE = #{planStartdate,jdbcType=VARCHAR},
    PLAN_ENDDATE = #{planEnddate,jdbcType=VARCHAR},
    PLAN_LASTEDITDATE = #{planLasteditdate,jdbcType=VARCHAR}
  where PLAN_ID = #{planId,jdbcType=VARCHAR}
</update>

<update id="updateByPrimaryKeySelective" parameterType="com.brsy.lq365.model.entity.saas.task.SaPlanUser">
  update kms_plan_user
  <set>
    <if test="planId != null">
      PLAN_ID = #{planId,jdbcType=VARCHAR},
    </if>
    <if test="userId != null">
      USER_ID = #{userId,jdbcType=VARCHAR},
    </if>
  </set>
  where ID = #{id,jdbcType=VARCHAR}
</update>

循环修改：

1. <!-- FOR MySQL mysql需要数据库连接配置&allowMultiQueries=true
2. 例如：jdbc:mysql://127.0.0.1:3306/mhc?allowMultiQueries=true -->

public void batchUpdateUsers(List users) ;

<update id="batchUpdateUsers" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
  update mhc_user
  <set>
    userName = #{item.userName}, password = #{item.password}
  </set>
  where id = #{item.id}
</foreach>

void updateOptionNum(Map<String, Object> data);

<!--修改选项的累加个数-->
<update id="updateOptionNum" parameterType="map">
    UPDATE
    option_ljj
    SET
    OPTIONCOUNT = OPTIONCOUNT + 1
    WHERE
    OPTIONID  in
    <foreach collection="options" item="item" index="index" open="(" separator="," close=")" >
        #{item}
    </foreach>

</update>

删除：

<delete id="deleteByPrimaryKey" parameterType="java.lang.String">
  delete from kms_plan_user
  where ID = #{id,jdbcType=VARCHAR}
</delete>

循环删除：

public void batchDeleteUsers(List ids);

<!-- 批量删除操作 -->
<delete id="batchDeleteUsers" parameterType="java.util.List">
  delete from mhc_user where id in
  <foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
    #{item}
  </foreach>
</delete>

查询：

  <select id="getUserJob" parameterType="java.lang.String" resultType="java.lang.String">
     SELECT
        DEP_NAME as depname
    FROM
        system_dep
    WHERE
       DEP_ID = #{id,jdbcType=VARCHAR}
</select>

<select id="selectProcessByid" parameterType="map" resultType="Map">
    SELECT
    IFNULL(jindu,0)AS jindu
    FROM
    t_video_rate
    WHERE 1=1
    <if test="courseid != null and courseid !='' and courseid !='null' ">
        AND courseid= #{courseid}
    </if>
    <if test="uid != null and uid !='' and uid !='null' ">
        AND uid= #{uid}
    </if>
    <if test="videoid != null and videoid !='' and videoid !='null' ">
        AND videoid= #{videoid}
    </if>


</select>

<select id="countCourseProcess" parameterType="map" resultType="int">
    SELECT
    IFNULL(sum(watchtime),0) AS totalwatchtime
    FROM
    t_video_rate
    WHERE
    1=1
    <if test="id != null and id !='' and id !='null' ">
        AND courseid= #{id}
    </if>
    <if test="uid != null and uid !='' and uid !='null' ">
        AND uid= #{uid}
    </if>

</select>

循环查询：

public List<User> batchSelectUsers(List ids);

<!-- 批量查询操作 -->
<select id="batchSelectUsers" resultType="User">
    select *
    from mhc_user where id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

public void batchUpdateUsers(List users) ;