springstoredproce.java

自己做的关于spring框架的使用

/**
 * 
 */
package myspring.v3.jdbc.dao;

/**
 * @author maboli
 * @date Sep 1, 2008
 *
 */
import java.sql.CallableStatement;   
import java.sql.Connection;   
import java.sql.ResultSet;   
import java.sql.ResultSetMetaData;
import java.sql.SQLException;   
import java.util.ArrayList;   
import java.util.HashMap;   
import java.util.List;   
import java.util.Map;   
  
import javax.sql.DataSource;   
  
import oracle.jdbc.OracleTypes;   
  
import org.springframework.dao.DataAccessException;   
import org.springframework.jdbc.core.CallableStatementCallback;   
import org.springframework.jdbc.core.CallableStatementCreator;   
import org.springframework.jdbc.core.JdbcTemplate;   
  

  
/**  
 * @author Jane Jiao  
 *  
 */  
public class SpringStoredProce {   
       
    public List<Map> execute(String storedProc, String params, JdbcTemplate template){   
        List<Map> resultList = null;   
        try{   
        	
            resultList = (List<Map>)template.execute(new ProcCallableStatementCreator(storedProc, params),   
                                                     new ProcCallableStatementCallback());   
        }catch(DataAccessException e){   
            throw new RuntimeException("execute method error : DataAccessException " + e.getMessage());   
        }   
         return resultList;   
    }   
       
       
    /**  
     * Create a callable statement in this connection.  
     */  
    private class ProcCallableStatementCreator implements CallableStatementCreator {   
        private String storedProc;   
        private String params;   
           
       
        /**  
         * Constructs a callable statement.  
         * @param storedProc                  The stored procedure's name.  
         * @param params                      Input parameters.  
         * @param outResultCount              count of output result set.  
         */  
        public ProcCallableStatementCreator(String storedProc, String params) {   
            this.params = params;   
            this.storedProc = storedProc;   
        }   
           
        /**  
         * Returns a callable statement  
         * @param conn          Connection to use to create statement  
         * @return cs           A callable statement  
         */  
        public CallableStatement createCallableStatement(Connection conn) {   
            StringBuffer storedProcName = new StringBuffer("call ");   
            storedProcName.append(storedProc + "(");   
            //set output parameters   
            storedProcName.append("?");   
            storedProcName.append(", ");   
               
            //set input parameters   
            storedProcName.append("?");   
            storedProcName.append(")");   
            
            CallableStatement cs = null;   
            try {   
                // set the first parameter is OracleTyep.CURSOR for oracel stored procedure   
                cs = conn.prepareCall(storedProcName.toString());   
                cs.registerOutParameter (1, OracleTypes.CURSOR);   
               // set the sencond paramter   
                cs.setObject(2, params);   
            } catch (SQLException e) {   
                throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());   
            }   
            return cs;   
        }   
           
    }   
       
    /**  
     *   
     * The ProcCallableStatementCallback return a result object,   
     * for example a collection of domain objects.  
     *  
     */  
    private class ProcCallableStatementCallback implements CallableStatementCallback {   
           
        /**  
         * Constructs a ProcCallableStatementCallback.  
         */  
        public ProcCallableStatementCallback() { 
        	
        }   
  
        /**  
         * Returns a List(Map) collection.  
         * @param cs                       object that can create a CallableStatement given a Connection  
         * @return resultsList             a result object returned by the action, or null  
         */  
        public Object doInCallableStatement(CallableStatement cs){   
            List<Map> resultsMap =  new ArrayList<Map>();   
            try {   
                cs.execute();    
                ResultSet rs = (ResultSet) cs.getObject(1); 
                ResultSetMetaData rsmd = rs.getMetaData();
                              
                while (rs.next()) {   
                    Map rowMap = new HashMap();   
                    
                    for(int i = 1; i <= rsmd.getColumnCount(); i++){
	                    rowMap.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));   
                    }
                    resultsMap.add(rowMap); 
                }      
                rs.close();   
            }catch(SQLException e) {   
                throw new RuntimeException("doInCallableStatement method error : SQLException " + e.getMessage());   
            }   
            return resultsMap;   
       }   
    }   
}