為了在 Hibernate 中使用存儲過程，你必須遵循一些規(guī)則。不遵循這些規(guī)則的存儲過程將不可用。如果你仍然想使用他們，你必須通過 session.connection() 來執(zhí)行他們。這些規(guī)則針對于不同的數(shù)據(jù)庫。因為數(shù)據(jù)庫提供商有各種不同的存儲過程語法和語義。
對存儲過程進行的查詢無法使用 setFirstResult()/setMaxResults() 進行分頁。
建議采用的調(diào)用方式是標準 SQL92: { ? = call functionName(<parameters>) } 或者 { ? = call procedureName(<parameters>) }。原生調(diào)用語法不被支持。
對于 Oracle 有如下規(guī)則：
對于 Sybase 或者 MS SQL server 有如下規(guī)則：
?
?
?http://www.iteye.com/topic/176032
Java代碼
CREATE TABLE `proctab` (
`id` int ( 11 ) NOT NULL auto_increment,
`Name` varchar ( 20 ),
`age` int ( 11 ),
PRIMARY KEY (`id`)
)
Java代碼
create PROCEDURE proc()beginselect * from proctab;end ;
Java代碼
<class name="com.test.User" table="proctab"><id name="id" column="id"><generator class="native"/></id><property name="name" column="name" type="string" /><property name="age" column="age" type="integer" />
</class><sql-query name="getUser" callable="true"><return alias="user" class="com.test.User"><return-property name="id" column="id" /><return-property name="name" column="name" /><return-property name="age" column="age" /></return>{call proc()}</sql-query>
Java代碼
Session ss= HibernateSessionFactory.getSession()List li=ss.getNamedQuery("getUser").list();ss.close();
Java代碼
Session session =HibernateSessionFactory.getSession();
Connection conn = session.connection();
ResultSet rs =null;
CallableStatement call = conn.prepareCall("{Call proc()}");
rs = call.executeQuery();
rs.close();
session.close();
Java代碼
Session session =HibernateSessionFactory.getSession();
SQLQuery query = session.createSQLQuery("{Call proc()}");
List list =query.list();
session.close();
Java代碼?? CallableStatement call = conn.prepareCall("{Call proc(?)}");
call.setString(1, 參數(shù));
rs = call.executeQuery();
Java代碼
?
========================
http://dishell.iteye.com/blog/298217
摘要：本文以詳盡的實例展示了hibernate3.x中調(diào)用存儲過程各步驟，從建立測試表、存儲過程的建立、工程的建立以及類的編寫和測試一步一步引導用戶學習hibernate3.x中調(diào)用存儲過程的方法.
Sql代碼? DROP TABLE IF EXISTS `user`;CREATE TABLE `tbl_user` (`userid` varchar(50) NOT NULL,`name` varchar(50) default '',`blog` varchar(50) default '',PRIMARY KEY (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
Sql代碼
INSERT INTO `tbl_user` (`userid`,`name`,`blog`) VALUES ('ant', '螞蟻', 'http://www.blogjava.net/qixiangnj');INSERT INTO `tbl_user` (`userid`,`name`,`blog`) VALUES ('beansoft', 'bean', 'http://www.blogjava.net/beansoft');INSERT INTO `tbl_user` (`userid`,`name`,`blog`) VALUES ('sterning', '似水流年', 'http://www.blogjava.net/sterning');INSERT INTO `tbl_user` (`userid`,`name`,`blog`) VALUES ('tom', 'tom' , 'http://www.blogjava.net/tom');
Sql代碼
DROP PROCEDURE IF EXISTS `getUserList`;CREATE PROCEDURE `getUserList`()beginselect * from tbl_user;end;
Sql代碼
DROP PROCEDURE IF EXISTS `createUser`;CREATE PROCEDURE `createUser`(IN userid varchar(50), IN name varchar(50), IN blog varchar(50))begininsert into tbl_user values(userid, name, blog);end;
Sql代碼
DROP PROCEDURE IF EXISTS `updateUser`;
CREATE PROCEDURE `updateUser`(IN nameValue varchar(50), IN blogValue varchar(50), IN useidValue varchar(50))
beginupdate tbl_user set name = nameValue, blog = blogValue where userid = useridValue;
end;
Sql代碼
DROP PROCEDURE IF EXISTS `deleteUser`;
CREATE PROCEDURE `deleteUser`(IN useridValue int(11))
begindelete from tbl_user where userid = useridValue;
end;
Java代碼
proc-lib-bin-src-com-amigo-proc-model
Xml代碼
……<property name="connection.url">jdbc:mysql://localhost:3306/test</property><property name="connection.username">root</property><property name="connection.password">root</property><property name="connection.driver_class">com.mysql.jdbc.Driver</property><property name="dialect">org.hibernate.dialect.MySQLDialect</property><property name="show_sql">true</property><mapping resource="com/amigo/proc/model/User.hbm.xml"/> ……
Java代碼
package com.amigo.proc.model;/** *//*** 用戶信息對象*/public class User implements java.io.Serializable {private static final long serialVersionUID = 1L;/** *//** 用戶id*/private String userid;/** *//** 用戶姓名*/private String name;/** *//** 用戶blog*/private String blog;//省略get/set方法}
Xml代碼
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping package="com.amigo.proc.model"><class name="User" table="tbl_user"><id name="userid" column="userid"><generator class="assigned"/></id><property name="name" column="name" type="string" /><property name="blog" column="blog" type="string" /></class><sql-query name="getUserList" callable="true"><return alias="user" class="User"><return-property name="userid" column="userid"/><return-property name="name" column="name"/><return-property name="blog" column="blog" /></return>{call getUserList()}</sql-query></hibernate-mapping>
Java代碼
package com.amigo.proc;import java.io.ByteArrayOutputStream;
import java.io.OutputStreamWriter;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;/** *//*** Hibernate相關控制*/public class HibernateSessionFactory {/** *//** Hibernate配置文件 */private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";/** *//** 存儲一個單獨的session實例 */private static final ThreadLocal threadLocal = new ThreadLocal();/** *//** Hibernate配置相關的一個實例 */private static Configuration configuration = null;/** *//** Hibernate SessionFactory的一個實例 */private static SessionFactory sessionFactory;/** *//** Hibernate的字符編碼集*/private static String charSet;/** *//** 若Hibernate未設置字符編碼集時，采用的字符編碼集*/private static final String encoding = (new OutputStreamWriter(new ByteArrayOutputStream())).getEncoding();/** *//*** 默認構造函數(shù)*/public HibernateSessionFactory() {}/** *//*** 打開Hibernate的數(shù)據(jù)庫連接*/public static final synchronized void open() {if (sessionFactory != null)return;try {sessionFactory = getConfiguration().buildSessionFactory();charSet = configuration.getProperty("hibernate.connection.charSet");if (charSet == null)charSet = encoding;return;} catch (Throwable throwable) {throw new ExceptionInInitializerError(throwable);}}/** *//*** 配置Hibernate數(shù)據(jù)庫,并將其打開*/private static synchronized void configure() throws HibernateException {if (sessionFactory == null) {if (configuration == null) {getConfiguration().configure(CONFIG_FILE_LOCATION);}open();}}/** *//*** 獲得配置實例*/public static synchronized final Configuration getConfiguration() {if (configuration == null) {configuration = new Configuration();}return configuration;}/** *//*** 功能說明：獲得SessionFactory*/public static final SessionFactory getSessionFactory() {return sessionFactory;}/** *//*** 功能說明：獲得session*/public static final Session getSession() throws HibernateException {configure();Session session = null;if (threadLocal.get() == null) {session = getSessionFactory().openSession();threadLocal.set(session);} else {try {session = (Session)threadLocal.get();} catch(Exception ex) {session = getSessionFactory().openSession();threadLocal.set(session);}}return session;}//其余方法略}
Java代碼
package com.amigo.proc; import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;
import com.amigo.proc.model.User;
import org.hibernate.Session;
import org.hibernate.Transaction; /** *//*** hibernate調(diào)用存儲過程* @author Amigo Xie(xiexingxing1121@126.com)* @since 2007/04/30*/public class ProcTest {/** *//*** @param args*/public static void main(String[] args) throws Exception {ProcTest proc = new ProcTest();Session session = HibernateSessionFactory.getSession();proc.testProcQuery(session);proc.testProcUpdate(session);System.out.println("update successfully");proc.testProcInsert(session);System.out.println("insert successfully");proc.testProcDelete(session);System.out.println("delete successfully");session.close();}/** *//*** 測試實現(xiàn)查詢的存儲過程* @throws Exception*/private void testProcQuery(Session session) throws Exception {//查詢用戶列表List list = session.getNamedQuery("getUserList").list();for (int i = 0; i < list.size(); i++) {User user = (User) list.get(i); System.out.print("序號: " + (i+1));System.out.print(", userid: " + user.getUserid());System.out.print(", name: " + user.getName());System.out.println(", blog: " + user.getBlog());}}/** *//*** 測試實現(xiàn)更新的存儲過程* @throws Exception*/private void testProcUpdate(Session session) throws Exception {//更新用戶信息Transaction tx = session.beginTransaction(); Connection con = session.connection(); String procedure = "{call updateUser(?, ?, ?)}"; CallableStatement cstmt = con.prepareCall(procedure); cstmt.setString(1, "陳xx");cstmt.setString(2, "http://www.blogjava.net/sterningChen");cstmt.setString(3, "sterning");cstmt.executeUpdate(); tx.commit();}/** *//*** 測試實現(xiàn)插入的存儲過程* @throws Exception*/private void testProcInsert(Session session) throws Exception {//創(chuàng)建用戶信息session.beginTransaction();PreparedStatement st = session.connection().prepareStatement("{call createUser(?, ?, ?)}");st.setString(1, "amigo");st.setString(2, "阿蜜果");st.setString(3, "http://www.wblogjava.net/amigoxie");st.execute();session.getTransaction().commit(); }/** *//*** 測試實現(xiàn)刪除的存儲過程* @throws Exception*/private void testProcDelete(Session session) throws Exception {//刪除用戶信息session.beginTransaction();PreparedStatement st = session.connection().prepareStatement("{call deleteUser(?)}");st.setString(1, "amigo");st.execute();session.getTransaction().commit();}}
Java代碼
Hibernate: {call getUserList()}
序號: 1, userid: ant, name: 螞蟻, blog: http://www.blogjava.net/qixiangnj序號: 2, userid: beansoft, name: bean, blog: http://www.blogjava.net/beansoft序號: 3, userid: sterning, name: 似水流年, blog: http://www.blogjava.net/sterning序號: 4, userid: tom, name: tom, blog: http://www.blogjava.net/tomupdate successfullyinsert successfullydelete successfully
hibernate提供了在*.hbm.xml中配置調(diào)用存儲過程，并通過向用戶提供session.getNamedQuery(“…”)方法來調(diào)用配置的調(diào)用查詢相關的存儲過程的方法 ，另外，hibernate還提供了取得sql的connection的方法，從而能夠通過connection中存儲過程調(diào)用相關的方法來實現(xiàn)存儲過程的調(diào)用。
總結
以上是生活随笔 為你收集整理的通过hibernate去调用存储过程 的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔 網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔 推薦給好友。
