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
