spring jdbc 调用存储过程封装

</pre><h1>1、调用存储过程--返回list以及无结果返回以及只返回一个参数</h1><pre code_snippet_id="671267" snippet_file_name="blog_20150520_2_9698049" name="code" class="java">

/**
		 *  获取数据库内的存储过程--返回一个List
		 * @param procedureName 存储过程名
		 * @param inParameter   输入的参数 
		 * @param outParamter   输出的参数
		 * @return
		 */
		public List<Map> callProcedure(final String procedureName,final List inParameter,final List outParamter){
			if(procedureName==null || procedureName.length() == 0 ){
				return null;
			}
			//没有返回参数
			if(outParamter == null ){
				if(callProcedureWithoutOut(procedureName,inParameter))return null;
			}
			 List resultList = (List) DBUtility.getJdbcTemplate().execute(  
				     new CallableStatementCreator() {
						public CallableStatement createCallableStatement(Connection con) throws SQLException {
							int inSize = inParameter==null?0:inParameter.size();
							int outSize = outParamter==null?0:outParamter.size();
							StringBuffer sbsql = new StringBuffer();
							sbsql.append("{call "+procedureName).append("(");
							for(int i=0;i<(inSize+outSize);i++){
								if(i == 0){
									sbsql.append("?");
								}else{
									sbsql.append(",?");
								}
							}
							sbsql.append(")}");
					         CallableStatement cs = con.prepareCall(sbsql.toString());
					         // 设置输入参数的值
					         if(inSize > 0 ){
					        	String typeName =  null;
					        	for(int i=0;i<inSize;i++){ 
					        		typeName = inParameter.get(i).getClass().getName().toString();
					        		cs.setObject(i+1, inParameter.get(i));
					        	}
					         }
					         // 注册输出参数的类型 
					           cs.registerOutParameter(inSize+1, OracleTypes.CURSOR);  
					           return cs;   
						}
					}, new CallableStatementCallback<List>() {
						public List<Map> doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {   
							   int inSize = inParameter==null?0:inParameter.size();
					           List<Map> resultsMap = new ArrayList();   
					           cs.execute();   
					           ResultSet rs = (ResultSet) cs.getObject(inSize+1);// 获取游标一行的值   
					           while (rs.next()) {// 转换每行的返回值到Map中   
					              Map rowMap = new HashMap();  
					              for(int i=0;i<outParamter.size();i++){
					            	  String outP = outParamter.get(i).toString();
					            	  rowMap.put(outP.toLowerCase(),rs.getObject(outP.toUpperCase()) );
					              }
					              resultsMap.add(rowMap);   
					           }   
					           rs.close();   
					           return resultsMap;   
					        }   
						
					}); 
			 return resultList;
		
		}
		/**
		 *  获取数据库内的存储过程--没有输出
		 * @param procedureName 存储过程名
		 * @param inParameter 输出的参数
		 */
		public Boolean callProcedureWithoutOut(final String procedureName,final List inParameter){
			if(procedureName==null || procedureName.length() == 0 ){
				return false;
			}
			StringBuffer sbsql = new StringBuffer();
			sbsql.append("{call "+procedureName).append("(");
			for(int i=0;i<inParameter.size();i++){
				if(i == 0){
					sbsql.append(inParameter.get(i));
				}else{
					sbsql.append(","+inParameter.get(i));
				}
			}
			sbsql.append(")}");
			DBUtility.getJdbcTemplate().execute(sbsql.toString());
			return true;
		}
		/**
		 *  获取数据库内的存储过程--输出只有一个参数
		 * @param procedureName
		 * @param inParameter
		 * @param outParamer
		 * @return
		 */
		public String callProcedureOnlyoneOut(final String procedureName,final List inParameter,final String outParamer){
			if(procedureName==null || procedureName.length() == 0 ){
				return null;
			}
			 String result = (String) DBUtility.getJdbcTemplate().execute(   
				     new CallableStatementCreator() {
						public CallableStatement createCallableStatement(Connection con) throws SQLException {
							int inSize = inParameter==null?0:inParameter.size();
							StringBuffer sbsql = new StringBuffer();
							sbsql.append("{call "+procedureName).append("(");
							for(int i=0;i<(inSize+1);i++){
								if(i == 0){
									sbsql.append("?");
								}else{
									sbsql.append(",?");
								}
							}
							sbsql.append(")}");
					         CallableStatement cs = con.prepareCall(sbsql.toString());
					         // 设置输入参数的值
					         if(inSize > 0 ){
					        	String typeName =  null;
					        	for(int i=0;i<inSize;i++){ 
					        		typeName = inParameter.get(i).getClass().getName().toString();
					        		cs.setObject(i+1, inParameter.get(i));
					        	}
					         }
					         // 注册输出参数的类型 
					           cs.registerOutParameter(inSize+1, OracleTypes.CURSOR);  
					           return cs;   
						}
					}, new CallableStatementCallback<Object>() {
						public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {   
							   int inSize = inParameter==null?0:inParameter.size();
					           List<Map> resultsMap = new ArrayList();   
					           cs.execute();   
					           return  cs.getObject(inSize+1);// 获取游标一行的值   
					        }   
						
					}); 
			 return result;
		}