IDC资讯中心
测试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
