Convert List<Map<String, String>> to Object in Java Spring boot jpa

Question

I have a problem how approach to convert List<Map<String, String>> to custom class objects to save it to JPA. If I apply the branchRepository.save(content)it does not work.

Here's my code:

-- BranchService.java --

public List<Map<String, String>> uploadEmployee(MultipartFile multip) throws Exception {

    String fileNames = multip.getOriginalFilename();

    DataFormatter formatter = new DataFormatter();

    File file = new File("./reports/" + fileNames);
    Workbook workbook = WorkbookFactory.create(file);

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    Sheet sheet = workbook.getSheetAt(0);

    int headerRowNum = sheet.getFirstRowNum();

    Map<Integer, String> colHeaders = new HashMap<Integer, String>();
    Row row = sheet.getRow(headerRowNum);
    for (Cell cell : row) {
        int colIdx = cell.getColumnIndex();
        String value = formatter.formatCellValue(cell, evaluator);
        colHeaders.put(colIdx, value);
    }

    List<Map<String, String>> content = new ArrayList<Map<String, String>>();
    for (int r = headerRowNum + 1; r <= sheet.getLastRowNum(); r++) {
        row = sheet.getRow(r);
        if (row == null)
            row = sheet.createRow(r);
        Map<String, String> valuesToHeaders = new HashMap<String, String>();
        for (Map.Entry<Integer, String> entry : colHeaders.entrySet()) {
            int colIdx = entry.getKey();
            Cell cell = row.getCell(colIdx);
            if (cell == null)
                cell = row.createCell(colIdx);
            String cellValue = formatter.formatCellValue(cell, evaluator);
            valuesToHeaders.put(entry.getValue(), cellValue);
        }
        content.add(valuesToHeaders);
    }

    workbook.close();

    System.out.println(content);

    return content;
}

How to convert and apply it to JPA?

Answer 1

Instead of producing something so generic as a List of Maps, directly return your JPA entities instead.

So turn this:

List<Map<String, String>> content = new ArrayList<Map<String, String>>();
for (int r = headerRowNum + 1; r <= sheet.getLastRowNum(); r++) {
    row = sheet.getRow(r);
    if (row == null)
        row = sheet.createRow(r);
    Map<String, String> valuesToHeaders = new HashMap<String, String>();
    for (Map.Entry<Integer, String> entry : colHeaders.entrySet()) {
        int colIdx = entry.getKey();
        Cell cell = row.getCell(colIdx);
        if (cell == null)
            cell = row.createCell(colIdx);
        String cellValue = formatter.formatCellValue(cell, evaluator);
        valuesToHeaders.put(entry.getValue(), cellValue);
    }
    content.add(valuesToHeaders);
}

Into something more like this:

List<Branch> content = new ArrayList<>();
for (int r = headerRowNum + 1; r <= sheet.getLastRowNum(); r++) {
    row = sheet.getRow(r);
    if (row == null)
        continue; //SKIP, don't bother creating empty stuff!
    Branch branch = new Branch();
    for (Map.Entry<Integer, String> entry : colHeaders.entrySet()) {
        int colIdx = entry.getKey();
        Cell cell = row.getCell(colIdx);
        if (cell != null) {
            String cellValue = formatter.formatCellValue(cell, evaluator);
            switch(entry.getValue()) {
                 case "Description": {
                      branch.setDescription(cellValue);
                      break;
                 }
                 case "name": //example with multiple headers mapping to same field
                 case "Label": {
                      branch.setLabel(cellValue);
                       break;
                 }
            }
            //alternatively use if-else block with regex matching or some other technique to map your headers to JPA entity fields
        }
    }
    content.add(branch);
}

Using switch you can map multiple different spellings or abbreviations (if your specification allows for that sort of thing), but for ultimate flexibility you could do if/else with regular expressions. It would also be possible to do away with headers altogether and make this index based (switch on colIdx).

Of course, you could keep your code as-is and write the above into a new convert function, e.g.:

List<Branch> convert(List<Map<String,String>> content) {
    return content.stream().map(this::convert).collect(Collectors.toList());
}
Branch convert(Map<String,String> props) {
    Branch branch = new Branch();
    for(String key : props.keySet()) {
        String value = props.get(key);
        switch(key) {
             case "Description": {
                  branch.setDescription(value);
                  break;
             }
             case "name": //example with multiple headers mapping to same field
             case "Label": {
                  branch.setLabel(value);
                  break;
             }
        }

    }
    return branch;
}

That might be the tidiest option (although you'll still want to remove the empty row and cell creation code).

Answer 2

If the Map, as element of the resulting List, contains your entity attributes mapped to its values, you can Use Gson in order to create an instance of your JPA entity:

Gson gson = new Gson();
JsonElement jsonElement = gson.toJsonTree(map);
MyEntity pojo = gson.fromJson(jsonElement, MyEntity.class);