java - 调用具有输入参数数组行类型的 db2 过程的任何替代方法?
问题描述
我正在尝试将行类型的数组作为输入传递给来自 jdbc 的存储过程。
编写了下面的代码并且连接中不支持获取“方法 createStruct”。
桌子:
create table wspout.test (username varchar(100),location varchar(100),status varchar(100));
行类型:
CREATE OR REPLACE TYPE TEST_ROW AS ROW ANCHOR ROW OF test;
数组行类型:
CREATE OR REPLACE TYPE TEST_ROW_ARRAY AS TEST_ROW ARRAY[];
存储过程名称:
CREATE OR REPLACE PROCEDURE TEST_PROC (IN p_test_row_array TEST_ROW_ARRAY)
P1: BEGIN
DECLARE i INTEGER;
DECLARE v_test_row TEST_ROW;
SET i = 1;
WHILE i < CARDINALITY(p_test_row_array) DO
SET v_test_row = p_test_row_array[i];
insert into test values(v_test_row.username,v_test_row.location,v_test_row.status);
set i = i + 1;
END WHILE;
END P1@
我的java类:从这里调用存储过程
package Test;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;
public class TestJdbc{
public static void main(String[] args) {
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
}
catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
System.out.println("DB2 driver is loaded successfully");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DriverManager.getConnection("jdbc:db2://localhost:40000/db2inst1","test","test123");
if (conn != null)
{
System.out.println("DB2 Database Connected");
List<Object> test_row_List = new ArrayList<>();
Struct test_row[] = new java.sql.Struct[3];
Array test_row_arr;
test_row[0] = conn.createStruct("TEST_ROW", new Object[] { "user1","Location1", "status1"});
test_row_List.add(test_row[0]);
test_row[1] = conn.createStruct("TEST_ROW", new Object[] { "user2","Location2", "status2"});
test_row_List.add(test_row[1]);
test_row[2] = conn.createStruct("TEST_ROW", new Object[] { "user3","Location3", "status4"});
test_row_List.add(test_row[2]);
test_row_arr = conn.createArrayOf("TEST_ROW", test_row_List.toArray());
System.out.println("Array value" + test_row_arr);
pstmt = conn.prepareCall("CALL TEST_PROC(?)");
pstmt.setArray(1, test_row_arr);
pstmt.execute();
}
} catch (SQLException e) {
System.out.println("DB2 Database connection e");
e.printStackTrace();
return;
}
}
运行时我低于异常。
*DB2 driver is loaded successfully
DB2 Database Connected
DB2 Database connection e
com.ibm.db2.jcc.am.SqlFeatureNotSupportedException: [jcc][t4][10181][12052][4.11.69] Method createStruct is not supported. ERRORCODE=-4450, SQLSTATE=0A504*
at com.ibm.db2.jcc.am.gd.e(gd.java:605)
at com.ibm.db2.jcc.am.gd.c(gd.java:625)
at com.ibm.db2.jcc.am.lb.createStruct(lb.java:6761)
at Test.TestJdbc.main(TestJdbc.java:38)
解决方案
根据在 JDBC 应用程序中使用 ARRAY of ROW 参数调用存储过程:
...
//PreparedStatement pstmt = null;
CallableStatement pstmt = null;
...
//java.util.List<Object> test_row_List = new java.util.ArrayList<>();
// Actually it's not needed at all
java.util.List<Object> test_row_List = new java.util.ArrayList<Object>();
...
//test_row_arr = con.createArrayOf("TEST_ROW", test_row_List.toArray());
test_row_arr = con.createArrayOf("TEST_ROW", test_row);
...
你应该使用以下,除非你真的想插入数组中的所有行,但最新的。
当i <= CARDINALITY(p_test_row_array)
推荐阅读
- python - Python:预处理数据以挖掘关联规则和频繁项集(apriori/SPADE)
- linux - 与 ifconfig 命令相比,ip addr 命令在为接口分配 IP 时设置了不正确的子网掩码
- c# - 新手和数据 SQL 数据库
- vim - vim 以不同于 `hlsearch` 的方式突出显示当前出现的搜索模式,以帮助快速找到光标位置
- excel - 将 Excel Windows 大小更改为屏幕的一半
- python - 如何从父实例创建子 SQL-Alchemy 实例?
- c# - MoreLinq - 如何在不从选择器中获取空值的情况下进行 FullJoin?
- java - Spring Gateway RouteLocator 空指针异常
- php - 我收到“PHP 致命错误:无法重新声明函数”,尽管我只有一次该函数
- typescript - 如何在角度 8 中减少 viewchild 的线?