跳到主要内容

04 - 条件构造器

4.1 基础条件​

条件方法一览​

方法说明示例
isn(字段)为空column IS NULL
isnn(字段)不为空column IS NOT NULL
eq(字段, 值)等于column = ?
ne(字段, 值)不等于column <> ?
gt(字段, 值)大于column > ?
ge(字段, 值)大于等于column >= ?
lt(字段, 值)小于column < ?
le(字段, 值)小于等于column <= ?
in(字段, 值列表)在列表中column IN (val1, val2, ...)
ni(字段, 值列表)不在列表中column NOT IN (val1, val2, ...)
lk(字段, 值)模糊匹配column LIKE ? (值会被加上%)
ll(字段, 值)左模糊匹配column LIKE ? (值后加%)
lr(字段, 值)右模糊匹配column LIKE ? (值前加%)
nl(字段, 值)非模糊匹配column NOT LIKE ? (值前后加%)
bt(字段, 值1, 值2)范围查询column BETWEEN ? AND ?
nb(字段, 值1, 值2)非范围查询column NOT BETWEEN ? AND ?

使用示例​

Lambda 方式（推荐）​

@Test
public void conditionTest4_1_1() {
    DB.Pojo.select(User.class)
        // 基础比较操作
        .eq(User::getStatus, 1)           // status = 1
        .ne(User::getType, 0)             // type &lt;> 0
        .gt(User::getAge, 18)             // age > 18
        .ge(User::getScore, 60)           // score >= 60
        .lt(User::getLevel, 10)           // level &lt; 10
        .le(User::getRetryCount, 3)       // retry_count &lt;= 3

        // 空值检查
        .isn(User::getDeleteTime)         // delete_time IS NULL
        .isnn(User::getEmail)             // email IS NOT NULL

        // 范围查询
        .in(User::getId, Arrays.asList(1, 2, 3, 4, 5))      // id IN (1,2,3,4,5)
        .in(User::getId, "1,2,3,4,5")      // id IN (1,2,3,4,5)
        .ni(User::getName, "admin,root")  // name NOT IN ('admin','root')
        .ni(User::getName, Arrays.asList("admin", "root"))  // name NOT IN ('admin','root')
        .bt(User::getScore, 20, 30)     // score BETWEEN 20 AND 20

        // 模糊查询
        .lk(User::getName, "张")          // name LIKE '%张%' (模糊匹配)
        .ll(User::getPhone, "138")        // phone LIKE '138%' (左模糊匹配)
        .lr(User::getAddress, "北京")      // address LIKE '%北京' (右模糊匹配)
        .nl(User::getDescription, "测试")// description NOT LIKE '%测试%' (非模糊匹配)

        .queryList();
}
//2026-01-24 20:54:07.171 INFO  [ilFoSPaS-main] c.d.db.util.DbLogUtil:109 caller:(ConditionTest.java:39) getList 0ms sql:select * from USER t where STATUS = 1 and TYPE &lt;> 0 and AGE > 18 and SCORE >= 60 and LEVEL &lt; 10 and RETRY_COUNT &lt;= 3 and DELETE_TIME is null and EMAIL is not null and ID in (1,2,3,4,5) and ID in (1,2,3,4,5) and NAME not in ('admin','root') and NAME not in ('admin','root') and SCORE between 20 and 30 and NAME like '%张%' and PHONE like '138%' and ADDRESS like '%北京' and DESCRIPTION not like '%测试%' and IS_DELETED = 0
//输出日志说明：
// 执行时间：        2026-01-24 20:54:07.171 
// traceId+线程ID:  [ilFoSPaS-main] 
// 日志输出：        c.d.db.util.DbLogUtil:109 
// 调用处(快速定位)： caller:(ConditionTest.java:39) 
// 执行dao方法：     getList
// 执行消耗时间：     0ms 
// 可直接执行的sql:   sql:select * from USER t where STATUS = 1 and TYPE &lt;> 0 and AGE > 18 and SCO

字符串参数名​

    @Test
public void conditionTest4_1_2() {
    //驼峰参数，自动转下划线
    final List&lt;User> users = DB.Pojo.select(User.class)
            .isn("deleteTime")  // delete_time IS NULL
            .queryBeanList();
    //条件sql: where DELETE_TIME is null

    //下划线字段
    final List&lt;ResultMap> users2 = DB.Pojo.select(User.class)
            .isn("delete_time") // delete_time IS NULL
            .queryList();
    //条件sql: where DELETE_TIME is null
}

条件执行控制​

链式条件控制​

@Test
public void conditionTest4_1_3() {
    String name = "test";        // 可能为 null
    DB.Pojo.select(User.class)
        .lk(!ValUtil.isEmpty(name), User::getName, name)  // name LIKE ?
        .queryList();
    name="";
    DB.Pojo.select(User.class)
        .lk(!ValUtil.isEmpty(name), User::getName, name)  // 条件不输出
        .queryList();
}
//sql:select * from USER t where NAME like '%test%' and IS_DELETED = 0  
//sql:select * from USER t where IS_DELETED = 0

手动条件控制​

@Test
public void conditionTest4_1_4() {
    String name = "test";
    PojoQuery&lt;User> query = DB.Pojo.select(User.class);
    // 根据不同参数动态添加条件
    if (!ValUtil.isEmpty(name)) {
        query.lk(User::getName, name); // name LIKE ?
    }
    List&lt;User> users = query.queryBeanList();
    //条件sql: where NAME like '%test%'
}

4.2 逻辑组合（AND / OR）​

默认逻辑​

多个条件默认使用 AND 连接：

@Test
public void conditionTest4_2_1() {
    //多个条件默认使用 **AND** 连接
    DB.Pojo.select(User.class)
        .eq(User::getStatus, 1)
        .gt(User::getAge, 18)
        .lk(User::getName, "张")
        .queryList();
    // 条件sql： WHERE status = 1 AND age > 18 AND name LIKE '%张%'
}

OR嵌套​

  • 嵌套条件中的多个条件是or关系
@Test
public void conditionTest4_2_2() {
    DB.Pojo.select(User2.class)
        .or(w -> w
            .eq(User::getCity, "北京")
            .eq(User::getCity, "上海")
            .eq(User::getCity, "广州")
        )
        .gt(User2::getAge, 18)
        .queryList();
    // 条件sql： WHERE (city = '北京' OR city = '上海' OR city = '广州') AND age > 18
}

AND 嵌套​

  • 嵌套条件中的多个条件是and关系
@Test
public void conditionTest4_2_3() {
    DB.Pojo.select(User.class)
        .eq(User::getStatus, 1)
        .and(w -> w
            .gt(User::getAge, 18)
            .lt(User::getAge, 60)
        )
        .queryList();
    //条件sql： WHERE status = 1 AND (age > 18 AND age &lt; 60)
}

混合嵌套​

  • 复杂业务条件混合嵌套
// 场景：查询 (状态正常) 且 (VIP用户 或 (普通用户且积分>100))
@Test
public void conditionTest4_2_4() {
    DB.Pojo.select(User.class)
        .eq(User::getStatus, 1)
        .or(w -> w
            .eq(User::getVip, 1)
            .and(o -> o
                .eq(User::getVip, 0)
                .gt(User::getScore, 100)
            )
        )
        .queryList();
    // 条件sql： WHERE status = 1 AND (vip = 1 OR (vip = 0 AND score > 100))
}

4.3 使用 Condition 复用查询条件​

@Test
public void conditionTest4_3_1() {
    // 创建可复用的条件
    Condition baseCondition = Condition.where()
            .eq("status", 1)
            .gt("age", 18);

    Condition vipCondition = Condition.where()
            .eq("vip", 1)
            .gt("level", 5);
    // 应用到查询
    DB.Pojo.select(User.class)
            .where(baseCondition)
            .or(w -> w.addChildren(vipCondition))
            .queryList();
    // 复用条件   STATUS = 1 and AGE > 18
    //sql:select * from USER t where STATUS = 1 and AGE > 18 and (VIP = 1 or LEVEL > 5) and IS_DELETED = 0

    // 应用到更新
    DB.Pojo.update(User.class)
            .set("flag", 1)
            .where(baseCondition)
            .execute();
    // 复用条件   STATUS = 1 and AGE > 18
    //sql:update USER t set FLAG=1 where STATUS = 1 and AGE > 18 and IS_DELETED = 0
}

4.4 模糊查询​

LIKE 查询​

方法说明SQL 示例
lk(字段, 值)模糊匹配column LIKE ? (值会被加上%)
ll(字段, 值)左模糊匹配column LIKE ? (值后加%)
lr(字段, 值)右模糊匹配column LIKE ? (值前加%)
nl(字段, 值)非模糊匹配column NOT LIKE ? (值前后加%)

使用示例​

@Test
public void conditionTest4_4_1() {
    DB.Pojo.select(User.class)
            .lk(User::getName, "张")// 名字包含"张"
            .lr(User::getEmail, "@gmail.com")// 邮箱以 @gmail.com 结尾
            .ll(User::getPhone, "138")// 手机号以 138 开头
            .nl(User::getAddress, "测试")// 地址不包含"测试"
            .queryList();
    // 生成 SQL：
    // WHERE name LIKE '%张%'
    //   AND email LIKE '%@gmail.com'
    //   AND phone LIKE '138%'
    //   AND address NOT LIKE '%测试%'
}

条件判断​

@Test
public void conditionTest4_4_2() {
    String keyword = "x";
    DB.Pojo.select(User.class)
        .lk(!ValUtil.isEmpty( keyword), User::getName, keyword)// keyword 不为空时才添加 LIKE 条件
        .queryList();
    //sql:select * from USER t where NAME like '%x%' and IS_DELETED = 0

    keyword = null;
    DB.Pojo.select(User.class)
        .lk(!ValUtil.isEmpty( keyword), User::getName, keyword)// keyword 为空时不添加 LIKE 条件
        .queryList();
    //sql:select * from USER t where IS_DELETED = 0
}

4.5 范围查询​

方法说明示例
in(字段, 值列表)在列表中column IN (val1, val2, ...)
ni(字段, 值列表)不在列表中column NOT IN (val1, val2, ...)
bt(字段, 值1, 值2)范围查询column BETWEEN ? AND ?
nb(字段, 值1, 值2)非范围查询column NOT BETWEEN ? AND ?

IN 查询​

@Test
public void conditionTest4_5_1() {
    // 方式1：传入list或数组
    DB.Pojo.select(User.class)
        .in(User::getId, Arrays.asList(1, 2, 3, 4, 5))
        .queryList();
    // 生成 SQL：WHERE id IN (1, 2, 3, 4, 5)
    DB.Pojo.select(User.class)
        .in(User::getId, new Integer[]{1, 2, 3, 4, 5})
        .queryList();
    // 生成 SQL：WHERE id IN (1, 2, 3, 4, 5)

    // 方式2：逗号分隔的数字
    DB.Pojo.select(User.class)
        .in(User::getId, "1,2,3,4,5")
        .queryList();
    // 生成 SQL：WHERE id IN (1,2,3,4,5)

    // 方式3：逗号分隔的字符串
    DB.Pojo.select(User.class)
        .in(User::getCode, "A,B,C")
        .queryList();
    // 生成 SQL：WHERE code IN ('A','B','C')

    // 方式4：混合类型统一处理为字符串
    DB.Pojo.select(User.class)
        .in(User::getCode, "A,111,B,222")
        .queryList();
    // 生成 SQL：WHERE code IN ('A','111','B','222')
}

子查询 IN​

@Test
public void conditionTest4_5_2() {
    // 直接传入sql(注意，可能存在sql注入风险)
    DB.Pojo.select(User.class)
        .in(User::getDeptId, "sql:SELECT id FROM department WHERE status = 1")
        .queryList();
}
// 生成 SQL：WHERE dept_id IN (SELECT id FROM department WHERE status = 1)

NOT IN​

@Test
public void conditionTest4_5_3() {
    DB.Pojo.select(User.class)
        .ni(User::getStatus, Arrays.asList(0, -1))
        .queryList();
}

// 生成 SQL：WHERE status NOT IN (0, -1)

BETWEEN 查询​

@Test
public void conditionTest4_5_4() {
    DB.Pojo.select(User.class).bt(User::getAge, 18, 30).queryList();
    //sql: where AGE between 18 and 30

    DB.Pojo.select(User.class).bt(User::getAge, Arrays.asList(18, 30)).queryList();
    //sql: where AGE between 18 and 30

    DB.Pojo.select(User.class).bt(User::getAge, Arrays.asList(18, 30)).queryList();
    //sql: where AGE between 18 and 30

    DB.Pojo.select(User.class).bt(User::getAge, new Integer[]{18,30}).queryList();
    //sql: where AGE between 18 and 30

    DB.Pojo.select(User.class).bt(User::getAge, "18","30").queryList();
    //sql: where AGE between '18' and '30'

    DB.Pojo.select(User.class).bt(User::getAge, "18,30").queryList();
    //sql: where AGE between '18' and '30'

    DB.Pojo.select(User.class).bt(User::getAge, new String[]{"18","30"}).queryList();
    //sql: where AGE between '18' and '30'

    Date startDate = DateUtil.getDate("2020-01-01");
    Date endDate = DateUtil.getDate("2021-01-01");
    DB.Pojo.select(User.class).bt(User::getCreateTime, startDate, endDate).queryList();
    //sql: where CREATE_TIME between '2020-01-01 00:00:00' and '2021-01-01 00:00:00'
}

NOT BETWEEN​

@Test
public void conditionTest4_5_5() {
    DB.Pojo.select(User.class).nb(User::getScore, 0, 60).queryList();
    //sql: where SCORE not between 0 and 60
}

综合示例​

// 查询条件：
// - ID 在列表中
// - 状态不在禁用列表
// - 年龄在 18-60 之间
// - 部门在子查询结果中
@Test
public void conditionTest4_5_6() {
    DB.Pojo.select(User.class)
        .in(User::getId, "1,2,3,4,5")
        .ni(User::getStatus, Arrays.asList(0, -1))
        .bt(User::getAge, 18, 60)
        .in(User::getDeptId, "sql:SELECT id FROM dept WHERE type = 'tech'")
        .queryList();
    //sql: 
    // where ID in (1,2,3,4,5) 
    //   and STATUS not in (0,-1) 
    //   and AGE between 18 and 60 
    //   and DEPT_ID in (SELECT id FROM dept WHERE type = 'tech')
}

4.6 自定义 SQL​

sql 方法（命名参数方式）​

  • 使用 #{参数名} 作为命名参数：
  • 子查询示例
  • EXISTS 示例
@Test
public void conditionTest4_6_1() {
    DB.Pojo.select(User.class)
            .eq(User::getStatus, 1)
            .sql("age > #{minAge} AND age &lt; #{maxAge}", new JSONMap("minAge", 18, "maxAge", 60))
            .queryList();
    //sql: where STATUS = 1 and (age > 18 AND age &lt; 60)

    // 子查询示例
    DB.Pojo.select(User.class)
            .sql("id IN (SELECT user_id FROM orders WHERE amount > #{amount})", new JSONMap("amount", 1000))
            .queryList();
    //sql: where (id IN (SELECT user_id FROM orders WHERE amount > 1000))

    // EXISTS 查询
    DB.Pojo.select(User.class)
            .eq(User::getStatus, 1)
            .sql("EXISTS (SELECT 1 FROM vip WHERE user_id = t.id AND level >= #{level})", new JSONMap("level", 3))
            .queryList();
    //sql: where STATUS = 1 and (EXISTS (SELECT 1 FROM vip WHERE user_id = t.id AND level >= 3)) 
}

sql 方法（命名参数方式）​

  • 使用 #{参数名} 作为命名参数：
  • 支持带条件，参数值为空或不存在时，条件不生效
  • sql预设，支持数据库动态配置
@Test
public void conditionTest4_6_2() {
    JSONMap params = new JSONMap("minAge", 18, "maxAge", 60);

    DB.Pojo.select(User.class)
            .eq(User::getStatus, 1)
            .sql("age > #{minAge} AND age &lt; #{maxAge}", params)
            .queryList();
    //sql: where STATUS = 1 and (age > 18 AND age &lt; 60)

    //带条件，参数值为空或不存在时，条件不生效
    DB.Pojo.select(User.class)
            .eq(User::getStatus, 1)
            .sql("age > #{minAge} [AND age &lt; #{maxAge}]", new JSONMap("minAge", 18))
            .queryList();
    //sql: where STATUS = 1 and (age > 18 )

    //sql预设，支持数据库动态配置
    //&lt;sql sqlId="key.conditionTest4_6_2">&lt;![CDATA[
    //    age > #{minAge} [AND age &lt; #{maxAge}]
    //]]>&lt;/sql>
    DB.Pojo.select(User.class)
            .eq(User::getStatus, 1)
            .sql("key.conditionTest4_6_2", new JSONMap("minAge", 18))
            .queryList();
    //sql: where STATUS = 1 and (age > 18 )
}

在 Condition 中使用​

@Test
public void conditionTest4_6_3() {
    Date endDate = DateUtil.getDate("2021-01-01");
    Condition condition = Condition.where()
            .eq("status", 1)
            .sql("score > #{minScore}", new JSONMap("minScore", 60))
            .sql("create_time > #{endDate}", new JSONMap("endDate", endDate));

    DB.Pojo.select(User.class).where(condition).queryBeanList();
    //sql: sql:select * from USER t where STATUS = 1 and (score > 60) and (create_time > '2021-01-01 00:00:00') and IS_DELETED = 0
    DB.Pojo.update(User.class).set("flag", 1).where(condition).execute();
    //sql: update USER t set FLAG=1 where STATUS = 1 and (score > 60) and (create_time > '2021-01-01 00:00:00') and IS_DELETED = 0
    DB.Pojo.delete(User.class).where(condition).execute();
    //sql: update USER t set IS_DELETED=1 where STATUS = 1 and (score > 60) and (create_time > '2021-01-01 00:00:00') and IS_DELETED = 0
}

安全提示​

// ✅ 安全：使用参数化查询
.sql("name = #{name}", new JSONMap("name", userInput))

// ⚠️ 危险：直接拼接用户输入
.sql("name = '" + userInput + "'")  // 可能 SQL 注入！