數組、二維數組、及類型，預存程序調用.相關java

OConnection.java

 

package test.oracle.conn;<br />import java.sql.Connection;<br />import java.sql.DriverManager;<br />public class OConnection {<br />public static Connection getConn() {<br />String URL = "jdbc:oracle:thin:@127.0.0.1:1521:oradb";<br />String user = "cartoon";// 這裡替換成你自已的資料庫使用者名稱<br />String password = "oracle";// 這裡替換成你自已的資料庫使用者密碼<br />Connection connection = null;<br />try {<br />Class.forName("oracle.jdbc.driver.OracleDriver");<br />System.out.println("類執行個體化成功!");<br />connection = DriverManager.getConnection(URL, user, password);<br />System.out.println("建立串連對像成功!");<br />} catch (Exception err) {<br />err.printStackTrace();<br />return null;<br />}<br />return connection;<br />}<br />}<br />

Parent.java

package test.oracle.array;<br />public class Parent {<br />private String id;<br />private String name;<br />private String title;<br />public String getId() {<br />return id;<br />}<br />public void setId(String id) {<br />this.id = id;<br />}<br />public String getName() {<br />return name;<br />}<br />public void setName(String name) {<br />this.name = name;<br />}<br />public String getTitle() {<br />return title;<br />}<br />public void setTitle(String title) {<br />this.title = title;<br />}<br />public String toString() {<br />return "Parent{id=" + this.id + ",name=" + this.name + ",title="<br />+ this.title + "}";<br />}<br />} 

 

 

package test.oracle.array;<br />public class Child {<br />private String id;<br />private String parentId;<br />private String childName;<br />private String childTitle;<br />private String childContent;<br />private String childTime;<br />public String getChildContent() {<br />return childContent;<br />}<br />public void setChildContent(String childContent) {<br />this.childContent = childContent;<br />}<br />public String getChildName() {<br />return childName;<br />}<br />public void setChildName(String childName) {<br />this.childName = childName;<br />}<br />public String getChildTime() {<br />return childTime;<br />}<br />public void setChildTime(String childTime) {<br />this.childTime = childTime;<br />}<br />public String getChildTitle() {<br />return childTitle;<br />}<br />public void setChildTitle(String childTitle) {<br />this.childTitle = childTitle;<br />}<br />public String getId() {<br />return id;<br />}<br />public void setId(String id) {<br />this.id = id;<br />}<br />public String getParentId() {<br />return parentId;<br />}<br />public void setParentId(String parentId) {<br />this.parentId = parentId;<br />}<br />public String toString() {<br />return "Child{id=" + this.id + ",parentId=" + this.parentId<br />+ ",childName=" + this.childName + ",childTitle="<br />+ this.childTitle + ",childContent=" + this.childContent<br />+ ",childTime=" + this.childTime + "}";<br />}<br />}<br />package test.oracle.array;<br />import java.sql.CallableStatement;<br />import java.sql.Connection;<br />import java.sql.SQLException;<br />import java.util.ArrayList;<br />import oracle.jdbc.driver.OracleTypes;<br />import oracle.sql.ARRAY;<br />import oracle.sql.ArrayDescriptor;<br />import oracle.sql.STRUCT;<br />import oracle.sql.StructDescriptor;<br />public class OracleArray {<br />private static final String T_PARENT = "T_PARENT";<br />private static final String T_PARENT_LST = "T_PARENT_LST";<br />private static final String T_CHILD = "T_CHILD";<br />private static final String T_CHILD_LST = "T_CHILD_LST";<br />private static final String T_CHILD_LST_MAP = "T_CHILD_LST_MAP";<br />private static final String PROC_INS_PARENT_CHILD = "{ call PROC_INS_PARENT_CHILD(?,?,?)}";<br />public static int insParentChils(ArrayList<Parent> plst,<br />ArrayList<ArrayList<Child>> clstMap, Connection con)<br />throws Exception {<br />CallableStatement cstmt = null;<br />int retVal = -1;<br />try {<br />ArrayDescriptor parentLstDesc = ArrayDescriptor.createDescriptor(<br />T_PARENT_LST, con);<br />StructDescriptor parentDesc = StructDescriptor.createDescriptor(<br />T_PARENT, con);<br />ArrayDescriptor childLstMapDesc = ArrayDescriptor.createDescriptor(<br />T_CHILD_LST_MAP, con);<br />ArrayDescriptor childLstDesc = ArrayDescriptor.createDescriptor(<br />T_CHILD_LST, con);<br />StructDescriptor childDesc = StructDescriptor.createDescriptor(<br />T_CHILD, con);<br />ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();<br />// 轉換plst為Oracle 對象數組<br />for (int i = 0; i < plst.size(); i++) {<br />Parent p = plst.get(i);<br />Object[] record = new Object[2];<br />record[0] = p.getName();<br />record[1] = p.getTitle();<br />STRUCT item = new STRUCT(parentDesc, con, record);<br />pstruct.add(item);<br />}<br />ARRAY dataps = new ARRAY(parentLstDesc, con, pstruct.toArray());<br />ArrayList<ARRAY> cMap = new ArrayList<ARRAY>();<br />// 轉換clst為Oracle 對象數組<br />for (int i = 0; i < clstMap.size(); i++) {<br />ArrayList<Child> childLst = clstMap.get(i);<br />ArrayList<STRUCT> cstruct = new ArrayList<STRUCT>();<br />for (int j = 0; j < childLst.size(); j++) {<br />Child c = childLst.get(j);<br />Object[] record = new Object[3];<br />record[0] = c.getChildName();<br />record[1] = c.getChildTitle();<br />record[2] = c.getChildContent();<br />STRUCT item = new STRUCT(childDesc, con, record);<br />cstruct.add(item);<br />}<br />ARRAY datacs = new ARRAY(childLstDesc, con, cstruct.toArray());<br />cMap.add(datacs);<br />}<br />ARRAY datacsMap = new ARRAY(childLstMapDesc, con, cMap.toArray());<br />cstmt = con.prepareCall(PROC_INS_PARENT_CHILD);<br />cstmt.setArray(1, dataps);<br />cstmt.setArray(2, datacsMap);<br />cstmt.registerOutParameter(3, OracleTypes.INTEGER);<br />cstmt.execute();<br />retVal = cstmt.getInt(3);<br />} catch (Exception ex) {<br />ex.printStackTrace();<br />} finally {<br />try {<br />if (cstmt != null) {<br />cstmt.close();<br />}<br />} catch (SQLException sqle) {<br />sqle.printStackTrace();<br />}<br />}<br />return retVal;<br />}<br />}<br />

package test.oracle;<br />import java.sql.Connection;<br />import java.sql.SQLException;<br />import java.util.ArrayList;<br />import java.util.Date;<br />import test.oracle.array.Child;<br />import test.oracle.array.OracleArray;<br />import test.oracle.array.Parent;<br />import test.oracle.conn.OConnection;<br />public class TestOracleArray {<br />public static void main(String[] args) {<br />ArrayList<Parent> plst = new ArrayList<Parent>();<br />for (int i = 0; i < 100; i++) {<br />Parent p = new Parent();<br />p.setName("name" + i);<br />p.setTitle("title" + i);<br />plst.add(p);<br />}<br />ArrayList<ArrayList<Child>> clstMap = new ArrayList<ArrayList<Child>>();<br />for (int i = 0; i < 100; i++) {<br />ArrayList<Child> clst = new ArrayList<Child>();<br />for (int j = 0; j < 100; j++) {<br />Child c = new Child();<br />c.setChildName("childName" + j);<br />c.setChildTitle("childT" + j);<br />c.setChildContent("childContent"+j);<br />clst.add(c);<br />}<br />clstMap.add(clst);<br />}<br />Connection con = null;<br />try {<br />long startTime = 0;<br />long endTime = 0;<br />con = OConnection.getConn();<br />startTime = new Date().getTime();<br />OracleArray.insParentChils(plst, clstMap, con);<br />endTime = new Date().getTime();<br />System.out.println("It takes " + (endTime - startTime)<br />+ " milliseconds to execute");<br />} catch (Exception e) {<br />e.printStackTrace();<br />} finally {<br />try {<br />if (con != null) {<br />con.close();<br />System.out.println("disconnected");<br />}<br />} catch (SQLException sqle) {<br />sqle.printStackTrace();<br />}<br />}<br />System.exit(0);<br />}<br />}<br />