前言
首先采用jdbc调用存储过程是因为，整合mybatis的话，对于以存储过程为主的系统没有多大的帮助，反而多了一个分层。
本文将给出常见的存储过程调用方式。
阅读前可以先参考一下：
关于postgresql的多结果集，或者游标返回存储过程请查看上篇文章：
【转载】postgresql存储过程中返回类型
mybatis调用存储过程 无参、带有输入输出参数，输出游标类型的 存储
必要数据及代码交代
数据表：
地区区域表一张，数据量大约30000条，不过不是重点，表结构如下：
"id" int4 DEFAULT nextval('common_region_id_seq'::regclass) NOT NULL,
"parent_id" int4 NOT NULL,
"name" varchar(30) COLLATE "default" NOT NULL,
"level" int2 NOT NULL,
"code" char(6) COLLATE "default" NOT NULL,
"pingyin" varchar(40) COLLATE "default",
"name_en" varchar(60) COLLATE "default",
CONSTRAINT "common_region_pkey" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;
ALTER TABLE "public"."common_region" OWNER TO "dbuser";
COMMENT ON TABLE "public"."common_region" IS '地区表';
COMMENT ON COLUMN "public"."common_region"."code" IS '地区码';
CREATE INDEX "common_region_id_pk" ON "public"."common_region" USING btree ("id");
CREATE INDEX "common_region_parent_id" ON "public"."common_region" USING btree ("parent_id");
CREATE INDEX "common_region_region_type" ON "public"."common_region" USING btree ("level");
前面部分记录如下：
常见模式，存储过程output游标，jdbc直接调用。
这是网上常见的模式，有很多文章都是这样写的，想必在postgresql下面也能这样用，文章一搜一大堆，例如：
spring jdbctemplate或jdbc调用返回游标或复杂数据类型的存储过程
Spring如何使用JdbcTemplate调用存储过程的三种情况
对了，还搜到一篇postgresql的存储过程游标调用方式：
好了，我们有这么多资料，那么肯定可以照搬不误了。
下面是测试代码：
存储过程：
/**两个输出函数测试1**/
CREATE OR REPLACE FUNCTION "public"."sp_test_multi_cursors2"(IN "id" int4, OUT "records_cursor_01" refcursor, OUT "records_cursor_02" refcursor)
RETURNS "record" AS $BODY$
declare tmpId integer;
begin
open records_cursor_01 for select * from common_region limit 3;
open records_cursor_02 for select * from common_region limit 2;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
java端代码：
工具类：
package net.w2p.Shared.common.DB;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
public class DataTableHelper
{
public static ArrayList<HashMap<String,Object>> rs2MapList(ResultSet rsList)
{
ArrayList<HashMap<String,Object>> mytable=new ArrayList<>();
if (rsList == null) {
return mytable;
}
try {
ResultSetMetaData mdata = rsList.getMetaData();
int columnCount=mdata.getColumnCount();
boolean firstGetColumnName = true;
while (rsList.next())
{
HashMap<String,Object> item=new HashMap<>();
for (int j = 0; j < columnCount; j++) {
item.put(mdata.getColumnName(j+1),rsList.getObject(j+1));
}
mytable.add(item);
}
return mytable;
}
catch (Exception e) {
e.printStackTrace();
}
return mytable;
}
}
java端调用代码：
采用的是spring，在配置文件里请先定义jdbc一下：
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="dataSource" />
</bean>
然后正式调用代码是：
package common;
import com.alibaba.fastjson.JSONObject;
import main.BaseTest;
import net.w2p.DevBase.service.common.RegionService;
import net.w2p.DevBase.service.common.RegionServiceCase1;
import net.w2p.DevBase.vo.common.Region;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
public class RegionTester1 extends BaseTest {
@Autowired
private RegionServiceCase1 case1;
@Test
public void multiCursor2(){
case1.multiCursors2();
}
}
好了，写写测试代码：
package common;
import com.alibaba.fastjson.JSONObject;
import main.BaseTest;
import net.w2p.DevBase.service.common.RegionService;
import net.w2p.DevBase.service.common.RegionServiceCase1;
import net.w2p.DevBase.vo.common.Region;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
public class RegionTester1 extends BaseTest {
@Autowired
private RegionServiceCase1 case1;
@Test
public void multiCursor2(){
case1.multiCursors2();
}
}
注意，spring的单元测试请参考：
执行以后结果是：
报错如下：
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException; SQL state [34000]; error code [0]; ERROR: cursor "<unnamed portal 1>" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1414)
是不是我们的存储过程错了？直接运行一下看看：
不，运行的结果是正确的。。。
这里要勘误一下。。
我不知道oracle，mysql，db2等等返回的游标是不是也会报这种错，不过，postgresql的一定会有这个错，而大部分文章也没提及，然而，这个错不是第一天了，很久之前已经有了：
PostgreSQL 返回游标的Function错误 [问题点数：100分，结帖人CSDN]
我PostgreSQL8.1.0中创建了如下Function
CREATE OR REPLACE FUNCTION getres(a "varchar")
RETURNS refcursor AS
$BODY$declare membercur refcursor;
begin
open membercur for select * from member where membercode=$1;
return membercur;
end; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
在Java中调用：
String driver = "org.postgresql.Driver";
String url = "jdbc:postgresql://localhost:5432/nop";
String user = "nop";
String passwd = "nop";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, passwd);
conn.setAutoCommit(false); // return refcursor must within a transaction
CallableStatement proc = conn.prepareCall("{ ? = call getres('') }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute(); //在此处出错。
ResultSet result = (ResultSet) proc.getObject(1);
System.out.println(result);
while(result.next())
{
System.err.println("Name : " + result.getString(2));
}
conn.commit();
错误信息如下：
org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1501)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1286)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:177)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:332)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLQuery(AbstractJdbc2Connection.java:198)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:176)
at org.postgresql.jdbc3.AbstractJdbc3ResultSet.internalGetObject(AbstractJdbc3ResultSet.java:39)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2322)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:339)
at PostgreSqlHelper.executeResultSet(PostgreSqlHelper.java:125)
at PostgreSqlHelperTest.testConnectDB(PostgreSqlHelperTest.java:14)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
这哥们十几年前提的问题。。嗯。。。
好了，来勘误了。
返回游标调用勘误
Spring + postgres + stored procedure
这位兄弟也遇到了这问题：
而有人回答：
还有，我们这边是早就知道这问题了：
jdbctempleate 执行postgres 数据库存储过程返回结果集
好了，我们改一下代码，再来：
package net.w2p.DevBase.service.common;
import com.alibaba.fastjson.JSONObject;
import net.w2p.DevBase.vo.common.Region;
import net.w2p.Shared.common.DB.DataTableHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@Service
public class RegionServiceCase1 {
@Autowired
JdbcTemplate jdbcTemplate;
public void multiCursors2(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String sql="{ call \"sp_test_multi_cursors2\"(?,?,?)}";
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
st.registerOutParameter(2, Types.REF_CURSOR);
st.registerOutParameter(3,Types.REF_CURSOR);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
ResultSet rs1=(ResultSet)cs.getObject(2);
ResultSet rs2=(ResultSet)cs.getObject(3);
//
ArrayList<HashMap<String,Object>> mapList1= DataTableHelper.rs2MapList(rs1);
ArrayList<HashMap<String,Object>> mapList2=DataTableHelper.rs2MapList(rs2);
System.out.println(JSONObject.toJSONString(mapList1));
System.out.println(JSONObject.toJSONString(mapList2));
rs1.close();
rs2.close();
return res;
}
});
}
public void multiCursors2_no_auto_commit(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
con.setAutoCommit(false);//-------看到没有？加上这一句。。
String sql="{ call \"sp_test_multi_cursors2\"(?,?,?)}";
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
st.registerOutParameter(2, Types.REF_CURSOR);
st.registerOutParameter(3,Types.REF_CURSOR);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
ResultSet rs1=(ResultSet)cs.getObject(2);
ResultSet rs2=(ResultSet)cs.getObject(3);
//
ArrayList<HashMap<String,Object>> mapList1= DataTableHelper.rs2MapList(rs1);
ArrayList<HashMap<String,Object>> mapList2=DataTableHelper.rs2MapList(rs2);
System.out.println(JSONObject.toJSONString(mapList1));
System.out.println(JSONObject.toJSONString(mapList2));
rs1.close();
rs2.close();
return res;
}
});
}
}
测试代码：
package common;
import com.alibaba.fastjson.JSONObject;
import main.BaseTest;
import net.w2p.DevBase.service.common.RegionService;
import net.w2p.DevBase.service.common.RegionServiceCase1;
import net.w2p.DevBase.vo.common.Region;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
public class RegionTester1 extends BaseTest {
@Autowired
private RegionServiceCase1 case1;
@Test
public void multiCursor2(){
case1.multiCursors2();
}
@Test
public void multiCursor2_no_auto_commit(){
case1.multiCursors2_no_auto_commit();
}
}
然后看结果：
好了，能正常运行了。
所以，正确调用方式是这样子的：
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
con.setAutoCommit(false);//-------看到没有？加上这一句。。
String sql="{ call \"sp_test_multi_cursors2\"(?,?,?)}";
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
st.registerOutParameter(2, Types.REF_CURSOR);
st.registerOutParameter(3,Types.REF_CURSOR);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
ResultSet rs1=(ResultSet)cs.getObject(2);
ResultSet rs2=(ResultSet)cs.getObject(3);
//
ArrayList<HashMap<String,Object>> mapList1= DataTableHelper.rs2MapList(rs1);
ArrayList<HashMap<String,Object>> mapList2=DataTableHelper.rs2MapList(rs2);
System.out.println(JSONObject.toJSONString(mapList1));
System.out.println(JSONObject.toJSONString(mapList2));
rs1.close();
rs2.close();
return res;
}
});
话说这个问题是太细了还是怎么了，我在资料搜索时候也有不少人遇到这问题，然而在调用存储过程中没人提及，这样太浪费时间了。
存储过程+多游标优化版
直接用out形式的参数返回游标在java的调用中可以完美获取，但是，假如我们在sql里面直接引用会发现。。额。。
顶多会有unnamed portal这种形式的代码。。很难显示里面的数据，参照之前的文章，可以得到解决方案：
out变更为inout。
存储过程代码：
CREATE OR REPLACE FUNCTION "public"."sp_test_multi_cursors"(IN "id" int4, INOUT "records_cursor_01" refcursor, INOUT "records_cursor_02" refcursor)
RETURNS "record" AS $BODY$
declare tmpId integer;
begin
open records_cursor_01 for select * from common_region limit 3;
open records_cursor_02 for select * from common_region limit 2;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
sql直接调用且显示数据：
java代码中引用：
public void multiCursors_enhance(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
con.setAutoCommit(false);//-------out参数中有游标时请加上这一句。
String sql="{ call \"sp_test_multi_cursors\"(?,?::refcursor,?::refcursor)}";
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
st.setObject(2,"crs_001");
st.registerOutParameter(2, Types.REF_CURSOR);
st.setObject(3,"crs_002");
st.registerOutParameter(3,Types.REF_CURSOR);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
ResultSet rs1=(ResultSet)cs.getObject(2);
ResultSet rs2=(ResultSet)cs.getObject(3);
//
ArrayList<HashMap<String,Object>> mapList1= DataTableHelper.rs2MapList(rs1);
ArrayList<HashMap<String,Object>> mapList2=DataTableHelper.rs2MapList(rs2);
System.out.println(JSONObject.toJSONString(mapList1));
System.out.println(JSONObject.toJSONString(mapList2));
rs1.close();
rs2.close();
cs.getConnection().setAutoCommit(true);//--恢复为自动提交。
return res;
}
});
}
测试函数：
@Test
public void multiCursor_enhance(){
case1.multiCursors_enhance();
}
执行结果：
这种形式完美满足sql里面的调用以及jdbc里面的调用。
存储过程+json
为了返回多个数据，我们可以用游标，然而，我们也是可以利用pg里面的json来处理的，这种方式比直接返回游标更友好，而且没有auto commit的痛苦。好了，请先参考下面文章：
row_to_json、array_to_json都是很有用的函数。
话说这文章我也是抄别人的。。。竟然还有其他网站直接爬我的来用，怎么不直接去爬原作者的。。。。。
存储过程：
CREATE OR REPLACE FUNCTION "sp_test_multi_json"(IN "id" int4, out json_result_01 text,out json_result_02 text)
RETURNS record AS $BODY$
declare tmpId integer;
begin
select json_result::text into json_result_01 from (select array_to_json(array_agg(row_to_json(t))) as json_result
from (select * from common_region cr limit 3) t) middle
;
select array_to_json(array_agg(row_to_json(t)))::text into json_result_02 from (
select * from common_region cr limit 8
) t;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
select sp_test_multi_json(1);
直接调用存储过程的结果：
java端代码：
public void multiJson(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String sql="{ call \"sp_test_multi_json\"(?,?,?)}";
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
st.registerOutParameter(2, Types.VARCHAR);
st.registerOutParameter(3,Types.VARCHAR);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
String json1=cs.getString(2);
String json2=cs.getString(3);
System.out.println(json1);
System.out.println(json2);
return res;
}
});
}
测试用代码：
@Test
public void multiJson(){
case1.multiJson();
}
测试结果：
存储过程+json 优化增强
对于java的调用来说，out json_1 varchar 这种形式很容易直接拿到值，不过，对于sql里面调用函数以后要拿到值也是不容易的，不过这次也不能用inout的形式。。因为。。。cursor的调用和生成变量跟传统的不一样，为了方便起见，直接返回setof varchar之类的。
存储过程代码：
CREATE OR REPLACE FUNCTION "sp_test_multi_json3"(IN "id" int4)
RETURNS setof varchar AS $BODY$
declare tmpId integer;
declare json_result_01 varchar;
declare json_result_02 varchar;
begin
select json_result::text into json_result_01 from (select array_to_json(array_agg(row_to_json(t))) as json_result
from (select * from common_region cr limit 3) t) middle
;
select array_to_json(array_agg(row_to_json(t)))::text into json_result_02 from (
select * from common_region cr limit 8
) t;
return next json_result_01;
return next json_result_02;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
sql调用：
java代码调用：
public void multiJsonEnhance(){
jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String sql="{ call \"sp_test_multi_json3\"(?)}";
// sql="select * from \"sp_test_multi_json3\"(?)"; --两种写法都可以。
CallableStatement st=con.prepareCall(sql);
st.setInt(1,1);
return st;
}
},new CallableStatementCallback<List<Region>>(){
@Override
public List<Region> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<Region> res=new ArrayList<>();
cs.execute();
ResultSet rs = cs.getResultSet();
while (rs.next()){
System.out.println(rs.getString(1));//--注意，从1开始
}
return res;
}
});
}
测试代码：
@Test
public void multiJsonEnhance(){
case1.multiJsonEnhance();
}
结果如下：
结论
对于简单的存储过程，要返回多个结果的，请遵循：
1、不要用inout和out参数；
2、统一返回setof varchar 字符串类型，然后由java端解码处理数据。
3、包括json格式的也请转成字符串，毕竟，jdbc，没有对json的原生类型支持。
对于多个复杂结果集的，请遵循：
1、使用inout形式输出游标
2、返回类型是record。
扫一扫
8402
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
