萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

JPA 視圖查詢實戰:跨表統計與無實體映射的解決方案

問題背景

需每日產生報表,資料來源為跨表 Join 並含 COUNT 聚合,結果不對應任何單一實體表。JPA 標準做法需映射實體,但視圖查詢無實體類對應,該如何處理?

範例資料模型

-- 學院表
CREATE TABLE depts (
    deptId INT PRIMARY KEY AUTO_INCREMENT,
    deptName VARCHAR(50) NOT NULL
);

-- 學生表
CREATE TABLE students (
    stuNo BIGINT PRIMARY KEY AUTO_INCREMENT,
    deptId INT NOT NULL,
    stuName VARCHAR(50) NOT NULL,
    FOREIGN KEY (deptId) REFERENCES depts(deptId)
);

SQL 方案比較

方案 A:GROUP BY(缺少無學生的學院)

SELECT b.deptId, b.deptName, COUNT(*) AS totalCount
FROM students a LEFT JOIN depts b ON a.deptId = b.deptId
GROUP BY b.deptId ORDER BY b.deptId;

結果僅顯示有學生的學院(內部連接行為),LEFT JOIN 配合 GROUP BY 仍會過濾掉右表無對應的行。

方案 B:子查詢(完整保留所有學院)

SELECT a.deptId, a.deptName,
       (SELECT COUNT(*) FROM students b WHERE b.deptId = a.deptId) AS totalCount
FROM depts a;

結果包含所有學院,無學生者 totalCount = 0。

JPA 實作策略

1. 共用 EntityManager


@Service
public class ObjectDaoServiceImpl implements ObjectDaoService {
    @PersistenceContext
    private EntityManager entityManager;

    public EntityManager getEntityManager() { return entityManager; }
}

2. 實體類定義(對應實體表)


@Entity @Table(name="depts")
public class Depts implements Serializable {
    @Id @GeneratedValue @Column(name="deptId") private Integer deptId;
    @Column(name="deptName", length=50) private String deptName;
    // getter/setter...
}

@Entity @Table(name="students")
public class Students implements Serializable {
    @Id @GeneratedValue @Column(name="stuNo") private Long stuNo;
    @Column(name="deptId") private Integer deptId;
    @Column(name="stuName", length=50) private String stuName;
    // getter/setter...
}

3. 視圖查詢三種實作路徑

路徑一:原生 SQL + ResultTransformer(推薦)


String sql = "SELECT a.deptId, a.deptName, " +
             "(SELECT COUNT(*) FROM students b WHERE b.deptId=a.deptId) AS totalCount " +
             "FROM depts a";

List rows = entityManager.createNativeQuery(sql).getResultList();

List result = new ArrayList<>();
for (Object[] row : rows) {
    DeptStatDTO dto = new DeptStatDTO();
    dto.setDeptId((Integer) row[0]);
    dto.setDeptName((String) row[1]);
    dto.setTotalCount(((Number) row[2]).longValue());
    result.add(dto);
}

優點:SQL 完全可控、效能最佳、無需額外映射類。
缺點:需手動映射 DTO。

路徑二:@SqlResultSetMapping + @NamedNativeQuery


@SqlResultSetMapping(name="DeptStatMapping", classes={
    @ConstructorResult(targetClass=DeptStatDTO.class, columns={
        @ColumnResult(name="deptId", type=Integer.class),
        @ColumnResult(name="deptName", type=String.class),
        @ColumnResult(name="totalCount", type=Long.class)
    })
})
@NamedNativeQuery(name="DeptStatQuery", query="...", resultSetMapping="DeptStatMapping")
@Entity
public class DeptStatDTO { ... } // 非資料表實體,僅作為 DTO

呼叫:entityManager.createNamedQuery("DeptStatQuery").getResultList() 直接回傳 List<DeptStatDTO>。

路徑三:資料庫 View + 實體映射

在資料庫建立 VIEW dept_stat AS ...,再建立 @Entity @Table(name="dept_stat") 映射,並標記 @Immutable。JPA 視為唯讀實體,可直接用 JPQL 查詢。

選型建議

  • 簡單報表、一次性查詢 → 路徑一(最彈性)。
  • 固定報表、重複使用、想要型別安全 → 路徑二。
  • 資料庫層面已有 View、團隊習慣 ORM 全程 → 路徑三。

關鍵提醒

  1. 聚合查詢無主鍵,不可直接映射為 @Entity 並嘗試持久化操作。
  2. 使用 ConstructorResult 時,DTO 需提供對應參數順序的建構子。
  3. 分頁查詢聚合視圖時,原生 SQL 需自行處理 LIMIT/OFFSET 或 ROW_NUMBER。
copyright © 萬盛學電腦網 all rights reserved