JDBC操作Clob,Blob字段

原创

wx63086371c7e9c 2022-08-26 15:21:34 博主文章分类：JSE ©著作权

文章标签 oracle 数据库 java exception sql 文章分类 后端开发

©著作权归作者所有：来自51CTO博客作者wx63086371c7e9c的原创作品，请联系作者获取转载授权，否则将追究法律责任

最近和别人做了个东西,关系到操作大字段Clob和Blob字段,虽然做完了,但感觉还是意犹未尽,在今天下午特意总结了一下,写了差不多三百行代码.都调试过.收获也不小.

说明1:首先所有的文件都是以二进制存储
2:二进制文件有.doc .xls .jpg
文本文件有 .txt .html .xml

先在oracle数据库中建一张表用与测试

create table CDL_TEST
(
  SID  VARCHAR2(20) not null,
  IMG  BLOB,       //存储二进制
  DOC  CLOB,     //存储文本
  DATA NUMBER
)

创建主键

alter table CDL_TEST
  add constraint CDL_SID primary key (SID)

初始化DB

package DataBaseUtil;
import java.sql.*;
import java.util.Scanner;
import java.io.*;
import oracle.sql.BLOB;

class InitDB{
 private static Connection con=null;
 private static Statement stmt=null;
 private static ResultSet rs=null;
 InitDB(){
  try{
  Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
      String url="jdbc:oracle:thin:@localhost:1521:ORCL";  //ORCL 是sid
  String user="cdl";  
  String password="1";  
  con= (Connection) DriverManager.getConnection(url,user,password);
  InitDB.setCon(con);
  }catch(Exception e){
   e.printStackTrace();
  }
 }
 
 public void closCon(){
  try{
   con.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }
 public void stmt(){
  try{
   con.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }
 public void rs(){
  try{
   con.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }

 public static Connection getCon() {
  return con;
 }

 public static void setCon(Connection con) {
  InitDB.con = con;
 }

 public static ResultSet getRs() {
  return rs;
 }

 public static void setRs(ResultSet rs) {
  InitDB.rs = rs;
 }

 public static Statement getStmt() {
  return stmt;
 }

 public static void setStmt(Statement stmt) {
  InitDB.stmt = stmt;
 }
}

插入Blob数据如:图片

class InsertBlobData{
 private ResultSet rs=null;
 private InitDB idb=null;
 InsertBlobData(){
   idb=new InitDB();
 }
 public  void insertBlob(String sql1) throws SQLException{
  Connection con=idb.getCon();
  try{
   con.setAutoCommit(false);//不设置自动提交
   BLOB blob = null; //插入空的Blob
   PreparedStatement pstmt = con.prepareStatement("insert into cdl_test(sid,img) values(?,empty_blob())");
   pstmt.setString(1,"100"); 
   pstmt.executeUpdate(); 
   pstmt.close(); 
   rs=con.createStatement().executeQuery(sql1);
   while(rs.next()){
     System.out.println("rs length is:");
     oracle.sql.BLOB  b=(oracle.sql.BLOB )rs.getBlob("img");
     System.out.println("cloblength is:"+b.getLength());
     File f=new File("d://img//1.jpg");
     System.out.println("file path is:"+f.getAbsolutePath());
     BufferedInputStream in=new BufferedInputStream(new FileInputStream(f));  
     BufferedOutputStream  out=new BufferedOutputStream(b.getBinaryOutputStream());
     int   c;   
     while ((c=in.read())!=-1)   {   
      out.write(c);   
     }   
     in.close();   
     out.close();
   }
   con.commit();
  }catch(Exception e){
   con.rollback();//出错回滚
   e.printStackTrace();
  } 
 }
}

插入大文本如:1.txt

class InsertClobData{
 private ResultSet rs=null;
 private InitDB idb=null;
 InsertClobData(){
   idb=new InitDB();
 }
 public  void insertClob(String sql1) throws SQLException{
  Connection con=idb.getCon();
  try{
   con.setAutoCommit(false);//不设置自动提交
   BLOB blob = null; //插入空的Clob
   PreparedStatement pstmt = con.prepareStatement("insert into cdl_test(sid,doc) values(?,empty_clob())");
   pstmt.setString(1,"101"); 
   pstmt.executeUpdate(); 
   pstmt.close(); 
   rs=con.createStatement().executeQuery(sql1);
   while(rs.next()){
    System.out.println("sdfasdfas");
    oracle.sql.CLOB  cb=(oracle.sql.CLOB)rs.getClob("doc");
    File f=new File("d://doc//1.txt");
    System.out.println("file path is:"+f.getAbsolutePath());
    BufferedWriter out = new BufferedWriter(cb.getCharacterOutputStream());
    BufferedReader in = new BufferedReader(new FileReader(f));
    int c;
    while ((c=in.read())!=-1) {
        out.write(c);
    }
    in.close();   
    out.close();
   }
   con.commit();
  }catch(Exception e){
   con.rollback();//出错回滚
   e.printStackTrace();
  } 
 }
}

读取图片

class ReadBlobData{
 private ResultSet rs=null;
 private InitDB idb=null;
 ReadBlobData(){
   idb=new InitDB();
 }
 public void getBlob(String sql2) throws SQLException{
  Connection con=idb.getCon();
  con.setAutoCommit(false);
  try{
   System.out.println("sq2 is:"+sql2);
   System.out.println("stmt is:"+con);
   rs=con.createStatement().executeQuery(sql2);
   while(rs.next()){
    System.out.println("rs length is:");
    Blob b=(Blob)rs.getBlob("img");
    File f=new File("D://saveimg//1.jpg");
    FileOutputStream fos=new FileOutputStream(f);
    InputStream is=b.getBinaryStream();//读出数据后转换为二进制流
    byte[] data=new byte[1024];
    while(is.read(data)!=-1){
     fos.write(data);
    }
    fos.close();
    is.close();
   }
  con.commit();//正式提交
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   //rs.close();
  }
 }
}

读取大文本

class ReadClobData{
 private ResultSet rs=null;
 private InitDB idb=null;
 ReadClobData(){
   idb=new InitDB();
 }
 public void getClob(String sql2) throws SQLException{
  Connection con=idb.getCon();
  try{
   con.setAutoCommit(false);//不设置自动提交
   System.out.println("sq2 is:"+sql2);
   rs=con.createStatement().executeQuery(sql2);
   while(rs.next()){
    oracle.sql.CLOB clob=(oracle.sql.CLOB)rs.getClob("doc");
    File f=new File("d://savedoc//1.txt");
    BufferedReader in = new BufferedReader(clob.getCharacterStream());
    //setCharacterStream()方法，可用于将CLOB字段与字节流相关联，
             BufferedWriter out = new BufferedWriter(new FileWriter(f));
             int c;
             while ((c=in.read())!=-1) {
                  out.write(c);
              }
    out.close();
    in.close();
   }
  con.commit();//正式提交
  rs.close();
  }catch(Exception e){
   e.printStackTrace();
   con.rollback();
  }
 }
}

主调方法

import java.util.Scanner;

public class TestDB {
  public static void main(String[] args) {
    String sql1 = "select * from cdl_test  for update";// 悲观锁锁定需更新的行
    String sql2 = "select * from cdl_test";
    System.out.println("/t/t/t欢迎使用:");
    System.out.println("1:插入图片");
    System.out.println("2:插入文本");
    System.out.println("3:读取图片");
    System.out.println("4:读取文本");
    System.out.println("5:退出");
    System.out.println("请选择:");
    while (true) {
      try {
        Scanner sc = new Scanner(System.in);
        int i = sc.nextInt();
        System.out.println("sss:" + i);
        switch (i) {
        case 1:
          InsertBlobData isd = new InsertBlobData();
          isd.insertBlob(sql1);
          break;
        case 2:
          InsertClobData icd = new InsertClobData();
          icd.insertClob(sql1);
          break;
        case 3:
          ReadBlobData rb = new ReadBlobData();
          rb.getBlob(sql2);
          break;
        case 4:
          ReadClobData rc = new ReadClobData();
          rc.getClob(sql2);
          break;
        case 5:
          System.exit(0);
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}