oracle的blob和clob字段的处理
发布日期:2021-10-17 16:08:07 浏览次数:1 分类:技术文章

本文共 18190 字,大约阅读时间需要 60 分钟。

/**

 * first of all,login the oracle's sqlplus with your userName and password
 * in this sample
 * SID = oracle
 * userName = jiaoxue
 * password = jiaoxue 
 *
 * create the two test tables for use with the following statements
 * CREATE TABLE TESTBLOB (id NUMBWE(20), name VARCHER2(20), blobattr BLOB);
 * CREATE TABLE TESTCLOB (id NUMBWE(20), name Varchar2(20), blobattr BLOB);
 *
 */
package com.thomas.blog_clob;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BlobAndClobTest {

 
 public static void main(String[] args){
  //insertStringIntoBlob();
  //getStringFromBlob();
  //insertByteArrayIntoBlob();
  
  //insertFileIntoBlob();
  //getFileFromBlob();
  //modifyFileIntoBlob();
  //modifyAFileIntoBlobClearly();
  
  //insertStringIntoClob();
  //getStringFromClob();
  //insertFileIntoClob();
  //getFileFromClob();
  //modifyFileArrayIntoClob();
  modifyFileArrayIntoClobClearly();  
 }
 
 
 /********************************************************************************
  *  字符串和blob之间的转换                                                        *
  ********************************************************************************/
 public static void insertStringIntoBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = true;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   stmt.executeUpdate("insert into testblob (id, name, blobattr) values (1, 'blobname1', empty_blob())");
   //一定要带上"for update"
   rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");
   if (rs.next()) {
    //convert java.sql.Blob into oracle.sql.BLOB
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
    OutputStream outStream = blob.getBinaryOutputStream();
    byte[] data = "我是一个字符串,我将被用来检验oracle的blob对象的插入,读取和修改功能的实现".getBytes();
    ByteArrayInputStream is = new ByteArrayInputStream(data);
    int size = blob.getBufferSize();
    byte[] buffer = new byte[size];
    int length = -1;
    try {
     while ((length = is.read(buffer)) != -1) {
      outStream.write(buffer, 0, length);
     }
    } catch (IOException e) {
     e.printStackTrace();
    }finally{      
     try {outStream.close(); } catch (IOException e) {e.printStackTrace();}
     try {is.close(); } catch (IOException e) {e.printStackTrace();}
    }
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 public static void getStringFromBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = true;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery("select BLOBATTR from TESTBLOB where ID=1");
   if (rs.next()){
        java.sql.Blob blob = rs.getBlob("BLOBATTR");
        byte[] data = blob.getBytes(1, (int) blob.length());
        String text = new String(data);
        System.out.println(text);
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 
 /********************************************************************************
  *  字节数组和blob之间的转换                                                       *
  ********************************************************************************/
 public static void insertByteArrayIntoBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = true;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   con.setAutoCommit(false);
   defaultCommit = con.getAutoCommit();   
   stmt = con.createStatement();
   stmt.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, 'blobname1', empty_blob())");
   rs = stmt.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");
   if (rs.next()){
        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
        OutputStream outStream = blob.getBinaryOutputStream();
        byte[] data = "我是一个字符串,我将被用来检验oracle的blob对象的插入,读取和修改功能的实现".getBytes();
        outStream.write(data, 0, data.length);
        outStream.flush();
        outStream.close();
    } 
    con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 /********************************************************************************
  *  文件流和blob之间的转换                                                         *
  ********************************************************************************/
 public static void insertFileIntoBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   stmt.executeUpdate("insert into testblob (id, name, blobattr) values (1, 'blobname1', empty_blob())");
   //一定要带上"for update"
   rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");
   if (rs.next()) {
    //convert java.sql.Blob into oracle.sql.BLOB
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
    BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
    BufferedInputStream in = new BufferedInputStream(new FileInputStream("e://Bocaloco_licence.doc"));
    int c;
    while ((c=in.read())!=-1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
  
 }
 public static void getFileFromBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery("select blobattr from testblob where ID=1");
   if (rs.next()) {
    //convert java.sql.Blob into oracle.sql.BLOB
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
    BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream("e://abcdefg.doc"));
    BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
    int c;
    while ((c=in.read())!=-1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
  
 }
 
 /**************************************************************************************
  * 该方法是覆盖式的修改blob字段,如果原来的数据过长的话将把新的内容与过长的部分进行衔接合并成一个*
  **************************************************************************************/
 public static void modifyFileIntoBlob() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   //一定要带上"for update"
   rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");
   if (rs.next()) {
    //convert java.sql.Blob into oracle.sql.BLOB
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
    BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
    BufferedInputStream in = new BufferedInputStream(new FileInputStream("e://log4j.properties"));
    int c;
    while ((c=in.read())!=-1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }  
 }
 /********************************************************************************
  *  先清空字段再重新赋值                                                           *
  ********************************************************************************/
 public static void modifyAFileIntoBlobClearly() {
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   System.out.println(11);
   stmt.executeUpdate("update testblob set blobattr=empty_blob()");
   System.out.println(22);
   //一定要带上"for update"
   rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");
   if (rs.next()) {
    //convert java.sql.Blob into oracle.sql.BLOB
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
    BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
    BufferedInputStream in = new BufferedInputStream(new FileInputStream("e://log4j.properties"));
    int c;
    while ((c=in.read())!=-1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }  
 }
 
 
 
 
 
 
 
 
 /********************************************************************************
  *  字符串和clob之间的转换                                                        *
  ********************************************************************************/
 public static void insertStringIntoClob(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = true;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   stmt.executeUpdate("insert into testclob (id, name, clobattr) values (1, 'clobname1', empty_clob())");
   rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");
   if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");
    Writer outStream = clob.getCharacterOutputStream();
    char[] data = "我是一个字符串,我将被用来检验oracle的clob对象的插入,读取和修改功能的实现".toCharArray();
    outStream.write(data,0,data.length);
    outStream.flush();
       outStream.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 public static void getStringFromClob(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = true;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery("select clobattr from testclob where ID=1");
   if (rs.next()) {
    java.sql.Clob clob = rs.getClob("CLOBATTR");
    Reader inStream = clob.getCharacterStream();
    char[] c = new char[(int) clob.length()];
    inStream.read(c);
    String data = new String(c);
    System.out.println(data);
    inStream.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 

 /********************************************************************************

  *  文件流和clob之间的转换                                                        *
  ********************************************************************************/
 public static void insertFileIntoClob(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   stmt.executeUpdate("insert into testclob (id, name, clobattr) values (1, 'clobname1', empty_clob())");
   rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");
   if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
    BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
    BufferedReader in = new BufferedReader(new FileReader("e://log4j.properties"));
    int c;
    while ((c = in.read()) != -1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 public static void getFileFromClob(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery("select clobattr from testclob where ID=1");
   if (rs.next()) {
    //注意:在这里java.sql.Clob和oracle.sql.CLOB也都能达到相同的目的
    java.sql.Clob clob = (java.sql.Clob)rs.getClob(1);
    //oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
    //注意:两种路径表达方式都可以达到想要的要求
    BufferedReader in = new BufferedReader(clob.getCharacterStream());
    BufferedWriter out = new BufferedWriter(new FileWriter("e://log4j1.properties"));
    //BufferedWriter out = new BufferedWriter(new FileWriter("e:/bbb.txt"));
    int c;
    while ((c=in.read())!=-1) {
     out.write(c);
    }
    out.close();
    in.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 /********************************************************************************
  *  进行覆盖修改                                                                  *
  ********************************************************************************/
 public static void modifyFileArrayIntoClob(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");
   if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
    BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
    BufferedReader in = new BufferedReader(new FileReader("e://StringUtil.java"));
    int c;
    while ((c = in.read()) != -1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 /********************************************************************************
  *  进行完全修改                                                                  *
  ********************************************************************************/
 public static void modifyFileArrayIntoClobClearly(){
  Connection con = null;
  Statement stmt = null;
  ResultSet rs = null;
  boolean defaultCommit = false;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");
   defaultCommit = con.getAutoCommit();
   con.setAutoCommit(false);
   stmt = con.createStatement();
   stmt.executeUpdate("update testclob set clobattr = empty_clob() where ID=1 for update");
   rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");
   if (rs.next()) {
    oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
    BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
    BufferedReader in = new BufferedReader(new FileReader("e://StringUtil.java"));
    int c;
    while ((c = in.read()) != -1) {
     out.write(c);
    }
    in.close();
    out.close();
   }
   con.commit();
  } catch (Exception e) {
   e.printStackTrace();
   try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}
  } finally {
   releaseResource(con,stmt,rs,defaultCommit);
  }
 }
 
 /**
  * release the connection resource and set the autoCommit true
  * @param con
  * @param stmt
  * @param rs
  */
 public static void releaseResource(Connection con,Statement stmt,ResultSet rs,boolean autoCommit){  
  if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}
  if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}} 
  if (con != null) {try {con.setAutoCommit(autoCommit);con.close();} catch (SQLException e) {e.printStackTrace();}}
 }
 /**
  * release the connection resource and set the autoCommit true
  * @param con
  * @param ps
  * @param rs
  */
 public static void releaseResource(Connection con,PreparedStatement ps,ResultSet rs,boolean autoCommit){  
  if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}
  if (ps != null) {try {ps.close();} catch (SQLException e) {e.printStackTrace();}} 
  if (con != null) {try {con.setAutoCommit(autoCommit);con.close();} catch (SQLException e) {e.printStackTrace();}}
 }
 
}

 

 

转载地址:https://blog.csdn.net/thamsyangsw/article/details/4192852 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:oracle大字段BLOB读取,转换异常
下一篇:XML SCHEMA

发表评论

最新留言

做的很好,不错不错
[***.243.131.199]2024年03月15日 06时56分34秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

python 公众号引流_公众号引流方法有哪些? 2019-04-21
java 减少内存_java中减少内存占用小技巧 2019-04-21
centos 7 mysql图形界面_centos7-vnstat图形界面搭建 2019-04-21
java 防渗透_「java、工程师工作经验怎么写」-看准网 2019-04-21
java中跳出当前循环怎么做_在java中,如何跳出当前的多重循环? 2019-04-21
java程序中执行maven_java – 将一个enviornment变量传递给Maven中的已执行进程 2019-04-21
java16下载_java lombok下载 2019-04-21
python 图像处理与识别书籍_Python图像处理之识别图像中的文字(实例讲解) 2019-04-21
java安全初始化_java安全编码指南之:声明和初始化 2019-04-21
java jstat gc_分析JVM GC及内存情况的方法 2019-04-21
php pclzip.lib.php,php使用pclzip类实现文件压缩的方法(附pclzip类下载地址) 2019-04-21
php dns更新,php_mzdns: 站群,大量域名 通过 dns 服务商 api 批量添加 ip 工具。你懂的~ 基于 mzphp2 框架。... 2019-04-21
jdk 1.8 java.policy,JDK1.8 导致系统报错:java.security.InvalidKeyException:illegal Key Size 2019-04-21
php linux权限,Linux权限详细介绍 2019-04-21
典型环节的matlab仿真分析,典型环节的MATLAB仿真.doc 2019-04-21
Php contenttype类型,各种类型文件的Content Type 2019-04-21
php使用redis持久化,redis如何持久化 2019-04-21
php7.1解压包安装,【Swoole】php7.1安装swoole扩展 2019-04-21
linux centos删除安装的包,CentOS yum认为已删除的软件包仍在安装中 2019-04-21
酒店管理系统c语言带注释,酒店管理系统--C语言版.pdf 2019-04-21