測試spring的預存程序

來源:互聯網
上載者:User
預存程序 測試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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。