Oracle 10g+WebLogic8x下存取blob对象

隆梦影

　　oracle10g的jdbc存在bug,在weblogic环境下处理的blob对象会出现错误：
encountered SQLException [ORA-01461: can bind a LONG value only for insert into a LONG column ]; nested exception is: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
针对这个问题，目前只能更改blob对象的处理方法以避免。
解决的思路是先向数据库里面插入一个空的blob对象，然后利用select ....for update语句单独向刚刚插入的行中放置blob对象，这两个操作要放在同一个事务中。同样，对于blob对象的更新问题，也需要，先把要替换的记录行的blob对象置空(EMPTY_BLOB())，然后再向里面插入blob对象。
代码如下：
　　/**
  * Oracle数据库附件专用方法
  */
 public static int insertDB(DataSource ds , String tableName, Map data) {
  if(ds==null)
  {
   throw new RuntimeException("DataSource is null");
  }
  
  byte[] temp = null;
  Object cols[] = data.keySet().toArray();
  Object vals[] = new Object[cols.length];
  
  /* 组织sql */
  StringBuffer colsSql = new StringBuffer();
　　StringBuffer valsSql = new StringBuffer();
  for(int i=0; i<cols.length ; i++)
  {
   String key = (String) cols;
   Object value = data.get(key);
   vals = value;
   if(key!=null && key.equals(WorkForm.COLUMN_NAME_SUBCONTENT))
   {
    temp =  UtilObject.getBytes(value);
   }
   if (i > 0) {
    colsSql.append(" ,");
    valsSql.append(" ,");
   }
   
   colsSql.append(key);
   
   if(key!=null)
   {
    if(key.equals(WorkForm.COLUMN_NAME_SUBCONTENT))
     valsSql.append("EMPTY_BLOB()");
    else
    { 
     if(key.equals(WorkForm.COLUMN_NAME_SUBTIME))
     {
      valsSql.append("SYSDATE");
      //valsSql.append("current_timestamp()");
     }
     else
     {
      valsSql.append("'")
          .append(value)
          .append("'");
     }
    }
   }
  }
  
  if(temp==null)
  {
   throw new RuntimeException("字节数组为空!");
  }
  /* 插入数据SQL */
  StringBuffer InsertSQL = new StringBuffer();
  InsertSQL.append("INSERT INTO ")
    .append(tableName)
    .append(" (")
    .append(colsSql.toString())
    .append(") VALUES (")
    .append(valsSql.toString())
    .append(")");
  
  /* 取出数据SQL */
  StringBuffer QuerySQL = new StringBuffer();
  QuerySQL.append("SELECT ")
    .append(WorkForm.COLUMN_NAME_SUBCONTENT)
    .append(" FROM ")
    .append(tableName)
    .append(" ")
    .append(" WHERE ID='")
    .append((String) data.get(WorkForm.COLUMN_NAME_SUBID))
    .append("' AND DATAID='")
    .append((String) data.get(WorkForm.COLUMN_NAME_DATAID))
    .append("' FOR UPDATE");
  
  /* 然后在从表中取出来，进行更新 */
  Connection conn = null;
  Statement stmt = null;
  boolean defaultCommit;
  ResultSet result = null;
  int returnValue = 0;
  
  try {
   conn = ds.getConnection();
   
   defaultCommit = conn.getAutoCommit();
   /* 设置为不能自动提交 */
   conn.setAutoCommit(false);
   stmt = conn.createStatement();
   /* 插入空的附件 */
   returnValue = stmt.executeUpdate(InsertSQL.toString());
   
   /* 查询此BLOB对象并锁定 */
   result = stmt.executeQuery(QuerySQL.toString());
   if (result == null) {
    throw new RuntimeException("result is null");
   }
   ResultSet rs = stmt.executeQuery(QuerySQL.toString());
   if (rs.next()) {
    weblogic.jdbc.wrapper.Blob blob = (weblogic.jdbc.wrapper.Blob) rs.getBlob(WorkForm.COLUMN_NAME_SUBCONTENT);
    oracle.sql.BLOB oblob = (oracle.sql.BLOB)blob.getVendorObj();
    OutputStream output = oblob.getBinaryOutputStream();
    ByteArrayInputStream inputStream = new ByteArrayInputStream(temp);
    int len = 0;
    while ((len = inputStream.read(temp)) != -1) {
     output.write(temp, 0, len);
    }
    inputStream.close();
    output.flush();
    output.close();
   }
   
   /* 正式提交 */
   conn.commit();
   stmt.close();
   conn.setAutoCommit(defaultCommit);
   
  } catch (IOException ioex) {
   throw new RuntimeException("IOException", ioex);
  }catch (SQLException ex) {
   ex.printStackTrace();
   try {
    conn.rollback();
   } catch (SQLException e) {
    throw new RuntimeException("回滚失败", e);
   }
  } finally {
   if (conn != null) {
    try {
     conn.close();
    } catch (SQLException e) {
     throw new RuntimeException("关闭连接失败", e);
    }
   }
  }
  return returnValue;
 }