JPA 查詢工具模組

Package: io.leandev.appfuse.jpa.*, io.leandev.appfuse.search.*

AppFuse Server 提供一系列 JPA 查詢建構工具，簡化複雜查詢的撰寫，支援動態過濾、分頁和投影查詢。

核心特色

1. 動態查詢建構

2. 工具類別總覽

類別	用途
Filter	結構化查詢條件建構
PredicateBuilder	將 Filter 轉換為 JPA Predicate
SpecificationBuilder	將 Filter 轉換為 Spring Data Specification
CriteriaQueryBuilder	Fluent API 建構 Criteria 查詢
TupleQueryBuilder	建構投影查詢（選取部分欄位）
TupleConstructor	將 Tuple 結果轉換為物件
QueryRunner	執行查詢並支援分頁
PageableBuilder	建構分頁參數並轉換排序欄位

Filter 查詢條件

Filter 是核心的查詢條件建構工具，提供流暢的 API 建構複雜查詢。

基本用法

import io.leandev.appfuse.search.Filter;

// 等於
Filter filter = Filter.eq("status", "ACTIVE");

// 不等於
Filter filter = Filter.neq("status", "DELETED");

// 大於、小於
Filter filter = Filter.gt("price", 100);
Filter filter = Filter.lt("price", 1000);
Filter filter = Filter.ge("quantity", 10);  // 大於等於
Filter filter = Filter.le("quantity", 100); // 小於等於

// 模糊比對（LIKE）
Filter filter = Filter.like("name", "Apple");  // %Apple%
Filter filter = Filter.startsWith("code", "PRD");  // PRD%

// 空值檢查
Filter filter = Filter.isNull("deletedAt");
Filter filter = Filter.isNotNull("assignee");

// IN / NOT IN
Filter filter = Filter.in("status", List.of("ACTIVE", "PENDING"));
Filter filter = Filter.notIn("category", List.of("ARCHIVED", "DELETED"));

// 區間查詢
Filter filter = Filter.between("createdAt", startDate, endDate);

組合條件

// AND 組合
Filter filter = Filter.eq("status", "ACTIVE")
    .and(Filter.ge("price", 100))
    .and(Filter.le("price", 1000));

// OR 組合
Filter filter = Filter.eq("status", "ACTIVE")
    .or(Filter.eq("status", "PENDING"));

// 複雜組合
Filter filter = Filter.eq("category", "ELECTRONICS")
    .and(
        Filter.ge("price", 100).or(Filter.eq("featured", true))
    );

支援的運算子

運算子	方法	說明
==	eq()	等於
!=	neq()	不等於
>	gt()	大於
>=	ge()	大於等於
<	lt()	小於
<=	le()	小於等於
=in=	in()	包含於集合
=out=	notIn()	不包含於集合
=has=	has()	集合包含元素
=is=	is(), isNull()	是（含 null 檢查）
=isnt=	isNot(), isNotNull()	不是（含 null 檢查）
=between=	between()	區間

搭配 Spring Data JPA

SpecificationBuilder

最簡單的整合方式是使用 SpecificationBuilder 搭配 JpaSpecificationExecutor：

import io.leandev.appfuse.jpa.SpecificationBuilder;
import io.leandev.appfuse.search.Filter;
import org.springframework.data.jpa.domain.Specification;

// Repository 需要繼承 JpaSpecificationExecutor
public interface ProductRepository extends
    JpaRepository<Product, Long>,
    JpaSpecificationExecutor<Product> {
}

// Service 中使用
@Service
public class ProductService {

    private final ProductRepository productRepository;

    public Page<Product> findProducts(ProductSearchCriteria criteria, Pageable pageable) {
        // 建構 Filter
        Filter filter = Filter.empty()
            .and(Filter.eq("status", criteria.getStatus()))
            .and(Filter.like("name", criteria.getName()))
            .and(Filter.ge("price", criteria.getMinPrice()))
            .and(Filter.le("price", criteria.getMaxPrice()));

        // 轉換為 Specification
        Specification<Product> spec = SpecificationBuilder.build(filter);

        // 執行查詢
        return productRepository.findAll(spec, pageable);
    }
}

關聯查詢

Filter 支援巢狀屬性查詢：

// 查詢 product.category.name = "Electronics"
Filter filter = Filter.eq("category.name", "Electronics");

// 查詢 order.customer.email 包含 "gmail"
Filter filter = Filter.like("customer.email", "gmail");

CriteriaQueryBuilder

對於更複雜的查詢，使用 CriteriaQueryBuilder 提供完整控制：

基本查詢

import io.leandev.appfuse.jpa.CriteriaQueryBuilder;

@PersistenceContext
private EntityManager entityManager;

public List<Product> findActiveProducts() {
    Filter filter = Filter.eq("status", "ACTIVE");

    CriteriaQuery<Product> query = CriteriaQueryBuilder
        .<Product, Product>of(entityManager)
        .from(Product.class)
        .where(filter)
        .orderBy(Sort.by("name"))
        .build();

    return entityManager.createQuery(query).getResultList();
}

JOIN 查詢

public List<Product> findProductsWithCategory(String categoryName) {
    Filter filter = Filter.eq("category.name", categoryName);

    CriteriaQuery<Product> query = CriteriaQueryBuilder
        .<Product, Product>of(entityManager)
        .from(Product.class)
        .join("category", JoinType.INNER)  // INNER JOIN category
        .where(filter)
        .build();

    return entityManager.createQuery(query).getResultList();
}

DISTINCT 查詢

CriteriaQuery<Product> query = CriteriaQueryBuilder
    .<Product, Product>of(entityManager)
    .from(Product.class)
    .distinct()  // SELECT DISTINCT
    .where(filter)
    .build();

TupleQueryBuilder 投影查詢

當只需要部分欄位時，使用 TupleQueryBuilder 建構投影查詢：

選取部分欄位

import io.leandev.appfuse.jpa.TupleQueryBuilder;
import jakarta.persistence.Tuple;

public List<Tuple> findProductSummaries() {
    CriteriaQuery<Tuple> query = TupleQueryBuilder
        .<Product>of(entityManager)
        .from(Product.class)
        .select("id", "name", "price")  // 只選取這些欄位
        .where(Filter.eq("status", "ACTIVE"))
        .orderBy(Sort.by("name"))
        .build();

    return entityManager.createQuery(query).getResultList();
}

聚合函數

// COUNT
CriteriaQuery<Tuple> query = TupleQueryBuilder
    .<Product>of(entityManager)
    .from(Product.class)
    .select("category.name")
    .countAs("id", "productCount")  // COUNT(id) AS productCount
    .groupBy("category.name")
    .build();

// SUM, AVG, MAX, MIN
CriteriaQuery<Tuple> query = TupleQueryBuilder
    .<Order>of(entityManager)
    .from(Order.class)
    .select("customer.id")
    .sum("totalAmount", "totalSpent")
    .avg("totalAmount", "avgOrder")
    .max("totalAmount", "maxOrder")
    .min("totalAmount", "minOrder")
    .groupBy("customer.id")
    .build();

轉換為物件

使用 TupleConstructor 將 Tuple 轉換為 DTO：

import io.leandev.appfuse.jpa.TupleConstructor;

// DTO 需要無參數建構子和對應的 setter
public class ProductSummaryDto {
    private Long id;
    private String name;
    private BigDecimal price;
    // getters and setters
}

public List<ProductSummaryDto> findProductSummaries() {
    CriteriaQuery<Tuple> query = TupleQueryBuilder
        .<Product>of(entityManager)
        .from(Product.class)
        .select("id", "name", "price")
        .build();

    TupleConstructor<ProductSummaryDto> constructor =
        new TupleConstructor<>(ProductSummaryDto.class);

    return entityManager.createQuery(query)
        .getResultList()
        .stream()
        .map(constructor::construct)
        .toList();
}

QueryRunner 分頁查詢

QueryRunner 簡化分頁查詢的執行：

import io.leandev.appfuse.jpa.QueryRunner;
import org.springframework.data.domain.Page;

public Page<Product> findProducts(Filter filter, Pageable pageable) {
    QueryRunner<Product> runner = new QueryRunner<>(entityManager);

    // 建構 data query 和 count query
    CriteriaQueryBuilder<Product, Product> builder = CriteriaQueryBuilder
        .<Product, Product>of(entityManager)
        .from(Product.class)
        .where(filter);

    CriteriaQuery<Product> dataQuery = builder.build();
    CriteriaQuery<Long> countQuery = builder.buildCountQuery();

    return runner.findAll(dataQuery, countQuery, pageable);
}

單一結果查詢

QueryRunner<Product> runner = new QueryRunner<>(entityManager);

// 回傳 Optional
Optional<Product> product = runner.findOne(query);

// 回傳物件或 null
Product product = runner.get(query);

// 回傳 List
List<Product> products = runner.findAll(query);

PageableBuilder 分頁建構

PageableBuilder 協助處理分頁參數，特別是排序欄位的轉換：

欄位名稱轉換

前端傳來的排序欄位名稱可能與資料庫欄位不同：

import io.leandev.appfuse.jpa.PageableBuilder;

@GetMapping("/products")
public Page<Product> getProducts(Pageable pageable) {
    // 將前端欄位名稱轉換為 Entity 欄位名稱
    Pageable convertedPageable = PageableBuilder.of(pageable)
        .sort(pageable.getSort(), fieldName -> switch (fieldName) {
            case "categoryName" -> "category.name";
            case "supplierName" -> "supplier.companyName";
            default -> fieldName;
        })
        .build();

    return productService.findAll(convertedPageable);
}

預設排序

Pageable pageable = PageableBuilder.of(requestPageable)
    .sort(requestPageable.getSort(),
          this::convertFieldName,
          Sort.by("createdAt").descending())  // 預設排序
    .build();

多租戶支援

JPA 模組提供多租戶數據隔離的基礎設施。

TenantAwareEntity

繼承 TenantAwareEntity 自動獲得租戶隔離：

import io.leandev.appfuse.jpa.tenant.TenantAwareEntity;

@Entity
@Table(name = "products")
public class Product extends TenantAwareEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private BigDecimal price;

    // tenantId 自動從 TenantAwareEntity 繼承
}

自動功能（由 Hibernate 原生 @TenantId 承擔，見 ADR-016）：

• 查詢：Session 建構時自動套用租戶 filter，含 entityManager.find()（applyToLoadByKey = true）
• 新增：TenantIdGeneration 於 INSERT 注入租戶；已顯式設值者 root 放行、非 root 須相符
• 更新：updatable = false，租戶歸屬不可改寫

啟用租戶過濾

不需要做任何事——租戶 filter 由 Hibernate 在 Session 建構時自動套用。應用層只需接線 resolver：

# config/feature-tenant.yml（記得加入 spring.config.import 清單）
spring:
  jpa:
    properties:
      hibernate:
        tenant_identifier_resolver: io.leandev.appfuse.jpa.tenant.TenantContextIdentifierResolver

不要寫 AOP 手動 enableFilter。本節原本示範的正是那個做法，而它有一個靜默的資安缺陷： aspect 因 @Order 跑在交易攔截器之前、無法自行綁定 Session，實際只有 OSIV 綁定時才生效—— spring.jpa.open-in-view=false 即租戶 filter 從未啟用、跨租戶全可見，且例外被吞。 TenantFilterSupport 已 @Deprecated（4.0.0，4.2.0 移除）。詳見 多租戶設計指南 與 ADR-016。

加入審計功能

建立應用層基類加入審計欄位：

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableTenantEntity extends TenantAwareEntity {

    @CreatedBy
    @Column(name = "created_by", updatable = false)
    private String createdBy;

    @CreatedDate
    @Column(name = "created_date", updatable = false)
    private Instant createdDate;

    @LastModifiedBy
    @Column(name = "last_modified_by")
    private String lastModifiedBy;

    @LastModifiedDate
    @Column(name = "last_modified_date")
    private Instant lastModifiedDate;
}

完整範例

Service 層整合

@Service
@Transactional(readOnly = true)
public class ProductService {

    private final ProductRepository productRepository;
    private final EntityManager entityManager;

    // 方式 1：使用 SpecificationBuilder（簡單場景）
    public Page<Product> findBySpec(ProductSearchCriteria criteria, Pageable pageable) {
        Filter filter = buildFilter(criteria);
        Specification<Product> spec = SpecificationBuilder.build(filter);
        return productRepository.findAll(spec, pageable);
    }

    // 方式 2：使用 CriteriaQueryBuilder（複雜場景）
    public Page<Product> findByCriteria(ProductSearchCriteria criteria, Pageable pageable) {
        Filter filter = buildFilter(criteria);
        QueryRunner<Product> runner = new QueryRunner<>(entityManager);

        CriteriaQueryBuilder<Product, Product> builder = CriteriaQueryBuilder
            .<Product, Product>of(entityManager)
            .from(Product.class)
            .join("category")
            .where(filter);

        return runner.findAll(builder.build(), builder.buildCountQuery(), pageable);
    }

    // 方式 3：投影查詢
    public List<ProductSummaryDto> findSummaries(ProductSearchCriteria criteria) {
        Filter filter = buildFilter(criteria);
        TupleConstructor<ProductSummaryDto> constructor =
            new TupleConstructor<>(ProductSummaryDto.class);

        CriteriaQuery<Tuple> query = TupleQueryBuilder
            .<Product>of(entityManager)
            .from(Product.class)
            .select("id", "name", "price", "category.name")
            .selectAs("category.name", "categoryName")
            .where(filter)
            .build();

        return entityManager.createQuery(query)
            .getResultList()
            .stream()
            .map(constructor::construct)
            .toList();
    }

    private Filter buildFilter(ProductSearchCriteria criteria) {
        return Filter.empty()
            .and(Filter.eq("status", criteria.getStatus()))
            .and(Filter.like("name", criteria.getName()))
            .and(Filter.eq("category.id", criteria.getCategoryId()))
            .and(Filter.ge("price", criteria.getMinPrice()))
            .and(Filter.le("price", criteria.getMaxPrice()));
    }
}

最佳實踐

1. 選擇適當的工具

場景	推薦工具
簡單 CRUD	Spring Data JPA + SpecificationBuilder
複雜 JOIN	CriteriaQueryBuilder
部分欄位投影	TupleQueryBuilder
聚合統計	TupleQueryBuilder + groupBy

2. Filter 建構技巧

// 使用 Filter.of 處理可能為 null 的條件
// 當 value 為 null 或空字串時，會自動回傳空 Filter
Filter filter = Filter.of("status", ComparisonOperator.EQUAL, criteria.getStatus());

// 等同於
if (criteria.getStatus() != null && !criteria.getStatus().isEmpty()) {
    filter = filter.and(Filter.eq("status", criteria.getStatus()));
}

3. 避免 N+1 問題

// 使用 JOIN FETCH 預載關聯
CriteriaQueryBuilder<Product, Product> builder = CriteriaQueryBuilder
    .<Product, Product>of(entityManager)
    .from(Product.class)
    .join("category", JoinType.LEFT)  // 預載 category
    .where(filter);

下一步

• Tenant 模組 - 深入了解多租戶架構
• Security 模組 - 安全性相關功能
• Cache 模組 - 查詢快取優化