Hibernate调用存储过程

最新推荐文章于 2023-07-26 16:55:55 发布

原创最新推荐文章于 2023-07-26 16:55:55 发布 · 691 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #hibernate #session #string #encoding #测试

ssh 专栏收录该内容

31 篇文章

订阅专栏

Sql代码

-------存储过程
create procedure insertEmp
@emName varchar(50),
@emAge int
as
begin
insert into employee values(@emName,@emAge);
end
create procedure updateEmp
@emId int,
@emName varchar(50),
@emAge int
as
begin
update employee set emName=@emName,emAge=@emAge where emId=@emId;
end
create procedure deleteEmp
@emId int
as
begin
delete employee where emId=@emId;
end
create procedure getEmpList
as
begin
select * from employee
create procedure insertEmp
@emName varchar(50),
@emAge int
as
begin
insert into employee values(@emName,@emAge);
end
create procedure updateEmp
@emId int,
@emName varchar(50),
@emAge int
as
begin
update employee set emName=@emName,emAge=@emAge where emId=@emId;
end
create procedure deleteEmp
@emId int
as
begin
delete employee where emId=@emId;
end
create procedure getEmpList
as
begin
select * from employee

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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.pojo.Employee" table="employee" catalog="master">
<id name="emId" type="java.lang.Integer">
<column name="emId" />
<generator class="native" />
</id>
<property name="emName" type="java.lang.String">
<column name="emName" length="20" />
</property>
<property name="emAge" type="java.lang.Integer">
<column name="emAge" />
</property>
</class>
<!-- 在该文件中需注意<sql-query…></sql-query>中的这段代码，调用的存储过程在其中定义，
并定义了调用存储过程后将记录组装成Emp对象，同时对记录的字段与对象的属性进行相关映射。 -->
<sql-query name="getEmpList" callable="true">
<return alias="employee" class="com.pojo.Employee">
<return-property name="emId" column="emId" />
<return-property name="emName" column="emName" />
<return-property name="emAge" column="emAge" />
</return>
{call getEmpList}
</sql-query>
</hibernate-mapping>

Java代码

// 测试实现查询的存储过程
private void testProcQuery(Session session) throws Exception {
// 查询用户列表
List list = session.getNamedQuery("getEmpList").list();
for (int i = 0; i < list.size(); i++) {
Employee em = (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());
}
}
private void testProcUpdate(Session session) throws Exception {
// 更新用户信息
Transaction tx = session.beginTransaction();
Connection con = session.connection();
String procedure = "{call updateEmp(?, ?, ?)}";
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1, 2);
cstmt.setString(2, "ddd");
cstmt.setInt(3, 100);
cstmt.executeUpdate();
tx.commit();
}
// 测试实现插入的存储过程
private void testProcInsert(Session session) throws Exception {
// 创建用户信息
session.beginTransaction();
PreparedStatement st = session.connection().prepareStatement(
"{call insertEmp(?,?)}");
st.setString(1, "阿蜜果");
st.setInt(2, 12);
st.execute();
session.getTransaction().commit();
}
// 测试实现删除的存储过程
private void testProcDelete(Session session) throws Exception {
// 删除用户信息
session.beginTransaction();
PreparedStatement st = session.connection().prepareStatement(
"{call deleteEmp(?)}");
st.setInt(1, 1);
st.execute();
session.getTransaction().commit();
}
public static void main(String[] args) throws Exception {
ProTest pt=new ProTest();
Session session=HibernateSessionFactory.getSession();
pt.testProcQuery(session);
//pt.testProcInsert(session);
//pt.testProcDelete(session);
//pt.testProcUpdate(session);
}
-------------- 得到输出参数 ------------------
public String countFeeByHand(final Date startTime, final String operBizId)
throws DataAccessException {
String destroyFee = (String) this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
CallableStatement cs = session.connection().prepareCall(
"{call MANUAL_USER_SETTLEMENT(?,?,?,?)}");// 存储过程调用有错误
cs.setString(1, DateTools.getYearMonthString(startTime));
cs.setString(2, operBizId);
cs.registerOutParameter(3, Types.INTEGER);
cs.registerOutParameter(4, Types.CHAR);
cs.execute();
return cs.getString(4); //得到输出参数
}
});
return destroyFee;
}