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; } }); } }
为人:谦逊、激情、博学、审问、慎思、明辨、 笃行
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/