jdbctempleate 执行postgres 数据库存储过程返回结果集

创建表：

create table myuser
(
id numeric(5,0) not 
null,
name varchar(20) 
not null,
sex varchar(8) not 
null
)
insert into myuser 
values(1 , '李亚希' , '男' );

create  table  myuser
(
id numeric(5,0)  not null,
name varchar(20)  not null,
sex  varchar(8) not null
)
insert into myuser values(1 , '李亚希'  , '男' );

创建存储过程

CREATE LANGUAGE plpgsql;

CREATE OR 
REPLACE FUNCTION getUsers()
RETURNS refcursor AS
$$
DECLARE recordcur refcursor;

BEGIN
OPEN recordcur FOR
SELECT id, name ,sex
FROM myuser;
RETURN recordcur;
END;
$$LANGUAGE plpgsql;

CREATE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION  getUsers()
RETURNS refcursor AS 
$$
DECLARE recordcur refcursor;

BEGIN
OPEN recordcur FOR
SELECT id, name ,sex FROM  myuser;
RETURN recordcur;
END;
$$LANGUAGE plpgsql;

这个文件我为了测试方便 放在 src 目录下
spring 配置文件

<bean 
id="dataSource" 
class="org.apache.commons.dbcp.BasicDataSource">
<property 
name="driverClassName">
<value>org.postgresql.Driver</value>
</property>
<property 
name="url">
<value>jdbc:postgresql://localhost:5432/postgres</value>
</property>
<property 
name="username">
<value>postgres</value>
</property>
<property 
name="password">
<value>postgres</value>
</property>
</bean>
<bean 
id="jdbcTemplate" 
class="org.springframework.jdbc.core.JdbcTemplate">
<property 
name="dataSource">
<ref 
local="dataSource"/>
</property>
</bean>

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> 
    <property name="driverClassName"> 
      <value>org.postgresql.Driver</value> 
    </property> 
    <property name="url"> 
      <value>jdbc:postgresql://localhost:5432/postgres</value> 
    </property> 
    <property name="username"> 
      <value>postgres</value> 
    </property> 
    <property name="password"> 
      <value>postgres</value> 
    </property> 
  </bean> 
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
    <property name="dataSource"> 
      <ref local="dataSource"/> 
    </property> 
  </bean> 

java 代码：

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;

public class Test {

/**
* @param args
*/
public static 
void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"dao.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate) context
.getBean("jdbcTemplate");
jdbcTemplate.execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con)
throws SQLException {
String storedProc = "{?=call getUsers()}";// 调用的sql
con.setAutoCommit(false); 
//这句很重要 ， 没有报错
CallableStatement cs = con.prepareCall(storedProc);
cs.registerOutParameter(1, Types.OTHER);
return cs;
}
}, new CallableStatementCallback<Object>() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
List<Map<String, String>> resultsMap = new ArrayList<Map<String, String>>();
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);// 获取游标一行的值
while (rs.next()) {// 转换每行的返回值到Map中
Map<String, String> rowMap = new HashMap<String, String>();
rowMap.put("id", rs.getString("id"));
rowMap.put("name", rs.getString("name"));
rowMap.put("sex", rs.getString("sex"));
System.out.println(rowMap.get("id") + 
"========="
+ rowMap.get("name") + "==========="
+ rowMap.get("sex"));
}
rs.close();
return resultsMap;
}
});
}
}