oracle 更新Clob字段

来源：互联网发布：android编程权威指南2 编辑：程序博客网时间：2024/06/04 18:38

oracle 更新Clob字段的时候报

java.lang.ClassCastException: weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB

原代码：

oracle.sql.CLOB clob=(oracle.sql.CLOB)rs.getClob("1");

BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());

BufferedReader in = new BufferedReader(new FileReader(infile));

int c;

while ((c=in.read())!=-1) {

out.write(c);

}
in.close();

out.close();

网上查了蛮久,有说是包衝突的，有的说是代理成了还是用java.sql.Clob 什麼都有，自己用的是weblogic发布的，最后只是简单修改就OK了：

java.sql.Clob clob = (java.sql.Clob)rs.getClob("xsl1");
Writer out = clob.setCharacterStream(1);

    BufferedReader in = new BufferedReader(new FileReader(infile));

    int c;

while ((c=in.read())!=-1) {

out.write(c);

}
in.close();

out.close();

-->上面写于2014-05-21

-->下面写于2014-08-06

知道为什么会报错了，因为是用的weblogic服务器，返回的是java.sql.Clob 类型。而且还发现些问题，就是在oracle里面的大字段类型，如果是普通的文本保存最好就用CLOB类型，以前用了BLOB,会出现乱码的问题，而且当本地没有问题，sit有问题就无语了。

--insert clob type

      DataFactory df = new DataFactory("");
   StringBuffer sql = new StringBuffer();
    sql.append("INSERT INTO table (LOGINID,SESSIONID,clob_1) ");
   //oracle 用empty_clob(),sybase无这种类型，可以插入空
   if(Constants.DBTYPE.equals(Constants.DBTYPE_ORACLE)) {
    sql.append("values("+FormatMan.toSQLStr(loginId)+","+FormatMan.toSQLStr(sessionId)+","+FormatMan.toSQLStr(funcName)+",empty_clob())");
    new DBUtil().execInsert(sql.toString(),conn);
   }else if ( Constants.DBTYPE.equals(Constants.DBTYPE_SYBASE) ) {
    sql.append("values("+FormatMan.toSQLStr(loginId)+","+FormatMan.toSQLStr(sessionId)+","+FormatMan.toSQLStr(funcName)+",'')");
    new DBUtil().execInsert(sql.toString(),conn);
   }

--update clob type

public void updateClobData(String jsonData,Connection conn) throws SQLException, IOException{
     byte[] jsonBytes = jsonData.getBytes();
     Writer writer = null;
     java.sql.Clob java_clob = null;
     oracle.sql.CLOB oracle_clob = null;
     ResultSet rs = null;
     PreparedStatement ps = null;

  String execSQL = "SELECT clob1 FROM table FOR UPDATE";
  ps = conn.prepareStatement(execSQL);
  rs = ps.executeQuery();
  if(rs.next()){

if("weblogic".equalsIgnoreCase(DBUtil.server_type)){ //weblogic server

     if ( Constants.DBTYPE.equals(Constants.DBTYPE_ORACLE) ) {//oracle db 直接转成流在写进去

       java_clob = (java.sql.Clob)rs.getClob("clob1");
       writer = java_clob.setCharacterStream(1);//java.sql.Clob

       writer.write(jsonData);
       writer.flush();
     }else{ //sybase数据库因为db里面的数据类型是long varchar类型，所以其实也就是个string来的，可以用普通的update 语句就可以。
      blob = ( rs).getBlob("clob1")+"";
       if(blob!=null)
        {
         if(jsonData!=null )
         {
          blob = jsonData;
         }
           }
      String sqlexc = "update table set clob1=? "

      ps = conn.prepareStatement(sqlexc);
      ps.setString(1, blob);
      ps.executeUpdate();

     }


     }else{//jboss server

      clobOracle = (oracle.sql.CLOB)rs.getClob("JSONDATA");
      writer = clobOracle.getCharacterOutputStream();

      writer.write(jsonData);
      writer.flush();

     }

   if (rs != null) {

    rs.close();
    rs=null;
   }

   if (ps != null) {

    ps.close();
    ps=null;
   }

   if (writer!=null){
    writer.close();
   }

   if (oracle_clob!=null)
    oracle_clob.close();
   if (java_clob!=null)
    java_clob=null;
}

jsonBytes = null;
}

--read clob type

String sql = "select jsondata from table ;

Collection col = dbUtil.execSelect(sql);

String jsondata = "";
   String uiLangCode = "";
   String errMsg = "";
   String sysDate = "";
   String timeout_ = "";
   java.sql.Clob clob = null;
   Writer writer = null;
   boolean isTimeout=false;
   if (col != null && col.size() > 0) {
       Iterator it = col.iterator();
    Data data=null;

    while (it.hasNext()) {

     data = (Data) it.next();
      clob=data.getClobProperty(colName[0]);

      if (clob != null){

        int valueLength = 65536;
        StringBuffer tempValue = new StringBuffer("");
        re = clob.getCharacterStream();
        char[]buffer = new char[65536];
        while ((valueLength = re.read(buffer)) > 0){
         tempValue.append(buffer, 0, valueLength);
       }
         if(re!=null){
       re.close();
         }
         jsondata=tempValue.toString();
        }

      }

    /*
    StringBuffer byte_data = new StringBuffer();
       byte[] b = new byte[4096];
       for (int n; (n = is.read(b)) != -1;) {
       byte_data.append(new String(b, 0, n));
       }

    jsondata = byte_data.toString();
    */
     }

0 0