CSVファイルでMySQLの一括データ挿入操作をインポートするJava操作

最近の仕事はexcelの中のデータをMySQLデータベースに導入することに関連して、Excelの中のデータは規範的ではないため、二次加工を行う必要があります.EXcelのデータを加工した後、mybatisを通じてmySQLデータベースに大量に挿入し、それに関連する技術点は比較的簡単で、半日の編纂を経て、任務を完成したと言える.しかし、テストの时に性能があまりにも悪くて、2 W条のデータのexcelファイルを処理するのに2分近くかかります.それからネット上でload data infileの方式を通じて、データベースにcsvデータファイルをロードさせて、効率を数十倍高めることができます.5 Wのデータをロードするexcelファイルの時間は6秒以内に抑えることができます【小可はすでに満足しています】、更に最適化することができるべきで、しかし小可の能力に限られて、先に完成した仕事を1つの総括をして、同じく1つの分かち合いでしょう.
プログラミング言語:Java
プラットフォームフレームワーク:Spring、Spring MVC、MyBatis
解析Excel:Apache POI
CSV生成:Apache commons-csv
一、解析excel

        Workbook workBook = parseExcelGetWorkbook(is,fileName);
        Sheet sheet = workBook.getSheetAt(0);
        int rowsNum = sheet.getPhysicalNumberOfRows();
        List> resultList = Lists.newArrayList();
        Row rootRow = sheet.getRow(0);
        int cellsNum = rootRow.getPhysicalNumberOfCells();
        for(int j=1;j list = new ArrayList();
            for(int i=0;i

resultListはexcelのすべてのデータであり、Mybatisによる一括挿入データベース処理

        StringBuffer sb = new StringBuffer("insert into ").append(tableName).append("(");
        StringBuilder messageFormatStr = new StringBuilder("(");
        for(int i=0;i

以上はMybatisでデータを挿入したMySQLデータベースで、性能が非常に悪いです.数千本のデータがまだ受け入れられると、W本のデータが非常に詰まって、CSVファイルを生成する操作方式を通じて、性能が数十倍向上します.
1.解析excelによりデータを処理し【解析excel部分省略】、データから.CSVファイルを生成する
commons-csvを準備します.jar
使用するAPIは主にCSVFormatがある.classとCSVprinter.class
CSVFormat csvFileFormat = CSVFormat.DEFAULT.withRecordSeparator("");//CSVFormatを作成し、行ごとにレコード間隔を改行します【】
説明:ここでCSVファイルの具体的な使用方法についてあまり紹介しないで、もし知らないなら関連資料を調べることができます
BufferedWriterを使用したファイル出力オブジェクトWriterの作成
BufferedWriter fileWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(filepath)),"UTF-8"));
説明:ここではUTF-8符号化を使用して、CSVファイルを生成する符号化フォーマットがUTF-8であることを確認します.【プロジェクト環境に応じてutf-8、gbk...】ここでトランスコード処理をしないと、csvファイルの中国語フィールドがmySQLデータベースに正しくインポートされない可能性があります.
私のプロジェクトでは、ヘッダーの生成には触れません.ヘッダーの生成に関連する場合は、次のように設定できます.
csvFilePrinter.printRecord(new String[]{"id","username","password","name","age"});
CSVPrinter csvFilePrinter = new CSVPrinter(fileWriter, csvFileFormat);//フォーマットされたオブジェクトと出力オブジェクトからCSVファイルの書き込みオブジェクトを作成
Excelファイルからデータを取得し、データを処理し、処理後のデータをcsvFilePrinterオブジェクトを介してターゲットファイルに書き込む

            Workbook workBook = parseExcelGetWorkbook(is,fileName);
            Sheet sheet = workBook.getSheetAt(0);
            int rowsNum = sheet.getPhysicalNumberOfRows();
            Row rootRow = sheet.getRow(0);
            int cellsNum = rootRow.getPhysicalNumberOfCells();
            StringBuilder recordStr = new StringBuilder();
            for(int j=1;j

説明:recordStr.append(StringUtils.noNull(j)+"&%$");の「&%$」は各フィールド間の分割子です.私たちが処理しているデータは比較的特殊なので、データではなくカンマ、スペースでしか使えません.例えば、
recordStr.append(StringUtils.noNull(j)+",");あるいはrecordStr.append(StringUtils.noNull(j)+"\t");どちらでもいいです.
 csvFilePrinter.printRecord(recordStr.toString());//行ごとのデータの書き込み
これにより、ターゲットcsvファイルが生成され、ファイルアドレスは上記のfilepathとなり、ここでcsvファイルの生成が完了し、残りの作業はjava操作データベースを通じてcsvファイルをmySQLデータベースにインポートし、元の生態JDBC操作とMybatis操作の2つの方法をテストし、ここで2つの処理方法のコードを貼り付けて参考にします.
JDBC方式:

        DruidDataSource dataSource = SpringContextHolder.getBean("dataSource");
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = dataSource.getConnection();
            String sql = "LOAD DATA LOCAL INFILE '" + filepath
                    + "' INTO TABLE " + tableName + " "
                    + " FIELDS TERMINATED BY '&%$'";
            pstmt = conn.prepareStatement(sql);
            if (pstmt.isWrapperFor(com.mysql.jdbc.Statement.class)) {
                com.mysql.jdbc.PreparedStatement mysqlStatement = pstmt
                        .unwrap(com.mysql.jdbc.PreparedStatement.class);
                result = mysqlStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(conn != null){
                    conn.close();
                }
                if(pstmt != null){
                    pstmt.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

Mybatis方式:
tablemNameとfilepathをパラメータとして、Mybatis機能を正常に呼び出せばいいので、ここで私たちのコードクリップを参考にしてください.
サービスで呼び出されます.
mapper.insertDataByCSVFile(filepath, tableName);
mapperインタフェース:

    @SelectProvider(type=DataFileSqlProvider.class,method="getInsertDataByCSVFileSql")
    public void insertDataByCSVFile(@Param("filepath") String filepath, @Param("tableName") String tableName);

    public String getInsertDataByCSVFileSql(Map param){
        String filepath = StringUtils.noNull(param.get("filepath"));
        String tableName = StringUtils.noNull(param.get("tableName"));
        String sql = "LOAD DATA LOCAL INFILE '" + filepath
                + "' INTO TABLE " + tableName + " "
                + " FIELDS TERMINATED BY '&%$'";
        return sql;
    }

説明:ローカルの場合

LOAD DATA LOCAL INFILE

サーバは次のようになります.

LOAD DATA INFILE