mysql - 想要在最短的时间内使用 excel 表中的值更新 MySQL DB

Question

我已经编写了下面的代码来从 excel 中读取单元格，然后将其更新到 MySQL 表。有超过 2000 条记录，此代码仅更新最后一条记录，而不是所有记录。如果我放 'pstm.executeBatch();' 在 for 循环中，然后它会逐一更新所有记录，大约需要 2 分钟。我想减少这个时间，所以在 URL 中添加了“&rewriteBatchedStatements=true”并放入 'pstm.executeBatch();' 在 for 循环之外。在控制台中，它显示所有记录的读取，但数据库仅更新了最后一条记录。

package com.company.testdata;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import javax.servlet.ServletException;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UpdateDataCopy {

    public static void main(String[] args) throws Exception {
        String user = "root";
        String pass = "test";

        String jdbcUrl = "jdbc:mysql://192.1.2.1/db_bro_mumbai?useSSL=false"+
                "&rewriteBatchedStatements=true";
        String driver = "com.mysql.jdbc.Driver";


        try {
            PreparedStatement pstm = null;
            Class.forName(driver);
            Connection myConn = DriverManager.getConnection(jdbcUrl, user, pass);
            FileInputStream input = new FileInputStream("E:\\Work\\TestData.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(input);
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                row = (XSSFRow) sheet.getRow(i);

                int id = (int)row.getCell(0).getNumericCellValue();
                System.out.println(id);
                String firstname = row.getCell(2).getStringCellValue();
                System.out.println(firstname);
                String middlename = row.getCell(3).getStringCellValue();
                System.out.println(middlename);
                String lastname = row.getCell(4).getStringCellValue();
                System.out.println(lastname);
                int physicalFitness = (int)row.getCell(25).getNumericCellValue();
                System.out.println(physicalFitness);

                String sql = "UPDATE fitness_details as p SET p.physicalFitness = ? "
                            + " WHERE CandidateID_FK1 = ?";

                pstm = (PreparedStatement) myConn.prepareStatement(sql);
                pstm.setInt(1, physicalFitness);
                pstm.setInt(2, id);
                pstm.addBatch();

                //Adding below line will update record one by one which is time consuming, so I commented this and added it after for loop.
                //pstm.executeBatch(); 

                System.out.println("Import rows " + i);
            }
            pstm.executeBatch();
            System.out.println("Imported");
            //myConn.commit();
            //pstm.close();
            //myConn.close();
            input.close();

        }
        catch (Exception exc) {
            exc.printStackTrace();
            throw new ServletException(exc);
        }   
   }
}

标签： mysqlexceljdbc

Answer 1

在我的评论中提到过...

    package com.company.testdata;
    import java.io.FileInputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import javax.servlet.ServletException;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class UpdateDataCopy {

        public static void main(String[] args) throws Exception {
            String user = "root";
            String pass = "test";

            String jdbcUrl = "jdbc:mysql://172.16.206.197/db_bro_mumbai?useSSL=false"+
            "&rewriteBatchedStatements=true";
            String driver = "com.mysql.jdbc.Driver";


            try {
                PreparedStatement pstm = null;
                Class.forName(driver);
                Connection myConn = DriverManager.getConnection(jdbcUrl, user, pass);
                String sql = "UPDATE fitness_details as p SET p.physicalFitness = ?  WHERE CandidateID_FK1 = ?";

                pstm = myConn.prepareStatement(sql);

                FileInputStream input = new FileInputStream("E:\\Work\\TestData.xlsx");
                XSSFWorkbook workbook = new XSSFWorkbook(input);
                XSSFSheet sheet = workbook.getSheetAt(0);
                XSSFRow row;

                for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                    row = (XSSFRow) sheet.getRow(i);

                    int id = (int)row.getCell(0).getNumericCellValue();
                    String firstname = row.getCell(2).getStringCellValue();
                    String middlename = row.getCell(3).getStringCellValue();
                    String lastname = row.getCell(4).getStringCellValue();
                    int physicalFitness = (int)row.getCell(25).getNumericCellValue();
                    pstm.setInt(1, physicalFitness);
                    pstm.setInt(2, id);
                    pstm.addBatch();

                    System.out.println("Import rows " + I + "ID: " +  id + " Middlename:" + middlename + " Lastname:" + lastname + "  Physicalfitness:" + physicalFitness );
                }
                pstm.executeBatch();
                System.out.println("Imported");
                //myConn.commit();
                pstm.close();
                myConn.close();
                input.close();

            }
            catch (Exception exc) {
               exc.printStackTrace();
                throw new ServletException(exc);
            }   
       }
    }