spring-jdbc 调用存储过程

spring作为java开源框架一直备受开发者的喜爱。在对数据库操作方面spring也做了很好的封装。在这里我随便说一下如何调用存储过程。

在讲之前我们先看传统的jdbc是怎么调用的：

以有返回结果集的存储过程为例（无返回的很简单，不多说了）

1.获取CallableStatement语句:

CallableStatement cs = conn.prepareCall("{call test(?,?,?)}");		//test是数据库中编写的存储过程名称，问号为参数

2.传入输入参数和注册输出参数

cs.setXXX(value);	//输入参数,value是你要输入的值
cs.registerOutParameter(type);		//type是注册输出参数的类型

3.执行存储过程:

cs.execute();

那么spring是如何处理的呢？Spring的SimpleJdbcTemplate将存储过程的调用进行了良好的封装，但要注意只能用于jdk1.5的环境，无法在jdk1.4环境下使用，而JdbcTemplate则完全适用于jdk1.5和jdk1.4下的环境

oracle为例：

1。无返回的存储过程

数据库中创建存储过程：

CREATE OR REPLACE PROCEDURE TEST(
	PARAM1 IN VARCHAR2,
	PARAM2 IN VARCHAR2
) AS
BEGIN
	dbms_output.put_line('param1=' || PARAM1 || ';param2=' || PARAM2 );
	insert into test(id,name) values(PARAM1 ,PARAM2 );		//将数据插入事先建立的text表中
END TESTPRO;

Java代码：    

package com.base.jdbc.test;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateTest {
	private JdbcTemplate jdbcTemplate;// 注入springj的JDBC模板

	public void test() {
		jdbcTemplate.execute("call test('1','测试')");// 调用存储过程,很简单
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}

2。有返回值的存储过程（非结果集）    ，相对复杂点。
创建存储过程：    

CREATE OR REPLACE PROCEDURE TEST(
	PARAM1 IN NUMBER,
	PARAM2 OUT VARCHAR2
) AS   
BEGIN    
	SELECT INTO PARAM2 FROM TEST WHERE ID= PARAM1;    //按输入的参数在text表中查询符合条件的数据放入返回参数中。
END TESTPRO;

Java代码：

public void test() {
	String param2 = (String) jdbcTemplate.execute(
			new CallableStatementCreator() {
				public CallableStatement createCallableStatement(
						Connection con) throws SQLException {
					String proc = "{call test(?,?)}";// 调用存储过程的sql语句
					CallableStatement cs = con.prepareCall(proc);// 调用存储过程
					cs.setInt(1, 3);// 设置输入参数的值
					cs.registerOutParameter(2, OracleTypes.VARCHAR);// 注册输出参数的类型
					return cs;
				}
			}, new CallableStatementCallback() {
				public Object doInCallableStatement(CallableStatement cs)
						throws SQLException, DataAccessException {
					cs.execute();
					return cs.getString(2);// 在这里返回输出参数的值
				}
			});
	System.out.println("返回结果：" + param2);
}

注意：cs.getString(2)中的数值2是存储过程中的参数对应的索引值(第一个参数索引为1,如此类推)

3。有返回值的存储过程（结果集）,这个要难一点。

因oracle存储过程所有返回值都是通过out参数返回的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage:   
1.建一个程序包,如下： 

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;

2.建立存储过程,如下： 

CREATE OR REPLACE PROCEDURE TEST(
	param1 in varchar2,
	test_cursor out TESTPACKAGE.TEST_CURSOR
) IS
BEGIN
	open test_cursor for select * from test where name =param1 ;//从test表中查询name等于输入参数的数据，将结果集放入游标
END TESTPRO;

可以看到，列表是通过把游标作为一个out参数来返回的。接着往下来

Java代码：

public void test() {
	List resultList = (List) jdbcTemplate.execute(
			new CallableStatementCreator() {
				public CallableStatement createCallableStatement(
						Connection con) throws SQLException {
					String sql = "{call test(?,?)}";// 调用存储过程的sql语句
					CallableStatement cs = con.prepareCall(sql);// 调用存储过程
					cs.setString(1, "武当派");// 设置输入参数的值
					cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型
					return cs;
				}
			}, new CallableStatementCallback() {
				public Object doInCallableStatement(CallableStatement cs)
						throws SQLException, DataAccessException {
					List resultsMap = new ArrayList();
					cs.execute();
					ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标的值
					while (rs.next()) {// 转换每行的返回值到Map中
						Map row = new HashMap();
						row.put("id", rs.getString("id"));
						row.put("name", rs.getString("name"));
						resultsMap.add(row);// 放入返回结果集合
					}
					rs.close();
					return resultsMap;
				}
			});
	// 打印返回的数据
	for (Iterator it = resultList.iterator(); it.hasNext();) {
		Map row = (Map) it.next();
		String id = row.get("id").toString();
		String name = row.get("name").toString();
		System.out.println("id=" + id + ";name=" + name);
	}
}

OK!学会了吧。很简单。

要强调的是如果不用spring框架,采用jdbc编程，对于一个存诸过程,如果返回的结果是我们预先知道的,那么可以根据存储过程定义的顺序来进行处理(实战中不会这样),但对于复杂的多结果集的处理,如何处理?

我们先看一下JDBC本身。一个存储过程执行后返回的是boolean型:

boolean flag = callableStatement.execute();

如果flag为true,那么说明返回了一个结果集(ResultSet)类型,你可以用getResultSet()得到当前行所在的结果,而如果返回为flase,说明什么呢?

如果你不进行处理,什么也不能说明,只能说明当前指针不是ResultSet,有可能是更新计数(updateCount)也可能什么也没返回.

那么如果当前指针为flase时怎么办呢?我们应该先getUpdateCount();如果返回-1,既不是结果集,又不是更新计数了.说明没返回了.而如果getUpdateCount()返回0或大于0,则说明当前指针是更新计数(0的时候有可能是DDL指令).无论是返回结果集或是更新计数,那么可能还继续有其它返回.只有在当前getResultSet()==null && getUpdateCount() == -1才说明没有返回.

存储过程的返回和ResultSet类似,每次处理的返回结果相当于是ResultSet的Row(数据的条数),只不过存储过程的Row最先在第一行而不是象ResultSet要先next才到第一行,存储过程向下移动一行用getMoreResults(),相当于ResultSet的next().同样它返回boolean和上面的flag一样,只是说明当前行是不是ResultSet,如果是flase,你还是要判断是不是updateCount,在每一行,都要先同时判断是否为ResultSet还是UpdateCount,如果是其中一种则要继续getMoreResults(),当不是ResultSet也不是updateCount时,说明没有返回结果了,
这时再获取输出参数.

明白了吗?也许糊涂了吧！呵呵！没关系！可以慢慢来！程序员就是要像武术家一样从内到外的修炼，内功就是编程的思想，套路招式就是各种技术的使用方法和技巧。终有一天你会发现二者可以融会贯通。

那我们就根据上面的规则来写一个通用的流程吧:

首先,我们要确定什么时候没说结果集了:

if(cs.getResultSet() == null && cs.getUpdateCount() == -1)

现在我们做一个循环:

ResultSet rs = null;
int updateCount = -1;
flag = cs.execute();
do{
	updateCount = cs.getUpdateCount();
	if(updateCount != -1){//说明当前行是一个更新计数
		//处理.
		cs.getMoreResults();
		continue;//已经是更新计数了,处理完成后应该移动到下一行
		//不再判断是否是ResultSet
	}
	rs = cs.getResultSet();
	if(rs != null){//如果到了这里,说明updateCount == -1
		//处理rs
		cs.getMoreResults();
		continue;
		//是结果集,处理完成后应该移动到下一行
	}
//如果到了这里,说明updateCount == -1 && rs == null,什么也没的了

}while(!(updateCount == -1 && rs == null));
cs.getXXX(int);//获取输出参数 

以上是对于通用的存储过程返回的结果集的处理,而oracle,它不能返回结果集,只能在输出参数中返回一个cursor,所以通用的流程中你获取不到任何结果:怎么办呢？当然是前面spring中说过的创建程序包，将结果集放入游标。对于上面的例子,存储过程有一个输入参数,一个输出参数,我们要接受输出参数作为结果集处理.所以注册的时候应该注册为游标类型:

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//输出参数类型

这样在存储过程执行后,获取输出数造型为ResultSet就可以处理:

ResultSet rs = (ResultSet)cs.getObject(2);

如果有多个结果集就用多个输出参数.