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;
    
    
   
   

   
   

    
    

   
   
   
   

    
    

     
           }