3

I want to update huge user database.

Below is the table format which contain 20k user records.

enter image description here

I have one excel sheet which have new data as below. This data is not specific to user. This is master data common to all user.

enter image description here

The task is read user specific data from table then read new data from excel sheet corresponding to old data after that update new data in same table for that user.

For example -

  • Step 1 - Read data(use_id, node_id and kmap_type) from database for user_id 95961800
  • Step 2 - Read new value from excel corresponding to database node_id(9908) which are 7707,1000,7707
  • Step 3 - Update these new value in same table for same user with KMAP_TYPE as SUBINDUSTRY,FUNCTION AND ROLE respectively.

I am thinking for using spring boot batch service for this. because I have average knowledge in java and spring boot technology

Can anyone please suggest how should I proceeed here?

Thanks in advance.

surendrapanday
  • 770
  • 2
  • 9
  • 24

1 Answers1

1

I don't know if there's any Spring features for such a job.

What I would do is :

1) use Apache POI lib that lets us handle Excel file easily.

2) read each value in the Excel file (that sould be a new one if I understood well what you said)

3) update the database values with the new ones

Here's a code sample of what can be done with Apache POI : you get an Excel file in the src/test/resources/Excel folder (for example) and store it in a 'Workbook' object.

@Test
public void FindExistingExcelFileTest() {
    System.out.println("\n----- FindExistingExcelFile -----");
    String pathExcel = "src/test/resources/Excel/";
    String fileName = "yourExcelFileHere.xlsx";
    File fileExcel = new File(pathExcel + fileName);    
    try {
        InputStream inp = new FileInputStream(fileExcel);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet1 = wb.getSheetAt(0);
        System.out.println("Sheet name = " + sheet1.getSheetName());
        Assert.assertNotNull(sheet1);
        wb.close();
    } catch (IOException | InvalidFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

You can then handle your datas with 'Row' or 'Cell' object.

Try this and come back here for more details.

Lovegiver
  • 413
  • 6
  • 22
  • Do not want to read excel file then update database. Want to read data from table first, then validate it from excel file after that update the database table. – surendrapanday Jan 22 '19 at 10:54
  • It is working. Solution - 1. Reading excel file first and storing in a cache 2. Reading database 3. Validating data with cache and updating database based on validation. – surendrapanday Jan 22 '19 at 13:52