在SSH项目中调用存储过程

 

一． 建表与初始化数据

create database user

create table userInfo

(

id int identity(1,1) primary key not null,

name varchar(20) not null,

age int not null

)

  建表成功后，在该表中任意插入几条数据。

二． 建立存储过程

create   PROCEDURE  searchproc

as 

      select * from prtab ;

----调用存储过程----  

exec searchproc;

三．User.hbm.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.yourcompany.struts.entity.UserInfo" table="prtab" schema="dbo" catalog="user">

        <id name="id" type="java.lang.Integer">

            <column name="id" />

            <generator class="native" />

        </id>

        <property name="name" type="java.lang.String">

            <column name="name" length="20" not-null="true" />

        </property>

        <property name="age" type="java.lang.Integer">

            <column name="age" not-null="true" />

        </property>

    </class>

    <sql-query name="getUser" callable="true">

    <return alias="UserInfo" class="com.yourcompany.struts.entity.UserInfo ">

    <return-property name="id" column="id" />

    <return-property name="name" column="name" />

    <return-property name="age" column="age" />

    </return>

    {call searchproc()}

    </sql-query>   

</hibernate-mapping>

 

在数据访问层调用存储过程

public List searchAll() {

     List list=getHibernateTemplate().findByNamedQuery("getUser");    

     return list;

   }

 

 

1. public class ExecuteProceduresDaoImpl extends JdbcDaoSupport implements ExecuteProceduresDao {   
2. public Object Call_prLS_OrderByMemberOrNotMember(final String[] parm) {   
3.      String procedureSql = "{?=call prLS_OrderByMemberOrNotMember(?,?,?,?,?,?,?,?,?,?)}";   
4.         return (Object) getJdbcTemplate().execute(procedureSql, new CallableStatementCallback() {   
5.                     public Object doInCallableStatement(CallableStatement cs)   
6.                             throws SQLException, DataAccessException {   
7.                         int j = 2;   
8.                          cs.registerOutParameter(1, Types.INTEGER);   
9.                         if (parm != null) {   
10.                             for (int i = 0; i < parm.length; i++) {   
11.                                  cs.setString(j, parm[i]);   
12.                                  ++j;   
13.                              }   
14.                          }   
15.                         if (cs.execute()) {   
16.                              ResultSet rs = cs.getResultSet();   
17.                             while (rs.next()) {   
18.                                  rs.getString(1);   
19.                                  rs.getString(2);   
20.                                  rs.getString(3);   
21.                              }   
22.                             return null;   
23.                          } else {   
24.                             return cs.getInt(1);   
25.                          }   
26.                      }   
27.                  });   
28.      }   
29.   
30. }