笔者参考原文:http://www.iteye.com/topic/176032
- 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));
- 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]