oracle 调用Java 工作实例

 java source  文件：ftpud

代码如下：

create or replace and compile java source named ftpud as
import java.io.File;
import java.io.FileWriter;
import java.io.FileReader;
import java.io.BufferedWriter;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.OutputStream;
import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileNotFoundException;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.StringTokenizer;
import java.util.regex.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import java.sql.* ;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.* ;
import java.util.Date;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import sun.net.TelnetInputStream;
import sun.net.TelnetOutputStream;
import sun.net.ftp.FtpClient;

public class FtpUD{
    private String ip = "";
    private String username = "";
    private String password = "";
    private int port = -1;
    private String path = "";

    FtpClient ftpClient = null;

    OutputStream os = null;
    FileInputStream is = null;

    /**
      *构造函数
      * @param serverIP 服务器地址
      * @param port     端口号
      * @param username 登陆用户
      * @param password 密码

    */
    public FtpUD(String serverIP, int port, String username, String password,String path) {
        this.ip = serverIP;
        this.username = username;
        this.password = password;
        this.port = port;
        this.path= path;
    }

    /**
     * 连接ftp服务器
     *
     * @throws IOException
     */
    public boolean connectServer(){
        ftpClient = new FtpClient();
        try {
              if(this.port != -1){
                    ftpClient.openServer(this.ip,this.port);
              }else{
                    ftpClient.openServer(this.ip);
              }

              ftpClient.login(this.username, this.password);

              if (this.path.length() != 0){
                    ftpClient.cd(this.path);    // path是ftp服务下主目录的子目录
              }

              ftpClient.binary();// 用2进制上传、下载
             
              System.out.println("已登录到\"" + ftpClient.pwd() + "\"目录");
             
              return true;
        }catch (IOException e){
              e.printStackTrace();
              return false;
        }
    }

    /**
     * 断开与ftp服务器连接
     *
     * @throws IOException
     */
    public boolean closeServer(){
        try{
            if (is != null) {
                is.close();
            }
            if (os != null) {
                os.close();
            }
            if (ftpClient != null) {
                ftpClient.closeServer();
            }
            System.out.println("已从服务器断开");
            return true;
        }catch(IOException e){
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 检查文件夹在当前目录下是否存在
     * @param dir
     * @return
     */
     private boolean isDirExist(String dir){
         String pwd = "";
         try {
             pwd = ftpClient.pwd();
             ftpClient.cd(dir);
             ftpClient.cd(pwd);
         }catch(Exception e){
             return false;
         }
         return true;
     }

     /**
     * 在当前目录下创建文件夹
     * @param dir
     * @return
     * @throws Exception
     */
     private boolean createDir(String dir){
         try{
            ftpClient.ascii();
            StringTokenizer s = new StringTokenizer(dir, "/"); //sign
            s.countTokens();
            String pathName = ftpClient.pwd();

            while(s.hasMoreElements()){
                pathName = pathName + "/" + (String) s.nextElement();
                try {
                    ftpClient.sendServer("MKD " + pathName + "\r\n");
                } catch (Exception e) {
                    e = null;
                    return false;
                }
                ftpClient.readServerResponse();
            }
            ftpClient.binary();
            return true;
        }catch (IOException e1){
            e1.printStackTrace();
            return false;
        }
     }

     /**
     * 取得相对于当前连接目录的某个目录下所有文件列表
     *
     * @param path
     * @return
     */
    public List getFileList(String path){
        List list = new ArrayList();
        DataInputStream dis;
        try {
            dis = new DataInputStream(ftpClient.nameList(this.path + path));
            String filename = "";
            while((filename = dis.readLine()) != null){
                list.add(filename);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

     /**
      * ftp上传
      * 如果服务器段已存在名为filename的文件夹，该文件夹中与要上传的文件夹中同名的文件将被替换
      *
      * @param filename 要上传的文件（或文件夹）名
      * @return
      * @throws Exception
      */
    public boolean upload(String filename){
        String newname = "";
        if(filename.indexOf("/") > -1){
            newname = filename.substring(filename.lastIndexOf("/") + 1);
        }else{
            newname = filename;
        }
        return upload(filename, newname);
    }

 

     /**
      * ftp上传
      * 如果服务器段已存在名为newName的文件夹，该文件夹中与要上传的文件夹中同名的文件将被替换
      *
      * @param fileName 要上传的文件（或文件夹）名
      * @param newName 服务器段要生成的文件（或文件夹）名
      * @return
      */
     public boolean upload(String fileName,String newName){
         try{
             String savefilename = new String(fileName.getBytes("ISO-8859-1"), "GBK");
             File file_in = new File(savefilename);//打开本地待上传的文件
             if(!file_in.exists()){
                 throw new Exception("此文件或文件夹[" + file_in.getName() + "]有误或不存在!");
             }
             if(file_in.isDirectory()){
                 upload(file_in.getPath(),newName,ftpClient.pwd());
             }else{
                 uploadFile(file_in.getPath(),newName);
             }

             if(is != null){
                 is.close();
             }
             if(os != null){
                 os.close();
             }
             return true;
         }catch(Exception e){
                e.printStackTrace();
                System.err.println("Exception e in Ftp upload(): " + e.toString());
                return false;
         }finally{
             try{
                 if(is != null){
                     is.close();
                 }
                 if(os != null){
                     os.close();
                 }
             }catch(IOException e){
                 e.printStackTrace();
            }
         }
     }

 

     /**
      * 真正用于上传的方法
      * @param fileName
      * @param newName
      * @param path
      * @throws Exception
      */
     private void upload(String fileName, String newName,String path) throws Exception{
             String savefilename = new String(fileName.getBytes("ISO-8859-1"), "GBK");
             File file_in = new File(savefilename);//打开本地待上传的文件
             if(!file_in.exists()){
                 throw new Exception("此文件或文件夹[" + file_in.getName() + "]有误或不存在!");
             }
             if(file_in.isDirectory()){
                 if(!isDirExist(newName)){
                     createDir(newName);
                 }
                 ftpClient.cd(newName);
                 File sourceFile[] = file_in.listFiles();
                 for(int i = 0; i < sourceFile.length; i++){
                     if(!sourceFile[i].exists()){
                         continue;
                     }
                     if(sourceFile[i].isDirectory()){
                         this.upload(sourceFile[i].getPath(),sourceFile[i].getName(),path+"/"+newName);
                     }else{
                         this.uploadFile(sourceFile[i].getPath(),sourceFile[i].getName());
                      }
                    }
             }else{
                 uploadFile(file_in.getPath(),newName);
             }
             ftpClient.cd(path);
     }

 

    /**
     *  upload 上传文件
     *
     * @param filename 要上传的文件名
     * @param newname 上传后的新文件名
     * @return -1 文件不存在 >=0 成功上传，返回文件的大小
     * @throws Exception
     */
    public long uploadFile(String filename, String newname) throws Exception{
        long result = 0;
        TelnetOutputStream os = null;
        FileInputStream is = null;
        try {
            java.io.File file_in = new java.io.File(filename);
            if(!file_in.exists())
                return -1;
            os = ftpClient.put(newname);
            result = file_in.length();
            is = new FileInputStream(file_in);
            byte[] bytes = new byte[1024];
            int c;
            while((c = is.read(bytes)) != -1){
                os.write(bytes, 0, c);
            }
        }finally{
            if(is != null){
                is.close();
            }
            if(os != null){
                os.close();
            }
        }
        return result;
    }

    /**
     * 从ftp下载文件到本地
     *
     * @param filename 服务器上的文件名
     * @param newfilename 本地生成的文件名
     * @return
     * @throws Exception
     */
    public long downloadFile(String filename, String newfilename){
        long result = 0;
        TelnetInputStream is = null;
        FileOutputStream os = null;
        try{
            is = ftpClient.get(filename);
            java.io.File outfile = new java.io.File(newfilename);
            os = new FileOutputStream(outfile);
            byte[] bytes = new byte[1024];
            int c;
            while ((c = is.read(bytes)) != -1) {
                os.write(bytes, 0, c);
                result = result + c;
            }
        }catch (IOException e){
            e.printStackTrace();
        }finally{
            try {
                if(is != null){
                        is.close();
                }
                if(os != null){
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

 

  /**
     * 读取 ftp 服务器上的指定文件的内容。
     * 供外部调用   
     * @param theFile    指定读取的文件的全路径。形如：D:\\holly\crm_FilesName.txt 
     * @throws Exception
     */
   public static String readFile(String theFile){
      String content = "";
      try{  
          File read = new File(theFile);  
          BufferedReader br = new BufferedReader(new FileReader(read));          
          content = br.readLine();
          System.out.println("文件内容："+content);
          br.close();  
      }catch(FileNotFoundException e){ //文件未找到
           e.printStackTrace();
      }catch(IOException e){
           e.printStackTrace();
      }
      return content;
   } 
    
   /**
     * 读取 ftp 服务器上的文件列表,并存入到指定的表中 。
     * 供外部调用        
     * @param fileDir    读取 ftp 服务器上指定的文件夹下列表。形如：hollydm    
     * @param rule     读取文件的规则。形如：20111111
     * @throws Exception
     */
    public static String getFilesNameIntoTable(String fileDir,String rule) throws Exception {
       String msg="";
       String filesName="";
       String ex= rule+".*\\_OBJECTCUSTOMER.TXT";       
       Pattern p=Pattern.compile(ex);   
      
       String tmpStr = "" ;
       PreparedStatement pstmt = null ;
       ResultSet rs = null ;
       Connection conn = null ;     
      
       FtpUD ftp = new FtpUD("FTP 地址",-1,"ftp用户名","密码","");     
     
  try {
        if(ftp.connectServer()){
            System.out.println("登录服务器成功！");
        }else{
            System.out.println("登录服务器失败！");
        }
       
        List list = ftp.getFileList(fileDir);       
        for (int i=0;i<list.size();i++)
        {
             String filename = (String)list.get(i);
             System.out.println(filename);
             Matcher m=p.matcher(filename);

            if(m.matches()) {
               try{
                   conn = DriverManager.getConnection("jdbc:default:connection:");
                   String sql ="insert into FTPUD_FILESNAMETABLE(fileName) values(?)";
                   pstmt=conn.prepareStatement(sql);
                   pstmt.setString(1, filename);           
                   pstmt.executeUpdate();
                   conn.commit();
               }catch(SQLException e){
                   conn.rollback();
                   e.printStackTrace();
               }finally{
                   try{
                       //rs.close();
                       pstmt.close();
                       conn.close();
                   }catch(SQLException e){
                       System.err.println(e.getMessage());
                   }
               }             
            }
         }
         msg="文件名存入数据库成功！";
      }catch (Exception e) {
         msg="文件名存入数据库出现异常！";
      }finally{
            ftp.closeServer();
      }
         return msg;
    }

   /**
     * 读取 ftp 服务器上的文件列表,并存入到指定的文件中 。
     * 供外部调用        
     * @param fileDir    读取 ftp 服务器上指定的文件夹下列表。形如：hollydm
     * @param path      存入到服务器上指定的文件名。形如：D:\\holly\crm_FilesName.txt
     * @param rule     读取文件的规则。形如：20111111
     * @throws Exception
     */
    public static String getFilesNameTest(String fileDir,String path,String rule) throws Exception {
       String msg="";
       String filesName="";
       String ex= rule+".*\\_OBJECTCUSTOMER.TXT";       
       Pattern p=Pattern.compile(ex);   

       FtpUD ftp = new FtpUD("FTP 地址",-1,"ftp用户名","密码","");     
          try {
        if(ftp.connectServer()){
            System.out.println("登录服务器成功！");
        }else{
            System.out.println("登录服务器失败！");
        }
       
        List list = ftp.getFileList(fileDir);       
        for (int i=0;i<list.size();i++)
        {
                 String filename = (String)list.get(i);
                 System.out.println(filename);
                 Matcher m=p.matcher(filename);

                 if(m.matches()) {
                     if(i==list.size()-1){
                         filesName+=filename;
                     }else{                      
                         filesName+=filename+",";
                     }                
                  }
         }
         //String path="E://javaWP/new.txt";
         try {
                //FileWriter fw=new FileWriter(path,true);    //在未尾添加的如下.
                FileWriter fw=new FileWriter(path);         //只有新写入内容的.          
                BufferedWriter bw=new BufferedWriter(fw);   //一行一行的读                
               bw.write(filesName);              
               bw.close();
               fw.close();
         } catch (IOException e) {              
             e.printStackTrace();
         }
         msg="读取成功！";
        } catch (Exception e) {
           msg="读取出现异常！";
         }finally
        {
            ftp.closeServer();
         }
         return msg;
    }

   /**
     * 从ftp下载文件到本地 。供外部调用
     *    
     * @param downFileDir     FTP 服务器上需要下载的文件夹名。形如：hollydm
     * @param localDir       下载到本地指定的文件夹名。形如：E:/javaWP/Ttt
     * @throws Exception
     */
    public static String downFileTest(String downFileDir,String localDir) throws Exception {
       String msg="";
      FtpUD ftp = new FtpUD("FTP 地址",-1,"ftp用户名","密码","");     
       try {
        if(ftp.connectServer()){
            System.out.println("登录服务器成功！");
        }else{
            System.out.println("登录服务器失败！");
        }
        //下载文件(或目录)
        List list = ftp.getFileList(downFileDir);
        msg+="文件列表：";
        for (int i=0;i<list.size();i++)
        {
                 String filename = (String)list.get(i);
                 System.out.println(filename);
                

 

create or replace procedure ftpUD_hollydm
is
v_msg varchar(32767);
begin
  /**  //获得文件列表写入指定txt 文件  */
  select FtpUD_getFilesNameTest('hollydm/OBJECTCUSTOMER','D:\\holly\crm_FilesName.txt','201109') into v_msg from dual;  
  
  /**  //读取指定文件
  select FtpUD_readFile('D:\\holly\crm_FilesName.txt') into v_msg from dual;
  dbms_output.put_line(v_msg);
  */ 
end;

 

/*根据指定规则，遍历指定文件夹，获取文件名称*/

create or replace function FtpUD_getFilesNameTest(fileDir in varchar,fpath in varchar,rule in varchar) return varchar
is LANGUAGE JAVA  NAME 'FtpUD.getFilesNameTest(java.lang.String,java.lang.String,java.lang.String) return String' ;

/*读取指定的文件*/

create or replace function FtpUD_readFile(theFile in varchar) return varchar
is LANGUAGE JAVA  NAME 'FtpUD.readFile(java.lang.String) return String' ;

/*上传文件夹*/

create or replace function FtpUD_uploadFileTest(uploadFileDir in varchar,ftpDir in varchar) return varchar
is LANGUAGE JAVA  NAME 'FtpUD.uploadFileTest(java.lang.String,java.lang.String) return String' ;

/*下载文件夹*/

create or replace function FtpUD_downFileTest(downFileDir in varchar,localDir in varchar) return varchar
is LANGUAGE JAVA  NAME 'FtpUD.downFileTest(java.lang.String,java.lang.String) return String' ;

 

测试：

exec 存储过程名(参数);

select 函数名(参数) from dual;;