How to export huge records(50k) from database in csv file in springboot

Question

I have 50k records in my oracle db and I need to export all the data to the csv file. It is using a native query in respository file.

Here is the controller file.

@PostMapping( value=Constants.DOWNLOAD)
@ApiOperation("download file for approvals")
public ResponseEntity<String> downloadFile(
    @RequestBody @ApiParam(value = "Payload Body", required = true ) 
ApiDownload apiDownload) throws Exception {
try {
    return mergeDebtService.downloadFile(apiDownload);
} catch (Exception e) {
    e.printStackTrace();
    throw new InvalidInputException(e.getMessage());
}

}

Here is the service file.

public ResponseEntity<String> downloadFile(ApiDownload apiDownload) throws Exception {
List<Integer> listIds = new ArrayList<Integer>();
List<Object[]> listObj = new ArrayList<>();
boolean blnAll=false;

if(UserMap.getRole()!=null && UserMap.getRole().equals("ADMIN")) {
        listObj = ddmApprovalsRepository.findAllData(ddmUserMap.getProfileType());
    }else {
        listObj =ddmApprovalsRepository.findData(apiDownload.getLoginName());
    }
}


File directory = new File("/tmp/uploads");
directory.mkdir();
Files.createDirectories(Paths.get("/tmp/uploads"));

    try {
                
    if(directory.exists()) {
        FileUtils.deleteDirectory(directory);
        directory.mkdir();
        Files.createDirectories(Paths.get("/tmp/uploads"));
    }
    }catch (Exception e) {
        
        // if the file is being accessed then it can throw error. 
    }

FILE_EXISTS=true;
ExcelFileGenerator fileGenerator = new ExcelFileGenerator();
String fileName =fileGenerator.generateCSVFile(listObj, 
apiApprovalsDownload.getLoginName(),"/tmp/uploads",cbControlParams,blnAll);
    
File file = new File("/tmp/uploads/"+fileName);
return ResponseEntity.status(200).header("filename", fileName).body("downloaded 
successfully");
}

It is taking almost 2 min to respond through the Api, how can I reduce the response timing. As it is giving 504 gateway timeout error .

504 indicates that a subsequent call is timing out, which I would assume is your findAllData or findData method. If you post that code someone could help you. — Magnus, Mar 07 '23 at 21:29
@Magnus fillAllData and findData contains a native query at repository file. I cant change that. If anything can be done at service file and controller file..Please do suggest — Ashish kumar, Mar 08 '23 at 02:47

score 0 · Answer 1 · answered Mar 08 '23 at 08:27

First step is to step away from list based processing. Currently you create a pretty big list of entities before even starting to create the file. Instead use a Stream as the result type, so writing out the data can interleave with reading the data.

The next step is to get rid of JPA and entities. Instead use a SQL statement that returns the data as close to the target format as you can manage. Run that query using a JdbcTemplate and a RowCallbackHandler

If it is still not returning fast enough you should skip the step of writing the file to disc before returning it and instead start streaming it to the client directly. Downloading large files via Spring MVC should help with that.

How to export huge records(50k) from database in csv file in springboot

1 Answers1