Hibernate 註解映射查詢：@NamedQuery、@NamedNativeQuery 與 @SqlResultSetMapping 實戰

Hibernate/JPA 提供靜態定義、預編譯、可復用的查詢定義機制：@NamedQuery (JPQL/HQL)、@NamedNativeQuery (原生 SQL) 配合 @SqlResultSetMapping 映射結果集。本文系統講解定義語法、參數綁定、Hint 優化、結果映射、調用方式，解決動態拼接 SQL 的維護痛點與注入風險。

@NamedQuery：JPQL 靜態查詢定義

@Entity
@NamedQueries({
    @NamedQuery(name = "User.findByEmail", 
                query = "SELECT u FROM User u WHERE u.email = :email"),
    @NamedQuery(name = "User.findActiveByRole", 
                query = "SELECT u FROM User u WHERE u.status = 1 AND u.role = :role",
                hints = {
                    @QueryHint(name = "org.hibernate.cacheable", value = "true"), // 啟用查詢緩存
                    @QueryHint(name = "org.hibernate.timeout", value = "5000"),   // 5秒超時
                    @QueryHint(name = "org.hibernate.fetchSize", value = "50"),   // JDBC fetch size
                    @QueryHint(name = "org.hibernate.readOnly", value = "true")   // 僅讀優化
                })
})
public class User { ... }

核心屬性

常用 Hibernate QueryHint 速查

調用方式

// EntityManager / Session 通用
TypedQuery query = em.createNamedQuery("User.findByEmail", User.class);
query.setParameter("email", "test@example.com");
User user = query.getSingleResult();

// Spring Data JPA 直接在 Repository 定義
// @Query(nativeQuery = false) 不需要，直接方法命名規約或 @NamedQuery 引用

@NamedNativeQuery + @SqlResultSetMapping：原生 SQL 映射

用於：複雜 SQL、存儲過程調用、特定數據庫特性、性能調優、非實體映射 (DTO/報表)。

映射實體結果集

@Entity
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "User.findWithNative",
        query = "SELECT id, username, email, created_at FROM t_user WHERE status = 1",
        resultSetMapping = "UserMapping"
    )
})
@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "UserMapping",
        entities = @EntityResult(entityClass = User.class) // 自動映射同名列
    )
})
public class User { ... }

映射部分字段 / 自定義列名

@SqlResultSetMapping(
    name = "UserPartialMapping",
    entities = @EntityResult(
        entityClass = User.class,
        fields = {
            @FieldResult(name = "id", column = "user_id"),
            @FieldResult(name = "username", column = "user_name"),
            @FieldResult(name = "email", column = "email_addr")
            // 未列出字段將不填充 (或用默認值)
        }
    )
)

映射 DTO / 構造函數結果 (ConstructorResult, JPA 2.1+)

@SqlResultSetMapping(
    name = "UserSummaryDTOMapping",
    classes = @ConstructorResult(
        targetClass = UserSummaryDTO.class, // 非實體類，需有對應構造函數
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "username", type = String.class),
            @ColumnResult(name = "email", type = String.class),
            @ColumnResult(name = "order_count", type = Long.class)
        }
    )
)

@NamedNativeQuery(
    name = "User.findSummary",
    query = "SELECT u.id, u.username, u.email, COUNT(o.id) as order_count " +
            "FROM t_user u LEFT JOIN t_order o ON u.id = o.user_id " +
            "GROUP BY u.id, u.username, u.email",
    resultSetMapping = "UserSummaryDTOMapping"
)

// DTO 類
public class UserSummaryDTO {
    private Long id;
    private String username;
    private String email;
    private Long orderCount;
    public UserSummaryDTO(Long id, String username, String email, Long orderCount) { ... }
}

調用原生查詢

// 返回實體
List users = em.createNamedQuery("User.findWithNative", User.class).getResultList();

// 返回 DTO
List summaries = em.createNamedQuery("User.findSummary", UserSummaryDTO.class).getResultList();
// 注意：返回類型必須與 ConstructorResult targetClass 一致

多實體關聯映射 (EntityResult 多個)

@SqlResultSetMapping(
    name = "UserWithDepartmentMapping",
    entities = {
        @EntityResult(entityClass = User.class, discriminatorColumn = "u_"),
        @EntityResult(entityClass = Department.class, discriminatorColumn = "d_")
    }
)

@NamedNativeQuery(
    name = "User.findWithDept",
    query = "SELECT u.id as u_id, u.username as u_username, d.id as d_id, d.name as d_name " +
            "FROM t_user u JOIN t_dept d ON u.dept_id = d.id",
    resultSetMapping = "UserWithDepartmentMapping"
)

// 調用返回 Object[] 數組：[User, Department]
List results = em.createNamedQuery("User.findWithDept").getResultList();
for (Object[] row : results) {
    User u = (User) row[0];
    Department d = (Department) row[1];
}

最佳實踐與避坑

總結

@NamedQuery 為 JPQL 提供預編譯、類型安全、可緩存、可 Hint 優化的靜態定義；@NamedNativeQuery + @SqlResultSetMapping 解決原生 SQL 映射實體/DTO/多實體的復雜場景。兩者均在啟動階段解析驗證，運行時零解析開銷。現代 Spring Data JPA 雖提供方法命名規約與 @Query 註解，但對於跨 Repository 復用、複雜 Hint 組合、原生 SQL 映射 DTO等場景，註解映射查詢仍是不可替代的利器。