用java实现从文本文件批量导入数据至数据库

1、将数据按一定规律录入到一个文本文件，每一行代表一条记录。 

下面是数据库建表SQL： 
CREATE TABLE t_FltPsgInfo  -- 航班乘客信息 

( 

    FltNum  VARCHAR(10), -- 航班号 

    FltLine  VARCHAR(30),  -- 航线 

    FltDate  VARCHAR(10),  -- 日期 

    PsgName  VARCHAR(30),  -- 姓名 

    PsgType  VARCHAR(30), -- 乘客类型，数字表示，目前是1-13 

    PsgSex  VARCHAR(1),  -- 0 男  1 女 

    PsgCab  VARCHAR(1),  -- 几等舱， F/Y  舱位按字母顺序排列 

    PsgSeatNo  VARCHAR(5),-- 座位号 2A,22F，根据这个得到一排有多少个座位，共有多少排座位信息 

    PsgInfo  VARCHAR(2048) -- 详细信息，可能很长 

) 

我们将向表t_FltPsgInfo中插入1000条记录。 

新建一个文本文件，每一行代表一条记录，如： 

HU7804,广州-北京,2007-07-18,谢丽珍,3,1,C,3A,服务保障信息：未用餐随行人员… 

其中以“，”作为字段的分隔标志，我们在解析这个文本文件时将根据“，”来拆分字段值。 

按照上面的格式，将要插入的数据输入到文本文件中，注意，是每一行代表一条记录，或者你已有从数据库导出的文本文件，那你就只需找到文件的规律，稍作调整就行了。 

2、编写Java源码 

1》数据库操作类InsertDB.java 

package test; 

import java.sql.Connection; 

import java.sql.DriverManager; 

import java.sql.ResultSet; 

import java.sql.Statement; 

public class InsertDB { 

    private static final String user = "sa"; 

    private static final String pwd = "sa"; 

    private static final String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=hhfly"; 

    private static final String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; 

    public static Connection getCon() { 

        Connection con = null; 

        try { 

            Class.forName(driver).newInstance(); 

            con = DriverManager.getConnection(url, user, pwd); 

            if (con != null) { 

                System.out.println("你已连接到数据库：" + con.getCatalog()); 

            } 

        } catch (Exception e) { 

            System.out.println("连接数据库失败！"); 

            e.printStackTrace(); 

        } 

        return con; 

    } 

    public boolean insertDB(String FltNum, String FltLine, String FltDate, 

            String PsgName, String PsgType, String PsgSex, String PsgCab, 

            String PsgSeatNo, String PsgInfo) { 

        Connection con = null; 

        Statement stm = null; 

        boolean flag = false; 

        String sql = "insert into t_FltPsgInfo values('" + FltNum + "','" 

                + FltLine + "','" + FltDate + "','" + PsgName + "','" + PsgType 

                + "','" + PsgSex + "','" + PsgCab + "','" + PsgSeatNo + "','" 

                + PsgInfo + "')"; 

        try { 

            con = getCon(); 

            stm = con.createStatement(); 

            int i = stm.executeUpdate(sql); 

            if (i > 0) { 

                flag = true; 

                System.out.println(flag + "插入数据成功！"); 

            } 

        } catch (Exception e) { 

            flag = false; 

            e.printStackTrace(); 

        } finally { 

            close(null, stm, con); 

        } 

        return flag; 

    } 

    //关闭相关连接 

    public void close(ResultSet rs, Statement stm, Connection con) { 

        if (rs != null) 

            try { 

                rs.close(); 

            } catch (Exception e) { 

                e.printStackTrace(); 

            } 

        if (stm != null) 

            try { 

                stm.close(); 

            } catch (Exception e) { 

                e.printStackTrace(); 

            } 

        if (con != null) 

            try { 

                con.close(); 

            } catch (Exception e) { 

                e.printStackTrace(); 

            } 

    } 

} 




2》数据采集类DataGather.java 

package test; 

import java.io.RandomAccessFile; 

import java.io.UnsupportedEncodingException; 

public class DataGather { 

    private static final String path = "src/resource/test"; 

    public static final String openFileStyle = "r"; 

    public static final String fieldLimitChar = ","; 

    public static final int fieldAllCount = 9; 

    private int count; 

    private String FltNum; 

    private String FltLine; 

    private String FltDate; 

    private String PsgName; 

    private String PsgType; 

    private String PsgSex; 

    private String PsgCab; 

    private String PsgSeatNo; 

    private String PsgInfo; 

    /* 

     * 功能：解析文本文件 

     */ 

    public void loadFile() { 

        try { 

            RandomAccessFile raf = new RandomAccessFile(path, openFileStyle); 

            String line_record = raf.readLine(); 

            while (line_record != null) { 

                // 解析每一条记录 

                parseRecord(line_record); 

                line_record = raf.readLine(); 

            } 

            System.out.println("共有合法的记录" + count + "条"); 

        } catch (Exception e) { 

            e.printStackTrace(); 

        } 

    } 



    /* 

* 功能：具体解析每一条记录，这里可以增加很多对记录的解析判断条件，如是否为字母、 

* 数字、email等。 

     */ 

    private void parseRecord(String line_record) throws Exception { 

     //拆分记录 

        String[] fields = line_record.split(fieldLimitChar); 

        if (fields.length == fieldAllCount) { 

            FltNum = tranStr(fields[0]); 

            FltLine = tranStr(fields[1]); 

            FltDate = tranStr(fields[2]); 

            PsgName = tranStr(fields[3]); 

            PsgType = tranStr(fields[4]); 

            PsgSex = tranStr(fields[5]); 

            PsgCab = tranStr(fields[6]); 

            PsgSeatNo = tranStr(fields[7]); 

            PsgInfo = tranStr(fields[8]); 

            System.out.println(FltNum + " " + FltLine + " " + FltDate + " " 

                    + PsgName + " " + PsgType + " " + PsgSex + " " + PsgCab 

                    + " " + PsgSeatNo + " " + PsgInfo); 

            InsertDB db = new InsertDB(); 

            db.insertDB(FltNum, FltLine, FltDate, PsgName, PsgType, PsgSex, 

                    PsgCab, PsgSeatNo, PsgInfo); 

            count++; 

        } 

    } 



    private String tranStr(String oldstr) { 

        String newstr = ""; 

        try { 

            newstr = new String(oldstr.getBytes("ISO-8859-1"), "GBK"); 

        } catch (UnsupportedEncodingException e) { 

            e.printStackTrace(); 

        } 

        return newstr; 

    } 

} 




3》测试类Test.java 

package test; 



public class Test { 

    public static void main(String[] args) { 

        try { 

            DataGather gather = new DataGather (); 

            gather.loadFile(); 

        } catch (Exception e) { 

            e.printStackTrace(); 

        } 

    } 

}