SSH中调用存储过程

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>