通过Java操控Oracle对象(2)

下面接着讲有关Oracle Object的更新

首先需要确定的是:标准Java Struct类并不能更新和修改Oracle Object, 具体原因, 我在稍后会解释.
幸运的是:Oracle提供了STRUCT和StructDescriptor类(它不提供也不行, 呵呵)

先看看如何利用STRUCT类更新已存在的数据:
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      ResultSet oraRs  = stmt.executeQuery("SELECT ELEMENT FROM DEMO_TABLE");
      if (oraRs.next()) {
         STRUCT updStruct       = (STRUCT) oraRs.getObject(1);
         Object oraStructAttr[] = updStruct.getAttributes();
         oraStructAttr[1]       = "U";
         oraStructAttr[2]       = "Update";
         oraRs.updateObject(1, new STRUCT(elemDesc, conn, oraStructAttr));
         oraRs.updateRow();
      }

注意conn.createStatement这句, 中的 TYPE_SCROLL_INSENSITIVE 和 CONCUR_UPDATABLE 属性, 通常在默认情况下ResultSet 是前向滚动(FORWARD SCROLL)且不可更新的, 现在我们为了修改已存在的记录, 有必要允许其可更新.其中:TYPE_SCROLL_INSENSITIVE 属性意味着敏感性滚动, 所谓敏感性滚动, 即指数据的及时更新, 一般情况下, ResultSet读取数据后, 放入Cache后, 数据不再随table中的数据变更. 而敏感性数据, 则会在table中的数据变更后及时刷新Cache, 使两者数据一致.

再看一看 oraRs.updateObject(1, new STRUCT(elemDesc, conn, oraStructAttr)) 这句, 这里的STRUCT是Oracle数据类型.前面我曾说过:标准Java Struct类并不能更新和修改Oracle Object. 为什么呢? 因为标准Java Struct类是一个interface!而 updateObject 时需要一个 Object instance, interface不能实例化, 这就是原因所在.

好了, 现在再来看看如何利用StructDescriptor类获得一个Oracle Object的相关信息.如下面的代码:
      String strTypeName = "DEMO_TYPE";
      StructDescriptor elemDesc = StructDescriptor.createDescriptor(strTypeName, conn);
      ResultSetMetaData elemMetaData = elemDesc.getMetaData();
      int intCols = elemMetaData.getColumnCount();
      System.out.println("Number of attribute:/t" + intCols);
      System.out.println("Attributes:");
      for (int i = 1; i <= intCols; i++) {
         System.out.println(elemMetaData.getColumnName(i) + "/t" +
                          elemMetaData.getColumnTypeName(i));
      }
通过该类的createDescriptor静态方法, 我们可以通过对象名及current connection得到有关对象的信息.

有关如何新增对象这里就不再详细描述, 请看下面的完整代码:
import java.sql.*;
import java.math.BigDecimal;
import oracle.sql.*;

public class testOraObj
{
   public static void main(String args[]) throws SQLException
   {
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
      Connection conn = DriverManager.getConnection
                        ("jdbc:oracle:thin:@localhost:1521:OEMREP", "scott", "tiger");
     
      String strTypeName = "DEMO_TYPE";
      StructDescriptor elemDesc = StructDescriptor.createDescriptor(strTypeName, conn);
      ResultSetMetaData elemMetaData = elemDesc.getMetaData();
      int intCols = elemMetaData.getColumnCount();
      System.out.println("Number of attribute:/t" + intCols);
      System.out.println("Attributes:");
      for (int i = 1; i <= intCols; i++) {
         System.out.println(elemMetaData.getColumnName(i) + "/t" +
                          elemMetaData.getColumnTypeName(i));
      }

      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      ResultSet oraRs  = stmt.executeQuery("SELECT ELEMENT FROM DEMO_TABLE");
      if (oraRs.next()) {
         STRUCT updStruct       = (STRUCT) oraRs.getObject(1);
         Object oraStructAttr[] = updStruct.getAttributes();
         oraStructAttr[1]       = "U";
         oraStructAttr[2]       = "Update";
         oraRs.updateObject(1, new STRUCT(elemDesc, conn, oraStructAttr));
         oraRs.updateRow();
      }

      Object elemAttr[] = new Object[4];
      elemAttr[0] = new BigDecimal(4);
      elemAttr[1] = "De";
      elemAttr[2] = "Demo";
      elemAttr[3] = new BigDecimal(5.665);
      STRUCT elemStruct = new STRUCT(elemDesc, conn, elemAttr);

      PreparedStatement ps = conn.prepareStatement(
                             "INSERT INTO DEMO_TABLE VALUES(?,?,?)");
      ps.setInt(1, 3);
      ps.setInt(2, 5);
      ps.setObject(3, elemStruct);
      int rows = ps.executeUpdate();

      Statement inSensitvieStmt = conn.createStatement
                       (ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
      ResultSet rs   = inSensitvieStmt.executeQuery("SELECT PERIOD, COLUMN_GROUP, " +
                                         "ELEMENT FROM DEMO_TABLE");
 
      elemAttr[0] = new BigDecimal(5);
      elemAttr[1] = "D2";
      elemAttr[2] = "Demo2";
      elemAttr[3] = new BigDecimal(9.03);
      elemStruct  = new STRUCT(elemDesc, conn, elemAttr);

      rs.moveToInsertRow();
      rs.updateInt(1, 6);
      rs.updateInt(2, 8);
      rs.updateObject(3, elemStruct);
      rs.insertRow();

      rs.close();     
      oraRs.close();
      stmt.close();
      inSensitvieStmt.close();
      conn.close();
   }
}