把數(shù)據(jù)從txt文件導入到數(shù)據(jù)庫的實現(xiàn)方法

1、把數(shù)據(jù)從txt文件導入到數(shù)據(jù)庫的實現(xiàn)方法文本文件dbo_M_CHTH.txt,數(shù)據(jù)格式如下:CHCDHB,CHCDTH1011102,F6719111011103,F6919111011104,F6819111011106,F601907········其中,第一行:CHCDHB,CHCDTH 為表頭名,不要求導入Oracle數(shù)據(jù)庫,從第二行以下的數(shù)據(jù)導入數(shù)據(jù)庫中的表:RPS.RPP_CONV_TEST0,表有兩個字段:(BP_CD,TH_CD?？梢杂脙煞N方式實現(xiàn)。先說簡單的:一、利用Oracle自帶的SQL*Lo

2、ader工具。步驟:1.建立一個控制文件input.ctl,不妨置于C盤根目錄。其內容如下:LOAD DATAINFILE 'C:dbo_M_CHTH.txt'INTO TABLE RPS.RPP_CONV_TEST0FIELDS TERMINA TED BY ","(BP_CD,TH_CD2.運行CMD命令,輸入如下命令行,并回車執(zhí)行:sqlldr userid=rps/rpsRPPBJ control =c:input.ctl其中:sqlldr userid=用戶名/密碼數(shù)據(jù)庫服務名稱control =絕對路徑、文件名具體執(zhí)行結果可以查看自動生成的Log

3、記錄文件。不再贅述。二、寫Java應用程序。索性先把我的代碼和注釋全貼出來,如下:package jp.co.ricoh;import java.io.BufferedReader;import java.io.File;import java.io.FileReader;import java.sql.Connection;import java.sql.PreparedStatement;public class CodeChange public static void txtToDB(String file_path_name throws Exception String file_

4、nameTag=""String table_name = ""String filed1 = ""String filed2 = ""String filed_content = ""boolean isFistLine = true;int count_num=0; /寫Log時,顯示的處理成功的記錄件數(shù)。Connection db =null; / New一個連接對象db。PreparedStatement stmt = null;PreparedStatement stmt1 = nul

5、l;LogWriter Errlog = new LogWriter(true; /LogWriter:寫Log的函數(shù)。LogWriter InfLog = new LogWriter(;InfLog.WriteLog(" 変換開始, 名: "+ file_path_name+ "。",true;/ -開始處理數(shù)據(jù)。try db= new DBConnection(.getConnection(; /一定要寫在try內,以捕捉異常File file = new File(file_path_name; /File 類提供了一種抽象方式,/以便以與機器無關

6、的方式處理機器相關情況下有關文件和路徑名。BufferedReader buffer = new BufferedReader(new FileReader(file;/此處利用BufferedReader,從字符輸入流中讀取文本,緩沖各個字符,從而實現(xiàn)字符、數(shù)組和行的高效讀取。/dbo_M_CHTH.txt ->表:RPP_CONV_TH;dbo_M_CHTH.txt ->表:RPP_CONV_TH/以下12行,只是據(jù)客戶要求,對這兩個txt文件,區(qū)分對應要插入的表和字段名。file_name=file_path_name.substring(file_path_name.len

7、gth(-5, file_path_name.length(-4;if (file_nameTag.equals("H" table_name = "RPP_CONV_TH"filed1 = "BP_CD"filed2 = "TH_CD" else if (file_nameTag.equals("S" table_name = "RPP_CONV_TS"filed1 = "BP_CD"filed2 = "TS_CD"String sq

8、lClearTable = " DELETE FROM " + table_name;/注意,此處不要用TRANCA TE TABLE ,太危險,無法回滾。stmt = db.prepareStatement(sqlClearTable;stmt.executeUpdate(;/ executeUpdate(適用于刪、插,如果單純查詢,可以用stmt.execute(。stmt.close(; /注意:此處非常有必要!如果不關閉prepareStatement ,/就只能最大開299個線程,如果你的表記錄條數(shù)超過300,也只能插入299條。/為提高效率,應盡量避免把prep

9、areStatement對象寫在循環(huán)內部頻繁操作。while (filed_content = buffer.readLine( != null if (!isFistLine /排除第一行,不插入。String str = filed_content.split("," /據(jù)txt文件內的逗號解析。if (str = null continue; /逐行的讀完為止。String sqlInsert = " INSERT INTO "+ table_name+ "( "+ filed1 + "," + filed2

10、 + " "+ " V ALUES"+ " ('" + str0 + "' , '" + str1 + "' "stmt1 = db.prepareStatement(sqlInsert;stmt1.executeUpdate(;stmt1.close(; /注意及時關閉prepareStatement。count_num+; /插入數(shù)據(jù)成功次數(shù),每次自增。 else isFistLine = false;mit(; / 別忘了提交!db.close(; /把該關

11、閉的連接關閉。buffer.close(;InfLog.WriteLog(" 変換正常終了,名:" + file_path_name +"," + count_num + "件処理。" ,true;InfLog.Release(;Errlog.Release(; catch (Exception e e.printStackTrace(;Errlog.WriteLog(" 変換異常終了,名:"+ file_path_name + "" +":"+ e.getMessage(+

12、 "。" , true;InfLog.WriteLog(" 変換異常終了,名:" + file_path_name + ","+ count_num + "件処理。", true;InfLog.Release(;Errlog.Release(;if (db != null /出異常情況下,非空的連接也要關閉。db.rollback(; /數(shù)據(jù)庫回滾db.close(;public static void main(String args throws Exception String txtFileName=&qu

13、ot;"String txtFilePath=""CONFReader.init(;txtFilePath=CONFReader.getTxtPath(;/得到文件路徑。txtFileName= CONFReader.getTxtFILENAME_CHTH(;/得到txt文件名dbo_M_CHTH.txt ->表:RPP_CONV_THtxtToDB( txtFilePath + txtFileName ;/調用函數(shù),開始處理。txtFileName= CONFReader.getTxtFILENAME_CHTS(;/得到txt文件名dbo_M_CHTS.t

14、xt ->表:RPP_CONV_TStxtToDB( txtFilePath + txtFileName ;××××××××××××××××××××××××××××××××××××CONF.PROPERTY中文件路徑的配置:TXTPA

15、TH=txt/txtFILENAME_CHTH=dbo_M_CHTH.txttxtFILENAME_CHTS=dbo_M_CHTS.txt××××××××××××××××××××××××××××××××××××調用CONFReader.ja

16、va 中的部分:package jp.co.ricoh;import java.util.*;import java.io.*;final public class CONFReader/private static String CONFIG_FILE = "./CONF.PROPERTY"/private static ExProperties propConfig = null;private static Properties propConfig = null;private CONFReader(public static void init(propConfig = new Properties(;tryFileInputStream isConfigFile = new FileInputStream(CONFIG_FILE;propConfig.load(isConfigFile;isConfigFile.close(;catch(Exception exex.p