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

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

目录

• spring jdbctemplate调用存储过程，返回list对象
  • 方法：
  • 存储过程：
  • 调用方法：
• 自己编写的代码

 

正文

注：本文来源于《  spring jdbctemplate调用存储过程，返回list对象 》

回到顶部

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

方法：

/**
	 * 调用存储过程
	 * @param spName
	 */
	@SuppressWarnings("unchecked")
	public List<HashMap<String, Object>> executeSP(String procedure) {
		//procedure = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";
		return (List<HashMap<String, Object>>) jdbcTemplate.execute(procedure,
				new CallableStatementCallback() {
					public Object doInCallableStatement(
							CallableStatement cs) throws SQLException,
							DataAccessException {

						List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();

						cs.registerOutParameter(1, OracleTypes.CURSOR);
						cs.execute();
						ResultSet rs = (ResultSet) cs.getObject(1);

						while (rs.next()) {
							HashMap<String, Object> dataMap = new HashMap<String, Object>();
							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;
					}
				});

	}

存储过程：

create or replace package WCITY2_STATISTIC is

  -- Author  : ADMINISTRATOR
  -- Created : 2012/10/24 9:48:34
  -- Purpose : 

  type Ref_Cursor is ref cursor;

  --
  procedure sp_pager_stats;

  --访问信息
  procedure sp_uservisit_stat(c_uservisit out Ref_Cursor);

end WCITY2_STATISTIC;

create or replace package body WCITY2_STATISTIC is

  --页面信息
  procedure sp_pager_stats is

  begin
    -- cur_page as select * from OMS_WIRELESS. TEMPLATE_FILE_WORKING;

    null;
  end sp_pager_stats;

  --访问信息
  procedure sp_uservisit_stat(c_uservisit out Ref_Cursor) as
    --定义游标
    /*
    cursor c_uservisit is
    select t.city,t.username,t.username as telphone,'' as ip
    from INTERFACE_WIRELESS.USER_LOGIN_LOG t ;*/
  begin
    open c_uservisit For

      select t.city, t.username, t.username as telphone, '' as ip
        from INTERFACE_WIRELESS.USER_LOGIN_LOG t;

  end;

end WCITY2_STATISTIC;

调用方法：

	@SuppressWarnings("rawtypes")
	public String getUserVisitStat(){
		//List lst=jdbcService.executeSP("");

		String procedure = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";
		List lst=spService.executeSP(procedure);
		if(lst!=null){
			System.out.println(lst.size());
		}

		return SUCCESS;
	}

回到顶部

自己编写的代码

import java.sql.CallableStatement;
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 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;

public class FuncReportTaskService {
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private DaoFactory daoFactory;


    /**
     * 调用存储过程
     * @param spName
     */
    @SuppressWarnings("unchecked")
    public List<HashMap<String, Object>> executeSP(String procedure) {
    	JdbcTemplate jdbcTemplate = daoFactory.getDao().getJdbcTemplate();
        //procedure = "{call WCITY2_STATISTIC.sp_uservisit_stat(?)}";
        return (List<HashMap<String, Object>>) jdbcTemplate.execute(procedure,
                new CallableStatementCallback() {
                    public Object doInCallableStatement(
                            CallableStatement cs) throws SQLException,
                            DataAccessException {

                        List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();

                        cs.registerOutParameter(1, OracleTypes.CURSOR);
                        cs.execute();
                        ResultSet rs = (ResultSet) cs.getObject(1);

                        while (rs.next()) {
                            HashMap<String, Object> dataMap = new HashMap<String, Object>();
                            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;
                    }
                });

    }
}