Java 调用存储过程中传递集合类型处理方法

最新推荐文章于 2021-02-13 10:19:15 发布

原创最新推荐文章于 2021-02-13 10:19:15 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

flex 技术专栏收录该内容

60 篇文章

订阅专栏

本文介绍了一个Java方法，用于通过存储过程向Oracle数据库中插入预案信息列表。具体包括：如何构建符合数据库Type规范的数据结构，使用CallableStatement执行存储过程，并处理返回状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public String addplaninfolist(String eventId, String advise, ArrayList P_ARR) {
		Object[] tmp = new Object[P_ARR.size()];
		for (int i = 0; i < tmp.length; i++) {
			Object[] tmparc = new Object[3];
			ASObject aso = (ASObject) P_ARR.get(i);
                        //必须与数据库中的TYPE名称一致
                        String PLANNAME = (String) (aso.get("name"));// 预案名称
			String PLANSIZE = (String) (aso.get("size"));// 大小
			String PLANTYPE = (String) (aso.get("type"));// 类型
			tmparc[0] = PLANNAME;
			tmparc[1] = PLANSIZE;
			tmparc[2] = PLANTYPE;
			tmp[i] = tmparc;
		}
		// 进行插入
		String state = addplaninfo(eventId, advise, tmp);
		return state;
	}

	public String addplaninfo(String eventId, String advise, Object[] obj) {
		Connection conn = DbManager.getInstance().getConnection();
		CallableStatement stmt = null;
		ResultSet rs = null;
		String state = "";
		try {
                        //处理Type
                       ArrayDescriptor arrayDept = ArrayDescriptor.createDescriptor(
					"TP_PLANINFOLIST", conn);
			STRUCT[] laneArray = new STRUCT[obj.length];
			ARRAY arrayObject = null;
			StructDescriptor st = new StructDescriptor("OBJ_PLANINFOLIST", conn);//连接
                         //类型转换
                        for (int i = 0; i < obj.length; i++) {
				STRUCT st2 = new STRUCT(st, conn, (Object[]) obj[i]);
				laneArray[i] = st2;
			}
			arrayObject = new ARRAY(arrayDept, conn, laneArray);
			stmt = conn
					.prepareCall("call YJCZPKG.INSERT_PLANINFOLIST(?,?,?,?)");
			stmt.setString(1, eventId);
			stmt.setString(2, advise);
			stmt.setArray(3, arrayObject);
			stmt.registerOutParameter(4, OracleTypes.VARCHAR);
			stmt.execute();
			state = stmt.getString(4);
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			try {
				DbManager.closeAll(rs, stmt, conn);
			} catch (Exception e) {
				System.out.println(e.toString());
			}
		}
		return state;
	}

2、数据库Type

CREATE OR REPLACE TYPE TP_PLANINFOLIST AS TABLE OF OBJ_PLANINFOLIST;//创建type

//创建数组
CREATE OR REPLACE TYPE OBJ_PLANINFOLIST AS OBJECT
(
  PLANNAME VARCHAR2(100), --附件名称
  PLANSIZE VARCHAR2(20), --附件大小
  PLANTYPE VARCHAR2(20) --附件类型
)