更新Oracle BLOB字段

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lyc417356935/article/details/65633475

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;
    }
}
展开阅读全文

没有更多推荐了,返回首页