問題描述

我想編寫一個 Java 程序，它將一行插入到具有多個嵌套表的表中.之后，我想向這些嵌套表中的每一個插入不可預知的行數.

I want to write a Java program that will insert a row to a table that has a number of nested tables. Following that, I want to insert an unpredictable number of rows to each of these nested tables.

有很多這樣的 PreparedStatement 示例:

There are lots of examples a PreparedStatement of this sort:

    new PreparedStatement("INSERT INTO CONTAINER_TBL (A, B, NESTED_TBL)
        VALUES ('X', 'Y', 
            NESTED_TBL_TYPE(NESTED_ROW_TYPE('Q', 99),
                            NESTED_ROW_TYPE('R', 999))
         )");

如果我提前知道我需要插入多少嵌套行，這很好.但如果我不這樣做呢?

This is fine if I know ahead of time how many nested rows I'll need to insert. But what if I don't?

推薦答案

將 Java 數組作為集合傳遞:

Pass a Java array as a collection:

Oracle 12c 設置:

CREATE USER test_user IDENTIFIED BY password;
GRANT CREATE SESSION TO test_user;
ALTER USER test_user QUOTA UNLIMITED ON users;

CREATE TYPE test_user.nested_row_type AS OBJECT( a CHAR(1), b INTEGER );
/

CREATE TYPE test_user.nested_tbl_type AS TABLE OF test_user.nested_row_type;
/

CREATE TABLE test_user.container_tbl(
  a CHAR(1),
  b CHAR(1),
  nested_tbl test_user.nested_tbl_type
) NESTED TABLE nested_tbl STORE AS nested_tbl_tbl;

Java:(使用ojdbc7.jar)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;

public class LoadOracleObjectCollection {
  public static void main(String[] args) {
    try{
      Class.forName("oracle.jdbc.OracleDriver");

      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test_user","password");

      Object[] objs = new Object[]{
        con.createStruct( "NESTED_ROW_TYPE", new Object[]{ "Q", 99 } ),
        con.createStruct( "NESTED_ROW_TYPE", new Object[]{ "R", 999 } )
      };

      ARRAY a = ((OracleConnection) con).createARRAY("NESTED_TBL_TYPE", objs);

      PreparedStatement st = con.prepareCall( "INSERT INTO container_tbl ( a, b, nested_tbl ) VALUES ( ?, ?, ? )" );

      st.setString( 1, "x" );
      st.setString( 2, "y" );
      ((OraclePreparedStatement) st).setARRAY( 3 , a );
      st.execute();
      st.close();
      con.close();
    } catch(ClassNotFoundException | SQLException e) {
      System.out.println(e);
    }
  }
}

Oracle 查詢

SELECT c.a, c.b, n.a, n.b
FROM   test_user.container_tbl c
       CROSS JOIN TABLE( c.nested_tbl ) n;

結果:

A B A          B
- - - ----------
x y Q         99
x y R        999

<小時>

舊語法版本:

只需傳入和傳出一個虛擬查詢(而不是插入到數據庫中)，還可以展示如何檢索對象數組:

Just passing to and from a dummy query (rather than inserting into the database) to also show how to retrieve an array of objects:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class ArrayOfObjectsTest
{
  public static void main( final String[] args ){
    try{
      Class.forName( "oracle.jdbc.OracleDriver" );

      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test_user","password");

      OracleConnection oc = (OracleConnection) con;
      StructDescriptor sd = new StructDescriptor( "NESTED_ROW_TYPE", oc );
      ArrayDescriptor  ad = new ArrayDescriptor( "NESTED_TBL_TYPE", oc );

      ARRAY array = new ARRAY( ad,oc,new STRUCT[]{
        new STRUCT(sd,oc,new Object[]{ 'P',99 } ),
        new STRUCT(sd,oc,new Object[]{ 'Q',999 } )
      } );

      OraclePreparedStatement st = (OraclePreparedStatement) con.prepareStatement( "SELECT ? FROM DUAL" );
      st.setARRAY( 1, array);
      ResultSet rs = st.executeQuery();

      while( rs.next() )
      {
        Object[] structs = (Object[]) rs.getArray( 1 ).getArray();
        for ( Object struct : structs )
        {
          Datum[] datums = ((STRUCT) struct).getOracleAttributes();
          System.out.println( datums[0].stringValue() + ", " + datums[1].intValue() ) );
        }
      }
      st.close();
      con.close();
    } catch (ClassNotFoundException | SQLException ex) {
      System.out.println( ex.getMessage() );
      ex.printStackTrace();
    }
  }  
}

輸出:

P, 99
Q, 999

這是使用 ojdbc6.jar 為我編譯的，并與 Oracle 11gR2 一起使用.您應該為您的數據庫找到正確的 ojdbc 版本并使用它.

This compiled for me with ojdbc6.jar and worked with Oracle 11gR2. You should find the correct ojdbc version for your database and use that.

這篇關于在 Java 中插入 Oracle 嵌套表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！