一. 建表与初始化数据
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;
}
- public class ExecuteProceduresDaoImpl extends JdbcDaoSupport implements ExecuteProceduresDao {
- public Object Call_prLS_OrderByMemberOrNotMember(final String[] parm) {
- String procedureSql = "{?=call prLS_OrderByMemberOrNotMember(?,?,?,?,?,?,?,?,?,?)}";
- return (Object) getJdbcTemplate().execute(procedureSql, new CallableStatementCallback() {
- public Object doInCallableStatement(CallableStatement cs)
- throws SQLException, DataAccessException {
- int j = 2;
- cs.registerOutParameter(1, Types.INTEGER);
- if (parm != null) {
- for (int i = 0; i < parm.length; i++) {
- cs.setString(j, parm[i]);
- ++j;
- }
- }
- if (cs.execute()) {
- ResultSet rs = cs.getResultSet();
- while (rs.next()) {
- rs.getString(1);
- rs.getString(2);
- rs.getString(3);
- }
- return null;
- } else {
- return cs.getInt(1);
- }
- }
- });
- }
- }