使用JOOQ进行删除，以指定“不在"中的多个列.条款 [英] Use JOOQ to do a delete specifying multiple columns in a "not in" clause

问题描述

我想使Postgres数据库表与Jooq记录列表同步.

I want to bring a postgres database table in sync with a list of Jooq Records.

我有一个带有复合主键的表，每行中还有三个其他值

I have a table with a composite primary key and three other values in each row

table(k1, k2, v1, v2, v3)

例如，数据可能是

Last, First, Age, Weight, Height
Smith, Joe,  21,  75,     160
Jones, Pete, 23,  80,     180

(请谅解使用名称作为主键的不良形式....)

(excuse the poor form of using names as primary keys....)

我的Java代码中也有该表的Jooq记录列表.假设有两个Java记录

I also have a list of Jooq Records for that table in my java code. Let's say that there's two java records

[
   <Smith, Joe, 21, 75, 180>,
   <Taylor, Mark, 54, 90, 170> 
]

我想发生的是当我运行一些代码时，

What I would want to happen is when I run some code,

• Joe Smith的数据库行的高度已更新
• 将为Mark Taylor插入新行
• Pete Jones的数据库行已删除

我设法创建了一个执行前两个部分的功能，但是卡在了第三个部分上.我希望在JOOQ中有一个非常简单的单班轮"，基本上可以做到

I have managed to create a function that does the first two parts, but am stuck on the third. I was hoping to have a pretty simple "one liner" in JOOQ that basically did

delete 
from my_table 
where (first, last) not in (values ('Joe', 'Smith'), ('Mark', 'Taylor')) 

但是我无法计算出相应的Java代码来做到这一点.

but I can't work out the corresponding Java code to do it.

是否有Jooq大师正在阅读此书?

Are there any Jooq-masters reading this?

我有明显的东西在俯视吗?

Is there an obvious thing I'm over-looking?

推荐答案

您的查询可以转换为以下jOOQ代码:

Your query can be translated to the following jOOQ code:

// Assuming this:
import static org.jooq.impl.DSL.*;

using(configuration)
   .deleteFrom(MY_TABLE)
   .where(row(MY_TABLE.FIRST, MY_TABLE.LAST).notIn(
        row("Joe", "Smith"),
        row("Mark", "Taylor")
   ))
   .execute();

这正在使用 DSL.row() 来构造行值表达式.请注意，ROW是PostgreSQL中的可选关键字.您只是在您的SQL示例中碰巧忽略了它.

This is using DSL.row() to construct row value expressions. Note that ROW is an optional keyword in PostgreSQL. You just happened to omit it in your SQL example.

另请参见手册中有关度数> 1的IN谓词的部分:

See also the manual's section about the IN predicate for degrees > 1:

http://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate-degree-n

这篇关于使用JOOQ进行删除，以指定“不在"中的多个列.条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！