Hello Rainer,
here is an example of a MERGE INTO from Stackoverflow:
http://stackoverflow.com/a/2692441. I dont have access to a Oracle DB
so I cant test it.
I created a ticket and will create a branch in the git repository.
- jan
Zitat von Rainer Döbele <[email protected]>:
Hi Jan,
I appreciate the idea.
What worries me a bit is that we only know how to implement it for 2 DBMS.
Ideally we would have broader support or workarounds for other
systems instead of throwing an Exception.
Perhaps we should at least give a client the opportunity to check
beforehand if this features is available.
This can be done by extending the enum DBDriverFeature.
Have you already thought what your Oracle version would look like?
But if you have thought it through carefully and still think it is a
good idea,
then you are welcome to go ahead, create a ticket and implement it.
Regards,
Rainer
-----Ursprüngliche Nachricht-----
Von: [email protected] [mailto:[email protected]]
Gesendet: Montag, 8. August 2016 13:01
An: [email protected]
Betreff: Proposal: Extending DBCommand with "insertOrUpdate" ("UPSERT")
Hello,
I'm currently writing a few sync jobs. The naive approach was to run an
UPDATE first and an INSERT with same DBCommand object if nothing was
updated. This works but is slow.
I figured there is a INSERT ... ON DUPLICATE KEY UPDATE Syntax in MySQL
(http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html)
which combines INSERT and UPDATE in one single statement. Using this I'm
able to perform a single batch satement instead of single statements. In my
first try it saved 70 % of running time.
My implementation is pretty simple, its just
public synchronized String getInsertOrUpdate() {
StringBuilder buf = new StringBuilder(getInsert());
buf.append(" ON DUPLICATE KEY UPDATE ");
long context = CTX_NAME | CTX_VALUE;
addListExpr(buf, set, context, ", ");
return buf.toString();
}
in DBCommandMySQL, but to add this in my DBSQLScript I have to cast my
DBCommand to DBCommandMySQL every time.
I think we should add a method to do this in DBCommand with a default
implementation that throws a NotSupportedException. Same in DBDatabase
(executeInserOrUpdate(...). IMO this is a good idea because its possible in
Oracle (using MERGE) and at least Postgres
(https://wiki.postgresql.org/wiki/UPSERT) - and is very useful.
Opinions?
- jan
