更新Oracle BLOB字段

BLOB字段一般存储一些超大的对象，如图片等
BLOB字段直接在plsql里面不好做操作，用java直接调用api去操作更方便一点。下面的例子是更新数据库里面的BLOB字段

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;

public class UpdateBlob {  

    public static void  main(String[] args) throws SQLException,UnsupportedEncodingException {

        Connection conn = connect(); 

        Statement bst = conn.createStatement();
        ResultSet bset = bst.executeQuery("select content from table FOR UPDATE");

        BLOB b = null;
        while (bset.next()) {
            b = ((OracleResultSet) bset).getBLOB(1);
            if (b != null && b.length() != 0) {
                @SuppressWarnings("deprecation")
                OutputStream outStream = b.getBinaryOutputStream();

                String blobstr = blobToString(b);
                blobstr = blobstr.replace("Agreegate","Aggregate");

                byte[] newData = blobstr.getBytes(); 


                try {
                    outStream.flush();
                    outStream.write(newData,0,newData.length);
                    outStream.close();
                }
                catch (IOException e) {
                    e.printStackTrace();
                }
            }       
        }


        // 最后统一提交
        conn.commit();   

        bst.close();
        bset.close();
        conn.close();

    }


    public static Connection connect() {
        Connection conn = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1522:cc","test","test");
        }
        catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;
    }


    private static String blobToString(BLOB blob) throws SQLException,UnsupportedEncodingException {
        long BlobLength; // BLOB字段长度
        int i = 1; // 循环变量
        byte[] bytes; // BLOB临时存储字节数组
        String newStr = ""; // 返回字符串

        byte[] msgContent = blob.getBytes(); // BLOB转换为字节数组
        BlobLength = blob.length();  //获取BLOB长度 
        if (msgContent == null || BlobLength == 0) //如果为空，返回空值 
        {
            return "";
        } else {
            while (i <= BlobLength) //循环处理字符串转换，每次1024；Oracle字符串限制最大4k 
            {
                bytes = blob.getBytes(i,1024);
                i = i + 1024;
                newStr = newStr + new String(bytes);
            }
        }
        return newStr;
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!