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

最新推荐文章于 2024-09-13 15:42:43 发布

mafangling

最新推荐文章于 2024-09-13 15:42:43 发布

阅读量301

点赞数

分类专栏： JavaEE spring 文章标签： postgresql sql spring jdbc java

本文链接：https://blog.csdn.net/mafangling/article/details/84364311

版权

JavaEE 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

spring

2 篇文章 0 订阅

订阅专栏

创建表：



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;
			}
		});
	}
}