Convert List> 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?

Can you share the structure of the class you want to map these `Maps inside the `List`? — Luiggi Mendoza, Nov 21 '19 at 14:56
By the way, your issue is about plain Java. Spring, Spring Boot and all those other things are not associated with your issue. — Luiggi Mendoza, Nov 21 '19 at 14:57
Okay sir @LuiggiMendoza noted thank you this the link https://stackoverflow.com/questions/58974646/save-the-data-to-table-and-save-it-to-database-even-if-empty-rows-in-excel-java — Bryan Caliwag, Nov 21 '19 at 15:02
You should add all required code directly to this question, not make readers jump back and forth to figure out which bits of code from some other question are required to understand and respond to this one. — Thomas Timbul, Nov 21 '19 at 15:23

score 1 · Answer 1 · answered Nov 21 '19 at 15:06

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);

Thomas Timbul · Accepted Answer · 2019-11-22T13:29:18.317

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).

I implement your codes Sir @ThomasTimbul but I have an error it says Stackoverflow it is overloading data? https://stackoverflow.com/questions/58986263/java-lang-stackoverflowerror-excel-in-spring-boot-jpa — Bryan Caliwag, Nov 22 '19 at 01:44
@BryanCaliwag there was a typo of one too many `>` at `Branch convert(Map> props) {`, which I have edited. If it still complains about overloading after that, perhaps try renaming one of the convert methods. — Thomas Timbul, Nov 22 '19 at 13:30

Convert List> to Object in Java Spring boot jpa

2 Answers2