Hibernate 調用存儲過程

-------存儲過程

create procedure insertEmp

@emName varchar(50),

@emAge int

as

begin

  insert into employee values(@emName,@emAge);

end

create procedure updateEmp

@emId int,

@emName varchar(50),

@emAge int

as

begin

  update employee set emName=@emName,emAge=@emAge where emId=@emId;

end
create procedure deleteEmp

@emId int

as

begin

  delete employee where emId=@emId;

end
create procedure getEmpList

as

begin

  select * from employee

create procedure insertEmp

@emName varchar(50),

@emAge int

as

begin

  insert into employee values(@emName,@emAge);

end
create procedure updateEmp

@emId int,

@emName varchar(50),

@emAge int

as

begin

  update employee set emName=@emName,emAge=@emAge where emId=@emId;

end
create procedure deleteEmp

@emId int

as

begin

  delete employee where emId=@emId;

end
create procedure getEmpList

as

begin

  select * from employee  </pre>
<?xml version="1.0" encoding="utf-8"?>             
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"             
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">             
<!--         
    Mapping file autogenerated by MyEclipse Persistence Tools         
-->             
<hibernate-mapping>             
    <class name="com.pojo.Employee" table="employee" catalog="master">             
        <id name="emId" type="java.lang.Integer">             
            <column name="emId" />             
            <generator class="native" />             
        </id>             
        <property name="emName" type="java.lang.String">             
            <column name="emName" length="20" />             
        </property>             
        <property name="emAge" type="java.lang.Integer">             
            <column name="emAge" />             
        </property>             
    </class>             
       <!--    在該文件中需注意<sql-query…></sql-query>中的這段代碼，調用的存儲過程在其中定義，             
       并定義了調用存儲過程后將記錄組裝成Emp對象，同時對記錄的字段與對象的屬性進行相關映射。 -->             
       <sql-query name="getEmpList" callable="true">             
           <!--別名-->             
           <return alias="employee" class="com.pojo.Employee">             
              <return-property name="emId" column="emId" />             
              <return-property name="emName" column="emName" />             
              <return-property name="emAge" column="emAge" />             
           </return>             
           <!--這里調用查詢的存儲過程-->             
            {call getEmpList}             
      </sql-query>             
</hibernate-mapping> 
// 測試實現查詢的存儲過程

    private void testProcQuery(Session session) throws Exception {

       // 查詢用戶列表

       List list = session.getNamedQuery("getEmpList").list();

       for (int i = 0; i < list.size(); i++) {

           Employee em = (Employee) list.get(i);

           System.out.print("序號: " + (i + 1));

           System.out.print(", emid: " + em.getEmId());

           System.out.print(", emname: " + em.getEmName());

           System.out.println(",emage: " + em.getEmAge());

       }

    }


private void testProcUpdate(Session session) throws Exception {             
   // 更新用戶信息             
   Transaction tx = session.beginTransaction();             
   Connection con = session.connection();             
   String procedure = "{call updateEmp(?, ?, ?)}";             
   CallableStatement cstmt = con.prepareCall(procedure);             
   cstmt.setInt(1, 2);             
   cstmt.setString(2, "ddd");             
   cstmt.setInt(3, 100);             
   cstmt.executeUpdate();             
   tx.commit();             
}             

// 測試實現插入的存儲過程             
private void testProcInsert(Session session) throws Exception {             
   // 創建用戶信息             
   session.beginTransaction();             
   PreparedStatement st = session.connection().prepareStatement(             
          "{call insertEmp(?,?)}");             
   st.setString(1, "阿蜜果");             
   st.setInt(2, 12);             
   st.execute();             
   session.getTransaction().commit();             
}             

// 測試實現刪除的存儲過程             
private void testProcDelete(Session session) throws Exception {             
   // 刪除用戶信息             
   session.beginTransaction();             
   PreparedStatement st = session.connection().prepareStatement(             
          "{call deleteEmp(?)}");             
   st.setInt(1, 1);             
   st.execute();             
   session.getTransaction().commit();             
}             

public static void main(String[] args) throws Exception {             
  ProTest pt=new ProTest();             
  Session session=HibernateSessionFactory.getSession();             
  pt.testProcQuery(session);             
  //pt.testProcInsert(session);             
   //pt.testProcDelete(session);             
  //pt.testProcUpdate(session);             
}             


--------------   得到輸出參數 ------------------

    public String countFeeByHand(final Date startTime, final String operBizId)

            throws DataAccessException {

        String destroyFee = (String) this.getHibernateTemplate().execute(

        new HibernateCallback() {

            public Object doInHibernate(Session session)

                    throws HibernateException, SQLException {

                CallableStatement cs = session.connection().prepareCall(

                        "{call MANUAL_USER_SETTLEMENT(?,?,?,?)}");// 存儲過程調用有錯誤

                cs.setString(1, DateTools.getYearMonthString(startTime));

                cs.setString(2, operBizId);

                cs.registerOutParameter(3, Types.INTEGER);

                cs.registerOutParameter(4, Types.CHAR);

                cs.execute();

                return cs.getString(4);   //得到輸出參數

            }

        });

        return destroyFee;

    }         </pre>