mybatis获取自增主键插入数据后id值的两种方式

骑猪看日落 2022-03-09 02:13 294阅读 0赞

## 1. ##

## useGeneratedKeys="true" keyProperty="id" ##

<insert id="insertCommentReplay" parameterType="com.macro.mall.dto.PmsBusCommentReplay"  
    useGeneratedKeys="true" keyProperty="id"> /*id对应属性值*/
      insert into pms_comment_replay
      <trim prefix="(" suffix=")" suffixOverrides="," >
        <if test="commentId != null" >
          comment_id,
        </if>
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides="," >
        <if test="commentId != null" >
          #{commentId,jdbcType=BIGINT},
        </if>
      </trim>
    </insert>

# 2. #

## <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">       select last\_insert\_id();   </selectKey> ##

<insert id="insertCommentReplay" parameterType="com.macro.mall.dto.PmsBusCommentReplay">
      <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
        SELECT LAST_INSERT_ID()    /*order的值可以为BEFORE,插入前获取;AFTER,插入后获取;且区分大小写,只能是大写,不可为小写*/
      </selectKey>
      insert into pms_comment_replay
      <trim prefix="(" suffix=")" suffixOverrides="," >
        <if test="commentId != null" >
          comment_id,
        </if>
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides="," >
        <if test="commentId != null" >
          #{commentId,jdbcType=BIGINT},
        </if>
      </trim>
    </insert>

@Override
    public PmsBusCommentReplay insertCommentReplay(PmsBusCommentReplay param) {
        param.setCreateTime(new Date());
        param.setType(1);
        int count=commentDao.insertCommentReplay(param); //插入方法
        param.setId(param.getId()); //插入成功后获取插入后的id值,存入返回
        if(count>0){
            return param;
        }
        return null;
    }

# 3.批量插入,返回自增主键  list,collection默认值  dao层不要使用@param起别名 #

<insert id="insertFullReduction" keyProperty="id" useGeneratedKeys="true"
                parameterType="java.util.List">
            insert into pms_full_reduction (id, full_price, reduce_price)
            values
            <foreach collection="list" item="obj" separator=",">
                (null, #{obj.fullPrice}, #{obj.reducePrice})
            </foreach>
        </insert>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

3.1 遍历之前传入的List集合 ,getId就能得到id

private String setfullReductionList(List<FullReduction> fullReductionList) {
            discountMapper.insertFullReduction(fullReductionList);//批量插入满减记录
            StringBuffer sb=new StringBuffer();
            for (FullReduction reduction:fullReductionList){
                Long id = reduction.getId();
                sb.append(id).append(",");
            }
            return sb.toString().substring(0,sb.lastIndexOf(","));
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20220309/8b0918ff466c442282e396420d2ffeae.png