Mybatis笔记大全

非情剑

已于 2024-03-12 10:13:00 修改

阅读量54

点赞数

于 2021-07-05 15:46:50 首次发布

本文链接：https://blog.csdn.net/qq_43542296/article/details/118406870

版权

数据库|SQL|Mybatis|Redis|Es|.. 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

批量插入1

  insert into ${tableName}
  (CampaignId,TempletId,OpenId,Status,SendDate)
  values
  <foreach collection="list" item="item" index="index" separator="," >
    (
    #{item.campaignId},
    #{item.templetId},
    #{item.openId},
    #{item.status},
    #{item.sendDate}
    )
  </foreach>

批量插入2

  //插入表
  void insertTable(@Param("brandSystemTableName") String brandSystemTableName,
                   @Param("filedList") List<FiledNameFiledValueObj> filedList,
                   @Param("increTypeTimestampEncapsulations")List<IncreTypeTimestampEncapsulation> increTypeTimestampEncapsulations,
                   @Param("increTypeIntEncapsulations") List<IncreTypeIntEncapsulation> increTypeIntEncapsulations) throws Exception;

<insert id="insertTable"><!--collection=“”允许这样吗？测试是可以的-->
    insert into ${brandSystemTableName}
    <foreach collection="filedList" item="item" index="index" separator="," open="(" close="," >
      ${item.fieldName}
    </foreach>
    <foreach collection="increTypeTimestampEncapsulations" item="item" index="index" separator="," open="" close="," >
      ${item.icreval_type_timestampName}
    </foreach>
    <foreach collection="increTypeIntEncapsulations" item="item" index="index" separator="," open="" close="," >
      ${item.icreval_type_intName}
    </foreach>
    is_del)
    values
    <foreach collection="filedList" item="item" index="index" separator="," open="(" close="," >
      #{item.fieldValue}
    </foreach>
    <foreach collection="increTypeTimestampEncapsulations" item="item" index="index" separator="," open="" close="," >
      #{item.icreval_type_timestampValue}
    </foreach>
    <foreach collection="increTypeIntEncapsulations" item="item" index="index" separator="," open="" close="," >
      #{item.icreval_type_intValue}
    </foreach>
    0)
  </insert>

foreach:

    <select id="findProviderListByproCode1" resultType="Provider"><!--须：打开自动映射！-->
<!--List<Provider> findProviderListByproCode1(List<String> Codes) throws Exception;-->
        select * from smbms_provider
        where proCode in
        <foreach collection="list" item="code" separator="," close=");" open="("><!--list-->
            #{code}
        </foreach>
    </select>
    
    <select id="findProviderListByproCode2" resultType="Provider">
<!--List<Provider> findProviderListByproCode2(String[] Codes) throws Exception;-->
        select * from smbms_provider
        <where>
            <foreach collection="array" open="proCode in (" close=")" separator="," item="code"><!--array-->
                #{code}
            </foreach>
        </where>
    </select>
    
    <select id="findProviderListByMap" resultType="Provider"><!--须：打开自动映射！-->
<!--public abstract List<Provider> findProviderListByMap(Map<String,Object> map) throws Exception;-->
        select * from smbms_provider
        where proCode in
        <foreach collection="str" item="code" separator="," close=")" open="("><!--str是map的某一个key（这个key对应的value是集合或数组）-->
            #{code}
        </foreach>
        and createdBy=#{cBy};<!--#{cBy}中放的也是：map的某一个key（这个key对应的value不是集合或数组）-->
    </select>

批量更新：

  单个字段单条件：
  update mydata_table
  set  status=
  <foreach collection="list" item="item" index="index"
  separator=" " open="case ID" close="end">
  when #{item.id} then #{item.status}
  </foreach>
  where id in
  <foreach collection="list" index="index" item="item"
  separator="," open="(" close=")">
  #{item.id,jdbcType=BIGINT}
  </foreach>

   多个字段多条件：
 1 <update id="updateBatch" parameterType="java.util.List">
 2     update CLIENT_INFO
 3     <trim prefix="set" suffixOverrides=",">
 4         CARD_NUM=
 5         <foreach collection="list" item="item" open="case " close=" end,">
 6             when ID_CARD_NO=#{item.idCardNo} and STATUS=4 then #{item.cardNum}
 7         </foreach>
 8         create_time =
 9         <foreach collection="list" item="item" open="case " close=" end,">
10           when field2=#{item.field2} and field3=#{item.field3} then
11           <choose>
12             <when test="item.createTime!=null">
13               #{item.createTime}
14             </when>
15             <otherwise>now()</otherwise>
16           </choose>
17         </foreach>
18     </trim>
19     WHERE
20     <foreach collection="list" item="item" open="( " separator=") or (" close=" )">
21       ID_CARD_NO=#{item.idCardNo} and field3=#{item.field3} 
22     </foreach>
23 </update>

多个字段多条件 实际应用案列：
<update id="updateMemberBatch" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index">
      update guest.member set
      MobileID=#{item.mobileID},MobileEncryption=#{item.mobileEncryption}
      where MemberID=#{item.memberID} and MemberShipID=#{item.memberShipID} and Mobile=#{item.mobile}
      and MobileID is null and MobileEncryption is null and IsDeleted=0
    </foreach>
    《上面是一种方式较低级，下面是一种方式较高级 是对上面的再实现》
    update guest.member
    <trim prefix="set" suffixOverrides=",">
      MobileID=
      <foreach collection="list" item="item" open="case " close=" end,">
        when MemberID=#{item.memberID} and MemberShipID=#{item.memberShipID} and Mobile=#{item.mobile} then #{item.mobileID}
      </foreach>
      MobileEncryption=
      <foreach collection="list" item="item" open="case " close=" end,">
        when MemberID=#{item.memberID} and MemberShipID=#{item.memberShipID} and Mobile=#{item.mobile} then #{item.mobileEncryption}
      </foreach>
    </trim>
    WHERE
    MobileID is null and MobileEncryption is null and IsDeleted=0;
  </update>
《拼装后的效果：见下图》

<!--批量更新-->
  <update id="update" parameterType="java.util.List">
    update dbo.employee_leave
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="State = case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          when Id=#{item.Id} then 1
        </foreach>
      </trim>
    </trim>
    where Isdeleted = 0 and State=0;
  </update>

  <update id="updateTable">
    update ${brandSystemTableName}
    set
    <foreach collection="filedList" index="index" item="item"
             separator="," open="" close=",">
      ${item.fieldName} = #{item.fieldValue}
    </foreach>
    <foreach collection="increTypeTimestampEncapsulations" index="index" item="item"
             separator="," open="" close="">
      ${item.icreval_type_timestampName} = #{item.icreval_type_timestampValue}
    </foreach>
    <foreach collection="increTypeIntEncapsulations" index="index" item="item"
             separator="," open="" close="">
      ${item.icreval_type_intName} = #{item.icreval_type_intValue}
    </foreach>
    where
    ${unionKeyName} = #{unionKeyValue}
    and is_del = 0  <!--?-->
  </update>

mybatis ~ 批量更新（sql循环）update foreach - 又又IT - 博客园

Mysql批量更新的一个坑-&allowMultiQueries=true允许批量更新_jacker-CSDN博客_allowmultiqueries

情况1

<update id=""  parameterType="">
    update tb_thread set isDelete=0
    where threadId in ( 
    <foreach collection="list" item="item" index="index" open="" close="" separator=",">
            #{item.threadId}
    </foreach>
            )     
  </update>

情况2

数据源后加：&allowMultiQueries=true 【Mysql需要，Sqlserver不用加】

 <update id="updateJdzClassifyDataGroupId"  parameterType="com.macro.mall.project.classify.domain.JdzClassifyData">
        <foreach collection="list" item="item" index="index">
            update jdz_classify_data
            set group_id=#{item.groupId}
            where id=#{item.id};
        </foreach>
    </update>

返回Map类型

@MapKey("tablename")

/**
   * Campaigncoupontablestatus表的
   * [tablename-id 键值对]
   * 结果:{cmt.`campaignsendmessagelist_couponcode_C1039g1093_1_TEST001201701`={Id=112, tablename=cmt.`campaignsendmessagelist_couponcode_C1039g1093_1_TEST001201701`}, cmt.`campaign_couponcode_C1194_1_DKFC001201822`={Id=118, tablename=cmt.`campaign_couponcode_C1194_1_DKFC001201822`}, cmt.`campaignsendmessagelist_couponcode_C1194_1_KCKFCMS128201809`={Id=120, tablename=cmt.`campaignsendmessagelist_couponcode_C1194_1_KCKFCMS128201809`}, cmt.`campaignsendmessagelist_couponcode_C1039g1091_1_TEST001201701`={Id=110, tablename=cmt.`campaignsendmessagelist_couponcode_C1039g1091_1_TEST001201701`}, cmt.`campaignsendmessagelist_couponcode_C1195_1_DKFC001201822`={Id=122, tablename=cmt.`campaignsendmessagelist_couponcode_C1195_1_DKFC001201822`}}
   * 【注意：返回的值不只是id，应以JSONObject接收为妥。】
   * @return
   * @throws Exception
   */
  @MapKey("tablename")
  Map<String, JSONObject> findTableNameStatusMapFromCampaigncoupontablestatus() throws Exception;

应以JSONObject接收： resultType="com.alibaba.fastjson.JSONObject"

<select id="findTableNameStatusMapFromCampaigncoupontablestatus" resultType="com.alibaba.fastjson.JSONObject">
    select distinct Id,tablename from campaigncoupontablestatus
    where Status=1 and Type='1';
</select>

Map<String, JSONObject> map = dao.findTableNameStatusMapFromCampaigncoupontablestatus();
for (Map.Entry<String, JSONObject> entry : map.entrySet()) {
        String tablename = entry.getKey();
        Integer id = entry.getValue().getInteger("Id");
        //..
}

插入数据时，返回自动生成的主键id

useGeneratedKeys="true" keyProperty="id"

Mybatis调用mysql存储过程 https://blog.csdn.net/cs373616511/article/details/82890306

映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kaytune.mapper.AlarmRecordExpandMapper">
  <resultMap id="BaseResultMap" type="com.kaytune.bean.AlarmRecord">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="guid" jdbcType="VARCHAR" property="guid" />
    <result column="alarm_content" jdbcType="VARCHAR" property="alarmContent" />
    <result column="alarm_concise" jdbcType="VARCHAR" property="alarmConcise" />
    <result column="alarm_state" jdbcType="INTEGER" property="alarmState" />
    <result column="alarm_level" jdbcType="INTEGER" property="alarmLevel" />
    <result column="alarm_time" jdbcType="TIMESTAMP" property="alarmTime" />
    <result column="deal_state" jdbcType="INTEGER" property="dealState" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="create_user" jdbcType="VARCHAR" property="createUser" />
    <result column="is_del" jdbcType="INTEGER" property="isDel" />
    <result column="brand_id" jdbcType="VARCHAR" property="brandId" />
    <result column="alarm_type_id" jdbcType="INTEGER" property="alarmTypeId" />
    <result column="alarm_rule_key" jdbcType="VARCHAR" property="alarmRuleKey" />
    <result column="alarm_point_key" jdbcType="VARCHAR" property="entryPointKey" />
    <result column="alarm_point_key_val" jdbcType="VARCHAR" property="entryPointKeyVal" />
    <result column="last_update_user" jdbcType="VARCHAR" property="lastUpdateUser" />
    <result column="last_update_time" jdbcType="TIMESTAMP" property="lastUpdateTime" />
    <result column="alarm_record_id" jdbcType="INTEGER" property="alarmRecordId" />
  </resultMap>
  <sql id="Base_Column_List">
    alarm_record_id, alarm_content, alarm_state, alarm_level, alarm_time, deal_state, create_time, create_user, is_del,
    guid, brand_id, alarm_concise, alarm_type_id, alarm_rule_key, alarm_point_key , alarm_point_key_val, last_update_user, last_update_time
  </sql>


  <select id="getListByLevelAndStateInfo" resultType="com.kaytune.dto.AlarmRecordDTO">
    select
      guid as guid,
      alarm_level as alarmLevel,
      alarm_concise as alarmConcise,
      alarm_state as alarmState,
      alarm_time as alarmTime,
      alarm_record_id as alarmRecordId,
      alarm_content as alarmContent,
      create_time as createTime,
      create_user as createUser,
      is_del as isDel,
    alarm_type_id as alarmTypeId,
    applicant,
      deal_state as  dealState,
      alarm_state as state,
      alarm_level as level
    from alarm_record_detail
    where is_del != 1
      <if test="alarmState != null">
        and alarm_state = #{alarmState}
      </if>
      <if test="alarmLevel != null">
        and alarm_level = #{alarmLevel}
      </if>
      <if test="brandId != null">
        and brand_id = #{brandId}
      </if>
      <if test="guid != null and guid != ''">
        and guid = #{guid}
      </if>
    <if test="alarmTypeId != null">
      and alarm_type_id = #{alarmTypeId}
    </if>
    <if test="applicant != null and applicant != ''">
      and applicant = #{applicant}
    </if>
    order by alarm_time desc , alarm_level desc, alarm_state asc ,guid asc
  </select>


  <select id="getTodayInfo" resultMap="BaseResultMap" parameterType="String">
    select
      <include refid="Base_Column_List"/>
    from alarm_record_detail
    where create_time <![CDATA[ >= ]]> current_date and is_del = 0
    and brand_id=#{brandId}
  </select>

  <select id="getTodayByDateInfo" resultType="com.kaytune.dto.AlarmRecordDTO">
    select
      alarm_record_id as alarmRecordId,
      alarm_content as alarmContent,
      alarm_state as alarmState,
      alarm_level as alarmLevel,
      alarm_time as alarmTime,
      deal_state dealState,
      create_time as createTime,
      create_user as createUser,
      is_del as isDel
    from alarm_record
    where create_time <![CDATA[ >= ]]> #{date} and is_del = 0
  </select>
</mapper>