测试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.
[email protected]
