如何使用jOOQ 3.10.x构建PSQL多记录更新的UPDATE FROM查询？

Alright, let's break down how to build a PostgreSQL-style UPDATE FROM query for bulk updates using jOOQ 3.10.x. This approach is way more efficient than looping through individual update statements, so it's perfect when you need to update multiple records at once.

First, let's recall what the raw PostgreSQL query looks like—this will help map directly to jOOQ's API:

UPDATE users u
SET email = tu.new_email, last_updated = tu.updated_at
FROM temp_users tu
WHERE u.id = tu.user_id;

Step-by-Step jOOQ Implementation #

Assuming you've already generated your jOOQ table objects (using the code generator, which is highly recommended), here's how to translate that SQL into jOOQ code:

1. Initialize your DSLContext: This is your entry point for all jOOQ operations—make sure you have it configured with your PostgreSQL connection.
2. Build the update statement: Use dsl.update() targeting your main table, then chain from() to specify the source of your update data.
3. Set the fields to update: Map each target field to the corresponding source field from your "from" table/subquery.
4. Add the matching condition: Link the main table to the source table using their shared key.
5. Execute the query: Call execute() to run the update and get the number of affected rows.

Full Example with Physical Temp Table

import static com.yourpackage.generated.Tables.*;

// Your configured DSLContext instance
DSLContext dsl = ...;

// Perform the bulk update
int updatedRows = dsl.update(USERS)
    .set(USERS.EMAIL, TEMP_USERS.NEW_EMAIL)
    .set(USERS.LAST_UPDATED, TEMP_USERS.UPDATED_AT)
    .from(TEMP_USERS)
    .where(USERS.ID.eq(TEMP_USERS.USER_ID))
    .execute();

System.out.println("Successfully updated " + updatedRows + " user records");

Example with a Subquery as the Source

If you don't have a physical temp table and instead want to use a subquery (like filtering pending updates from another table), you can do this:

// Create a subquery that returns the update data
Table<?> updateSource = dsl.select(
        USER_UPDATES.USER_ID,
        USER_UPDATES.NEW_EMAIL,
        USER_UPDATES.UPDATED_AT
    ).from(USER_UPDATES)
    .where(USER_UPDATES.STATUS.eq("PENDING"))
    .asTable("update_source");

// Use the subquery in the UPDATE FROM
int updatedRows = dsl.update(USERS)
    .set(USERS.EMAIL, updateSource.field(USER_UPDATES.NEW_EMAIL))
    .set(USERS.LAST_UPDATED, updateSource.field(USER_UPDATES.UPDATED_AT))
    .from(updateSource)
    .where(USERS.ID.eq(updateSource.field(USER_UPDATES.USER_ID)))
    .execute();

Key Notes for jOOQ 3.10.x #

• jOOQ 3.10 fully supports PostgreSQL's UPDATE FROM syntax via the from() method on the UpdateQuery type—no need for workarounds.
• Always use the generated table/field objects to avoid typos and ensure type safety.
• The execute() method returns the count of rows actually updated, which is useful for verifying the operation's success.

内容的提问来源于stack exchange，提问作者Saravanan