通过for update向Oracle的BLOB存储文件

2019独角兽企业重金招聘Python工程师标准>>>

    基础知识：

1.Oracle的BLOB空值     

Create table b(id varchar,obj blob);
Insert into b(id) values(“A”);
Insert into b(id,obj) values(“B”,empty_blob());
Insert into b(id,obj) values(“C”,’’);
Insert into b(id,obj) values(“D”,null);
Select id,obj from b;
   id     obj     length
-----------------------------------------
   A      0x        0
   B      0x        0
   C      0x        0
   D      NULL     NULL

2.SQL语法：for update  [of 。。]

【该部分是引用网友的博文，欢迎提供原文链接，我必尊重原作者，贴上连接】

数据库 oracle for update of   和   for update区别     
      select * from TTable1 for update 锁定表的所有行，只能读不能写 
      select * from TTable1 where pkid = 1 for update 只锁定pkid=1的行 
      select * from Table1 a join Table2 b on a.pkid=b.pkid for update 锁定两个表的所有记录 
      select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update 锁定两个表的中满足条件的行 
      select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update of a.pkid 只锁定Table1中满足条件的行 
      for update 是把所有的表都锁定；for update of 根据of 后表的条件锁定相对应的表 

3.BLOB的jdbc方法

 http://docs.oracle.com/cd/B28359_01/java.111/b31224/oralob.htm

主要代码

 /**
     *@author Liu Yuanyuan
     */
    public static void main(String[] args)
    {
        Connection conn = null;
        Statement smt = null;
        ResultSet rs = null;
        try
        {
            String driver = "oracle.jdbc.driver.OracleDriver";
            String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            Class.forName(driver);
            System.out.println("class");
            conn = DriverManager.getConnection(url, "lob", "lob");
            System.out.println("connect");
            smt = conn.createStatement();
            String sql = "INSERT INTO blobtable(id,obj) VALUES(10241,empty_blob())";
            smt.execute(sql);
            System.out.println("sql = " + sql);

            conn.setAutoCommit(false);

            sql = "SELECT obj FROM blobtable WHERE id=10241 FOR UPDATE";
            rs = smt.executeQuery(sql);
            System.out.println("sql = " + sql);
            while (rs.next())
            {
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
                OutputStream outstream = blob.setBinaryStream(1L);
                File file = new File("d:\\10240.dbf");
                InputStream instream = new FileInputStream(file);
                byte[] buffer = new byte[1024];//other size also ok，like 512
                int length = -1;
                while ((length = instream.read(buffer)) != -1)
                {
                    outstream.write(buffer, 0, length);
                }
                instream.close();
                outstream.close();
                conn.commit();
                conn.setAutoCommit(true);
            }
            conn.setAutoCommit(true);
        }
        catch (Exception ex)
        {
            ex.printStackTrace(System.out);
        }
        finally
        {
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (smt != null)
                {
                    smt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException ex)
            {
               ex.printStackTrace(System.out);
            }
            finally
            {
                System.out.println("finally");
            }
        }
    }

常见错误

1.表空间不足：

java.io.IOException: ORA-01654: unable to extend index LOB.SYS_IL0000077694C00002$$ by 128 in tablespace LOB

at oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream.java:236)
at oracle.jdbc.driver.OracleBlobOutputStream.write(OracleBlobOutputStream.java:151)
at JDBCTest.UpdateBLOB.main(UpdateBLOB.java:65)

解决方法：当向表中插入上百兆的文件时，会导致这样的错误，这由于表空间大小不够了，可以再创建一个数据文件进行扩展,或者设置表空间的存储改为可自动扩展增大。

alter database datafile 'd:/MyTablespace' autoextend on [next 1M maxsize 100M];

转载于:https://my.oschina.net/liuyuanyuangogo/blog/151647