oracle 调用Java 工作实例

最新推荐文章于 2021-04-09 15:35:12 发布

iteye_4537

最新推荐文章于 2021-04-09 15:35:12 发布

阅读量94

点赞数

文章标签： 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);
ftp.downloadFile(downFileDir+"/"+filename,localDir+"/"+filename);
msg+=filename+" ; ";
}
msg+="下载成功！";
} catch (Exception e) {
msg="下载出现异常！";
}finally
{
ftp.closeServer();
}
return msg;
}

/**
* 从ftp上传文件。供外部调用
*
* @param uploadFileDir 本地需要上传的文件夹名。形如：E:/javaWP/Ttt
* @param ftpDir 上传到 FTP 上指定的文件夹下。形如：hollydm
* @throws Exception
*/
public static String uploadFileTest(String uploadFileDir,String ftpDir) throws Exception {
String msg="";
FtpUD ftp = new FtpUD("FTP 地址",-1,"ftp用户名","密码","");
try {
//ftp.connectServer();
if(ftp.connectServer()){
System.out.println("登录服务器成功！");
}else{
System.out.println("登录服务器失败！");
}

//上传
boolean result = ftp.upload(uploadFileDir,ftpDir);
//System.out.println(result?"上传成功！":"上传失败！");
if(result){
msg+="上传成功！";
}else{
msg+="上传失败！";
}
} catch (Exception e) {
msg="上传出现异常！";
}finally
{
ftp.closeServer();
}
return msg;
}

}

oracle 调用：

/*按指定格式遍历文件目录，并将里面的文件名称存入表中*/

create or replace procedure FtpUD_getFilesNameIntoTable(fileDir in varchar,rule in varchar)
is LANGUAGE JAVA NAME 'FtpUD.getFilesNameIntoTable(java.lang.String,java.lang.String)' ;

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;;