对象序列化后,写入Oracle Blob字段:“无法从套接字读取更多的数据”

03-09-15 mellon
..............

StringBuffer sb=new StringBuffer("insert into LF_CTRL_PLAN(plan_no,fcno,itemid,netid,fore_category,plan_name,creator,plan_ser,createdate,fctype,foreType,recommendation) values(?,?,?,?,?,?,?,?,?,?,?,?)");

PreparedStatement preStat=conn.prepareStatement(sb.toString());

//对象序列化后,存入数据库

ByteArrayOutputStream byteOut=new ByteArrayOutputStream();

ObjectOutputStream outObj=new ObjectOutputStream(byteOut);

outObj.writeObject(this) ;

final byte[] objbytes=byteOut.toByteArray();

InputStream in=new InputStream(){

int objbytesIndex=0;

public int read(){

while(objbytesIndex<objbytes.length){

return objbytes[objbytesIndex++];

}

return -1;

}

};

preStat.setBinaryStream(8,in,objbytes.length);

...........

preStat.executeUpdate() ;

return plan_no;

}

mellon
2003-09-15 18:04
java.sql.SQLException: 无法从套接字读取更多的数据

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)

at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:944)

at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:741)

at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:700)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:373)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)

at com.nbw.forecast.Plan.save(Plan.java:274)

at com.nbw.forecast.Plan.save(Plan.java:219)

at com.nbw.forecast.ForecastResult.save(ForecastResult.java:87)

at com.nbw.forecast.helper.HelperSaveForecastResult.process(HelperSaveForecastResult.java:27)

at com.nbw.forecast.ForecastServlet.doGet(ForecastServlet.java:69)

at com.nbw.forecast.ForecastServlet.doPost(ForecastServlet.java:76)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)

我原本使用的是

ByteArrayOutputStream byteOut=new ByteArrayOutputStream();

ObjectOutputStream outObj=new ObjectOutputStream(byteOut);

outObj.writeObject(obj) ;

preStat.setBytes(8,byteOut.toByteArray());

使用数组直接输入,提示出错为:

java.sql.SQLException: 数据大小超出此类型的最大值: 4610

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)

at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:99)

at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:1761)

at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:856)

at oracle.jdbc.driver.OraclePreparedStatement.setBytes(OraclePreparedStatement.java:1825)

robbin
2003-09-15 18:41
Oracle Blob 不是这么用滴...

robbin
2003-09-15 18:44
应该是这样用滴...

import java.sql.*;
import java.io.*;
import oracle.sql.*;
public class WriteBlob {

  public static void main(String[] args) {

    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","fankai","fankai");
      conn.setAutoCommit(false);

      BLOB blob = null;

      PreparedStatement pstmt = conn.prepareStatement("insert into javatest(name,content) values(?,empty_blob())");
      pstmt.setString(1,"fankai");
      pstmt.executeUpdate();
      pstmt.close();

      pstmt = conn.prepareStatement("select content from javatest where name= ? for update");
      pstmt.setString(1,"fankai");
      ResultSet rset = pstmt.executeQuery();
      if (rset.next()) blob = (BLOB) rset.getBlob(1);

      String fileName = "oraclejdbc.jar";
      File f = new File(fileName);
      FileInputStream fin = new FileInputStream(f);
      System.out.println("file size = " + fin.available());

      pstmt = conn.prepareStatement("update javatest set content=? where name=?");

      OutputStream out = blob.getBinaryOutputStream();

      int count = -1, total = 0;
      byte[] data = new byte[(int)fin.available()];
      fin.read(data);
      out.write(data);
      /*
      byte[] data = new byte[blob.getBufferSize()];  另一种实现方法,节省内存
      while ((count = fin.read(data)) != -1) {
        total += count;
        out.write(data, 0, count);
      }
      */

      fin.close();
      out.close();

      pstmt.setBlob(1,blob);
      pstmt.setString(2,"fankai");

      pstmt.executeUpdate();
      pstmt.close();

      conn.commit();
      conn.close();
    } catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    } catch (IOException e) {
      System.err.println(e.getMessage());
    }
  }

}
    
<p>

robbin
2003-09-15 19:12
上面是用JDBC写的demo,下面再来一个Hibernate写的demo,相比较之下,简单很多了:

      sf = HibernateSessionFactory.getSessionFactory();
      s = sf.openSession();  
      tx = s.beginTransaction();
      c = new Cat();
      c.setName("Robbin");
      c.setImage(Hibernate.createBlob(buffer));
      s.save(c);
      s.flush();
      s.refresh(c, LockMode.UPGRADE);    
      BLOB blob = (BLOB) c.getImage();      
      OutputStream out = blob.getBinaryOutputStream();   
      String fileName = "ojdbc14.jar";
      File f = new File(fileName);
      FileInputStream fin = new FileInputStream(f);   
      int count = -1, total = 0;
      byte[] data = new byte[(int)fin.available()];
      fin.read(data);
      out.write(data);      
      fin.close();
      out.close();
      s.flush();
      tx.commit();
<p>

mellon
2003-09-16 09:10
我也知道这样用是可以滴。

但是我不知道为什么我那样用会有大小限制,因为上面的程序在DB2是可以用的,并且,大点也没关系。

为什么Oracle会有这样一个限制。究竟临界值是多少呢?

如果那样用不对,那么PrepareStatement.setBytes(int parameterIndex, byte[] x) 又该如何用呢?用来干什么呢?

mellon
2003-09-16 09:22
public void setBytes(int parameterIndex,

byte[] x)

throws SQLExceptionSets the designated parameter to the given Java array of bytes. The driver converts this to an SQL VARBINARY or LONGVARBINARY (depending on the argument's size relative to the driver's limits on VARBINARY values) when it sends it to the database.

看这段介绍,好像是和Driver有关。

robbin
2003-09-16 09:37
Oracle的Blob字段比较特殊,他比long字段的性能要好很多,可以用来保存例如图片之类的二进制数据。

写入Blob字段和写入其它类型字段的方式非常不同,因为Blob自身有一个cursor,你必须使用cursor对blob进行操作,因而你在写入Blob之前,必须获得cursor才能进行写入,那么如何获得Blob的cursor呢?

这需要你先插入一个empty的blob,这将创建一个blob的cursor,然后你再把这个empty的blob的cursor用select查询出来,这样通过两步操作,你就获得了blob的cursor,可以真正的写入blob数据了。

mellon
2003-09-16 11:01
这个不能解决我的问题,我上面的代码并不是总不能用,只有当我要序列化的对象大于xxx字节的时候,才不能用。

虽然使用Oracle的Blob对象可以实现,但是,我的这段程序就不能在DB2和Oarcle下公用了。

我需要一个不使用orale Blob对象的实现。

mellon
2003-09-16 11:27
Writing data using a LOB

First you need a reference to the LOB. Then you write the data to the LOB, which buffers it. Finally, you update the LOB column with the modified LOB, like this:

//Use oracle.sql.BLOB because java.sql.Blob lacks setBytes()

//JDBC3 java.sql.Blob adds the method setBytes(int,byte[])

//Oracle JDBC uses the method putBytes(int,byte[])

oracle.sql.BLOB dbBlob = (oracle.sql.BLOB)rs.getBlob(1);

//update blob

ps = cxn.prepareStatement(sqlSetBlob);

ps.setString(2,SomeValue);

dbBlob.putBytes(1,blob.getRaw());

/*

You can't do this:

ps.setBinaryStream(1,blob.getInputStream(),blob.getRaw().length);

You must do it like this:

ps.setBlob(1,dbBlob);

Which is weird because you CAN do this:

InputStream is = rs.getBinaryStream(1);

Note that if the column were declared LONGVARBINARY then

setBinaryStream() would work.

*/

ps.setBlob(1,dbBlob);

cxn.commit();

In the above example, you may be wondering what blob.getRaw() is. This blob object is a helper object that I wrote to contain blob data in memory. The getRaw() method returns a byte[] containing the blob data, which is what you need for the putBytes() method of oracle.sql.BLOB.

终于发现一篇小文章,将字段类型设为long raw ,代码就可以了。

虽然没有Blob优秀,但我的对象反正不大不会超过10K ,也只好将就了。

khx
2004-06-01 16:53
写不进去,不是程序的问题。

而是数据库的回滚段不够大。oracle9i以下的会滚段都要人工管理的。

只要把它调大就行了

猜你喜欢