jdbctemplate mysql 分页查询返回list对象_spring jdbctemplate调用存储过程，返回list对象...

最新推荐文章于 2022-11-16 23:24:17 发布

Enthusiast

最新推荐文章于 2022-11-16 23:24:17 发布

阅读量213

点赞数

文章标签： jdbctemplate mysql 分页查询返回list对象

本文链接：https://blog.youkuaiyun.com/weixin_36333122/article/details/114313073

版权

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;

}

});

}

存储过程：

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;

/**