title: How to read a excel file containing 150K records, validate, persist rows in DB using Java, Spring boot?
body: <p>I have a spring boot application and a REST API endpoint, which takes in an excel file containing 150K rows and 5 columns. Some of these columns are part of the composite primary key colums of the Employee table(Sql server)</p>
  <p>Table Employee:
  employeeId - varchar(20),
  name- varchar(50),
  age - smallint,
  effective_date - Date,
  expiration_date - Date,
  last_updated_ts - Timestamp,
  last_updated_by - varchar(20)</p>
  <p>Composite Primary Key columns: employeeId, name, age, effectiveDate</p>
  <p>Excel columns: employeeId, name, age, effectiveDate, expirationDate</p>
  <p>Excel Sample:
  |employeeId|name |age|effective_date|expiration_date|last_updated_ts|last_updated_by|
  |--    | --  |-- | --       | --        | --        | --            |
  |1     |John |25 |2019-05-30| null      |2019-05-20 | System        |
  |2     |Alice|28 |2017-08-15| 2021-02-15|2020-12-30 | HR            |
  |3     |Bob  |35 |2015-10-10| 2018-12-05|2018-11-20 | HR            |</p>
  <p>Here, John is active from 2019-05-30 without an expiration date. Alice is currently active, but expiring on 2021-02-15. Bob is already expired as he has an expiration_date in the past.</p>
  <p><strong>Requirement:</strong></p>
  <p>Input will be the above excel file. We will have to do the validations mentioned below and insert/update data into the tables.</p>
  <p>The requirement is that the user will use the UI to upload this excel file containing the employee details and following validations should be done.</p>
  <ol>
  <li><p>Add a new column isDataValid in the excel. It will contain values 'Y' or 'N'. If any of the validation fails for a row, then we mark it as 'N'. Or else, it is 'Y'.</p>
  </li>
  <li><p>Each row must have employeeId, name, age, effective_date.</p>
  </li>
  <li><p>expiration_date is optional. If it exists, expiration_date must be &gt;= effective_date</p>
  </li>
  <li><p>Each row of the excel should be validated for datatype(eg: employee name should contain only strings, not numbers etc.).</p>
  </li>
  <li><p>Each row of the excel has 5 columns and the first 4 columns are the composite primary key columns of the Employee table. Each row in the excel has to be compared against the Employee table.</p>
  <p>a. If the row already exists, but it is expired: Insert new row with data in excel(with new effective_date and expiration_date).
  b. If the row already exists, but it is not expired: Don't insert new row with data and update isValid as 'N'.
  c. If the row is not in the table, insert the data and mark isValid as 'Y'.</p>
  </li>
  </ol>
  <p>Output: The excel file, along with the newly created column 'isValid' should be sent back to the user via the REST API controller, so that the user can figure out, if there were any validation errors in any of the rows of the excel.</p>
  <p><strong>Flow:</strong></p>
  <ol>
  <li>Get Excel file as multipart file input from REST API.</li>
  <li>Use Apache POI to read the excel and get the workbook and sheet.</li>
  <li>Iterate each row, do the above validations, check against the Employee Table if record exists, follow the above validation rules to mark them in the excel row in the isValid column accordingly.</li>
  <li>If the row has to be saved/updated, persist the row in db.</li>
  <li>After all rows in excel are iterated, validated and marked as valid or not, send it back to the caller from the REST API.</li>
  </ol>
  <p><strong>Questions:</strong></p>
  <ul>
  <li>With the excel sheet having around 150000 rows, validations, what is the approach that I can follow? Regular loop, JPA saveAll takes around 1 minute for a small sample set of 500-1000 records(depending on the data).</li>
  <li>Thinking of using a batch(Spring batch), but how do I make it fast enough so that, the user is able to able to download the excel within a few mins at max.</li>
  <li>I couldn't find an unique enough duplicate question. Please give the links at the comments.</li>
  </ul>
  <p>Please suggest me some design options to complete the operation with my requirement. Thanks in advance.</p>
  <p>Code:</p>
  <p>We use Apache POI for excel manipulation</p>
  <p>Controller class</p>
  <code>
  <pre><code>@PostMapping(path = &quot;/employee/validation&quot;)
  public ResponseEntity&lt;InputStreamResource&gt; validateEmployeeAndUpload(@RequestParam Multipartfile excelFile) {
  
  SXSSFWorkbook workbook = new SXSSFWorkbook();
  HttpHeaders header = new HttpHeaders();
  try{
  //All validations handled here
  workbook = validateAndGetWorkbook(excelFile);
  ByteArrayOutputstream outputStream = new ByteArrayOutputStream();
  workbook.write(outputStream);
  ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toArray());
  
  headers.add(&quot;Content-Disposition&quot;, &quot;attachment; filename=Employee.xlsx&quot;);
  return ResponseEntity.ok().headers(header).body(new InputStreamResource(inputStream));
  }catch(Exception e){
  ....return bad request
  }
  }
  </code></pre>
  </code>
  <p>Employee entity
  <code></p>
  <pre><code>@Entity
  @Table(name=&quot;employee)
  class Employee implements Serializable{
  
  @EmbeddedId
  EmployeeId employeeId;
  
  @Column(&quot;expiration_date)
  Date expirationDate;
  
  @Column(&quot;last_updated_ts)
  Timestamp lastUpdatedTs;
  
  @Column(&quot;last_updated_by)
  String lastUpdatedBy;
  
  }
  </code></pre>
  </code>
  EmployeeId Embedded Id class
  <code>
  <pre><code>    @Embeddable
      class EmployeeId implements Serializable{
      
      @Column(name = &quot;employee_id)
      String employeeId;
      
      @Column(name = &quot;employeeName&quot;)
      String employeeName;
      
      @Column(name = &quot;age&quot;)
      Integer age;
      
      @Column(name = &quot;effective_date&quot;)
      Date effectiveDate;
      
      }
  </code></pre>
  </code>
  <p>Repository Interface</p>
  <code>
  <pre><code>interface EmployeeRepository implements CrudRepository&lt;Employee, EmployeeId&gt;{
  }
  </code></pre>
  </code>