You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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...
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.
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 :)
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.
@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 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?
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.
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.
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:
AggregateRootsave(AggregateRootaggregateRoot);
<selectid="save"parameterType="AggregateRoot"resultMap="aggregateRootResults"flushCache="true">
INSERT INTO <includerefid="aggregateRootTable"/>( <includerefid="aggregateRootColumns"/> )
VALUES ( #{id}, #{prop1}, #{prop2}, #{prop3} )
ON CONFLICT (id) DO UPDATE SET column1 = #{prop1},
column2 = #{prop2},
column3 = #{prop3}
RETURNING <includerefid="aggregateRootColumns"/>;
<iftest="elementCollection != null and elementCollection.size > 0">
INSERT INTO <includerefid="elementCollectionTable"/> ( <includerefid="elementCollectionColumns"/> )
VALUES
<foreachcollection="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 <includerefid="elementCollectionColumns"/>;
</if>
DELETE FROM <includerefid="elementCollectionTable"/> WHERE aggregateRootId = #{id}
<foreachcollection="elementCollection"item="e"open="AND"separator="AND">
id != #{e.id}
</foreach>;
</select>
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:
AggregateRootsave(AggregateRootaggregateRoot);
<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!
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.
Activity
jeffgbutler commentedon Apr 18, 2019
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 commentedon Apr 18, 2019
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 commentedon Apr 18, 2019
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 commentedon May 30, 2020
@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 commentedon May 31, 2020
@kdubb No worries. I am thinking about some different options to make it easier to do things like this.
cosmoseeker commentedon Feb 17, 2022
@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 commentedon Feb 17, 2022
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 commentedon Apr 24, 2023
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 commentedon May 15, 2023
hope i can help u
isfong commentedon May 15, 2023
Yes, you can do it in xml, but hopefully using
mybatis-dynamic-sql
isfong commentedon May 15, 2023
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:
isfong commentedon May 15, 2023
Another elegant way is to encapsulate query support for JSON/JSONB columns with mybatis-dynamic-sql, aha!
jobmission commentedon Jun 5, 2025
Hi ,is there any progress now? How can we expand and implement it.