spring 中StoredProcedure的用法--转载_语言综合

概述 StoredProcedure是一个抽象类，必须写一个子类来继承它，这个类是用来简化JDBCTemplate执行存储过程 *** 作的。首先我们写一个实现类：package com.huaye.framework.dao;import java.sql.Types;import java.util.HashMap;import java.util.Map;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.SqlOutParameter;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.core.SqlReturnResultSet;import org.springframework.jdbc.object.StoredProcedure;/*** Name:StoredProcedureTemplate User: HP Date: 2007-7-21 Time: 7:40:01* Description:*/public class StoredProcedureTemplate extends StoredProcedure {private HashMap<String, Object> map = new HashMap<String, Object>();public StoredProcedureTemplate() {super();}public HashMap getMap(){return this.map;}public void setValue(String key, Object obj) {map.put(key, obj);}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));}public void setInParam(String param,int valueType){this.declareParameter(new SqlParameter(param, valueType));}public void setOutParam(String param,int valueType){this.declareParameter(new SqlOutParameter(param, valueType));}public void setReturnParam(String param, RowMapper rowMapper) {this.declareParameter(new SqlReturnResultSet(param,rowMapper));}}写一个测试：public void test2() {ApplicationContext context = new ClassPathXmlApplicationContext("classpath:spring/applicationContext-base.xml");JdbcTemplate jdbc = (JdbcTemplate) context.getBean("jdbcTemplate");StoredProcedureTemplate template = new StoredProcedureTemplate();template.setJdbcTemplate(jdbc);template.setSql("testproc");//注意有返回结果集的时候，第一个参数必须设置为返回结果集参数，不然会报错。template.setReturnParam("rows", new FirstReportRowMapper());template.setIntegerParam("@parama");template.setValue("@parama", 9);Map map = template.execute();Object o = map.get("rows");List<FirstReportVO> list = (List<FirstReportVO>)o;for (FirstReportVO vo : list) {System.out.println(vo.getSortID()+","+vo.getSortName());}}唯一要注意的地方就是测试里备注的地方，我测试了好久才发现，郁闷的一塌糊涂，老是莫名其妙的错，原来将参数互换一下位置就OK了，比如你把template.setIntegerParam("@parama");写在前面然后再写template.setReturnParam("rows", new FirstReportRowMapper());的话，就会报空指针错误。这个“rows”可以随便取名字，不过下面map.get("rows")要和你取的名字一致，因为StoredProcedureTemplate会将结果集以这个名字保存在map中返回。还有要注意的就是设置sqlparamter的顺序要和存储过程中参数的顺序要一致，不然也会报错. 原文：http://blog.csdn.net/xiao_jun_0820/article/details/7268219 http://forum.spring.io/forum/spring-projects/data/13984-multiple-calls-to-storedprocedure-using-same-connection

StoredProcedure是一个抽象类，必须写一个子类来继承它，这个类是用来简化JDBCTemplate执行存储过程 *** 作的。

首先我们写一个实现类：

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.sqlOutParameter;
import org.springframework.jdbc.core.sqlParameter;
import org.springframework.jdbc.core.sqlReturnResultSet;
import org.springframework.jdbc.object.StoredProcedure;

/**

name:StoredProcedureTemplate User: HP Date: 2007-7-21 Time: 7:40:01

Description:
*/
public class StoredProcedureTemplate extends StoredProcedure {

private HashMap<String,Object> map = new HashMap<String,Object>();

public StoredProcedureTemplate() {
super();

}

public HashMap getMap()
{
return this.map;
}

public voID setValue(String key,Object obj) {
map.put(key,obj);
}

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));
}

public voID setInParam(String param,int valueType)
{
this.declareParameter(new sqlParameter(param,valueType));

}

public voID setoutParam(String param,int valueType)
{
this.declareParameter(new sqlOutParameter(param,valueType));

}

public voID setReturnParam(String param,RowMapper rowMapper) {
this.declareParameter(new sqlReturnResultSet(param,rowMapper));
}

}

写一个测试：

= "classpath:spring/applicationContext-base.xml"= (JdbcTemplate) context.getBean("jdbcTemplate"

    StoredProcedureTemplate template </span>= <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; StoredProcedureTemplate();    template.setJdbcTemplate(jdbc);    template.set<a href="https://www.jb51.cc/tag/sql/" target="_blank" >sql</a>(</span>"testproc"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt;//</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #008000;"&gt;注意有返回结果集的时候，第<a href="https://www.jb51.cc/tag/yige/" target="_blank" >一个</a><a href="https://m.jb51.cc/tag/canshu/" target="_blank" >参数</a>必须<a href="https://m.jb51.cc/tag/shezhi/" target="_blank" >设置</a>为返回结果集<a href="https://m.jb51.cc/tag/canshu/" target="_blank" >参数</a>，不然会<a href="https://m.jb51.cc/tag/baocuo/" target="_blank" >报错</a>。</span>    template.setReturnP<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m("rows",<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;new</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; F<a href="https://www.jb51.cc/tag/irs/" target="_blank" >irs</a>tReportRowMapper());    template.setIntegerP<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>m(</span>"@p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ma"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);    template.setValue(</span>"@p<a href="https://www.jb51.cc/tag/ara/" target="_blank" >ara</a>ma",9<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);    Map map </span>=<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; template.execute();    Object o </span>= map.get("rows"<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;);    <a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a></span><FirstReportVO> <a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a> = (<a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a><FirstReportVO><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;)o;    </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"&gt;for</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt; (F<a href="https://www.jb51.cc/tag/irs/" target="_blank" >irs</a>tReportVO vo : <a href="https://m.jb51.cc/tag/List/" target="_blank" >List</a>) {        Sy<a href="https://www.jb51.cc/tag/stem/" target="_blank" >stem</a>.out.println(vo.getSort<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>()</span>+","+<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"&gt;vo.getSort<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>());    }}</span></pre>

唯一要注意的地方就是测试里备注的地方，我测试了好久才发现，郁闷的一塌糊涂，老是莫名其妙的错，原来将参数互换一下位置就OK了，比如你把

template.setIntegerParam("@parama");写在前面然后再写template.setReturnParam("rows",new FirstReportRowMapper());的话，就会报空指针错误。

这个“rows”可以随便取名字，不过下面map.get("rows")要和你取的名字一致，因为StoredProcedureTemplate会将结果集以这个名字保存在map中返回。

还有要注意的就是设置sqlparamter的顺序要和存储过程中参数的顺序要一致，不然也会报错.

原文：http://blog.csdn.net/xiao_jun_0820/article/details/7268219

http://forum.spring.io/forum/spring-projects/data/13984-multiple-calls-to-storedprocedure-using-same-connection

总结

以上是内存溢出为你收集整理的spring 中StoredProcedure的用法--转载全部内容，希望文章能够帮你解决spring 中StoredProcedure的用法--转载所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/langs/1270018.html