OConnection.java package test.oracle.conn; import java.sql.Connection; import java.sql.DriverManager; public class OConnection { public static Connection getConn() { String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oradb"; String user = "cartoon";// 这里替换成你自已的数据库用户名 String password = "oracle";// 这里替换成你自已的数据库用户密码 Connection connection = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("类实例化成功!"); connection = DriverManager.getConnection(URL, user, password); System.out.println("创建连接对像成功!"); } catch (Exception err) { err.printStackTrace(); return null; } return connection; } } Parent.java package test.oracle.array; public class Parent { private String id; private String name; private String title; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String toString() { return "Parent{id=" + this.id + ",name=" + this.name + ",title=" + this.title + "}"; } } package test.oracle.array; public class Child { private String id; private String parentId; private String childName; private String childTitle; private String childContent; private String childTime; public String getChildContent() { return childContent; } public void setChildContent(String childContent) { this.childContent = childContent; } public String getChildName() { return childName; } public void setChildName(String childName) { this.childName = childName; } public String getChildTime() { return childTime; } public void setChildTime(String childTime) { this.childTime = childTime; } public String getChildTitle() { return childTitle; } public void setChildTitle(String childTitle) { this.childTitle = childTitle; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getParentId() { return parentId; } public void setParentId(String parentId) { this.parentId = parentId; } public String toString() { return "Child{id=" + this.id + ",parentId=" + this.parentId + ",childName=" + this.childName + ",childTitle=" + this.childTitle + ",childContent=" + this.childContent + ",childTime=" + this.childTime + "}"; } } package test.oracle.array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import oracle.jdbc.driver.OracleTypes; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; public class OracleArray { private static final String T_PARENT = "T_PARENT"; private static final String T_PARENT_LST = "T_PARENT_LST"; private static final String T_CHILD = "T_CHILD"; private static final String T_CHILD_LST = "T_CHILD_LST"; private static final String T_CHILD_LST_MAP = "T_CHILD_LST_MAP"; private static final String PROC_INS_PARENT_CHILD = "{ call PROC_INS_PARENT_CHILD(?,?,?)}"; public static int insParentChils(ArrayList<Parent> plst, ArrayList<ArrayList<Child>> clstMap, Connection con) throws Exception { CallableStatement cstmt = null; int retVal = -1; try { ArrayDescriptor parentLstDesc = ArrayDescriptor.createDescriptor( T_PARENT_LST, con); StructDescriptor parentDesc = StructDescriptor.createDescriptor( T_PARENT, con); ArrayDescriptor childLstMapDesc = ArrayDescriptor.createDescriptor( T_CHILD_LST_MAP, con); ArrayDescriptor childLstDesc = ArrayDescriptor.createDescriptor( T_CHILD_LST, con); StructDescriptor childDesc = StructDescriptor.createDescriptor( T_CHILD, con); ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>(); // 转换plst为Oracle 对象数组 for (int i = 0; i < plst.size(); i++) { Parent p = plst.get(i); Object[] record = new Object[2]; record[0] = p.getName(); record[1] = p.getTitle(); STRUCT item = new STRUCT(parentDesc, con, record); pstruct.add(item); } ARRAY dataps = new ARRAY(parentLstDesc, con, pstruct.toArray()); ArrayList<ARRAY> cMap = new ArrayList<ARRAY>(); // 转换clst为Oracle 对象数组 for (int i = 0; i < clstMap.size(); i++) { ArrayList<Child> childLst = clstMap.get(i); ArrayList<STRUCT> cstruct = new ArrayList<STRUCT>(); for (int j = 0; j < childLst.size(); j++) { Child c = childLst.get(j); Object[] record = new Object[3]; record[0] = c.getChildName(); record[1] = c.getChildTitle(); record[2] = c.getChildContent(); STRUCT item = new STRUCT(childDesc, con, record); cstruct.add(item); } ARRAY datacs = new ARRAY(childLstDesc, con, cstruct.toArray()); cMap.add(datacs); } ARRAY datacsMap = new ARRAY(childLstMapDesc, con, cMap.toArray()); cstmt = con.prepareCall(PROC_INS_PARENT_CHILD); cstmt.setArray(1, dataps); cstmt.setArray(2, datacsMap); cstmt.registerOutParameter(3, OracleTypes.INTEGER); cstmt.execute(); retVal = cstmt.getInt(3); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (cstmt != null) { cstmt.close(); } } catch (SQLException sqle) { sqle.printStackTrace(); } } return retVal; } } package test.oracle; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import test.oracle.array.Child; import test.oracle.array.OracleArray; import test.oracle.array.Parent; import test.oracle.conn.OConnection; public class TestOracleArray { public static void main(String[] args) { ArrayList<Parent> plst = new ArrayList<Parent>(); for (int i = 0; i < 100; i++) { Parent p = new Parent(); p.setName("name" + i); p.setTitle("title" + i); plst.add(p); } ArrayList<ArrayList<Child>> clstMap = new ArrayList<ArrayList<Child>>(); for (int i = 0; i < 100; i++) { ArrayList<Child> clst = new ArrayList<Child>(); for (int j = 0; j < 100; j++) { Child c = new Child(); c.setChildName("childName" + j); c.setChildTitle("childT" + j); c.setChildContent("childContent"+j); clst.add(c); } clstMap.add(clst); } Connection con = null; try { long startTime = 0; long endTime = 0; con = OConnection.getConn(); startTime = new Date().getTime(); OracleArray.insParentChils(plst, clstMap, con); endTime = new Date().getTime(); System.out.println("It takes " + (endTime - startTime) + " milliseconds to execute"); } catch (Exception e) { e.printStackTrace(); } finally { try { if (con != null) { con.close(); System.out.println("disconnected"); } } catch (SQLException sqle) { sqle.printStackTrace(); } } System.exit(0); } }