使用jOOQ在PostgreSQL中进行UPSERT [英] UPSERT in PostgreSQL using jOOQ
问题描述
我正在尝试使用jOOQ库在PostgreSQL中执行UPSERT。
I am trying to perform an UPSERT in PostgreSQL using the jOOQ library.
为此，我目前正在尝试在jOOQ中实现以下SQL语句：
https://stackoverflow.com/a/6527838
For doing this I am currently trying to implement the following SQL statement in jOOQ: https://stackoverflow.com/a/6527838
到目前为止，我的代码如下：
My code looks like this so 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) {
/*
* All of this is for trying to do an 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;
for (int rowsAffected : result) {
rowsAffectedTotal += rowsAffected;
}
if (rowsAffectedTotal != 1) {
throw new RuntimeException("Upsert must only 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;
}
}
}
但是此代码无法编译，因为select（）不支持值映射：
This code does however not compile, since select() doesn't support a map of values:
SelectConditionStep<Record> insertIntoSelect = jooqProvider.getDSLContext().select(recordValues).whereNotExists(notExistsSelect);
问题
我如何提供select（）具有一组预定义值，例如： SELECT 3，'C'，'Z'？
我设法使代码正常工作。这是完整的类：
I managed to get the code working. Here is the 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) {
/*
* All of this is for trying to do an UPSERT on PostgreSQL. See:
* https://stackoverflow.com/a/6527838
*/
Map<Field<?>, Object> recordValuesMap = new HashMap<Field<?>, Object>();
for (FieldValue<Field<?>, ?> entry : recordValues) {
recordValuesMap.put(entry.getFieldName(), entry.getFieldValue());
}
List<Param<?>> params = new LinkedList<Param<?>>();
for (FieldValue<Field<?>, ?> entry : recordValues) {
params.add(val(entry.getFieldValue()));
}
List<Field<?>> fields = new LinkedList<Field<?>>();
for (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;
for (int rowsAffected : result) {
rowsAffectedTotal += rowsAffected;
}
if (rowsAffectedTotal != 1) {
throw new RuntimeException("Upsert must only 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;
}
}
}
但是感觉并不十分用 List< FieldValue< Field<？>，？>>清理recordValues 参数。有什么更好的主意吗？
It does however not feel very clean with the List<FieldValue<Field<?>, ?>> recordValues parameter. Any better ideas on how to do this?
推荐答案
jOOQ 3.7+支持PostgreSQL 9.5的 ON CONFLICT 子句：
jOOQ 3.7+ supports PostgreSQL 9.5's ON CONFLICT clause:
- https://github.com/jOOQ/jOOQ/issues/4299
- http://www.postgresql.org/docs/9.5/static/sql-insert.html
尚不支持完整的PostgreSQL供应商特定语法，但是您可以使用MySQL或H2语法，都可以使用PostgreSQL的<$来模拟c $ c> ON CONFLICT ：
The full PostgreSQL vendor-specific syntax is not yet supported, but you can use the MySQL or H2 syntax, which can both be emulated using PostgreSQL's ON CONFLICT:
DSL.using(configuration)
.insertInto(TABLE)
.columns(ID, A, B)
.values(1, "a", "b")
.onDuplicateKeyUpdate()
.set(A, "a")
.set(B, "b")
.execute();
H2 合并到..
H2 MERGE INTO ..
DSL.using(configuration)
.mergeInto(TABLE, A, B, C)
.values(1, "a", "b")
.execute();
这篇关于使用jOOQ在PostgreSQL中进行UPSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
