Oracle JAVA SORCE and BLOB OBJECT

最新推荐文章于 2023-12-18 17:12:34 发布

阿三哥哥

最新推荐文章于 2023-12-18 17:12:34 发布

阅读量858

点赞数

分类专栏： oracle 文章标签： java source oracle

本文链接：https://blog.csdn.net/yhx119/article/details/9192637

版权

oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

较老得plsql不能处理与OS的交互，比如获得文件目录，获得文件的大小等等，

plsql从9I开始支持javasource使用java处理OS交互。

例如

需求 :导入到指定目录下面所有的文件到数据库，文件类型可能是图片，PDF，WORD等。

INPUT

１. 指定的目录(包含子文件夹)

２. 指定的文件类型(suffix such as .pdf,take all is *) Such as

exec pkg_file.FILE_INPUT('D:\SYSTEM','*');

EXPORT

１. 指定的文件名(包含全路经)

２. 指定的数据库中导出文件的文件ID Suchas

execpkg_file.FILE_EXPORT('D:\kaokaokao.jpg',708);

Java IO FILE refer

http://download.oracle.com/javase/6/docs/api/java/io/File.html

GRANT JAVAUSERPRIV TO SCOTT;
GRANT Create ANY Directory TO SCOTT;
GRANT DROP ANY Directory TO SCOTT; 

DECLARE
  l_schema VARCHAR2(30) := 'SCOTT';
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/

CREATE SEQUENCE SEQ_FILE START WITH 1 INCREMENT BY 1;

CREATE TABLE FILE_LIST_INFO
( FILE_ID   NUMBER(10) PRIMARY KEY,
  FILE_NAME VARCHAR(200 CHAR) NOT NULL,
  FILE_PATH VARCHAR(200 CHAR) NOT NULL,
  FILE_TYPE VARCHAR(20 CHAR),
  FILE_LENGTH NUMBER(20) NOT NULL,
  FILE_CREATE_TIME VARCHAR(20 CHAR),
  FILE_MODIFY_TIME VARCHAR(20 CHAR),
  FILE_UPDATE_TIME DATE NOT NULL,
  FILE_LIST_ID NUMBER(10) 
) ROWDEPENDENCIES;

CREATE TABLE FILE_LIST
( LIST_ID     NUMBER(10) PRIMARY KEY,
  LIST_DATE   DATE DEFAULT SYSDATE,
  LIST_FILE   BLOB
) ROWDEPENDENCIES;

create or replace and compile java source named DirList as
  import java.lang.*;
  import java.io.*;
  import java.sql.*;
  import oracle.jdbc.*;
  import oracle.sql.*;
  import java.util.*;
  import java.text.*;

  public class DirList
  { 
    static void Process(File path_file,String path_suffix) throws SQLException
    {
      String v_file_name;
      String v_file_path;
      String v_parent_path;
      String v_file_type;
      long v_file_length;
      long v_file_modify;
      if(path_file.isFile())
      {
        v_file_name =path_file.getName();
        v_file_path =path_file.getPath();
        v_parent_path=path_file.getParent();
        v_file_type ="FILE";
        v_file_length =path_file.length();
        v_file_modify =path_file.lastModified(); 
        Date d = new Date(v_file_modify);
        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
        String v_file_dateString = sdf.format(d);
        
        if (path_suffix.equals("*"))
        {
          Connection conn = null;
          CallableStatement cs = null;
          String val = null;
          conn = DriverManager.getConnection("jdbc:default:connection");
          cs = conn.prepareCall("{ call PKG_FILE.FILE_INPUT_DB(?,?,?,?,?,?) }");
          cs.setString(1, v_file_name);
          cs.setString(2, v_file_path);
          cs.setString(3, v_file_type);
          cs.setFloat(4, v_file_length);
          cs.setString(5, v_file_dateString);
          cs.setString(6, v_parent_path);
          cs.execute();
          cs.close();
          conn.close();
        }
        else
        {
          if (v_file_name.endsWith(path_suffix))
          {
            Connection conn = null;
            CallableStatement cs = null;
            String val = null;
            conn = DriverManager.getConnection("jdbc:default:connection");
            cs = conn.prepareCall("{ call PKG_FILE.FILE_INPUT_DB(?,?,?,?,?,?) }");
            cs.setString(1, v_file_name);
            cs.setString(2, v_file_path);
            cs.setString(3, v_file_type);
            cs.setFloat(4, v_file_length);
            cs.setString(5, v_file_dateString);
            cs.setString(6, v_parent_path);
            cs.execute();
            cs.close();
            conn.close();
          }
        }
      }
      else if (path_file.isDirectory())
      {
        v_file_name =path_file.getName();
        v_file_path =path_file.getPath();
        v_parent_path=path_file.getParent();
        v_file_type ="DIRECTORY";
        v_file_length =0;
        v_file_modify =path_file.lastModified(); 
        Date d = new Date(v_file_modify);
        SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy hh:mm:ss");
        String v_file_dateString = sdf.format(d);
        Connection conn = null;
        CallableStatement cs = null;
        String val = null;
        conn = DriverManager.getConnection("jdbc:default:connection");
        cs = conn.prepareCall("{ call PKG_FILE.FILE_INPUT_DB(?,?,?,?,?,?) }");
        cs.setString(1, v_file_name);
        cs.setString(2, v_file_path);
        cs.setString(3, v_file_type);
        cs.setFloat(4, v_file_length);
        cs.setString(5, v_file_dateString);
        cs.setString(6, v_parent_path);
        cs.execute();
        cs.close();
        conn.close();
        File[] listOfFiles = path_file.listFiles();
        if(listOfFiles!=null)
        {
          for (int j = 0; j < listOfFiles.length; j++)
          {
           Process(listOfFiles[j],path_suffix);
          }
        } 
      }
    }
    
    public static void Inputfile(String directory,String suffix)
                     throws SQLException
    {
      File path = new File( directory );
      #sql { truncate table FILE_LIST_INFO
      };
      #sql { truncate table FILE_LIST
      };
      Process(path,suffix);
    }
    
    public static void Exportfile(String myFile, BLOB myBlob) 
                     throws Exception
    {
      // Bind the image object to the database object
      // Open streams for the output file and the blob
      File binaryFile = new File(myFile);
      FileOutputStream outStream = new FileOutputStream(binaryFile);
      InputStream inStream = myBlob.getBinaryStream();
      // Get the optimum buffer size and use this to create the read/write buffer
      int size = myBlob.getBufferSize();
      byte[] buffer = new byte[size];
      int length = -1;
      // Transfer the data
      while ((length = inStream.read(buffer)) != -1)
      {
        outStream.write(buffer, 0, length);
        outStream.flush();
      }
      // Close everything down
      inStream.close();
      outStream.close();
    } 
  };
/

CREATE OR REPLACE PACKAGE PKG_FILE AS
  PROCEDURE FILE_INPUT(P_DIRECTORY IN VARCHAR2, P_SUFFIX IN VARCHAR2);
  PROCEDURE FILE_INPUT_DB(P_FILENAME   IN VARCHAR2,
                          P_FILEPATH   IN VARCHAR2,
                          P_FILETYPE   IN VARCHAR2,
                          P_FILELENGTH IN NUMBER DEFAULT 0,
                          P_FILEMODIFY IN VARCHAR2,
                          P_PARENTPATH IN VARCHAR2);
  PROCEDURE FILE_EXPORT_JAVA(P_FILE IN VARCHAR2, P_BLOB IN BLOB);
  PROCEDURE FILE_EXPORT(P_FILEPATH  IN VARCHAR2,P_FILEID IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_FILE AS
  PROCEDURE FILE_INPUT(p_directory IN VARCHAR2, p_suffix IN VARCHAR2) AS
    LANGUAGE JAVA NAME 'DirList.Inputfile( java.lang.String , java.lang.String)';
  PROCEDURE FILE_INPUT_DB(P_FILENAME   IN VARCHAR2,
                          P_FILEPATH   IN VARCHAR2,
                          P_FILETYPE   IN VARCHAR2,
                          P_FILELENGTH IN NUMBER DEFAULT 0,
                          P_FILEMODIFY IN VARCHAR2,
                          P_PARENTPATH IN VARCHAR2) IS
    l_blob       BLOB;
    l_bfile      BFILE;
    v_sql_insert VARCHAR2(200);
  BEGIN
    --dynamic create the direcory FILE_DIR
    BEGIN
      v_sql_insert := 'Create Or Replace Directory FILE_DIR As ' || '''' ||
                      P_PARENTPATH || '\''';
      EXECUTE IMMEDIATE v_sql_insert;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20017,
                                'Can Not Dynamic Create the FILE_DIR Directory.' ||
                                SQLERRM);
    END;
  
    IF P_FILETYPE = 'FILE' THEN
    
      INSERT INTO FILE_LIST_INFO
        (FILE_ID,
         FILE_NAME,
         FILE_PATH,
         FILE_TYPE,
         FILE_LENGTH,
         FILE_UPDATE_TIME,
         FILE_MODIFY_TIME,
         FILE_LIST_ID)
      VALUES
        (SEQ_FILE.NEXTVAL,
         P_FILENAME,
         P_FILEPATH,
         P_FILETYPE,
         P_FILELENGTH,
         SYSDATE,
         P_FILEMODIFY,
         SEQ_FILE.CURRVAL);
    
      INSERT INTO FILE_LIST
        (LIST_ID, LIST_FILE)
      VALUES
        (SEQ_FILE.CURRVAL, empty_blob())
      RETURNING LIST_FILE INTO l_blob;
    
      l_bfile := bfilename('FILE_DIR', P_FILENAME);
      dbms_lob.fileopen(l_bfile);
      dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
      dbms_lob.fileclose(l_bfile);
    ELSE
      INSERT INTO FILE_LIST_INFO
        (FILE_ID,
         FILE_NAME,
         FILE_PATH,
         FILE_TYPE,
         FILE_LENGTH,
         FILE_UPDATE_TIME,
         FILE_MODIFY_TIME)
      VALUES
        (SEQ_FILE.NEXTVAL,
         P_FILENAME,
         P_FILEPATH,
         P_FILETYPE,
         P_FILELENGTH,
         SYSDATE,
         P_FILEMODIFY);
    END IF;
    COMMIT;
  END;
  
  PROCEDURE FILE_EXPORT_JAVA(P_FILE IN VARCHAR2, P_BLOB IN BLOB) AS
    LANGUAGE JAVA NAME 'DirList.Exportfile( java.lang.String, oracle.sql.BLOB)';

  PROCEDURE FILE_EXPORT(P_FILEPATH IN VARCHAR2, P_FILEID IN NUMBER) IS
    v_fileid   NUMBER(10);
    v_filename VARCHAR(200);
    v_filetype VARCHAR(20);
    v_blob     BLOB;
  BEGIN
    --check the file id
    BEGIN
      SELECT FILE_LIST_INFO.FILE_LIST_ID,
             FILE_LIST_INFO.FILE_NAME,
             FILE_LIST_INFO.FILE_TYPE,
             FILE_LIST.LIST_FILE
        INTO v_fileid, v_filename, v_filetype, v_blob
        FROM FILE_LIST_INFO, FILE_LIST
       WHERE FILE_LIST_INFO.FILE_LIST_ID = FILE_LIST.LIST_ID
         AND FILE_LIST_INFO.FILE_LIST_ID = P_FILEID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20017,
                                'Can Not Find The File ID.' ||
                                SQLERRM);
    END;
    FILE_EXPORT_JAVA(P_FILEPATH,v_blob);
  END;
END PKG_FILE;
/