1

I'm inserting a map data into postgresql using mybatis in a springboot program and I want mybatis to return the generated key. The key is auto generated by a sequence and it's name is 'corp_id'. But there comes out a problem that makes me confused. Here is the right way:

<insert id="addOrUpdCorp" parameterType="java.util.Map" useGeneratedKeys="true" keyProperty="corp_id">
    insert into t_corporation
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="corp_name != null and corp_name != ''">
            corp_name,
        </if>
        <if test="corp_note != null and corp_note != ''">
            corp_note,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="corp_name != null and corp_name != ''">
            #{corp_name},
        </if>
        <if test="corp_note != null and corp_note != ''">
            #{corp_note},
        </if>
    </trim>
</insert>

And here is the wrong way:

<insert id="addOrUpdCorp" parameterType="java.util.Map" useGeneratedKeys="true" keyProperty="corp_id">
    <selectKey keyProperty="count" resultType="java.lang.Integer" order="BEFORE">
        select count(corp_id) from t_corporation where corp_id = #{corp_id}
    </selectKey>
    <if test="count > 0">
        update t_corporation
        <set>
            <if test="corp_name != null and corp_name != ''">
                corp_name = #{corp_name},
            </if>
            <if test="corp_note != null and corp_note != ''">
                corp_note = #{corp_note},
            </if>
        </set>
        where corp_id = #{corp_id}
    </if>
    <if test="count == 0">
        insert into t_corporation
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="corp_name != null and corp_name != ''">
                corp_name,
            </if>
            <if test="corp_note != null and corp_note != ''">
                corp_note,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="corp_name != null and corp_name != ''">
                #{corp_name},
            </if>
            <if test="corp_note != null and corp_note != ''">
                #{corp_note},
            </if>
        </trim>
    </if>
</insert>

Why the value of 'corp_id' in the map(after the mapper method returns) is null in the wrong way above? I just want to use update and insert operation together. Besides, the value of 'corp_id' in the map is a real number in right way. Any opinion will be appreciated.

SixSixSix
  • 25
  • 7
  • You are trying to implement the [merge a.k.a. upsert](https://en.wikipedia.org/wiki/Merge_(SQL)). It is [not easy](https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/) to make it right. You might want to check [how to do](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) that. – Roman-Stop RU aggression in UA Jan 03 '19 at 09:44
  • When you say that `cord_id` is null do you mean that it is null in the map after the mapper method returns? Is it null for both cases (insert and update) in the `wrong` query? – Roman-Stop RU aggression in UA Jan 03 '19 at 09:46
  • Yes, i mean it is null in the map after the mapper method returns.Actually the data is already in the database. – SixSixSix Jan 03 '19 at 09:51

0 Answers0