在 Java 中插入 Oracle 嵌套表

问题描述

我想编写一个 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 嵌套表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！