日常工作分享: PGJDBC 调用存储过程案例2则

原创大表哥 All China Database Union 2024-03-04

344

大家好，今天和大家分享一下日常工作中的JAVA的JDBC 调用PG中的store procedure 2 则案例。

最近公司开发同学正在尝试把oracle的PLSQL,改写成PLPGSQL, 应用程序是JAVA的springboot 框架。

案例一：自定义类型存储过程中的传参问题

我们先来看ORACLE 的中的存储过程是什么样子的

1.自定义类型的type TYPE APP_MSG_GW.“TYP_BLACK_LIST_OBJ”
2.基于自定义类型的type APP_MSG_GW.“TYP_BLACK_LIST_OBJ” 的集合 collection
3.基于自定义type APP_MSG_GW.“TYP_BLACK_LIST_OBJ” 的集合 collection 传参的存储过程：简单的说就是根据入参的数据，对数据库表中的数据进行merge操作。

TYP_BLACK_LIST_OBJ：

TYP_BLACK_LIST_TAB：

PROCEDURE PROC_BLACK_LIST_BATCH_INSERT 传参类型是自定义对象TYP_BLACK_LIST_OBJ的集合

开发小伙伴的问题是 PG中是否存在 TABLE(PI_BLACK_LIST_BATCH) 这种写法? 显然是不存在的，但是PG中相应的workaround 关键字 unnest :

postgres=# select unnest(Array[1,2,3]) as a;
a
---
1
2
3
(3 rows)

复制

于是为开发小伙伴写了一个PLPGSQL的从测试小demo 版本：

创建表：t_person_list
CREATE table t_person_list                             
(
      id         bigint primary key ,
      name          VARCHAR(100)
);

PG中创建type
CREATE TYPE typ_perosn_obj  as                            
(
id         bigint,
name       VARCHAR(100)
);

typ_black_list_obj[] 进行传参转换

create or replace procedure proc_merge_test(person_list in typ_perosn_obj[])
as
$$
begin
merge into t_person_list t1 using
(SELECT
tmp.id,
tmp.name
from "unnest"(person_list) tmp) t2 on t1.id = t2.id
WHEN NOT MATCHED THEN
INSERT (id, name)
  VALUES (t2.id, t2.name);
end;
$$
language plpgsql;


测试程序

do
$$
declare
data typ_perosn_obj[];
begin
data[1] := (1, 'jason');
data[2] := (2, 'tom');
call proc_merge_test(data);
end;
$$;

返回结果：

postgres=# select * from t_person_list;
id | name  
----+-------
  1 | jason
  2 | tom
(2 rows)

复制

自己和开发同学在PSQL的client下测试就无任何问题，本以为事情至此结束。。。

但是开发小伙伴第二天找到我说，JAVA调用出现了异常。。。无论代码怎默写都不管用???

开发同学大致试了3,4调用方式吧，无论如何都不行。。。

同样我也写了个小测试程序试了一下，也是不行的（各种调试了一整晚）

@RequestMapping(value="/plpgsql", method=RequestMethod.GET)
public String callProc() throws SQLException {


    Connection conn = jdbcTemplate.getDataSource().getConnection();
    Person[] personArray = new Person[1];
    personArray[0] =  new Person(100,"jason100");


    final Array sqlArray =   conn.createArrayOf("typ_perosn_obj", personArray);
    PreparedStatement stmt = conn.prepareStatement("call proc_merge_test( ? )");
   // CallableStatement proc = con.prepareCall("{call commitproc( ? )}");
    stmt.setArray(1,sqlArray);
    stmt.execute();
    conn.commit();
    return "SUCCESS!!";


}


复制

报错如下：感觉是PG的自定义类型和JAVA中没有mapping 上

2024-03-03 19:46:57,451 ERROR (DirectJDKLog.java:175)- Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.postgresql.util.PSQLException: ERROR: malformed record literal: "Person(id=100, name=jason100)"
  Detail: Missing left parenthesis.
  Where: unnamed portal parameter $1 = '...'] with root cause
org.postgresql.util.PSQLException: ERROR: malformed record literal: "Person(id=100, name=jason100)"
  Detail: Missing left parenthesis.
  Where: unnamed portal parameter $1 = '...'

复制

下一步去问问GOOGLE 吧（国内PLPGSQL 这块基本上是空白…）

果然发现了一个2008年的帖子：貌似是PGJDBC驱动确实不支持自定义type类型的传参

由于这个帖子时间太久远了，我决定在github 给 PGJDBC 开一个issue: 来确实一下目前版本是否支持？或者未来是否有支持的打算？

https://github.com/pgjdbc/pgjdbc/issues/3143

大致过了一个星期，收到了官方大佬 Davecramer 的回复：确实当前版本还是不支持自定义type的传参，未来在google summer 有希望实现这个功能。

既然目前驱动不支持，我们就需要改写一下PG中的实现方式，我们可以考虑换成text[] 数组类型（PGJDBC对于这种基本类型的参数是支持的），每条记录是json串的格式：

测试demo 如下：

创建表：
t_person_list
CREATE table t_person_list                             
(
      id         bigint primary key ,
      name          VARCHAR(100)
);

create or replace procedure proc_merge_test_new(person_list in text[])
as
$$
begin
merge into t_person_list t1 using
(
select
(per::json->>'id')::bigint as id,  
(per::json->>'name')::text as name
from "unnest" (person_list)
as per
) t2 on t1.id = t2.id
WHEN NOT MATCHED THEN
INSERT (id, name)
  VALUES (t2.id, t2.name);
end;
$$
language plpgsql;




测试程序：
do
$$
declare
data text[];
begin
data[1] := '{"id":100,"name":"jason1"}';
data[2] := '{"id":200,"name":"jason2"}';
call proc_merge_test_new(data);
end;
$$;


复制

开发小伙伴同样在JAVA端测试成功了！

案例二： PLPGSQL 函数返回游标问题

开发小伙伴遇到的另一个问题也是JAVA调用存储过程返回out 类型的参数 refcursor

JAVA错误： org.postgresql.util.PSQLException: ERROR: cursor “<unnamed portal 1>” does not exist

Google上给出的答案是手动设置一下 conn.setAutoCommit(false);

我们来测试一下

PLPGSQL

CREATE OR REPLACE FUNCTION TEST_QUERY_CURSOR () RETURNS refcursor AS
$$
    DECLARE
      ref refcursor;                                                     
    BEGIN
      OPEN ref FOR


        SELECT
           1, 'jason';


      RETURN ref;                                                       
    END;
    $$
LANGUAGE 'plpgsql';

复制

JAVA code:



@RequestMapping(value="/queryCursor", method=RequestMethod.GET)
public String queryCursor() throws SQLException {


    Connection conn = jdbcTemplate.getDataSource().getConnection();
    conn.setAutoCommit(false);
    CallableStatement proc = conn.prepareCall("{? = call TEST_QUERY_CURSOR()}");
    proc.registerOutParameter(1, Types.REF_CURSOR);
    proc.execute();
    ResultSet results = (ResultSet) proc.getObject(1);
    while (results.next())
    {
       System.out.println(results.getInt(1) + "-->" +results.getString(2));
    }
    results.close();
    proc.close();
    return "SUCCESS!!";


}

复制

果然在代码中设置了 conn.setAutoCommit(false); 之后返回结果正常了

1-->jason

复制

但是有的公司明确规定代码里不可以自己手动设置改变事务的默认提交方式，我们其实针对这个case相应的workaround有很多：
1.JAVA中直接写SQL QUERY
2.改写function 返回table的方式

我们测试一下改写function 返回table的方式

PLPGSQL：

CREATE OR REPLACE FUNCTION PROC_QUERY()
RETURNS table(id bigint,name varchar(100))
AS
$$
begin
return query
select t.id, t.name  from t_person_list t;
end;
$$ LANGUAGE plpgsql;

复制

JAVA code:

@RequestMapping(value="/queryCursor2", method=RequestMethod.GET)
public String queryCurso2r() throws SQLException {


    Connection conn = jdbcTemplate.getDataSource().getConnection();
    //conn.setAutoCommit(false); -- function return table 方式无需手动设置事务提交方式
    PreparedStatement pstmt = conn.prepareStatement("select * from PROC_QUERY()");
    ResultSet rs = pstmt.executeQuery();
    while (rs.next())
    {
        System.out.println(rs.getBigDecimal(1) + rs.getString(2));
    }
    rs.close();
    pstmt.close();
    return "SUCCESS!!";


}

复制

测试结果：

100jason1
200jason2

复制

最后我们总结一下:

1)目前PG的JDBC驱动不支持自定义类型的传参，我们可以考虑用 json 字符串的形式带替代
2)PLLGSQL中的函数返回refcursor 需要手动设置事务自动提交为false => “conn.setAutoCommit(false)”, 可以考虑用JAVA代码直接 SQL query 或者 PLLGSQL函数返回table => RETURNS table(col1 type1, col2 type2) 来替代

Have a fun 🙂 !