测试spring的存储过程

luckfly

测试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配置数据源：
      
  com.microsoft.jdbc.sqlserver.SQLServerDriver
  jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs;SelectMethod=Cursor
  sa
  

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\\src\\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
GO
SET ANSI_NULLS ON
GO
create  procedure testsp(@count  int,@ret int out)
as
begin
select @ret=@count+1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET 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\\src\\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.


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/15511/showart_355605.html