大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
http://blog.csdn.net/xiao_jun_0820/article/details/7268219
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));
- }
- }
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());
- }
- }
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://www.w3china.org/blog/more.asp?name=hongrui&id=5156
[java语言]spring对oracle的clob和StoredProcedure 的处理 邢红瑞 发表于 2005-4-21 9:16:20 |
|
|
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/164512.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...