[Feature]JDBC Savemode Supports replace

[melin]

Search before asking

• [X] I had searched in the feature and found no similar feature requirement.

Description

When writing data into a relational database, data duplication needs to be considered. Both mysql and postgres support upsert syntax.

mysql:
replace into t(id, update_time) values(1, now()); 

pg:
INSERT INTO %s (id,name,data_time,remark) VALUES ( ?,?,?,? ) ON CONFLICT (id,name) DO UPDATE SET id=excluded.id,name=excluded.name,data_time=excluded.data_time,remark=excluded.remark   

oracle: https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlfornosql/adding-table-rows-using-insert-and-upsert-statements.html

db2 or sqlserver

MERGE INTO mytable AS mt USING (
    SELECT * FROM TABLE (
        VALUES 
            (123, 'text')
    )
) AS vt(id, val) ON (mt.id = vt.id)
WHEN MATCHED THEN
    UPDATE SET val = vt.val
WHEN NOT MATCHED THEN
    INSERT (id, val) VALUES (vt.id, vt.val)
; 

Usage Scenario

No response

Related issues

No response

Are you willing to submit a PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Hisoka-X]

You can use customUpdateStmt in seatunnel spark jdbc sink to fulfill this requirement. Check this https://seatunnel.apache.org/docs/2.1.2/connector/sink/Jdbc#customupdatestmt-string

[kalencaya]

You can also refer to #1843. It is a flink jdbc connector implementations which supporting replace or insert into xxx on duplicate xxx similar grammar for upsert mode, of course you can write merge grammar.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.