spring jdbctemplate调用存储过程，返回list对象-白红宇

spring jdbctemplate调用存储过程，返回list对象

阅读量：6441 次

发布时间：2019-06-23

本文共 3711 字，大约阅读时间需要 12 分钟。

注：本文来源于《》

spring jdbctemplate调用存储过程，返回list对象

方法：

/**	 * 调用存储过程	 * @param spName	 */	@SuppressWarnings("unchecked")	public List
     
      
       > executeSP(String procedure) {		//procedure = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";		return (List
       
        
         >) jdbcTemplate.execute(procedure,				new CallableStatementCallback() {					public Object doInCallableStatement(							CallableStatement cs) throws SQLException,							DataAccessException {						List
         
          
           > list = new ArrayList
           
            
             >(); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); ResultSet rs = (ResultSet) cs.getObject(1); while (rs.next()) { HashMap
             
               dataMap = new HashMap
              
               (); ResultSetMetaData rsMataData = rs.getMetaData(); for (int i = 1; i <= rsMataData.getColumnCount(); i++) { dataMap.put(rsMataData.getColumnName(i), rs .getString(rsMataData.getColumnName(i))); } list.add(dataMap); } return list; } }); }

存储过程：

replace package WCITY2_STATISTIC   -- Author  : ADMINISTRATOR  -- Created : 2012/10/24 9:48:34  -- Purpose :   type Ref_Cursor  ref ;  --   sp_pager_stats;  --访问信息   sp_uservisit_stat(c_uservisit out Ref_Cursor); WCITY2_STATISTIC;  replace package body WCITY2_STATISTIC   --页面信息   sp_pager_stats       -- cur_page as select * from OMS_WIRELESS. TEMPLATE_FILE_WORKING;    ;   sp_pager_stats;  --访问信息   sp_uservisit_stat(c_uservisit out Ref_Cursor)     --定义游标    /*    cursor c_uservisit is    select t.city,t.username,t.username as telphone,'' as ip    from INTERFACE_WIRELESS.USER_LOGIN_LOG t ;*/       c_uservisit        t.city, t.username, t.username  telphone, ''  ip         INTERFACE_WIRELESS.USER_LOGIN_LOG t;  ; WCITY2_STATISTIC;

调用方法：

@SuppressWarnings("rawtypes")	 String getUserVisitStat(){		//List lst=jdbcService.executeSP("");		String  = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";		List lst=spService.executeSP();		(lst!=){			System.out.println(lst.());		}		 SUCCESS;	}

自己编写的代码

import java..CallableStatement;import java..ResultSet;import java..ResultSetMetaData;import java..SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.CallableStatementCallback;import org.springframework.jdbc.core.JdbcTemplate;import com.tt.pwp.framework.data.dao.DaoFactory;import oracle.jdbc.OracleTypes; class FuncReportTaskService {    protected final Logger logger = LoggerFactory.getLogger(this.getClass());    @Autowired    private DaoFactory daoFactory;    /**     * 调用存储过程     * @param spName     */    @SuppressWarnings("unchecked")     List
     
      
       > executeSP(String ) {    	JdbcTemplate jdbcTemplate = daoFactory.getDao().getJdbcTemplate();        // = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";         (List
       
        
         >) jdbcTemplate.(,                new CallableStatementCallback() {                     Object doInCallableStatement(                            CallableStatement cs) throws SQLException,                            DataAccessException {                        List
         
          
           > list = new ArrayList
           
            
             >(); cs.registerOutParameter(1, OracleTypes.); cs.(); ResultSet rs = (ResultSet) cs.getObject(1); (rs.()) { HashMap
             
               dataMap = new HashMap
              
               (); ResultSetMetaData rsMataData = rs.getMetaData(); ( i = 1; i <= rsMataData.getColumnCount(); i++) { dataMap.put(rsMataData.getColumnName(i), rs .getString(rsMataData.getColumnName(i))); } list.(dataMap); } list; } }); }}