SSH中调用带返回结果集的存储过程:
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); } } }); } }
SSH中调用带返回值的存储过程:
public class RechargeDAOImpl extends JdbcDaoSupport implements RechargeDAO { static int result = -1; public int rechargeCard() { String procedureSql = "{? = call sp_pb_addmoney(?,?,?,?,?,?,?,?,?,?,?,?,?)}"; this.getJdbcTemplate().execute(procedureSql, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cstmt) throws SQLException, DataAccessException { cstmt.registerOutParameter(1, java.sql.Types.INTEGER); cstmt.setInt(2, 2);//dptid cstmt.setInt(3, 000002);// sysno;char;6 cstmt.setString(4, "0100100000006321");//serial;char;16 cstmt.setDate(5, null);//dealdate;datetime;8; cstmt.setInt(6, 100);//bagvalue;money;8 cstmt.setInt(7, 0);//oldbagvalue;money;8; cstmt.setInt(8, 0);//icanorder;int; cstmt.setInt(9, 0);//cardtransno;int cstmt.setString(10, "0");//operid;varchar cstmt.setInt(11, 0);//subappid;int cstmt.setDate(12, null);//expiredDate;datetime;8 cstmt.setString(13, "0");//tac;varchar;8 cstmt.setInt(14, 1);//@returnv;设置-1,0,1 cstmt.execute(); result = cstmt.getInt(1);//返回值 return result; } }); return result; } }
补充:
继承了JdbcDaoSupport ,需要Spring助于dataSource对象.
配置的例子:
<bean id="recharegeDAO" class="com.potevio.quancun.dao.impl.RechargeDAOImpl" scope="singleton">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
</bean>
<bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource" > <property name="alias" value="Pool"/> <property name="driver" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/> <property name="driverUrl" value="jdbc:microsoft:sqlserver://172.16.11.20:1433;user=sa;password=111;DatabaseName=news;SelectMethod=cursor"/> <property name="user" value="sa"/> <property name="password" value="111"/> <property name="houseKeepingSleepTime" value="90000"/> <property name="prototypeCount" value="5"/> <property name="maximumConnectionCount" value="100"/> <property name="minimumConnectionCount" value="10"/> <property name="trace" value="true"/> <property name="verbose" value="true"/> </bean>