第四章 数据查询-类型安全与灵活性¶

// 一个典型的现代企业应用查询场景
public List<OrderSummaryDTO> findOrdersWithComplexCriteria(OrderQueryCriteria criteria) {
    // 1. 动态的查询条件（可能有10+个过滤条件）
    // 2. 关联多个实体（订单、客户、商品、支付记录等）
    // 3. 复杂的统计计算（订单总额、商品数量、折扣金额等）
    // 4. 权限控制过滤
    // 5. 数据分页与排序
    // 6. 可能的全文搜索需求
    // ...
}

// 原生SQL示例
String sql = "SELECT o.id, o.order_number, c.name AS customer_name, " +
             "SUM(i.quantity * i.price) AS total_amount " +
             "FROM orders o " +
             "JOIN customers c ON o.customer_id = c.id " +
             "JOIN order_items i ON o.id = i.order_id " +
             "WHERE o.status = ? AND o.created_time > ? " +
             "GROUP BY o.id, o.order_number, c.name";

List<Map<String, Object>> results = jdbcTemplate.queryForList(
    sql, OrderStatus.PAID.name(), LocalDateTime.now().minusDays(30)
);

// JPA Criteria API示例
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Order> order = query.from(Order.class);
Join<Order, Customer> customer = order.join("customer");
Join<Order, OrderItem> items = order.join("items");

query.multiselect(
    order.get("id"),
    order.get("orderNumber"),
    customer.get("name").alias("customerName"),
    cb.sum(cb.prod(items.get("quantity"), items.get("price"))).alias("totalAmount")
);

query.where(
    cb.equal(order.get("status"), OrderStatus.PAID),
    cb.greaterThan(order.get("createdTime"), LocalDateTime.now().minusDays(30))
);

query.groupBy(order.get("id"), order.get("orderNumber"), customer.get("name"));

List<Tuple> results = entityManager.createQuery(query).getResultList();

// Jimmer类型安全查询示例
BookTable book = Tables.BOOK_TABLE;
List<Book> javaBooks = sqlClient.createQuery(book)
    .where(book.name().like("%Java%"))
    .where(book.price().between(
        new BigDecimal("50"), 
        new BigDecimal("200")
    ))
    .select(book)
    .execute();

// Jimmer流畅API示例
OrderTable order = Tables.ORDER_TABLE;
CustomerTable customer = Tables.CUSTOMER_TABLE;
OrderItemTable item = Tables.ORDER_ITEM_TABLE;

List<OrderView> orders = sqlClient.createQuery(order)
    .leftJoin(customer, customer.id().eq(order.customerId()))
    .leftJoin(item, item.orderId().eq(order.id()))
    .where(order.status().eq(OrderStatus.PAID))
    .where(order.createdTime().gt(LocalDateTime.now().minusDays(30)))
    .groupBy(order.id())
    .select(
        order.fetch(
            OrderFetcher.$.orderNumber()
                .customer(CustomerFetcher.$.name())
                .items(OrderItemFetcher.$.price().quantity())
        )
    )
    .execute();

// Jimmer动态查询示例
public List<Book> searchBooks(BookQuery query) {
    BookTable book = Tables.BOOK_TABLE;
    return sqlClient.createQuery(book)
        .whereIf(
            query.getName() != null && !query.getName().isEmpty(),
            () -> book.name().like("%" + query.getName() + "%")
        )
        .whereIf(
            query.getMinPrice() != null && query.getMaxPrice() != null,
            () -> book.price().between(query.getMinPrice(), query.getMaxPrice())
        )
        .whereIf(
            query.getCategoryId() != null,
            () -> book.category().id().eq(query.getCategoryId())
        )
        .orderBy(book.price().desc())
        .select(book)
        .execute();
}

// Jimmer对象图查询示例
BookTable book = Tables.BOOK_TABLE;
List<Book> booksWithAuthorAndStore = sqlClient.createQuery(book)
    .where(book.price().ge(new BigDecimal("100")))
    .select(
        book.fetch(
            BookFetcher.$.name()
                .price()
                .author(AuthorFetcher.$.name().gender())
                .store(StoreFetcher.$.name().address())
        )
    )
    .execute();

// 数据库优化示例
BookTable book = Tables.BOOK_TABLE;
Page<Book> pagedBooks = sqlClient.createQuery(book)
    .where(book.category().eq("编程"))
    .orderBy(book.publishTime().desc())
    .select(book)
    .fetchPage(0, 20);

// 字符串拼接SQL - 容易出现SQL注入风险
String sql = "SELECT * FROM t_book WHERE category = '" + category + "'";

// 预编译SQL - 字段名称错误无法在编译期检测
String sql = "SELECT * FROM t_book WHERE categry = ?"; // 注意这里categry拼写错误

// ORM框架属性映射 - 属性名修改后查询可能失效
Criteria criteria = session.createCriteria(Book.class);
criteria.add(Restrictions.eq("categoy", "编程语言")); // 属性名称错误

@Test
@DisplayName("测试类型安全查询的编译时安全保障")
void testTypeSafeQuerySafety() {
    // 使用类型安全的表达式API
    BookTable book = BookTable.$;

    // 编译时类型安全的查询构建
    List<Book> programmingBooks = sqlClient.createQuery(book)
        .where(book.category().eq("编程语言")) // 属性名自动补全，编译时检查
        .where(book.price().ge(new BigDecimal("50"))) // 类型安全，不能用字符串比较数值
        .orderBy(book.publishTime().desc()) // 排序字段类型安全
        .select(book) // 返回类型安全
        .execute();

    // 验证查询结果
    assertThat(programmingBooks).isNotEmpty();
    assertThat(programmingBooks).allMatch(b -> b.category().equals("编程语言"));
}

@Test
@DisplayName("测试查询API的架构设计")
void testQueryApiArchitecture() {
    BookTable book = BookTable.$;

    // 1. 演示查询阶段的类型安全
    // 每个阶段返回的是特定类型的对象，确保链式调用的类型安全
    var configurationStage = sqlClient.createQuery(book);
    var whereStage = configurationStage.where(book.category().eq("编程语言"));
    var selectStage = whereStage.select(book);
    List<Book> books = selectStage.execute();

    // 2. 验证结果
    assertThat(books).isNotEmpty();
    assertThat(books).allMatch(b -> b.category().equals("编程语言"));
}

@Test
@DisplayName("测试查询所有记录")
void testFindAll() {
    // 查询所有测试图书
    BookTable table = BookTable.$;
    List<Book> books = sqlClient.createQuery(table)
        .where(table.id().like(testPrefix + "-%"))
        .select(table)
        .execute();

    // 验证结果
    assertThat(books).isNotEmpty();
    assertThat(books).hasSize(3);
    assertThat(books).allMatch(book -> book.id().startsWith(testPrefix));
}

@Test
@DisplayName("测试根据ID查询单条记录")
void testFindById() {
    // 根据ID查询图书
    String bookId = testPrefix + "-book-1";
    Book book = sqlClient.findById(Book.class, bookId);

    // 验证结果
    assertThat(book).isNotNull();
    assertThat(book.id()).isEqualTo(bookId);
    assertThat(book.name()).contains("Java编程思想");
}

@Test
@DisplayName("测试简单条件查询")
void testSimpleConditionQuery() {
    // 按价格区间查询图书
    BookTable table = BookTable.$;
    List<Book> books = sqlClient.createQuery(table)
        .where(table.id().like(testPrefix + "-%"))
        .where(table.price().between(
            new BigDecimal("50"), 
            new BigDecimal("100")
        ))
        .select(table)
        .execute();

    // 验证结果
    assertThat(books).hasSize(2);

    // 验证每本书的价格都在指定区间内
    assertThat(books).allMatch(book -> 
        book.price().compareTo(new BigDecimal("50")) >= 0 &&
        book.price().compareTo(new BigDecimal("100")) <= 0
    );
}

@Test
@DisplayName("测试排序操作")
void testOrderBy() {
    // 按价格降序查询所有测试图书
    BookTable table = BookTable.$;
    List<Book> books = sqlClient.createQuery(table)
        .where(table.id().like(testPrefix + "-%"))
        .orderBy(table.price().desc())
        .select(table)
        .execute();

    // 验证顺序是否正确（价格降序）
    for (int i = 0; i < books.size() - 1; i++) {
        assertThat(books.get(i).price().compareTo(books.get(i + 1).price())).isGreaterThanOrEqualTo(0);
    }
}

@Test
@DisplayName("测试查询结果的映射转换")
void testResultMapping() {
    BookTable book = BookTable.$;

    // 1. 只选择ID字段
    List<String> bookIds = sqlClient.createQuery(book)
        .where(book.id().like(testPrefix + "-%"))
        .select(book.id())
        .execute();

    assertThat(bookIds).hasSize(3);
    assertThat(bookIds).allMatch(id -> id.startsWith(testPrefix));

    // 2. 选择多个字段到简单DTO
    List<Book> books = sqlClient.createQuery(book)
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 手动映射到DTO
    List<BookView> views = books.stream()
        .map(b -> new BookView(b.id(), b.name(), b.price()))
        .toList();

    // 验证结果
    assertThat(views).hasSize(3);
    assertThat(views).allMatch(v -> 
        v.id() != null && 
        v.name() != null && 
        v.price() != null
    );
}

// 直接映射到元组
List<Tuple2<String, BigDecimal>> idAndPrices = sqlClient.createQuery(book)
    .where(book.id().like(testPrefix + "-%"))
    .select(book.id(), book.price())
    .execute();

// 使用TypedArray映射到多个字段
List<Object[]> rows = sqlClient.createQuery(book)
    .where(book.id().like(testPrefix + "-%"))
    .select(
        book.id(),
        book.name(),
        book.price()
    )
    .execute();

// 动态投影到自定义DTO
class BookDto {
    private String id;
    private String name;
    private BigDecimal price;
    // 构造函数和getter/setter
}

List<BookDto> dtos = sqlClient.createQuery(book)
    .where(book.id().like(testPrefix + "-%"))
    .select(book.id(), book.name(), book.price())
    .map((id, name, price) -> {
        BookDto dto = new BookDto();
        dto.setId(id);
        dto.setName(name);
        dto.setPrice(price);
        return dto;
    })
    .execute();

@Test
@DisplayName("测试异常处理")
void testNullSafety() {
    // 查询不存在的记录
    String nonExistentId = testPrefix + "-non-existent";
    Book book = sqlClient.findById(Book.class, nonExistentId);

    // 验证结果为null
    assertThat(book).isNull();
}

// 使用orElse提供默认值
Book bookOrDefault = sqlClient.findById(Book.class, nonExistentId)
    .orElse(() -> createDefaultBook());

// 使用orElseThrow在找不到时抛出异常
Book bookOrThrow = sqlClient.findById(Book.class, nonExistentId)
    .orElseThrow(() -> new BookNotFoundException(nonExistentId));

// 使用fetchOne获取单条记录（期望最多一条记录）
Book singleBook = sqlClient.createQuery(book)
    .where(book.id().eq(bookId))
    .select(book)
    .fetchOne();

// 使用fetchOptional获取Optional包装的结果
Optional<Book> optionalBook = sqlClient.createQuery(book)
    .where(book.id().eq(bookId))
    .select(book)
    .fetchOptional();

// 传统查询构建方案中的问题
String sql = "SELECT * FROM books WHERE 1=1 ";
List<Object> params = new ArrayList<>();

if (category != null) {
    sql += "AND category = ? ";
    params.add(category);
}

if (minPrice != null && maxPrice != null) {
    sql += "AND price BETWEEN ? AND ? ";
    params.add(minPrice);
    params.add(maxPrice);
}

if (keyword != null) {
    sql += "AND name LIKE ? ";
    params.add("%" + keyword + "%"); // 潜在的SQL注入风险
}

if (hasDescription) {
    sql += "AND description IS NOT NULL ";
} else {
    sql += "AND description IS NULL ";
}

// 执行查询...这里省略

@Test
@DisplayName("测试等值比较与不等比较")
void testEqualityComparisons() {
    BookTable book = BookTable.$;

    // 1. 等值比较 (=)
    List<Book> javaBooks = sqlClient.createQuery(book)
        .where(book.category().eq("编程语言"))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(javaBooks).hasSize(3);
    assertThat(javaBooks).allMatch(b -> b.category().equals("编程语言"));

    // 2. 不等比较 (!=)
    List<Book> nonJavaBooks = sqlClient.createQuery(book)
        .where(book.category().ne("编程语言"))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(nonJavaBooks).hasSize(3);
    assertThat(nonJavaBooks).noneMatch(b -> b.category().equals("编程语言"));

    // 3. NULL值比较
    List<Book> booksWithDescription = sqlClient.createQuery(book)
        .where(book.description().isNotNull())
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    List<Book> booksWithoutDescription = sqlClient.createQuery(book)
        .where(book.description().isNull())
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(booksWithDescription.size() + booksWithoutDescription.size()).isEqualTo(6);
}

@Test
@DisplayName("测试范围比较操作符")
void testRangeComparisons() {
    BookTable book = BookTable.$;

    // 1. 大于比较 (>)
    List<Book> expensiveBooks = sqlClient.createQuery(book)
        .where(book.price().gt(new BigDecimal("100")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(expensiveBooks).isNotEmpty();
    assertThat(expensiveBooks).allMatch(b -> b.price().compareTo(new BigDecimal("100")) > 0);

    // 2. 大于等于比较 (>=)
    List<Book> notCheapBooks = sqlClient.createQuery(book)
        .where(book.price().ge(new BigDecimal("100")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(notCheapBooks).isNotEmpty();
    assertThat(notCheapBooks).allMatch(b -> b.price().compareTo(new BigDecimal("100")) >= 0);

    // 3. 小于比较 (<)
    List<Book> cheapBooks = sqlClient.createQuery(book)
        .where(book.price().lt(new BigDecimal("80")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(cheapBooks).isNotEmpty();
    assertThat(cheapBooks).allMatch(b -> b.price().compareTo(new BigDecimal("80")) < 0);

    // 4. 小于等于比较 (<=)
    List<Book> affordableBooks = sqlClient.createQuery(book)
        .where(book.price().le(new BigDecimal("80")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(affordableBooks).isNotEmpty();
    assertThat(affordableBooks).allMatch(b -> b.price().compareTo(new BigDecimal("80")) <= 0);

    // 5. 范围比较 (BETWEEN)
    List<Book> midRangeBooks = sqlClient.createQuery(book)
        .where(book.price().between(new BigDecimal("80"), new BigDecimal("120")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(midRangeBooks).isNotEmpty();
    assertThat(midRangeBooks).allMatch(b -> 
        b.price().compareTo(new BigDecimal("80")) >= 0 && 
        b.price().compareTo(new BigDecimal("120")) <= 0
    );
}

@Test
@DisplayName("测试字符串模糊匹配")
void testStringPatternMatching() {
    BookTable book = BookTable.$;

    // 1. 简单的LIKE匹配
    List<Book> javaBooks = sqlClient.createQuery(book)
        .where(book.name().like("%Java%"))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(javaBooks).isNotEmpty();
    assertThat(javaBooks).allMatch(b -> b.name().contains("Java"));

    // 2. 以特定字符串开始
    List<Book> booksStartingWithE = sqlClient.createQuery(book)
        .where(book.name().like("E%"))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(booksStartingWithE).isNotEmpty();
    assertThat(booksStartingWithE).allMatch(b -> b.name().startsWith("E"));

    // 3. 以特定字符串结束
    List<Book> booksEndingWithA = sqlClient.createQuery(book)
        .where(book.name().like("%想"))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果 (应该包含Java编程思想)
    assertThat(booksEndingWithA).isNotEmpty();
    assertThat(booksEndingWithA).allMatch(b -> b.name().endsWith("想"));

    // 4. 使用不同的LIKE模式
    List<Book> booksWithPattern = sqlClient.createQuery(book)
        .where(book.name().like("SQL%", LikeMode.EXACT)) // 精确匹配模式
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(booksWithPattern).hasSize(1);
    assertThat(booksWithPattern.get(0).name()).startsWith("SQL");
}

// 传统方案中的条件组合
StringBuilder sql = new StringBuilder("SELECT * FROM products WHERE 1=1 ");
List<Object> params = new ArrayList<>();

// AND条件
if (minPrice != null && maxPrice != null) {
    sql.append("AND (price >= ? AND price <= ?) ");
    params.add(minPrice);
    params.add(maxPrice);
}

// OR条件
if (keywords != null && !keywords.isEmpty()) {
    sql.append("AND (");
    for (int i = 0; i < keywords.size(); i++) {
        if (i > 0) sql.append(" OR ");
        sql.append("name LIKE ?");
        params.add("%" + keywords.get(i) + "%");
    }
    sql.append(") ");
}

// NOT条件
if (excludedCategories != null && !excludedCategories.isEmpty()) {
    sql.append("AND category_id NOT IN (");
    for (int i = 0; i < excludedCategories.size(); i++) {
        if (i > 0) sql.append(", ");
        sql.append("?");
        params.add(excludedCategories.get(i));
    }
    sql.append(") ");
}

// 执行查询...

@Test
@DisplayName("测试逻辑运算符 - AND")
void testLogicalAnd() {
    BookTable book = BookTable.$;

    // 使用AND组合多个条件
    List<Book> javaAndExpensive = sqlClient.createQuery(book)
        .where(
            book.name().like("%Java%").and(
                book.price().gt(new BigDecimal("85"))
            )
        )
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(javaAndExpensive).isNotEmpty();
    assertThat(javaAndExpensive).allMatch(b -> 
        b.name().contains("Java") && 
        b.price().compareTo(new BigDecimal("85")) > 0
    );

    // 使用链式调用方式组合AND条件
    List<Book> javaAndExpensiveAlt = sqlClient.createQuery(book)
        .where(book.name().like("%Java%"))
        .where(book.price().gt(new BigDecimal("85")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证两种方式的结果一致
    assertThat(javaAndExpensive.size()).isEqualTo(javaAndExpensiveAlt.size());
}

@Test
@DisplayName("测试逻辑运算符 - OR")
void testLogicalOr() {
    BookTable book = BookTable.$;

    // 使用OR组合多个条件
    List<Book> javaOrDatabase = sqlClient.createQuery(book)
        .where(
            Predicate.or(
                book.name().like("%Java%"),
                book.name().like("%SQL%")
            )
        )
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(javaOrDatabase).isNotEmpty();
    assertThat(javaOrDatabase).allMatch(b -> 
        b.name().contains("Java") || b.name().contains("SQL")
    );

    // 使用Predicate.or方法的另一种形式
    List<Book> javaOrDatabaseAlt = sqlClient.createQuery(book)
        .where(book.name().like("%Java%").or(book.name().like("%SQL%")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证两种方式的结果一致
    assertThat(javaOrDatabase.size()).isEqualTo(javaOrDatabaseAlt.size());
}

@Test
@DisplayName("测试逻辑运算符 - NOT")
void testLogicalNot() {
    BookTable book = BookTable.$;

    // 使用NOT反转条件
    List<Book> notJavaBooks = sqlClient.createQuery(book)
        .where(book.name().like("%Java%").not())
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(notJavaBooks).isNotEmpty();
    assertThat(notJavaBooks).noneMatch(b -> b.name().contains("Java"));

    // 使用另一种NOT形式
    List<Book> notJavaBooksAlt = sqlClient.createQuery(book)
        .where(Predicate.not(book.name().like("%Java%")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证两种方式的结果一致
    assertThat(notJavaBooks.size()).isEqualTo(notJavaBooksAlt.size());
}

// 错误理解：(name包含Java AND 价格>100) OR 类别是编程语言
// 正确理解：name包含Java AND (价格>100 OR 类别是编程语言)
Predicate wrongCondition = book.name().like("%Java%").and(book.price().gt(new BigDecimal("100")))
                            .or(book.category().eq("编程语言"));

// 正确表达第一种逻辑
Predicate correctCondition1 = Predicate.or(
    book.name().like("%Java%").and(book.price().gt(new BigDecimal("100"))),
    book.category().eq("编程语言")
);

// 正确表达第二种逻辑
Predicate correctCondition2 = book.name().like("%Java%").and(
    Predicate.or(
        book.price().gt(new BigDecimal("100")),
        book.category().eq("编程语言")
    )
);

@Test
@DisplayName("测试复杂条件组合")
void testComplexConditionCombination() {
    BookTable book = BookTable.$;

    // 构建复杂的查询条件: (name包含Java OR 价格>100) AND (category是编程语言 OR name包含SQL)
    Predicate condition = Predicate.and(
        Predicate.or(
            book.name().like("%Java%"),
            book.price().gt(new BigDecimal("100"))
        ),
        Predicate.or(
            book.category().eq("编程语言"),
            book.name().like("%SQL%")
        )
    );

    // 添加测试数据过滤条件
    condition = condition.and(book.id().like(testPrefix + "-%"));

    // 执行查询
    List<Book> books = sqlClient.createQuery(book)
        .where(condition)
        .select(book)
        .execute();

    // 验证结果
    assertThat(books).isNotEmpty();
    assertThat(books).allMatch(b -> 
        (b.name().contains("Java") || b.price().compareTo(new BigDecimal("100")) > 0) &&
        (b.category().equals("编程语言") || b.name().contains("SQL"))
    );
}

// 传统IN查询实现
StringBuilder sql = new StringBuilder("SELECT * FROM products WHERE 1=1 ");
List<Object> params = new ArrayList<>();

if (brands != null && !brands.isEmpty()) {
    sql.append("AND brand_id IN (");
    for (int i = 0; i < brands.size(); i++) {
        if (i > 0) sql.append(", ");
        sql.append("?");
        params.add(brands.get(i));
    }
    sql.append(") ");
}

// 执行查询...省略

@Test
@DisplayName("测试IN查询")
void testInQuery() {
    BookTable book = BookTable.$;

    // 使用IN查询多个可能的值
    List<Book> specificCategories = sqlClient.createQuery(book)
        .where(book.category().in(Arrays.asList("编程语言", "框架技术")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(specificCategories).isNotEmpty();
    assertThat(specificCategories).allMatch(b -> 
        b.category().equals("编程语言") || b.category().equals("框架技术")
    );

    // 使用NOT IN排除特定值
    List<Book> excludeCategories = sqlClient.createQuery(book)
        .where(book.category().notIn(Arrays.asList("编程语言", "框架技术")))
        .where(book.id().like(testPrefix + "-%"))
        .select(book)
        .execute();

    // 验证结果
    assertThat(excludeCategories).isNotEmpty();
    assertThat(excludeCategories).noneMatch(b -> 
        b.category().equals("编程语言") || b.category().equals("框架技术")
    );
}

// 当categories为空集合时
List<String> categories = Collections.emptyList();
Predicate condition = book.category().in(categories);

// Jimmer会将其优化为TRUE常量
// 等价于SQL: WHERE 1=1

Predicate condition = null;
if (categories != null && !categories.isEmpty()) {
    condition = book.category().in(categories);
} else {
    // 自定义空集合行为
    condition = Predicate.constant(false); // 返回空结果
    // 或者
    condition = null; // 忽略此条件
}

// EXISTS查询示例
BookTable book = BookTable.$;
ReviewTable review = ReviewTable.$;

List<Book> booksWithHighRating = sqlClient.createQuery(book)
    .where(
        sqlClient.createSubQuery(review)
            .where(review.bookId().eq(book.id()))
            .where(review.rating().eq(5))
            .exists()
    )
    .select(book)
    .execute();

// IN子查询示例
BookTable book = BookTable.$;
ReviewTable review = ReviewTable.$;

List<Book> booksWithCriticalReviews = sqlClient.createQuery(book)
    .where(
        book.id().in(
            sqlClient.createSubQuery(review)
                .where(review.type().eq(ReviewType.CRITICAL))
                .select(review.bookId())
        )
    )
    .select(book)
    .execute();