測試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
|
|
當前位置：開發者網絡 >> 技術教程 >> JSP教程 >> Java技巧及代碼 >> 內容
