java - UPSERT in PostgreSQL using jOOQ -
i trying perform upsert in postgresql using jooq library.
for doing trying implement following sql statement in jooq: https://stackoverflow.com/a/6527838
my code looks far:
public class upsertexecutor { private static final logger logger = loggerfactory.getlogger(upsertexecutor.class); private final jooqcontextprovider jooqprovider; @inject public upsertexecutor(jooqcontextprovider jooqprovider) { preconditions.checknotnull(jooqprovider); this.jooqprovider = jooqprovider; } @transactional public <t extends record> void executeupsert(table<t> table, condition condition, map<? extends field<?>, ?> recordvalues) { /* * of trying upsert on postgresql. see: * https://stackoverflow.com/a/6527838 */ selectconditionstep<record1<integer>> notexistsselect = jooqprovider.getdslcontext().selectone().from(table).where(condition); selectconditionstep<record> insertintoselect = jooqprovider.getdslcontext().select(recordvalues).wherenotexists(notexistsselect); try { int[] result = jooqprovider.getdslcontext().batch( jooqprovider.getdslcontext().update(table).set(recordvalues).where(condition), jooqprovider.getdslcontext().insertinto(table).select(insertintoselect) ).execute(); long rowsaffectedtotal = 0; (int rowsaffected : result) { rowsaffectedtotal += rowsaffected; } if (rowsaffectedtotal != 1) { throw new runtimeexception("upsert must affect 1 row. affected: " + rowsaffectedtotal + ". table: " + table + ". condition: " + condition); } } catch (dataaccessexception e) { if (e.getcause() instanceof batchupdateexception) { batchupdateexception cause = (batchupdateexception)e.getcause(); logger.error("batch update error in upsert.", cause.getnextexception()); } throw e; } } } this code not compile, since select() doesn't support map of values:
selectconditionstep<record> insertintoselect = jooqprovider.getdslcontext().select(recordvalues).wherenotexists(notexistsselect); the question
how provide select() set of predefined values this: select 3, 'c', 'z'?
update 1
i managed code working. here complete class:
public class upsertexecutor { private static final logger logger = loggerfactory.getlogger(upsertexecutor.class); private final jooqcontextprovider jooqprovider; @inject public upsertexecutor(jooqcontextprovider jooqprovider) { preconditions.checknotnull(jooqprovider); this.jooqprovider = jooqprovider; } @transactional public <t extends record> void executeupsert(table<t> table, condition condition, list<fieldvalue<field<?>, ?>> recordvalues) { /* * of trying upsert on postgresql. see: * https://stackoverflow.com/a/6527838 */ map<field<?>, object> recordvaluesmap = new hashmap<field<?>, object>(); (fieldvalue<field<?>, ?> entry : recordvalues) { recordvaluesmap.put(entry.getfieldname(), entry.getfieldvalue()); } list<param<?>> params = new linkedlist<param<?>>(); (fieldvalue<field<?>, ?> entry : recordvalues) { params.add(val(entry.getfieldvalue())); } list<field<?>> fields = new linkedlist<field<?>>(); (fieldvalue<field<?>, ?> entry : recordvalues) { fields.add(entry.getfieldname()); } selectconditionstep<record1<integer>> notexistsselect = jooqprovider.getdslcontext().selectone().from(table).where(condition); selectconditionstep<record> insertintoselect = jooqprovider.getdslcontext().select(params).wherenotexists(notexistsselect); try { int[] result = jooqprovider.getdslcontext().batch( jooqprovider.getdslcontext().update(table).set(recordvaluesmap).where(condition), jooqprovider.getdslcontext().insertinto(table, fields).select(insertintoselect) ).execute(); long rowsaffectedtotal = 0; (int rowsaffected : result) { rowsaffectedtotal += rowsaffected; } if (rowsaffectedtotal != 1) { throw new runtimeexception("upsert must affect 1 row. affected: " + rowsaffectedtotal + ". table: " + table + ". condition: " + condition); } } catch (dataaccessexception e) { if (e.getcause() instanceof batchupdateexception) { batchupdateexception cause = (batchupdateexception)e.getcause(); logger.error("batch update error in upsert.", cause.getnextexception()); } throw e; } } } it not feel clean list<fieldvalue<field<?>, ?>> recordvalues parameter. better ideas on how this?
jooq 3.7+ supports postgresql 9.5's on conflict clause:
the full postgresql vendor-specific syntax not yet supported, can use mysql or h2 syntax, can both emulated using postgresql's on conflict:
mysql insert .. on duplicate key update:
dsl.using(configuration) .insertinto(table) .columns(id, a, b) .values(1, "a", "b") .onduplicatekeyupdate() .set(a, "a") .set(b, "b") .execute(); h2 merge ..
dsl.using(configuration) .mergeinto(table, a, b, c) .values(1, "a", "b") .execute();
Comments
Post a Comment