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的顺序要和存储过程中參数的顺序要一致,不然也会报错.