Hibernate 呼叫帶複合主鍵的 Stored Procedure 完整映射範例

場景說明

資料表採用複合主鍵 (日期 + 字串 + 整數)，需透過 Hibernate 呼叫 Stored Procedure 查詢全表資料，並正確映射回實體物件。

1. Hibernate 映射檔 (TestTable.hbm.xml)

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  "https://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
  <class name="com.bgi.fia.hibernate.TestTable" table="TestTable">
    <composite-id name="comp_id" class="com.bgi.fia.hibernate.TestTablePK">
      <key-property name="keyDate" column="keyDate" type="java.sql.Timestamp" length="23"/>
      <key-property name="keyString" column="keyString" type="java.lang.String" length="50"/>
      <key-property name="keyInt" column="keyInt" type="java.lang.Integer" length="10"/>
    </composite-id>

    <property name="conteng" column="conteng" type="java.lang.String" length="200"/>

    <!-- 關鍵：指定 loader 使用具名 SQL 查詢 -->
    <loader query-ref="selectAllValues_SP"/>
  </class>

  <!-- Stored Procedure 呼叫定義 -->
  <sql-query name="selectAllValues_SP" callable="true">
    <return alias="testTable" class="com.bgi.fia.hibernate.TestTable">
      <return-property name="comp_id">
        <return-column name="keyDate"/>
        <return-column name="keyString"/>
        <return-column name="keyInt"/>
      </return-property>
      <return-property name="conteng" column="conteng"/>
    </return>
    {call selectAllValues}
  </sql-query>
</hibernate-mapping>

2. Java 實體類別

TestTable.java (主實體)

package com.bgi.fia.hibernate;

import java.io.Serializable;
import org.apache.commons.lang.builder.*;

public class TestTable implements Serializable {
    private TestTablePK comp_id;  // 複合主鍵物件
    private String conteng;

    // 建構子
    public TestTable() {}
    public TestTable(TestTablePK comp_id) { this.comp_id = comp_id; }
    public TestTable(TestTablePK comp_id, String conteng) {
        this.comp_id = comp_id; this.conteng = conteng;
    }

    // Getter/Setter
    public TestTablePK getComp_id() { return comp_id; }
    public void setComp_id(TestTablePK comp_id) { this.comp_id = comp_id; }
    public String getConteng() { return conteng; }
    public void setConteng(String conteng) { this.conteng = conteng; }

    // equals/hashCode 基於複合主鍵
    public boolean equals(Object o) {
        if (!(o instanceof TestTable)) return false;
        TestTable other = (TestTable) o;
        return new EqualsBuilder().append(this.comp_id, other.comp_id).isEquals();
    }
    public int hashCode() {
        return new HashCodeBuilder().append(comp_id).toHashCode();
    }
}

TestTablePK.java (複合主鍵類別)

package com.bgi.fia.hibernate;

import java.io.Serializable;
import java.util.Date;
import org.apache.commons.lang.builder.*;

public class TestTablePK implements Serializable {
    private Date keyDate;
    private String keyString;
    private Integer keyInt;

    public TestTablePK() {}
    public TestTablePK(Date keyDate, String keyString, Integer keyInt) {
        this.keyDate = keyDate; this.keyString = keyString; this.keyInt = keyInt;
    }

    // Getter/Setter 略
    public Date getKeyDate() { return keyDate; }
    public void setKeyDate(Date keyDate) { this.keyDate = keyDate; }
    public String getKeyString() { return keyString; }
    public void setKeyString(String keyString) { this.keyString = keyString; }
    public Integer getKeyInt() { return keyInt; }
    public void setKeyInt(Integer keyInt) { this.keyInt = keyInt; }

    // equals/hashCode 必須實作，涵蓋三個欄位
    public boolean equals(Object o) {
        if (!(o instanceof TestTablePK)) return false;
        TestTablePK other = (TestTablePK) o;
        return new EqualsBuilder()
            .append(keyDate, other.keyDate)
            .append(keyString, other.keyString)
            .append(keyInt, other.keyInt)
            .isEquals();
    }
    public int hashCode() {
        return new HashCodeBuilder()
            .append(keyDate).append(keyString).append(keyInt).toHashCode();
    }
}

3. 呼叫測試程式

package com.bgi.fia.test;

import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import com.bgi.fia.hibernate.HibernateSessionFactory;

public class TestHibernateSP {
    public static void main(String[] args) {
        Session session = HibernateSessionFactory.currentSession();
        Query query = session.getNamedQuery("selectAllValues_SP");
        List list = query.list();
        System.out.println(list);
    }
}

關鍵設定解析

注意事項

• PK 類別 必須實作 equals() 與 hashCode()，否則 Session 快取、Set 集合行為異常
• SP 回傳欄位順序、名稱、型別必須與 <return-column> 完全一致
• 若 SP 有輸入參數，可在 <sql-query> 中使用 ? 佔位符，呼叫時 query.setParameter(...)
• 建議搭配 HibernateSessionFactory 單例管理 Session 生命週期