spring如何使用sqlserver存储过程

这个问题有人问了很久，StoredProcedure instances是线程安全的，一次初始化，可以多次重用。
发个sqlserver的例子
java代码
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.dao.DataAccessException;
import org.springframework.core.CollectionFactory;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.Collection;
import java.sql.*;

public class EbookProcedureDaoJdbc extends JdbcDaoSupport {
    private String procedureName;

    public List getSqlvalues() {
        return sqlvalues;
    }

    public void setSqlvalues(List sqlvalues) {
        this.sqlvalues = sqlvalues;
    }

    private List sqlvalues;

    public String getProcedureName() {
        return procedureName;
    }

    public void setProcedureName(String procedureName) {
        this.procedureName = procedureName;
    }

    public List execute() {
        final ArrayList list = new ArrayList();
        Object o = getJdbcTemplate().execute(genSqlStrig(), new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement arg0) throws SQLException, DataAccessException {
                arg0.registerOutParameter(1, Types.INTEGER);
                for (int i = 0; i < sqlvalues.size(); i++) {
                    arg0.setObject(i + 2, sqlvalues.get(i));
                }
                ResultSet rs = arg0.executeQuery();
                ListResultSetExtractor ls = new ListResultSetExtractor();
                list.addAll((Collection) ls.extractData(rs));
                return null;
            }

        });
        return list;
    }

    private String genSqlStrig() {
        StringBuffer sb = new StringBuffer();
        sb.append("{?= call ");
        sb.append(procedureName);
        sb.append("( ");
        if (sqlvalues != null && sqlvalues.size() > 0) {
            for (int i = 0; i < sqlvalues.size(); i++) {
                if (i == sqlvalues.size() - 1) {
                    sb.append("?");
                } else {
                    sb.append("?");
                }
            }

        }
        sb.append(")}");
        return sb.toString();
    }

    private static class ListResultSetExtractor  {

        public Object extractData(ResultSet rs) throws SQLException {
            List listOfRows = new ArrayList();
            ResultSetMetaData rsmd = null;
            int numberOfColumns = -1;
            while (rs.next()) {
                if (rsmd == null) {
                    rsmd = rs.getMetaData();
                    numberOfColumns = rsmd.getColumnCount();
                }
                Map mapOfColValues = CollectionFactory.createLinkedMapIfPossible(numberOfColumns);
                for (int i = 1; i <= numberOfColumns; i++) {
                    switch (rsmd.getColumnType(i)) {
                        case java.sql.Types.TIMESTAMP:
                            mapOfColValues.put(rsmd.getColumnName(i), rs.getTimestamp(i));
                            break;
                        default:
                            mapOfColValues.put(rsmd.getColumnName(i), rs.getObject(i));
                    }
                }
                listOfRows.add(mapOfColValues);
            }
            return listOfRows;
        }
    }
}
配置文件
<bean id="EbookProcedureDaoJdbc" class="com.ebook.dao.jdbc.EbookProcedureDaoJdbc" >
        <property name="dataSource">
            <ref local="dataSource1"/>
        </property>
        <property name="procedureName">
        <value>getzhongtubook</value>
        </property>
    </bean>
传入的存储过程的名字，返回一个list。
测试用例
import junit.framework.TestCase;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ebook.dao.jdbc.EbookProcedureDaoJdbc;

import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.util.Map;
import java.sql.ResultSet;

public class testDAO extends TestCase {
    private ApplicationContext context;

    protected void setUp() throws Exception {
       context = new ClassPathXmlApplicationContext("test.xml");
    }

    public void testProcedureDAO()
    {
      EbookProcedureDaoJdbc epd=(EbookProcedureDaoJdbc) context.getBean("EbookProcedureDaoJdbc");
      ArrayList ls=new ArrayList();
        List ls1=new ArrayList();
        ls.add("%001");
        epd.setSqlvalues(ls);
        ls1=epd.execute();
        Iterator it=ls1.iterator();
        while(it.hasNext())
        {
          Map map=(Map) it.next();
            System.out.println(map.get("bkname"));
        }
    }
}

使用jdbctemplate可以使用Stored procedures，有以下几种情况：

用 PreparedStatement使用RowMapper, ResultSetHandler, RowCallbackHandler得到list，唯一缺点只能返回一个result set，而且不能使用output parameters，这不是spring的问题，是PreparedStatement的限制。

例子

final String sql = "{ call UpdateStoredProcedure(?, ?, ?) }";
JdbcTemplate.update(sql, new Object[] { new Integer(5), "a", new Double(7.0) });

使用CallableStatements，例如 call(CallableStatementCreator csc, List declaredParameters)，使用CallableStatementCreator创建CallableStatement，declaredParameters可以用 input/output参数。

StoredProcedure可以得到多个 resultsets，并且重新排列output parameters。

oracle的例子看我以前的blog。