Hi Jan, I've tested it on MySql and I have one problem with it.
1. The insertOrUpdate doesn't do the same as the fallback method in case of an update and if I don't define any where conditions. I have a table, with columns ID (PK) and NAME and the following initial values. ID NAME ---------------- 1 A 2 B *1. InsertOrUpdate* DBCommand cmd = db.createCommand(); cmd.set(T.ID.to <http://db.UPSERT_TEST.ID.to>(2)); cmd.set(T.NAME.to <http://db.UPSERT_TEST.NAME.to>("C")); db.executeInsertOrUpdate(cmd, conn); After executing it, the table looks OK: ID NAME ---------------- 1 A 2 C *2. Fallback => try update, then insert* cmd.set(T.ID.to <http://db.UPSERT_TEST.ID.to>(2)); cmd.set(T.NAME.to <http://db.UPSERT_TEST.NAME.to>("C")); int count = db.executeUpdate(cmd, conn); if (count < 1) { count += db.executeInsert(cmd, conn); } This throws a Duplicate key exception in the executeUpdate() method, because it tries to update ALL records (there is no update condition defined). If I add the condition cmd.where(T.ID.i <http://db.upsert_test.name.to/>s(2), it's OK, but I think it's problematic that insertOrUpdate behaves differently with different drivers. (Maybe the where conditions on the PK and UQ constraints should be added to the update statement automatically in the fallback method?) 2. MSSQL implementation On MSSQL I see much harder to implement because you have to make some kind of join with a temporary table on the PKs and UQs. (MERGE) ----------- Regards, Ivan <[email protected]> ezt írta (időpont: 2016. aug. 9., K, 7:57): > I pushed it to the EMPIREDB-247 branch. > > Ivan: can you test if that implementation works for you usecase, too? > > - jan > > Zitat von [email protected]: > > > 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 > > > >
