用（java或plsql的存储过程）将文件以二进制的形式写入到oracle数据库（适用dwg格式文件）

java  将文件以二进制的形式导入到oracle数据库的blob或long raw格式的字段中。

 

package com.dsit.zsdw.test;

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.cfg.annotations.ArrayBinder;

import com.fr.report.script.function.ARRAY;

public class OracleImageTest {
 public static void mainI(String filename,String dir,String year) {
   Connection connection = null; 
  try {
   
   connection = getORACLEConn(); 
   List list=getFileName(filename,",");
   for(int i=0;i<list.size();i++){
    String itemfilename=dir+list.get(i).toString();
    File f = new File(itemfilename); 
    FileInputStream fis = new FileInputStream(f); 
    String sql = "update GQ_TEST set file=? where year='"+year+"' and name='"+list.get(i).toString()+"'";
             PreparedStatement ps = connection.prepareStatement(sql);
             fis = new FileInputStream(f);   
             ps.setBinaryStream(1, fis,(int)f.length());
             ps.execute();
            
   }
        } catch (FileNotFoundException e) { 
             e.printStackTrace(); 
         } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
       } finally { 
 
            try { 
                 connection.close(); 
             } catch (SQLException e) { 
                 e.printStackTrace(); 
             } 
         } 
     } 
 private static List getFileName(String strfilename,String split){
  String str="";
  List list = new ArrayList();
  for (int i=0;i<strfilename.length();i++){
   char c=strfilename.charAt(i);
   if(c==split.charAt(0)){
    list.add((Object)str);
    str="";
   }else{
    str=str+String.valueOf(c);
   }
   if(i==strfilename.length()-1){
    list.add((Object)str);
    str="";
   }
  }
  return list;
 }
 public static Connection getORACLEConn() throws ClassNotFoundException, 
             SQLException { 
  Connection connection; 
   Class.forName("oracle.jdbc.driver.OracleDriver"); 
   connection = DriverManager.getConnection( 
          "jdbc:oracle:thin:@192.1.0.5:1521:sdsf", "sddba", 
     "123456"); 
  return connection; 
  } 
 public static void main(String[] args) {
  mainI("7+555.dwg"
    ,"C:////"
    ,"2012");
 }

}

 

plsql  只能写入到blob格式的字段中

--create          chenya
--data            2012-6-12
--Description     将险段的图形文件以二进制的格式写入到字段为blob格式的字段中
--                其中文件的名称要和桩号一一对应。
--                photo_dir：图片所在的包的路径，最后面一定要带   /
--                filename ：图片名称，如果是多张图片，以,号分隔开
--                clyear   : 创建时间
create or replace procedure PRC_IMPORT_FILE_XD(photo_dir varchar2,
                                               filename  varchar2 ,
                                               clyear number
                                               ) as
  a_blob  blob;
  a_bfile bfile;
  str_sql varchar(200):='create or replace directory TMP as '''|| photo_dir||'''';
begin
  prc_get_split(filename, ',');
  EXECUTE IMMEDIATE str_sql;

  for cur_img in (select splitname
                    from get_split
                   where trim(splitname) is not null) loop
    a_bfile  := bfilename('TMP', cur_img.splitname);
    dbms_lob.fileopen(a_bfile);
    dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
    dbms_lob.fileclose(a_bfile);
   -- update GQ_TEST
   EXECUTE IMMEDIATE 'update GQ_TEST set file=a_blob where year='''+clyear+''' and name='''+cur_img.splitname+''''
    commit;
  end loop;
  --EXECUTE IMMEDIATE 'drop directory TMP';
end PRC_IMPORT_FILE_XD;