小太阳的知识库

Appearance

Mybatis框架 ​

Choose When使用 ​

xml
<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">
    select * from t_blog where 1 = 1 
    <choose>
        <when test="title != null">
            and title = #{title}
        </when>
        <when test="content != null">
            and content = #{content}
        </when>
        <otherwise>
            and owner = "owner1"
        </otherwise>
    </choose>
</select>

XML中集合或数组判断长度 ​

xml
集合：<if test="arr != null and arr.size() > 0"> 或 <if test="arr != null and arr.size > 0">
数组：<if test="col != null and col .length > 0">

XML中使用枚举 ​

  1. 如果是单独的枚举类(enum XXXXX)
java
@Getter
@AllArgsConstructor
public enum StatusEnum {
  STATUS_0(0, "禁用"),
  STATUS_1(1, "正常"),
  ;
  private Integer code;
  private String desc;
}

在xml.mapper文件中：
select * from table where status = ${@com.cn.entity.enums.StatusEnum@STATUS_1.code}
  1. 如果是class中包含的枚举类
java
//在一个class中，包含多个枚举类
public class AllEnums {
  @Getter
  @AllArgsConstructor
  public enum Status {
    STATUS_0(0, "禁用"),
    STATUS_1(1, "正常"),
    ;
    private final Integer code;
    private final String desc;
  }
}

在xml.mapper文件中：
select * from table where status = ${@com.cn.entity.enums.AllEnums$StatusEnum@STATUS_1.code}

foreach ​

foreach元素的属性主要有 item、index、collection、open、separator、close。

  • item：表示集合中每一个元素进行迭代时的别名，
  • index：指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
  • open：表示该语句以什么开始，
  • separator：表示在每次进行迭代之间以什么符号作为分隔 符，
  • close：表示以什么结束。

传入集合 ​

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list，如果你用@Param("")指定了名称，那么collection属性值为你指定的名称。

接口方法 ​

java
public List dynamicForeachTest(List ids);

xml ​

xml
<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
	select * from t_blog where id in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
		#{item}       
    </foreach> 
</select>

传入数组 ​

如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

接口方法 ​

java
public List dynamicForeach2Test(int[] ids);

xml ​

xml
<select id="dynamicForeach2Test" parameterType="java.util.ArrayList" resultType="Blog">
    select * from t_blog where id in
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
		#{item}
    </foreach>
</select>

传入Map ​

如果传入的参数是多个的时候，我们可以把它们封装成一个Map了

接口方法 ​

java
public List dynamicForeach3Test(Map params);

xml ​

xml
<select id="dynamicForeach3Test" parameterType="java.util.HashMap" resultType="Blog">
	select * from t_blog where title like "%"#{title}"%" and id in
	<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
	     #{item}
	</foreach>
</select>

传入Map配合Choose When使用 ​

接口方法 ​

java
List<Map<String,Object>> selectFileListByDir(@Param("params") Map<String, String> params);

xml ​

xml
<select id="selectFileListByDir" resultType="java.util.Map" parameterType="hashmap">
    SELECT * FROM RTOC_FILE_SYSTEM WHERE
    <foreach collection="params" index="key" item="value" separator="AND">
        <choose>
            <when test='key=="startTime"'>
                UPLOAD_DATE >= to_date(#{value},'yyyy-MM-dd hh24:mi:ss')
            </when>
            <when test='key=="endTime"'>
                UPLOAD_DATE &lt;= to_date(#{value},'yyyy-MM-dd hh24:mi:ss')
            </when>
            <when test='key=="UPLOAD_USER"'>
                ${key} = #{value}
            </when>
            <otherwise>
                ${key} like '%' || #{value} || '%'
            </otherwise>
        </choose>
    </foreach>
    ORDER BY UPLOAD_DATE DESC
</select>

ORACLE批量插入 ​

接口方法 ​

java
Integer insertBatchData(List<Map<String, Object>> list);

xml第一种编写方式 ​

xml
//注意加上useGeneratedKeys="false"
<insert id="insertBatchInterruptData" parameterType="java.util.List" useGeneratedKeys="false">
    insert into RTOC_CHECK_RTDBRK_COMP_RECORD
    (PK_RTDBRK_COMP_RECORD_ID,
    PK_KEYWELL_INFO_ID,
    PK_RTDBRK_COMP_COUNT_ID,
    START_DATE,
    END_DATE,
    CREATE_DATE,
    BSFLAG,
    RECOMPENSATION_STATE,
    RECOMPENSATION_REMARK,
    INTERRUPT_REASON_TYPE,
    INTERRUPT_REASON,
    P_PK_RECORD_ID)
    (
    <foreach collection="list" index="index" item="item" separator="union all">
        SELECT
        #{item.PK_RTDBRK_COMP_RECORD_ID},
        #{item.PK_KEYWELL_INFO_ID},
        #{item.PK_RTDBRK_COMP_COUNT_ID},
to_date(#{item.START_DATE},'YYYY-MM-DD HH24:MI:SS'),
to_date(#{item.END_DATE},'YYYY-MM-DD HH24:MI:SS'),
to_date(#{item.CREATE_DATE},'YYYY-MM-DD HH24:MI:SS'),
        #{item.BSFLAG},
        #{item.RECOMPENSATION_STATE},
        #{item.RECOMPENSATION_REMARK},
        #{item.INTERRUPT_REASON_TYPE},
        #{item.INTERRUPT_REASON},
        #{item.P_PK_RECORD_ID}
        FROM DUAL
    </foreach>
    )
</insert>

xml第二种编写方式 ​

xml
<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
    INSERT ALL
    <foreach item="item" index="index" collection="list">
    INTO T_APPLAUD
    (
        ID,
        USER_ID,
        BUSINESS_TYPE,
        PRODUCT_ID,
        CREATE_TIME
    ) VALUES
    (
        #{item.id, jdbcType=NUMERIC},
        #{item.userId, jdbcType=VARCHAR},
        #{item.businessType, jdbcType=VARCHAR},
        #{item.productId, jdbcType=VARCHAR},
        #{item.createdTime, jdbcType=NUMERIC} 
    )
    </foreach>
    SELECT 1 FROM DUAL
</insert>

Oracle批量插入 - 不存在时则插入 ​

接口方法 ​

java
Integer insertBatchWork(List<Map<String, Object>> list);

xml ​

xml
<insert id="insertBatchWork" parameterType="java.util.List" useGeneratedKeys="false">
    insert into RTOC_REALTIME_RTD
    (
        ID,
        WELLBORE_ID,
        START_TIME,
        END_TIME,
        START_MDEPTH,
        END_MDEPTH,
        RTD_ACTIVE_CODE,
        EMP_WHO,
        BSFLAG,
        WELL_ID
    )
    (
    <foreach collection="list" index="index" item="item" separator="union all">
        SELECT
        #{item.ID,jdbcType=VARCHAR},
        #{item.WELLBORE_ID,jdbcType=VARCHAR},
        to_date(#{item.START_TIME,jdbcType=DATE},'YYYY-MM-DD HH24:MI:SS'),
        to_date(#{item.END_TIME,jdbcType=DATE},'YYYY-MM-DD HH24:MI:SS'),
        #{item.START_MDEPTH,jdbcType=NUMERIC},
        #{item.END_MDEPTH,jdbcType=NUMERIC},
        #{item.RTD_ACTIVE_CODE,jdbcType=VARCHAR},
        #{item.EMP_WHO,jdbcType=VARCHAR},
        #{item.BSFLAG,jdbcType=VARCHAR},
        #{item.WELL_ID,jdbcType=VARCHAR}
        FROM DUAL
        WHERE NOT EXISTS (SELECT t.ID FROM RTOC_REALTIME_RTD t WHERE t.ID = #{item.ID})
    </foreach>
    )
</insert>

Oracle批量插入 - 存在则更新，不存在则插入 ​

接口方法 ​

java
Integer insertBatchWork(List<Map<String, Object>> list);

xml ​

xml
<insert id="insertBatchWork" parameterType="java.util.List">
    MERGE INTO RTOC_REALTIME_RTD T1
    USING (
        <foreach collection="list" index="index" item="item" separator="union">
            SELECT
                #{item.ID,jdbcType=VARCHAR} ID,
                #{item.WELLBORE_ID,jdbcType=VARCHAR} WELLBORE_ID,
                to_date(#{item.START_TIME,jdbcType=DATE},'YYYY-MM-DD HH24:MI:SS') START_TIME,
                to_date(#{item.END_TIME,jdbcType=DATE},'YYYY-MM-DD HH24:MI:SS') END_TIME,
                #{item.START_MDEPTH,jdbcType=NUMERIC} START_MDEPTH,
                #{item.END_MDEPTH,jdbcType=NUMERIC} END_MDEPTH,
                #{item.RTD_ACTIVE_CODE,jdbcType=VARCHAR} RTD_ACTIVE_CODE,
                #{item.EMP_WHO,jdbcType=VARCHAR} EMP_WHO,
                #{item.BSFLAG,jdbcType=VARCHAR} BSFLAG,
                #{item.WELL_ID,jdbcType=VARCHAR} WELL_ID
            FROM DUAL
        </foreach>) T2
        ON ( T1.ID = T2.ID )
    WHEN MATCHED THEN
        UPDATE SET T1.END_TIME = T2.END_TIME
    WHEN NOT MATCHED THEN
        INSERT
            (T1.ID,T1.WELLBORE_ID,T1.START_TIME,T1.END_TIME,T1.START_MDEPTH,T1.END_MDEPTH,T1.RTD_ACTIVE_CODE,T1.EMP_WHO,T1.BSFLAG,T1.WELL_ID)
        VALUES
            (T2.ID,T2.WELLBORE_ID,T2.START_TIME,T2.END_TIME,T2.START_MDEPTH,T2.END_MDEPTH,T2.RTD_ACTIVE_CODE,T2.EMP_WHO,T2.BSFLAG,T2.WELL_ID)
</insert>

Copyright © 2020-2024 版权所有 - 小太阳 - 黑ICP备2023000004号