Support for for handling duplicates?

[schanwanyu]

I'm using MyBatis Dynamic SQL with MySQL, and would like to know whether there are any methods to handle duplicates, such that I can do something similar to:

INSERT INTO ...... ON DUPLICATE KEY UPDATE...
or
INSERT IGNORE INTO...

Thanks! :)

[jeffgbutler]

The library doesn't support those types of inserts. You could alter the insert statement after it is generated, but it might be simpler to just code the insert statement manually in this case.

[schanwanyu]

Oh that's unfortunate... any chance your team will add that functionality? I do currently code it manually on my xml mapper, so it's a little less elegant of a solution. But hey, it works, so no biggie if that's not in line with the library's plans. I appreciate the response though :)

[jeffgbutler]

I'm trying very hard to keep the library vendor neutral - we don't really have the time to support all the different SQL dialects. If something is missing that is relatively standard, then I will consider adding it. I did this recently for limit/offset support because it is widely, but not universally, supported.

I'm not sure about this - does any database other than MySQL support this syntax?

I will also say that the insert statements are the hardest to extend in the current version of the library. I will look into adding some extension points where you might be able to plug in to the code generation cycle and make some changes.

[kdubb]

@jeffgbutler Apologies for the resurrection but I was just investigating "upsert" support and found this.

PostgreSQL also supports a very similar syntax using INSERT INTO ... ON CONFLICT (<keys or constraint>) DO UPDATE.

To your point this means the library would have to wade into the vendor specific territory. Even so these statements are great and finding a way to integrate them would be awesome.

[jeffgbutler]

@kdubb No worries. I am thinking about some different options to make it easier to do things like this.

[cosmoseeker]

@jeffgbutler I hava found the solution for several days. Now I know that it has not been supported yet.
The issue has been opening for such long time, if it possible to know whether there is a solution now?

[jeffgbutler]

There is no solution in the library now. As far as I know, there isn't a vendor neutral way to handle this issue. My current thinking to provide some kind of hook to make it easier to modify the generated SQL, but that thought isn't fully formed yet.

[isfong]

There is no solution in the library now. As far as I know, there isn't a vendor neutral way to handle this issue. My current thinking to provide some kind of hook to make it easier to modify the generated SQL, but that thought isn't fully formed yet.

Supporting it would fit well with the concept of saving aggregate roots in the DDD domain, and would require additional SQL save child entities, which I use for now in xml <select> tag completes saving the aggregation root and storing the child entities in batches, which is not very elegant.

[DaZuiZui]

hope i can help u

<insert id="testDebugFunction1" parameterType="UrParameterType">
  INSERT INTO your_table (c1, c2, c3)
  VALUES (#{p1}, #{p2}, #{p3})
  ON DUPLICATE KEY UPDATE column1 = #{p1}, column2 = #{p2}, column3 = #{p3}
</insert>

<insert id="testDebugFunction2" parameterType="UrParameterType">
  INSERT IGNORE INTO your_table (c1, c2, c3)
  VALUES (#{p1}, #{p2}, #{p3})
</insert>

[isfong]

hope i can help u

<insert id="testDebugFunction1" parameterType="UrParameterType">
  INSERT INTO your_table (c1, c2, c3)
  VALUES (#{p1}, #{p2}, #{p3})
  ON DUPLICATE KEY UPDATE column1 = #{p1}, column2 = #{p2}, column3 = #{p3}
</insert>

<insert id="testDebugFunction2" parameterType="UrParameterType">
  INSERT IGNORE INTO your_table (c1, c2, c3)
  VALUES (#{p1}, #{p2}, #{p3})
</insert>

Yes, you can do it in xml, but hopefully using mybatis-dynamic-sql

[isfong]

Saving aggregation roots in an RDBMS is a hassle, and to avoid using JPA annotations, you have to do a lot of things with mybatis, such as implementing the save method:

AggregateRoot save(AggregateRoot aggregateRoot);

<select id="save" parameterType="AggregateRoot" resultMap="aggregateRootResults" flushCache="true">
        INSERT INTO <include refid="aggregateRootTable"/>( <include refid="aggregateRootColumns"/> )
        VALUES ( #{id}, #{prop1}, #{prop2}, #{prop3} )
        ON CONFLICT (id) DO UPDATE SET column1 = #{prop1},
                                       column2 = #{prop2},
                                       column3 = #{prop3}
        RETURNING <include refid="aggregateRootColumns"/>;

        <if test="elementCollection != null and elementCollection.size > 0">
            INSERT INTO <include refid="elementCollectionTable"/> ( <include refid="elementCollectionColumns"/> )
            VALUES
            <foreach collection="elementCollection" item="e" separator=",">
                (#{e.id}, #{id}, #{e.prop1}, #{e.prop2}, #{e.prop3})
            </foreach>
            ON CONFLICT (id) DO UPDATE SET column1 = EXCLUDED.prop1,
                                           column2 = EXCLUDED.prop2,
                                           column3 = EXCLUDED.prop3
            RETURNING <include refid="elementCollectionColumns"/>;
        </if>

        DELETE FROM <include refid="elementCollectionTable"/> WHERE aggregateRootId = #{id}
        <foreach collection="elementCollection" item="e" open="AND" separator="AND">
            id != #{e.id}
        </foreach>;
    </select>

[isfong]

Saving aggregation roots in an RDBMS is a hassle, and to avoid using JPA annotations, you have to do a lot of things with mybatis, such as implementing the save method:

AggregateRoot save(AggregateRoot aggregateRoot);

<select id="save" parameterType="AggregateRoot" resultMap="aggregateRootResults" flushCache="true">
        INSERT INTO <include refid="aggregateRootTable"/>( <include refid="aggregateRootColumns"/> )
        VALUES ( #{id}, #{prop1}, #{prop2}, #{prop3} )
        ON CONFLICT (id) DO UPDATE SET column1 = #{prop1},
                                       column2 = #{prop2},
                                       column3 = #{prop3}
        RETURNING <include refid="aggregateRootColumns"/>;

        <if test="elementCollection != null and elementCollection.size > 0">
            INSERT INTO <include refid="elementCollectionTable"/> ( <include refid="elementCollectionColumns"/> )
            VALUES
            <foreach collection="elementCollection" item="e" separator=",">
                (#{e.id}, #{id}, #{e.prop1}, #{e.prop2}, #{e.prop3})
            </foreach>
            ON CONFLICT (id) DO UPDATE SET column1 = EXCLUDED.prop1,
                                           column2 = EXCLUDED.prop2,
                                           column3 = EXCLUDED.prop3
            RETURNING <include refid="elementCollectionColumns"/>;
        </if>

        DELETE FROM <include refid="elementCollectionTable"/> WHERE aggregateRootId = #{id}
        <foreach collection="elementCollection" item="e" open="AND" separator="AND">
            id != #{e.id}
        </foreach>;
    </select>

Another elegant way is to encapsulate query support for JSON/JSONB columns with mybatis-dynamic-sql, aha！