spring中调用存储过程

org.springframework.jdbc.object.StoredProcedure是对应存储过程调用的操作对象，它通过其父类org.springframework.jdbc.object.SqlCall获得相应的底层API支持(CallableStatementCreator)， 然后在此基础之上构建了调用存储过程的执行方法。

StoredProcedure是抽象类，所以需要实现相应子类以封装对特定存储过程的调用，还记得我们在讲解JdbcTemplate调用存储过程时候定义的存储过程吗？

CREATE PROCEDURE CountTable(IN tableName varchar(1000),OUT sqlStr varchar(1000) , INOUT v INT)
BEGIN
    set @flag = v;
    set @sql = CONCAT('select count(*) into @res from ' , tableName , ' where ACTIVE_FLAG=?');
    PREPARE stmt FROM @sql;
    EXECUTE stmt using @flag;
    DEALLOCATE PREPARE stmt;
    set v = @res;
    set sqlStr = @sql;
END
						

通过继承StoredProcedure，我们可以为该存储过程的调用提供一个对应的操作对象：

public class CountTableStoredProcedure extends StoredProcedure {
	
	private static final String PROCEDURE_NAME = "CountTable";
	
	public static final String IN_PARAMETER_NAME = "tableName";
	public static final String OUT_PARAMETER_NAME = "sqlStr";
	public static final String INOUT_PARAMETER_NAME = "v";
	
	public CountTableStoredProcedure(DataSource dataSource)
	{
		super(dataSource,PROCEDURE_NAME);
		// setFunction(true);
		declareParameter(new SqlParameter(IN_PARAMETER_NAME,Types.VARCHAR));
		declareParameter(new SqlOutParameter(OUT_PARAMETER_NAME,Types.VARCHAR));
		declareParameter(new SqlInOutParameter(INOUT_PARAMETER_NAME,Types.INTEGER));
		compile();
	}
	
	public CountTableResult doCountTable(String tableName,Integer v)
	{
		Map paraMap = new HashMap();
		paraMap.put(IN_PARAMETER_NAME, tableName);
		paraMap.put(INOUT_PARAMETER_NAME, v);
		
		Map resultMap = execute(paraMap);
		
		CountTableResult result = new CountTableResult();
		result.setSql((String)resultMap.get(OUT_PARAMETER_NAME));
		result.setCount((Integer)resultMap.get(INOUT_PARAMETER_NAME));
		return result;
	}
}
						

关于该存储过程操作对象，部分细节我们有必要关注一下：

• 存储过程操作对象对应的SQL是存储过程的名称，而不是真正意义上的SQL语句，当我们调用compile方法的时候， StoredProcedure的父类SqlCall会根据你提供的存储过程名称拼装真正意义上的符合SQL92标准的存储过程调用语句， 类似于“{ call CountTable(?,?,?) }”的形式。

  因为我们的CountTableStoredProcedure只针对CountTable存储过程调用，所以，该存储过程的名称我们在类一开始就声明为常量：

  private static final String PROCEDURE_NAME = "CountTable";

  如果有多个存储过程的参数顺序相同，结果处理也一样的话，你也可以将存储过程的名称声明为变量，这完全要取决于具体的应用场景。

   

• 在构造方法中，我们将“setFunction(true);”注释掉了，因为我们调用的CountTable不是一个Function， 如果你要调用的存储过程类型为Function的话，你需要通过该方法将“function”的值设置为true，以告知StoredProcedure在处理调用的时候要区别对待。

• 在complie之前通过declareParameter声明参数，这几乎是雷打不动的惯例，不过，在StoredProcedure中使用declareParameter的时候却要有所注意了：

  • 针对存储过程参数类型为IN，OUT和INOUT不同，declareParameter接受的参数类型也应该是SqlParameter，SqlOutParameter和SqlInOutParameter；

  • SqlParameter，SqlOutParameter和SqlInOutParameter的相应实例在构造的时候，必须指定对应的参数名称，因为在调用存储过程的时候， 需要根据名称传入参数，更需要根据名称取得调用结果；

   

• StoredProcedure提供了execute方法执行存储过程调用，通过Map的形式传入调用所需要的IN或者INOUT类型的参数值， 所以，在构建参数Map的时候，该Map中的Key应该与declareParameter时候声明的参数名称相同； 另外，execute执行后返回的结果也是Map形式，从该结果Map中取得具体的结果值的时候，也是通过declareParameter中声明的OUT/INOUT参数名作为key来获取的， 所以，这就是我们将各个参数的名称在类定义的开始声明为常量的原因：

  public static final String IN_PARAMETER_NAME = "tableName";
  public static final String OUT_PARAMETER_NAME = "sqlStr";
  public static final String INOUT_PARAMETER_NAME = "v";
  									

  无论是输入参数Map还是输出参数结果对应的Map，他们中的Key应该与通过declareParameter方法声明的参数名称一一对应。

   

通过扩展StoredProcedure，我们不但封装了参数的声明和结果的提取，我们还为调用方提供了强类型的调用方法， 现在，调用方可以通过doCountTable方法的强类型参数声明传入参数值，并取得强类型的CountTableResult对象作为结果，而不是泛泛的一个Map。

 

对于存储过程的调用者来说，它的代码现在可以简洁到两行代码：

// DataSource dataSource = ...;
CountTableStoredProcedure storedProcedure = new CountTableStoredProcedure(dataSource);
CountTableResult          result          = storedProcedure.doCountTable("tableName",1);
...
						

漂亮多了，不是吗？

 

StoredProcedure提供了两个execute方法执行存储过程的调用，一个就是我们刚才使用的通过Map提供输入参数的execute方法，另一个则是使用ParameterMapper类型提供输入参数的execute方法。 那么，为什么要提供这个使用ParameterMapper类型提供输入参数的execute方法那？

ParameterMapper定义的callback方法暴露了相应的Connection，如果说在构造输入参数列表的时候，必须用到Connection的话， ParameterMapper恰好可以提供支持。比如，Oracle中定义的一存储过程，接收数组类型作为参数，而在oracle中，你只能通过Oracle.sql.ARRAY和相应的Oracle.sql.ArrayDescriptor来定义数组类型的参数， ARRAY和ArrayDescriptor都需要用到相应的Connection进行构造。所以，对于Oracle中需要使用数组传入参数的存储过程来说，我们可以通过如下类似代码进行调用：

public class OracleStoredProcedure
{
	...
	
	public Map call(...)
	{
		ParameterMapper paramMapper = new ParameterMapper(){

			public Map createMap(Connection connection) throws SQLException {
				Map inMap = new HashMap();
				...
				Integer[] params = new Integer[]{new Integer(1),new Integer(2)};
				ArrayDescriptor desc = new ArrayDescriptor("numbers", connection);
				ARRAY nums = new ARRAY(desc, connection, params);
				inMap.put("ArrayParameterName", nums);
				...
				return inMap;
			}};
		
		return execute(paramMapper);
	}
}
						

当然啦，我们的CountTableStoredProcedure在调用存储过程的时候也可以使用ParameterMapper传入相应的调用参数，只不过，ParameterMapper的createMap方法暴露的Connection对于我们来说没有太大用处罢了。

评论

3 楼 wgcniler 2012-06-16  

请问如果传到存储过程的参数是一个嵌套表的话该怎么写?

自定义的oracle对象类型为:
create or replace
TYPE passenger_trip_condition AS OBJECT(
    ticket_no                   VARCHAR2(20),
    departure_date              VARCHAR2(10),
    departure_airport_code      VARCHAR2(3),
    arrival_airport_code        VARCHAR2(3)
    );

我自定义了一个嵌套表:
create or replace
TYPE psg_trip_condition_table AS TABLE OF passenger_trip_condition;

存储过程为:

create or replace
PROCEDURE update_psg_trip_proc (V_ARRAY IN psg_trip_condition_table) IS
BEGIN
...
END update_psg_trip_proc;
以上的存储过程应该怎么用spring调用?

2 楼 wgcniler 2012-06-16  

请问如果传到存储过程的参数是ARRAY,但ARRAY的元素不是基本类型(varchar,number之类)而是oracle的复杂类型(自定义的oracle对象类型)的话该怎么写?

1 楼 zglxx2004 2008-12-25  

请问：
如果是个CLOB型的参数该怎么获取，比如
declareParameter(new SqlInOutParameter           (INOUT_PARAMETER_NAME,Types.CLOB));

原文为：(String)resultMap.get(OUT_PARAMETER_NAME)