CCNAIVE

1 mybatis常用符号

2023/4/6

# 1 mybatis常用符号

		  特殊字符   		    替代符号
		
		     &            		 &
		
		     <            		 &lt;
		
		     >                   &gt;
		
		     "                   &quot;
		
		     '                   &apos;
     
小于等于    a<=b                 a &lt;= b      a <![CDATA[<= ]]>b

大于等于    a>=b                 a &gt;= b      a <![CDATA[>= ]]>b

不等于     a!=b             a <![CDATA[ <> ]]>b      a <![CDATA[!= ]]>b

# 1 有则update - 无则insert

使用这个必须要有主键 ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=4
# 1.1 简单使用
  insert into `enterprise_rectification_details`
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        `id`,
      </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
    </trim>
    on duplicate key update 
    <trim suffixOverrides=",">
      <if test="id != null">
        `id` = #{id,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
# 1.2 list集合的情况下使用mybatis配合ON DUPLICATE KEY UPDATE
<insert id="insertOrUpdateCameraInfoByBatch" parameterType="java.util.List">
      insert into camera_info(
          zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,
          cameraId
          )VALUES
           <foreach collection ="list" item="cameraInfo" index= "index" separator =",">
             (
                #{cameraInfo.zone1Id}, #{cameraInfo.zone1Name}, #{cameraInfo.zone2Id},
                #{cameraInfo.zone2Name}, #{cameraInfo.zone3Id}, #{cameraInfo.zone3Name},
                #{cameraInfo.zone4Id}, #{cameraInfo.zone4Name}, 
                #{cameraInfo.cameraId}, 
             )
           </foreach>
           ON DUPLICATE KEY UPDATE
          car_icon_id=values(carIconId), car_alias=values(carAlias)
    </insert>

# 2 坑

# mapper
 两种情况
 void relevantEmission(Map param);

 void relevantEmission(@Param("param") Map param);//这种会报错，xml找不到
# xml
<insert id="relevantEmission" parameterType="java.util.Map">
    insert into carbon_module_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            `id`,
        </if>
        <if test="emissionName != null">
            `emission_name`,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id},
        </if>
        <if test="emissionName != null">
            #{emissionName},
        </if>
    </trim>
</insert>

# 3 手动拼接SQL循环插入

<insert id="saveBatch” parameterType="java.util.List">
	<foreach collection="list" item="item" index="index" separator=",">
		(#(item.a), #(item.b),#litem.c, #item.d), #/item.el, #(item.f), #item.gh, #item.h), #/item.i), #(item.j), #(item.k)
	</foreach>
</insert>

# 4 判断是否存在返回Boolean

<select id="isQuoteAccountingBoundary" resultType="java.lang.Boolean">
    SELECT CASE WHEN COUNT(1) > 0 THEN true ELSE false END AS isQuote
    FROM carbon_calculate_task
    WHERE accounting_boundary_id = #{id}
</select>

# 5 where in

<if test="ids != null and !ids.empty">
    AND id IN
    <foreach item="id" collection="ids" open="(" separator="," close=")">
        #{id}
    </foreach>
</if>

#

Mybatis