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 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>
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;
}
});
}
}
转载：
