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;
      }