如何在JOOQ中实现MySQL的SUM嵌套IF条件查询？

Got it, let's break down how to replicate your MySQL query logic in jOOQ. I'll show you a couple of approaches that match your original SQL perfectly, depending on whether you prefer jOOQ's native condition API or a direct 1:1 mapping of MySQL's IF function.

First, assuming you've already set up your jOOQ code generation (so you have generated table/field references like TABLE.COLUMN1, etc.), here's the implementation:

Using jOOQ's Native when/otherwise API (Recommended) #

This is jOOQ's idiomatic way to handle conditional logic, and it's more readable in Java code:

import org.jooq.DSLContext;
import static org.jooq.impl.DSL.*;

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

var result = dsl.select(
        // Equivalent to SUM(IF(COLUMN1 > COLUMN2, COLUMN2, COLUMN1))
        sum(when(TABLE.COLUMN1.greaterThan(TABLE.COLUMN2), TABLE.COLUMN2)
                .otherwise(TABLE.COLUMN1))
                .as("sum_min_col1_col2"),
        
        // Equivalent to SUM(IF(COLUMN3 = 0, 4, 10))
        sum(when(TABLE.COLUMN3.eq(0), inline(4))
                .otherwise(inline(10)))
                .as("sum_conditional_col3")
)
.from(TABLE)
.fetchOne();

Direct Mapping to MySQL's IF Function #

If you want the generated SQL to mirror your original query exactly, you can use jOOQ's if_ function (the underscore avoids conflict with Java's if keyword):

import org.jooq.DSLContext;
import static org.jooq.impl.DSL.*;

DSLContext dsl = ...;

var result = dsl.select(
        // Exact match for SUM(IF(COLUMN1 > COLUMN2, COLUMN2, COLUMN1))
        sum(if_(TABLE.COLUMN1.greaterThan(TABLE.COLUMN2), TABLE.COLUMN2, TABLE.COLUMN1))
                .as("sum_min_col1_col2"),
        
        // Exact match for SUM(IF(COLUMN3 = 0, 4, 10))
        sum(if_(TABLE.COLUMN3.eq(0), inline(4), inline(10)))
                .as("sum_conditional_col3")
)
.from(TABLE)
.fetchOne();

Key Notes: #

• inline(4) and inline(10) tell jOOQ to treat these as literal values in the generated SQL, ensuring correct type handling.
• Both approaches will generate SQL that behaves exactly like your original query. The when/otherwise API is more portable across databases if you ever need to switch away from MySQL, while if_ is MySQL-specific but matches your original code structure.

Once you run this, you can extract the results using result.get("sum_min_col1_col2", Long.class) (or whatever numeric type matches your columns) and result.get("sum_conditional_col3", Long.class).

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