笔者参考原文:http://www.iteye.com/topic/176032
使用hibernate 调用存储过程。
public class StuInfo {
private int id;
private String stuName;
private String stuNo;
private int stuAge;
private String stuId;
private String stuSeat;
private String stuAddress;
setters();getters();
}
对应的数据库表:
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo /*创建学员信息表**/
(
stuName varchar(20) not null,-- 姓名,非空
stuNo char(6) not null,-- 学号,非空
stuAge int not null,-- 年齡,int 默认为4个长度
stuId numeric(18,0),
stuSeat smallint ,-- 坐位车,使用自增
stuAddress text -- 住址 可以为空
)
-- 给stuInfo添加一列
alter table stuInfo add id int identity(1,1) primary key;
创建存储过程:
-- 存储过程
if exists(select name from sysobjects where name='proc_stuInfo' and type='p')
drop proc proc_stuInfo
go
create proc proc_stuInfo
as
select * from stuInfo
go
-- 调用存储过程
exec proc_stuInfo;
在hibernate 中调用存储过程的几种方法。
第一种:命名查询
<sql-query name="getStuInfo" callable="true">
<return alias="stuInfo" class="com.hkrt.domain.StuInfo">
<return-property name="id" column="id" />
<return-property name="stuName" column="stuName" />
<return-property name="stuAge" column="stuAge" />
<return-property name="stuNo" column="stuNo"/>
<return-property name="stuSeat" column="stuSeat" />
<return-property name="stuAddress" column="stuAddress"/>
<return-property name="stuId" column="stuId"/>
</return>
{call proc_stuInfo()}
</sql-query>
List li=session.getNamedQuery("getStuInfo").list();
System.out.println(li.get(0));
第二种:类型于jdbc
System.out.println("jdbc 调用-------------");
Connection conn = session.connection();
ResultSet rs =null;
CallableStatement call;
try {
call = conn.prepareCall("{Call proc_stuInfo()}");
rs = call.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getString(4));
System.out.println(rs.getString(5));
System.out.println(rs.getString(6));
System.out.println(rs.getString(7));
System.out.println("------------------");
}
} catch (SQLException e) {
e.printStackTrace();
}
第三种:最简单的一种
SQLQuery query = session.createSQLQuery("{call proc_stuInfo()}").addEntity(StuInfo.class);
List list =query.list();
System.out.println(list.get(0));
注:在第三种调用时,一定要加上addEntity();否则没有数据返回。
hibenate 调用带参的存储程
-- 带参数据的存储过程
if exists(select name from sysobjects where name='proc_find_stu' and type='p')
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int,@endId int)
as
select * from stuInfo where id between @startId and @endId;
go
exec proc_find_stu 1,4;
/**hibernate 调用带参的存储过程*/
@SuppressWarnings("unchecked")
@Test
public void msTest2(){
SessionFactory sf = SessionFactoyUtil.getSessionFactory();
Session session = sf.openSession();
SQLQuery query = session.createSQLQuery("{CALL proc_find_stu(?,?)}").addEntity(StuInfo.class);
query.setLong(0, 2);
query.setLong(1, 4);
List<StuInfo> list =query.list();
for(int i =0;i<list.size();i++){
System.out.println(list.get(i));
}
}
Hibernate:
{CALL proc_find_stu(?,?)}
StuInfo [id=2, stuAddress=北京, stuAge=12, stuId=123456789012345687, stuName=李四, stuNo=112345, stuSeat=2]
StuInfo [id=4, stuAddress=北京, stuAge=12, stuId=123456789012345688, stuName=王五, stuNo=112345, stuSeat=3]