作者:故事我忘了^￠
个人微信公众号:程序猿的月光宝盒

Oracle整合Mybatis实现list数据插入时,存在就更新,不存在就插入

entity 对应表中字段,如不对应,在xml中起别名

/**

 * MH管理实体

 *

 * @author 金聖聰

 * @version 1.0

 * @className Excel4MH

 * @date 2020/7/21 12:19

 */

@Data

@NoArgsConstructor

@AllArgsConstructor

public class Excel4MH implements Serializable {

    private static final long serialVersionUID = 8925979180415560488L;

    /** 管理机构名 */

    private String orgName;

    /** 执行值1 */

    private Double zxz01;

    /** 执行值2 */

    private Double zxz02;

    /** 业务时间 */

    private String ywsj;

}

map Interface

/**

 * MH管理实体

 *

 * @author 金聖聰

 * @version 1.0

 * @className Excel4MHMapper

 * @date 2020/7/21 12:20

 */

public interface Excel4MHMapper {

    void save(List<Excel4MH> list);

}

map xml

<?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.bsoft.mapper.Excel4MHMapper">

  <insert id="save" parameterType="list">

    merge into EXCEL_EHR_ZFBC_006 t1

            using (

    <foreach collection="list" item="item" index="index" separator="union all">

        -- 给实体类中的字段起别名,与数据库中一致

      select #{item.orgName}                     ORGNAME,

             #{item.zxz01}                       ZXZ_01,

             #{item.zxz02}                       ZXZ_02,

        -- 字符串转日期函数

             to_date(#{item.ywsj}, 'yyyy-mm-dd') YWSJ

        -- Oracle中的dual表是一个单行单列的虚拟表 不懂想懂的 出门谷歌或百度,不想懂的这里不用改

      from dual

    </foreach>) t2

          on (

      			  --这里是判断条件 根据name和ywsj判读是否是同一条记录

                  t1.ORGNAME = t2.ORGNAME and t1.YWSJ = t2.YWSJ

                  )

          -- 如果相等

          when matched then

                  update

      				-- 这里注意: 在on中的判断条件中出现的字段这里不能出现

                  set t1.ZXZ_01 = t2.ZXZ_01,

                      t1.ZXZ_02 = t2.ZXZ_02

          --如果不相等

          when not matched then

                  insert ( ORGNAME

                         , ZXZ_01

                         , ZXZ_02

                         , YWSJ)

                  values ( t2.ORGNAME

                         , t2.ZXZ_01

                         , t2.ZXZ_02

                         , t2.YWSJ)

  </insert>

</mapper>

在查询出的result中随机抽取一条记录

entity

@Data

@NoArgsConstructor

@AllArgsConstructor

public class CheckValue4EvaluationIndex implements Serializable {

    private static final long serialVersionUID = 222790624982912873L;

    /** 上传条数 */

    private Integer scts;

    /** 抽样机构代码 */

    private String cyjgdm;

    /** 抽样机构综合评价指数 */

    private Double cyjgzhpjzs;

    /** 年份 格式 yyyy */

    private String nf;

    /** 修改标志 */

    private String xgbz;

}

map interface

/**

 * 评价指数mapper

 *

 * @author 金聖聰

 * @version 1.0

 * @className CheckValue4EvaluationIndexMapper

 * @date 2020/7/13 10:19

 */

public interface CheckValue4EvaluationIndexMapper {

    /**

     * 根据传入的年份查询

     *

     * @param time 需要查询的年份 yyyy

     * @return com.bsoft.pojo.vo.CheckValue4EvaluationIndex

     * @author 金聖聰

     * @email jinshengcong@163.com

     * @version v1.0

     * @date 2020/7/13 10:21

     */

    CheckValue4EvaluationIndex queryByTime(@Param("time") String time);

}

map xml

<?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.bsoft.mapper.CheckValue4EvaluationIndexMapper">

  <select id="queryByTime" resultType="CheckValue4EvaluationIndex">

    select (select count(1) from EXCEL_EVALUATIONINDEX where TIME = #{time}) "scts",

           jgdm                                                              "cyjgdm",

           zhpjzs                                                           "cyjgzhpjzs",

           #{time}                                                           "nf",

           '1'                                                               "xgbz"

    from (

                 select *

                 from EXCEL_EVALUATIONINDEX

                 where TIME = #{time}

                 order by dbms_random.value

                 )

    where rownum = 1

    group by jgdm, zhpjzs, JGM

  </select>

</mapper>