Hibernate 调用存储过程
jopen
13年前
-------存储过程 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 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> // 测试实现查询的存储过程 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; }