注：本文来源于《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 : /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 isbegin-- 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 isselect t.city,t.username,t.username as telphone,'' as ipfrom INTERFACE_WIRELESS.USER_LOGIN_LOG t ;*/beginopen c_uservisit Forselect t.city, t.username, t.username as telphone, '' as ipfrom 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());@Autowiredprivate 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;}});}}
如果觉得《spring jdbctemplate调用存储过程 返回list对象》对你有帮助，请点赞、收藏，并留下你的观点哦！
