Skip to content

Support for for handling duplicates? #82

Open
@schanwanyu

Description

@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! :)

Activity

jeffgbutler

jeffgbutler commented on Apr 18, 2019

@jeffgbutler
Member

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

schanwanyu commented on Apr 18, 2019

@schanwanyu
Author

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

jeffgbutler commented on Apr 18, 2019

@jeffgbutler
Member

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

kdubb commented on May 30, 2020

@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

jeffgbutler commented on May 31, 2020

@jeffgbutler
Member

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

cosmoseeker

cosmoseeker commented on Feb 17, 2022

@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

jeffgbutler commented on Feb 17, 2022

@jeffgbutler
Member

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

isfong commented on Apr 24, 2023

@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

DaZuiZui commented on May 15, 2023

@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

isfong commented on May 15, 2023

@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

isfong commented on May 15, 2023

@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

isfong commented on May 15, 2023

@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!

jobmission

jobmission commented on Jun 5, 2025

@jobmission

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.

Hi ,is there any progress now? How can we expand and implement it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

      Support for for handling duplicates? · Issue #82 · mybatis/mybatis-dynamic-sql