问题描述
我正在遍历一组对象,将它们插入到PostgreSQL数据库中,如下所示:
I am looping over a set of objects inserting them into my PostgreSQL database like this:
sql.withTransaction {
try {
sql.withBatch(1000, 'insert into category (id, version, name, parent_id) values (:id, :version, :name, :parent_id);') { stmt ->
categoryInserts.each {
try {
stmt.addBatch([id: it.id, version: 0, name: it.name, parent_id: it.parent?.id])
} catch (SQLException e) {
log.error("Category ${it.name} with parent ${it.parent?.id} could not be inserted.")
}
}
}
} catch (BatchUpdateException e) {
log.error("Categories could not be inserted.")
}
sql.commit()
}
类别表上有一个唯一的约束(名称,parent_id)。如果违反了约束,则程序将捕获BatchUpdateException,并且不会插入后续对象。不幸的是,在执行addBatch方法时并没有引发异常。
There is a unique constraint (name, parent_id) on the category table. If the constraint is violated the program catches a BatchUpdateException and subsequent objects are not inserted. Unfortunately the exception is not thrown while executing the addBatch-method.
有什么方法可以继续withBatch语句,以便忽略重复项并插入新记录吗?批处理插入不提供这种行为对我来说似乎很奇怪。
Is there any way to continue with the withBatch statement, so that duplicates are ignored and new records are inserted? It seems strange to me that a batch-Insert does not offer this behaviour.
推荐答案
我能够通过帖子解决此问题在Alexandros评论中提到。现在的解决方案如下所示:
I was able to solve this with the post mentioned in Alexandros comment. The solution now looks like this:
sql.withTransaction {
try {
sql.withBatch(1000, 'insert into category (id, version, name, parent_id) ' +
'select :id, :version, :name, :parent_id ' +
'where not exists (select name, parent_id from category where name = :name and parent_id = :parent_id);') { stmt ->
categoryInserts.each {
try {
stmt.addBatch([id: it.id, version: 0, name: it.name, parent_id: it.parent?.id])
} catch (SQLException e) {
log.error("Category ${it.name} with parent ${it.parent?.id} could not be inserted.")
}
}
}
} catch (BatchUpdateException e) {
log.error("Categories could not be inserted.", e)
}
sql.commit()
}
请注意,这是通过postgresql解决的SQL的方言。对于其他DBMS,在withBatch方法中使用SQL过程可能是一种有用的方法。
Be aware that this is solved with the postgresql dialect of SQL. For other DBMSs it might be a useful approach to use a SQL-procedure in the withBatch-method.
如果有人知道使用标准SQL进行此操作的方法,请给我一个提示。
If someone knows a way to do this with a standard-SQL, please give me a hint.
这篇关于使用withBatch处理BatchUpdateException的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!