把数据从txt文件导入到数据库的实现方法

1、把数据从txt文件导入到数据库的实现方法文本文件dbo_M_CHTH.txt,数据格式如下:CHCDHB,CHCDTH1011102,F6719111011103,F6919111011104,F6819111011106,F601907········其中,第一行:CHCDHB,CHCDTH 为表头名,不要求导入Oracle数据库,从第二行以下的数据导入数据库中的表:RPS.RPP_CONV_TEST0,表有两个字段:(BP_CD,TH_CD。可以用两种方式实现。先说简单的:一、利用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命令,输入如下命令行,并回车执行:sqlldr userid=rps/rpsRPPBJ control =c:input.ctl其中:sqlldr userid=用户名/密码数据库服务名称control =绝对路径、文件名具体执行结果可以查看自动生成的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时,显示的处理成功的记录件数。Connection db =null; / New一个连接对象db。PreparedStatement stmt = null;PreparedStatement stmt1 = nul

5、l;LogWriter Errlog = new LogWriter(true; /LogWriter:写Log的函数。LogWriter InfLog = new LogWriter(;InfLog.WriteLog(" 変換開始, 名: "+ file_path_name+ "。",true;/ -开始处理数据。try db= new DBConnection(.getConnection(; /一定要写在try内,以捕捉异常File file = new File(file_path_name; /File 类提供了一种抽象方式,/以便以与机器无关

6、的方式处理机器相关情况下有关文件和路径名。BufferedReader buffer = new BufferedReader(new FileReader(file;/此处利用BufferedReader,从字符输入流中读取文本,缓冲各个字符,从而实现字符、数组和行的高效读取。/dbo_M_CHTH.txt ->表:RPP_CONV_TH;dbo_M_CHTH.txt ->表:RPP_CONV_TH/以下12行,只是据客户要求,对这两个txt文件,区分对应要插入的表和字段名。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个线程,如果你的表记录条数超过300,也只能插入299条。/为提高效率,应尽量避免把prep

9、areStatement对象写在循环内部频繁操作。while (filed_content = buffer.readLine( != null if (!isFistLine /排除第一行,不插入。String str = filed_content.split("," /据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+; /插入数据成功次数,每次自增。 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(; /数据库回滚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 ;/调用函数,开始处理。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