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。