测试spring的存储过程

测试spring的存储过程

- 中国WEB开发者网络 (http://www.webasp.net)
-- 技术教程 (http://www.webasp.net/article/)
--- 测试spring的存储过程 (http://www.webasp.net/article/22/21511.htm)

-- 作者：未知
-- 发布日期： 2005-04-30

测试spring的存储过程
spring对存储过程进行封装.它的实现细节与jdbc类似

下面进行测试

1):写存储过程执行类:package jdbc;import java.sql.Types;import java.util.HashMap;import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.object.StoredProcedure;import org.springframework.jdbc.core.SqlOutParameter;import org.springframework.jdbc.core.SqlParameter;

public class StoreTemplate    extends StoredProcedure {

    HashMap map = new HashMap();     public StoreTemplate() { super();

    }

    public void setValue(String key, Object obj) { map.put(key, obj);    }

    public StoreTemplate(DataSource ds) { setDataSource(ds);    }     public Map execute() {        if(this.getSql()==null || this.getSql().equals("")) return null; this.compile(); return execute(map);    }

    public void setVarcharParam(String param) { this.declareParameter(new SqlParameter(param, Types.VARCHAR));    }     public void setDoubleParam(String param) { this.declareParameter(new SqlParameter(param, Types.DOUBLE));    }     public void setIntegerParam(String param) { this.declareParameter(new SqlParameter(param, Types.INTEGER));    }     public void setVarcharOutParam(String param) { this.declareParameter(new SqlOutParameter(param, Types.VARCHAR));    }     public void setDoubleOutParam(String param) { this.declareParameter(new SqlOutParameter(param, Types.DOUBLE));    }    public void setIntegerOutParam(String param) { this.declareParameter(new SqlOutParameter(param, Types.INTEGER));    }}

2):用spring配置数据源：<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>       <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  <property name="driverClassName"><value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value></property>  <property name="url"><value>jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs;SelectMethod=Cursor</value></property>  <property name="username"><value>sa</value></property>  <property name="password"><value></value></property> </bean></beans>

3):写junit:package jdbc;import junit.framework.*;import org.springframework.context.ApplicationContext;import org.springframework.context.support.FileSystemXmlApplicationContext;import java.util.List;import java.util.Map;import javax.sql.DataSource;public class TestStoreTemplate extends TestCase { ApplicationContext ctx=null; protected void setUp() throws Exception {      ctx= new FileSystemXmlApplicationContext("D:\\work\\jpetstore\ rc\\jdbc\\Context-jdbc.xml"); } public void testStore(){     DataSource datasource=(DataSource)ctx.getBean("dataSource");      StoreTemplate qry=new StoreTemplate(datasource);     qry.setSql("testsp");     qry.setIntegerParam("count");      qry.setIntegerOutParam("ret");     qry.setValue("count",new Integer(1));     Map map=qry.execute();     if(map!=null){  System.out.println(map.get("ret"));     } }

    protected void tearDown() throws Exception {

}

}

4):写测试存储过程SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure testsp(@count int,@ret int out)asbegin select @ret=@count+1endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

5):编译运行 ok.

6):附jdbc调用存储过程的方法

package jdbc;

import junit.framework.*;import org.springframework.context.ApplicationContext;import org.springframework.context.support.FileSystemXmlApplicationContext;

import javax.sql.DataSource;import java.sql.*;

public class TestJdbcCallStore    extends TestCase {    ApplicationContext ctx = null;    protected void setUp() throws Exception { ctx = new FileSystemXmlApplicationContext("D:\\work\\jpetstore\ rc\\jdbc\\Context-jdbc.xml");    }

    public void testStore() { DataSource datasource = (DataSource) ctx.getBean("dataSource"); CallableStatement cstmt = null; try {     cstmt = datasource.getConnection().prepareCall(  "{call testsp(?,?)}");     cstmt.setInt(1, 1);     cstmt.registerOutParameter(2, Types.INTEGER);     cstmt.executeUpdate();     Object obj = cstmt.getObject(2);     if (obj != null) {  System.out.println(obj.toString());     } } catch (SQLException es) {     es.printStackTrace(System.out); } finally {

}    }

    protected void tearDown() throws Exception {

    }

}

//为了简单，有些代码省去了，象transaction.

mzhanker@126.com

webasp.net