spring 调用存储过程的三种方法

/*
	alter proc mytestproc
(
 @name varchar(60) ,
 @id int output
)
as
begin 
select top 6 * from clients
 select top 6 * from products where name like '%'+@name+'%'

 set @id=5 
 return 6
 end
	 */
	
	//调用带返回值的存储过程
		public int Addcrmclient()
		{
			
			SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);
			  simpleJdbcCall.withProcedureName("mytestproc").withoutProcedureColumnMetaDataAccess().withReturnValue();
			  simpleJdbcCall.declareParameters(new SqlOutParameter("RETURN_VALUE", Types.INTEGER)); //添加参数的名称和数据库中的存储过程参数名称无关，顺序相关
			  simpleJdbcCall.addDeclaredParameter(new SqlParameter("name", Types.VARCHAR));
			  //simpleJdbcCall.addDeclaredParameter(new SqlParameter("Password", Types.VARCHAR));
			  simpleJdbcCall.addDeclaredParameter(new SqlOutParameter("idx", Types.VARCHAR));
			  Map<String, Object> inParamsValue = new HashMap<String, Object>();
			  inParamsValue.put("name", "阿");
			  inParamsValue.put("idx", 0);
			  simpleJdbcCall.returningResultSet("xx",new HashMapRowMapper()); //第一个结果集
			  simpleJdbcCall.returningResultSet("xxd",new HashMapRowMapper());//第二个结果集
			  simpleJdbcCall.getJdbcTemplate().setResultsMapCaseInsensitive(true);
			  simpleJdbcCall.getJdbcTemplate().setSkipUndeclaredResults(true);
			  Map<String, Object> resultMap = simpleJdbcCall.execute(inParamsValue);
			
			  int iRetValue = Integer.parseInt(resultMap.get("RETURN_VALUE").toString());
			  if(iRetValue == 6){
			   String id = resultMap.get("idx").toString();
			   logger.info("--------------"+id);
			  // logger.info("--------------"+resultMap.toString());
			   logger.info("-----xx---------"+resultMap.get("xx").toString());
			   logger.info("-----xxd---------"+resultMap.get("xxd").toString());
			  
			   
			  }
			  
			  return iRetValue;
		}
		
		
		public int Addcrmclient2()
		{
			String i = jdbcTemplate.execute("{?=call FN_UPDATE_BUSINESS_UNIT(?,?,?)}", new CallableStatementCallback<String>() {
                public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    cs.registerOutParameter(1, java.sql.Types.VARCHAR);
                  
                    cs.setInt(2, 2);
                    cs.setInt(3, 16);
                    cs.setInt(4, 8);
                    cs.execute();
                    //cs.getResultSet();
                    //cs.getMoreResults();
                    return cs.getString(1);
                }
            });
			
			
			return 1;
		}
		
		public int Addcrmclient3()
		{
			final String callFunctionSql = "{call SP_Test(?,?,?)}";  
	        
	        List<SqlParameter> params = new ArrayList<SqlParameter>();  
	        params.add(new SqlParameter(Types.INTEGER));  
	        params.add(new SqlReturnResultSet("result",  
	                new ResultSetExtractor<Integer>() {  
	            @Override  
	            public Integer extractData(ResultSet rs) throws SQLException,DataAccessException {  
	                while(rs.next()) {  
	                    return rs.getInt(1);  
	                }  
	               return 0;  
	        }})); 
	        
	        Map<String,Object> map =  jdbcTemplate.call(new CallableStatementCreator()
	        {

	           

				@Override
				public CallableStatement createCallableStatement(Connection con) throws SQLException {
					// TODO Auto-generated method stub
					 CallableStatement cstmt = con.prepareCall(callFunctionSql);  
		                cstmt.setInt(1, 2);
		                cstmt.setInt(2, 16);
		                cstmt.setInt(3, 10);
		                return cstmt;  
					
				}
	            
	        }, params);
			
			
			return 1;
		}

public String Addcrmclient2() {
		JSONArray ja = new JSONArray();
		String i = jdbcTemplate.execute("{?=call mytestproc(?,?)}", new CallableStatementCallback<String>() {
			public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.registerOutParameter(1, java.sql.Types.INTEGER);
				cs.setString(2, "阿");
				cs.registerOutParameter(3, java.sql.Types.INTEGER);
				boolean bool = cs.execute(); // true if the first result is a ResultSet object;
				int i = 0;
				while (bool) {

					ResultSet rs = cs.getResultSet();
					logger.info("cs---------------" + rs.toString());
					logger.info("cs---------------" + rs.toString());
					// logger.info("cs---------------"+resultSetToJsonArry(rs).toString());
					ja.add(resultSetToJsonArry(rs).toString());
					bool = cs.getMoreResults();
				}
				// 必须到最后一个记录集了才能取参数
				logger.info("cs.getInt(1)---------" + cs.getInt(1));
				logger.info("cs.getInt(3)---------" + cs.getInt(3));

				// cs.getResultSet();

				return cs.getString(1);
			}
		});

		return ja.toJSONString();
	}

	/**
	 * 将resultSet转化为JSON数组
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 * @throws JSONException
	 */

	public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException, JSONException

	{

		// json数组

		JSONArray array = new JSONArray();

		// 获取列数

		ResultSetMetaData metaData = rs.getMetaData();

		int columnCount = metaData.getColumnCount();

		// 遍历ResultSet中的每条数据

		while (rs.next()) {

			JSONObject jsonObj = new JSONObject();

			// 遍历每一列

			for (int i = 1; i <= columnCount; i++) {

				String columnName = metaData.getColumnLabel(i);

				String value = rs.getString(columnName);

				jsonObj.put(columnName, value);

			}

			array.add(jsonObj);

		}

		return array;

	}