Hibernate调用存储过程-优快云博客

Sql代码

-------存储过程
createprocedureinsertEmp
@emNamevarchar(50),
@emAgeint
as
begin
insertintoemployeevalues(@emName,@emAge);
end
createprocedureupdateEmp
@emIdint,
@emNamevarchar(50),
@emAgeint
as
begin
updateemployeesetemName=@emName,emAge=@emAgewhereemId=@emId;
end
createproceduredeleteEmp
@emIdint
as
begin
deleteemployeewhereemId=@emId;
end
createproceduregetEmpList
as
begin
select*fromemployee
createprocedureinsertEmp
@emNamevarchar(50),
@emAgeint
as
begin
insertintoemployeevalues(@emName,@emAge);
end
createprocedureupdateEmp
@emIdint,
@emNamevarchar(50),
@emAgeint
as
begin
updateemployeesetemName=@emName,emAge=@emAgewhereemId=@emId;
end
createproceduredeleteEmp
@emIdint
as
begin
deleteemployeewhereemId=@emId;
end
createproceduregetEmpList
as
begin
select*fromemployee

Xml代码

<?xmlversion="1.0"encoding="utf-8"?>
<!DOCTYPEhibernate-mappingPUBLIC"-//Hibernate/HibernateMappingDTD3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
MappingfileautogeneratedbyMyEclipsePersistenceTools
-->
<hibernate-mapping>
<classname="com.pojo.Employee"table="employee"catalog="master">
<idname="emId"type="java.lang.Integer">
<columnname="emId"/>
<generatorclass="native"/>
</id>
<propertyname="emName"type="java.lang.String">
<columnname="emName"length="20"/>
</property>
<propertyname="emAge"type="java.lang.Integer">
<columnname="emAge"/>
</property>
</class>
<!--在该文件中需注意<sql-query…></sql-query>中的这段代码，调用的存储过程在其中定义，
并定义了调用存储过程后将记录组装成Emp对象，同时对记录的字段与对象的属性进行相关映射。-->
<sql-queryname="getEmpList"callable="true">
<returnalias="employee"class="com.pojo.Employee">
<return-propertyname="emId"column="emId"/>
<return-propertyname="emName"column="emName"/>
<return-propertyname="emAge"column="emAge"/>
</return>
{callgetEmpList}
</sql-query>
</hibernate-mapping>

Java代码

//测试实现查询的存储过程
privatevoidtestProcQuery(Sessionsession)throwsException{
//查询用户列表
Listlist=session.getNamedQuery("getEmpList").list();
for(inti=0;i<list.size();i++){
Employeeem=(Employee)list.get(i);
System.out.print("序号:"+(i+1));
System.out.print(",emid:"+em.getEmId());
System.out.print(",emname:"+em.getEmName());
System.out.println(",emage:"+em.getEmAge());
}
}
privatevoidtestProcUpdate(Sessionsession)throwsException{
//更新用户信息
Transactiontx=session.beginTransaction();
Connectioncon=session.connection();
Stringprocedure="{callupdateEmp(?,?,?)}";
CallableStatementcstmt=con.prepareCall(procedure);
cstmt.setInt(1,2);
cstmt.setString(2,"ddd");
cstmt.setInt(3,100);
cstmt.executeUpdate();
tx.commit();
}
//测试实现插入的存储过程
privatevoidtestProcInsert(Sessionsession)throwsException{
//创建用户信息
session.beginTransaction();
PreparedStatementst=session.connection().prepareStatement(
"{callinsertEmp(?,?)}");
st.setString(1,"阿蜜果");
st.setInt(2,12);
st.execute();
session.getTransaction().commit();
}
//测试实现删除的存储过程
privatevoidtestProcDelete(Sessionsession)throwsException{
//删除用户信息
session.beginTransaction();
PreparedStatementst=session.connection().prepareStatement(
"{calldeleteEmp(?)}");
st.setInt(1,1);
st.execute();
session.getTransaction().commit();
}
publicstaticvoidmain(String[]args)throwsException{
ProTestpt=newProTest();
Sessionsession=HibernateSessionFactory.getSession();
pt.testProcQuery(session);
//pt.testProcInsert(session);
//pt.testProcDelete(session);
//pt.testProcUpdate(session);
}
--------------得到输出参数------------------
publicStringcountFeeByHand(finalDatestartTime,finalStringoperBizId)
throwsDataAccessException{
StringdestroyFee=(String)this.getHibernateTemplate().execute(
newHibernateCallback(){
publicObjectdoInHibernate(Sessionsession)
throwsHibernateException,SQLException{
CallableStatementcs=session.connection().prepareCall(
"{callMANUAL_USER_SETTLEMENT(?,?,?,?)}");//存储过程调用有错误
cs.setString(1,DateTools.getYearMonthString(startTime));
cs.setString(2,operBizId);
cs.registerOutParameter(3,Types.INTEGER);
cs.registerOutParameter(4,Types.CHAR);
cs.execute();
returncs.getString(4);//得到输出参数
}
});
returndestroyFee;
}