DB2中簡單存儲過程 selectAllUsers
CREATE procedure selectAllUsers
DYNAMIC RESULT SETS 1
BEGIN
DECLARE temp_cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM test;
OPEN temp_cursor1;
END;
映射文件中關(guān)于存儲過程內(nèi)容如下
<hibernate-mapping package="com.unmi.vo">
<class name="Test" table="TEST">
............
</class>
<sql-query callable="true" name="selectAllUsers">
<return alias="aa" class="Test">
<return-property name="oborqt" column="OBORQT"/>
<return-property name="moorqt" column="MOORQT"/>
<return-property name="roschn" column="ROSCHN"/>
<return-property name="plandate" column="PLANDATE"/>
</return>
{ ? = call selectAllUsers() }
</sql-query>
</hibernate-mapping>
{ ? = call selectAllUsers() } 也可以寫成{ call selectAllUsers() }，
如果有參數(shù)就寫成
{ ? = call selectAllUsers(?,?,?) }
代碼中對query設(shè)置相應(yīng)位置上的值就OK
Java調(diào)用關(guān)鍵代碼如下
Session session = HibernateUtil.currentSession();
Query query = session.getNamedQuery("selectAllUsers");
List list = query.list();
System.out.println(list);
要求你的存儲過程必須能返回記錄集，否則要出錯
如果你的存儲過程是完成非查詢?nèi)蝿?wù)就應(yīng)該在配置文件用以下三個標(biāo)簽
<sql-insert callable="true">{call createPerson (?, ?)}</sql-insert>
<sql-delete callable="true">{? = call deletePerson (?)}</sql-delete>
<sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>
有一點不好的地方就是調(diào)用存儲過程獲取記錄集時，不能對Query使用
setFirstResult(int)和setMaxResults(int)方法來分頁
要求你的存儲過程必須能返回記錄集，否則要出錯
上面這句話是關(guān)鍵。
==========================================================================
hibernate調(diào)用mysql5.0存儲過程
關(guān)鍵代碼：
數(shù)據(jù)庫存儲過程
Create procedure SP_get_UserInfo()
as
begin
select *
from user
order by Id
end
hibernate相對應(yīng)的配置文件User.hbm.xml
<?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.test.hibernate.db.User" table="user"
catalog="user">
<id name="id" type="java.lang.Long">
<column name="ID" />
<generator class="assigned" />
</id>
<property name="name" type="java.lang.String">
<column name="name" length="500" />
</property>
<property name="sex" type="java.lang.String">
<column name="sex" length="10" />
</property>
<!--
<property name="born" type="java.util.Date">
<column name="born" length="0" />
</property>
-->
<property name="born" type="java.lang.String">
<column name="born" length="0" />
</property>
<property name="address" type="java.lang.String">
<column name="address" />
</property>
</class>
<!--
調(diào)用存儲過程就在這里配 -->
<sql-query name="getUser" callable="true">
<return alias="user" class="com.test.hibernate.db.User">
<return-property name="id" column="ID" />
<return-property name="name" column="name" />
<return-property name="sex" column="sex" />
<return-property name="born" column="born" />
<return-property name="address" column="address"/>
</return>
{ call SP_get_UserInfo() }
</sql-query>
</hibernate-mapping>
調(diào)用存儲過程的Java代碼：
Session querySession = HibernateSessionFactory.getSession();
querySession.clear();
List lst = uerySession.getNamedQuery("getUser").list();
for(int i=0;i<lst.size();i++)
{
usr=(User)lst.get(i);
System.out
.println(usr.getId() + " " + usr.getName() + " "
+ usr.getSex() + " " + usr.getBorn() + " "
+ usr.getAddress());
}
HibernateSessionFactory.closeSession();
一個簡單的存儲過程調(diào)用就這樣完成了！