Updating rows in tables concurrently using Multithreading in Java

Question

I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be possible if I divide batches of 100 rows in excel file and assign different batches of rows to different threads to concurrently update the rows in table from multiple threads ?

Answer 1

You can split your file into multiple files.

Try POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files (Apache POI Project)

Here's an example of how to read an Excel file :

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
   }

Answer 2

If updating all 50K rows is a common practice, then you have designed the schema wrong. Please elaborate on the schema; we may be able to suggest ways for the "updates" to happen when you fetch the data.