- -------存储过程
- 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;
- }