java写的从ftp上找到文件并把文件里的数据插入到数据库的工具类

4h93l2ctw5

因为有个导入数据的需求，要求是从ftp服务器找到文件，并将文件读到数据库的临时表中。
所以写了一个工具类。（注：字段间是用’,‘隔开的）


import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.RandomAccessFile;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import com.eos.system.annotation.Bizlet;
import sun.net.TelnetInputStream;
import sun.net.ftp.FtpClient;
/**
* 从ftp找到文件并将文件里的数据插入到数据库
*
* @author yourname (mailto:yourname@primeton.com)
*/
public class FTPdownload {
/**
* @param fileUrl 文件的路径
* @param tableName 对应的表的名字
* @throws Exception
*/
public void fileDownload(String fileUrl,String tableName) throws Exception {
TelnetInputStream fget = null;
RandomAccessFile getFile = null;
FtpClient fc = null;
PreparedStatement ps = null;
String ftpIP = "192.168.0.55";
String ftpUserName = "ryan";
String passWord = "ryan";
String fileUrls = fileUrl;//"测试/contacts20100520.txt";
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 加载JDBC驱动
String dbURL = "jdbc:sqlserver://192.168.0.55:3689; DatabaseName=ermis"; // 连接服务器和数据库test
String userName = "sa"; // 默认用户名
String userPwd = "123456"; // 密码
Connection dbConn = null;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
System.out.println("Connection Successful!"); // 如果连接成功控制台输出Connection Successful!
} catch (Exception e) {
e.printStackTrace();
}
try {
fc = new FtpClient();// ftp客户端对象
fc.openServer(ftpIP);// 连接ftp服务器
fc.login(ftpUserName, passWord);// 登录ftp服务器
fc.binary();// 使用二进制的方式下载
fget = fc.get(fileUrls);// 读取ftp远程文件
InputStreamReader read = new InputStreamReader(fget, "UTF-8");
BufferedReader br = new BufferedReader(read);
String deleteSql = "delete from "+tableName+";";
ps = dbConn.prepareStatement(deleteSql);
ps.executeUpdate();
ps.close();
try {
String line;
int index=0;
while ((line = br.readLine()) != null) {
if(index>0){
StringBuffer sql = new StringBuffer();
sql.append("insert into "+tableName+" values(");
String[] row = line.split(",");
for (int i = 0; i < row.length; i++) {
if (i < row.length - 1)
sql.append("'" + row + "',");
if (i == row.length - 1)
sql.append("'" + row + "'");
}
sql.append(");");
//System.out.println(sql);
ps = dbConn.prepareStatement(sql.toString());
ps.executeUpdate();
ps.close();
}
index++;
}
} catch (FileNotFoundException e) {
e.printStackTrace(System.out);
throw new Exception(e.getMessage());
} catch (IOException e) {
}
} catch (Exception e) {
e.printStackTrace(System.out);
throw new Exception(e.getMessage());
} finally {
try {
if(dbConn!=null)
dbConn.close();
System.out.println("Connection Close!");
fget.close();
} catch (Exception e) {
}
try {
fget.close();
} catch (Exception e) {
}
try {
getFile.close();
} catch (Exception e) {
}
try {
fc.closeServer();
} catch (Exception e) {
}
}
}
public static void main(String[] args) throws Exception {
FTPdownload f = new FTPdownload();
f.fileDownload("测试/contacts20100520.txt", "tmp_employee");
}
}