Hibernate 调用SQL Server2008的存储过程(有参无参两种情况)和SQL语句

Hibernate 中同样可以调用存储过程，或SQL语句，下面列举如下；

一，创建相关表

if OBJECT_ID(N'Book') is not null
goto showData

--//create table BOOKS
--//----------Start create table----------
create table Book(
[bID] int identity(1,1) not null,
[bName] varchar(100) null,
[bCategory] int null,
[bDescriptioin] varchar(250) null,
[bAddTime] datetime null default getdate(),
[bMark] varchar(250) null,
primary key([bID])
)

二，创建相应存储过程

use TESTDB01
GO

----------------------------------
-- getAllBook()
----------------------------------
if exists(select 1 from sysobjects where xtype=N'P' and name=N'getAllBook')
drop procedure getAllBook

go
create procedure getAllBook
as 
begin
select * from Books
end
go

--TEST
exec getAllBook

----------------------------------
-- getBookByCategoryName()
----------------------------------
if exists(select 1 from sysobjects where xtype=N'P' and name=N'getBookByCategoryName')
drop procedure getBookByCategoryName

go
create procedure getBookByCategoryName
@category int,
@bookName varchar(50)
as
begin
declare @cid int
declare @findName varchar(50)
set @cid = @category
set @findName = @bookName
if(0 = @cid)
	select * from Books where charIndex(@findName,bName) > 0
else
	select * from Books where bCategory = @cid and charIndex(@findName,bName) > 0
end
go

--TEST
exec getBookByCategoryName 0,'基础'

三，Hibernate中的表映射XML文件（Book.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">
<hibernate-mapping>
 <class catalog="TESTDB01" name="com.test.models.Book" schema="dbo" table="Book">
  <id name="bid" type="integer">
   <column name="bID"/>
   <generator class="native"/>
  </id>
  <property generated="never" lazy="false" name="bname" type="string">
   <column length="100" name="bName"/>
  </property>
  <property generated="never" lazy="false" name="bcategory" type="integer">
   <column name="bCategory"/>
  </property>
  <property generated="never" lazy="false" name="bdescriptioin" type="string">
   <column length="250" name="bDescriptioin"/>
  </property>
  <property generated="never" lazy="false" name="baddTime" type="timestamp">
   <column length="23" name="bAddTime"/> 
  </property>
  <property generated="never" lazy="false" name="bmark" type="string">
   <column length="250" name="bMark"/>
  </property>
 </class>

 <sql-query callable="true" name="getAllBook">
 {call getAllBook()}
 <!-- 此处也可以放SQL语句  -->
 <return alias="book" class="com.test.models.Book" entity-name="com.test.models.Book" >
   <!-- entity-name="" 
   其中，alias属性可以符合标志符名称即可，无特别要求，class和entity-name指向相同，或只能二选一，因它们两都是指定返回对象类型 -->
   <return-property column="bID" name="bid"/>
   <return-property column="bName" name="bname"/>
   <return-property column="bCategory" name="bcategory"/>
   <return-property column="bDescriptioin" name="bdescriptioin"/>
   <return-property column="bAddTime" name="baddTime"/>
   <return-property column="bMark" name="bmark"/>
  </return>
 </sql-query>
 <sql-query callable="true" name="getBookByCategoryName">
 {call getBookByCategoryName(?,?)}
 <return  alias="book" class="com.test.models.Book">
 	<return-property column="bID" name="bid"/>
    <return-property column="bName" name="bname"/>
    <return-property column="bCategory" name="bcategory"/>
    <return-property column="bDescriptioin" name="bdescriptioin"/>
    <return-property column="bAddTime" name="baddTime"/>
    <return-property column="bMark" name="bmark"/>
 </return>
 </sql-query>
</hibernate-mapping>

四，页面或action中通过Hibernate调用存储过程

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page language="java" import="com.test.hbm.HibernateSessionFactory"%>
<%@ page language="java" import="org.hibernate.SessionFactory"%>
<%@ page language="java" import="org.hibernate.Session"%>
<%@ page language="java" import="com.test.models.Book"%>
<%@ page language="java" import="com.test.common.Funs"%>
<%@ page language="java" import="org.hibernate.Query,org.hibernate.SQLQuery"%>
<%@page import="java.sql.ResultSet,java.sql.Connection,java.sql.CallableStatement"%>
<%@page import="org.hibernate.Hibernate"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">   
<title>Hibernate call ms sql server 2008 procedure</title>   
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">    
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="css/css01.css">
</head>  
<body>
<p>
Hibernate call ms sql server 2008 SQL/Procedure [option with parameters]<br>
Hibernate 调用MS SQL Server2008的SQL语句或存储过程，有参无参两种情况
</p>

<p><b>一，通过createQuery调用存储过程</b></p>  
<% 
//SessionFactory sf = HibernateSessionFactory.getSessionFactory();
//HibernateSessionFactory.rebuildSessionFactory();
//out.print("<br><br>hs object : " + hs.toString());
Session hs = HibernateSessionFactory.getSession();
out.print("<br>QueryString [无参] : " + hs.getNamedQuery("getAllBook").getQueryString());
List list = hs.getNamedQuery("getAllBook").list();
if(list.isEmpty()){
	out.print("<br><br>---------No data list---------");
}else{
	out.print("<br><br>");
	Iterator it = list.iterator();
	out.print("<table>");
	out.print("<tr bgColor=\"#cdcdcd\">");
	out.print("<td>ID</td>"); 
	out.print("<td>BookName</td>"); 
	out.print("<td>categoryID</td>"); 
	out.print("<td>Description</td>"); 
	out.print("<td>addTime</td>"); 
	out.print("<td>mark</td>"); 
	out.print("</tr>");
	while(it.hasNext()){
		Book book = (Book)it.next();
		out.print("<tr>");
		out.print("<td>" + book.getBid() + "</td>");
		//out.print("<td>" + Funs.str2UTF8(book.getBname()) + "</td>");
		out.print("<td>" + book.getBname() + "</td>");
		out.print("<td>" + book.getBcategory() + "</td>");
		out.print("<td>" + book.getBdescriptioin() + "</td>");
		out.print("<td>" + book.getBaddTime() + "</td>");
		out.print("<td>" + book.getBmark() + "</td>");
		out.print("</tr>");
	}
	out.print("</table>");
}
%>
<hr size="1" color="#999999">
<%  
out.print("<br><br>QueryString [有参] : " + hs.getNamedQuery("getBookByCategoryName").getQueryString());
hs = HibernateSessionFactory.getSession();
Query query = hs.getNamedQuery("getBookByCategoryName");
query.setInteger(0,1); //Hibernate的参数设置下标从 0 开始
query.setString(1,"java");
list = query.list();
if(list.isEmpty()){
	out.print("<br><br>---------No data list---------"); 
}else{
	out.print("<br><br>");
	Iterator it = list.iterator();
	out.print("<table>");
	out.print("<tr bgColor=\"#cdcdcd\">");
	out.print("<td>ID</td>");
	out.print("<td>BookName</td>");
	out.print("<td>categoryID</td>");
	out.print("<td>Description</td>");
	out.print("<td>addTime</td>");
	out.print("<td>mark</td>");
	out.print("</tr>");
	while(it.hasNext()){
		Book book = (Book)it.next();
		out.print("<tr>");
		out.print("<td>" + book.getBid() + "</td>");
		//out.print("<td>" + Funs.str2UTF8(book.getBname()) + "</td>");
		out.print("<td>" + book.getBname() + "</td>");
		out.print("<td>" + book.getBcategory() + "</td>");
		out.print("<td>" + book.getBdescriptioin() + "</td>");
		out.print("<td>" + book.getBaddTime() + "</td>");
		out.print("<td>" + book.getBmark() + "</td>");
		out.print("</tr>");
	} 
	out.print("</table>");
}
%>
<br>

<hr size="2" color="blue">
<p><b>二，通过createSQLQuery调用存储过程</b></p>
<% 
out.print("<br>QueryString [无参] : " + hs.getNamedQuery("getAllBook").getQueryString());
SQLQuery query3=hs.createSQLQuery("{call getAllBook()}");
query3.addEntity(Book.class);

//上面两行等同于下面一行，把查询的结果绑定到对应的实体类，查询结果和实体类已经在hibernate的XML完成映射[字段名和实体属性名的映射]
//SQLQuery query3=hs.createSQLQuery("{call getAllBook()}").addEntity(Books.class);

list = query3.list();
if(list.isEmpty()){
	out.print("<br><br>---------No data list---------");
}else{
	out.print("<br><br>");
	Iterator it = list.iterator();
	out.print("<table>");
	out.print("<tr bgColor=\"#cdcdcd\">");
	out.print("<td>ID</td>");
	out.print("<td>BookName</td>");
	out.print("<td>categoryID</td>");
	out.print("<td>Description</td>");
	out.print("<td>addTime</td>");
	out.print("<td>mark</td>");
	out.print("</tr>");
	while(it.hasNext()){
		Book book = (Book)it.next();
		out.print("<tr>");
		out.print("<td>" + book.getBid() + "</td>");
		out.print("<td>" + book.getBname() + "</td>");
		out.print("<td>" + book.getBcategory() + "</td>");
		out.print("<td>" + book.getBdescriptioin() + "</td>");
		out.print("<td>" + book.getBaddTime() + "</td>");
		out.print("<td>" + book.getBmark() + "</td>");
		out.print("</tr>");
	}
	out.print("</table>");
}
%>
<hr size="1" color="#999999">
<% 
out.print("<br><br>QueryString [有参] : " + hs.getNamedQuery("getBookByCategoryName").getQueryString());
hs = HibernateSessionFactory.getSession();
SQLQuery query4 = hs.createSQLQuery("{call getBookByCategoryName(?,?)}").addEntity(Book.class);
query4.setInteger(0,1);
query4.setString(1,"java");
list = query4.list();
if(list.isEmpty()){
	out.print("<br><br>---------No data list---------"); 
}else{
	out.print("<br><br>");
	Iterator it = list.iterator();
	out.print("<table>");
	out.print("<tr bgColor=\"#cdcdcd\">");
	out.print("<td>ID</td>");
	out.print("<td>BookName</td>");
	out.print("<td>categoryID</td>");
	out.print("<td>Description</td>");
	out.print("<td>addTime</td>");
	out.print("<td>mark</td>");
	out.print("</tr>");
	while(it.hasNext()){
		Book book = (Book)it.next();
		out.print("<tr>");
		out.print("<td>" + book.getBid() + "</td>");
		//out.print("<td>" + Funs.str2UTF8(book.getBname()) + "</td>");
		out.print("<td>" + book.getBname() + "</td>");
		out.print("<td>" + book.getBcategory() + "</td>");
		out.print("<td>" + book.getBdescriptioin() + "</td>");
		out.print("<td>" + book.getBaddTime() + "</td>");
		out.print("<td>" + book.getBmark() + "</td>");
		out.print("</tr>");
	} 
	out.print("</table>");
}
%>
<hr size="2" color="blue">
<p><b>三，通过JDBC方式调用存储过程</b></p>

<% 
hs =HibernateSessionFactory.getSession();   
Connection conn = hs.connection();   
ResultSet rs =null;  
CallableStatement cs = conn.prepareCall("{Call getAllBook()}");  
rs = cs.executeQuery();  
out.print("<br>调用无参数的存储过程：");
out.print("<table>");
out.print("<tr bgColor=\"#cdcdcd\">");
out.print("<td>ID</td>");
out.print("<td>BookName</td>");
out.print("<td>categoryID</td>");
out.print("<td>Description</td>");
out.print("<td>addTime</td>");
out.print("<td>mark</td>");
out.print("</tr>");
while(rs.next()){
		//Book book = (Book)it.next();
		out.print("<tr>");
		out.print("<td>" + rs.getInt("bid") + "</td>");
		out.print("<td>" + rs.getString("bName") + "</td>");
		out.print("<td>" + rs.getInt("bCategory") + "</td>");
		out.print("<td>" + rs.getString("bDescriptioin") + "</td>");
		out.print("<td>" + rs.getTimestamp("bAddTime") + "</td>");
		out.print("<td>" + rs.getString("bMark") + "</td>");
		out.print("</tr>");
}
out.print("<table>");
rs = null;  
cs = null;
conn = null;
%>
<hr size="1" color="#999999">
<% 
hs =HibernateSessionFactory.getSession();   
Connection conn2 = hs.connection();   
ResultSet rs2 =null;  
CallableStatement cs2 = conn2.prepareCall("{call getBookByCategoryName(?,?)}");  
cs2.setInt(1,1); //CallableStatement的参数设置下标从1 开始
cs2.setString(2,"java");
rs2 = cs2.executeQuery();  
out.print("<br>调用有参数的存储过程：");
out.print("<table>");
out.print("<tr bgColor=\"#cdcdcd\">");
out.print("<td>ID</td>");
out.print("<td>BookName</td>");
out.print("<td>categoryID</td>");
out.print("<td>Description</td>");
out.print("<td>addTime</td>");
out.print("<td>mark</td>");
out.print("</tr>");
while(rs2.next()){
		out.print("<tr>");
		out.print("<td>" + rs2.getInt("bid") + "</td>");
		out.print("<td>" + rs2.getString("bName") + "</td>");
		out.print("<td>" + rs2.getInt("bCategory") + "</td>");
		out.print("<td>" + rs2.getString("bDescriptioin") + "</td>");
		out.print("<td>" + rs2.getTimestamp("bAddTime") + "</td>");
		out.print("<td>" + rs2.getString("bMark") + "</td>");
		out.print("</tr>");
}
out.print("<table>");
rs2=null;  
cs2 = null;
conn2 = null;
%>
<hr size="2" color="blue">
<p><b>四，通过createSQLQuery方式调用SQL语句/存储过程返回单个或部分字段</b></p>
<%
out.print("<br>[无参情况, 求总共有多少本书]: ");
String totalNum = hs.createSQLQuery("select count(bid) as totalNum from Books").addScalar("totalNum",Hibernate.INTEGER).list().get(0).toString();
out.print("<br>totalNum = " + totalNum );
%>
<hr size="1" color="#999999">
<% 
out.print("<br>[有参情况, 通过ID找书名]: "); 
SQLQuery query5 = hs.createSQLQuery("select bName as bookName,bAddTime as bookAddTime from Books where bid = ?");
query5.setInteger(0,1);
query5.addScalar("bookName",Hibernate.STRING);
query5.addScalar("bookAddTime",Hibernate.TIMESTAMP);
String bookName = ((Object[])query5.list().get(0))[0].toString();
String bookAddTime = ((Object[])query5.list().get(0))[1].toString();
out.print("<br>条件bid = 1, 查找结果: bookName = " + bookName + " , bookAddTime = " + bookAddTime );
//上面这里仅取List中的第一个，list中每个元素相当于一条记录，记录中的字段数量跟SQL语句中的字段数量一样；如果取全部，则循环取出来，
List list5 = query5.list();
for(int i=0; i<list5.size(); i++){
	Object[] obj = (Object[])query5.list().get(i); //取出list中第i个元素(即记录),记录中字段数量跟SQL语句中的数量一样  
	out.print("<br>条件bid = 1, 查找结果: bookName = " + obj[0].toString());
	out.print(" , bookAddTime = " + obj[1].toString());
}
%>

<hr size="2" color="blue">
<p><b>五，通过createQuery方式调用hibernate中映射的XML文件中的HQL语句返回单个或部分或全部字段</b></p>
<% 

%>
<br>
<br>
<br>
<br>
<hr size="2" color="blue">
<p><b>六，通过createSQLQuery方式调用hibernate中映射的XML文件中的SQL语句返回单个或部分或全部字段</b></p>
<% 

%>
<br>
<br>参考 http://www.iteye.com/topic/176032
</body>
</html>

涉及函数：

public class Funs {
	public static String str2UTF8(String refString){
		//return String width UTF-8 encode
		if(null == refString || refString.trim().isEmpty()){
			return "";
		}
		String tempString = "";
		try {
			tempString = new String(refString.getBytes("ISO-8859-1"),"UTF-8");	
		} catch (Exception e) {
			tempString = "";
		}
		return tempString;
     }
}