Java spring boot import csv into mysql using rest api

Question

I created API using spring boot to upload the CSV file using rest controller, I want to import the data into MySQL table while upload the csv to the server.

I have mysql table called attendee as like below

id | event_id | name | email

User want to upload csv file from client app (angular 2) to server and need to import data into attendee table.

CSV will be like this

name  | email
John  , Doe

So I created REST API events/<event_id>/attendee

@PostMapping(value = "/{id}/attendee")
public ResponseEntity<String> uploadAttendee(@PathVariable Integer id,@RequestParam("file") MultipartFile file) {

    String message = "";
    try {
        storageService.store(file);
        files.add(file.getOriginalFilename());

        message = "You successfully uploaded " + file.getOriginalFilename() + "!";


       // Here I am to import into database 


        return ResponseEntity.status(HttpStatus.OK).body(message);
    } catch (Exception e) {
        message = "FAIL to upload " + file.getOriginalFilename() + "!";
        return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(message);
    }
}

Can someone so me full Example of importing into database. I'm not much experienced in java spring framework. It will useful someone explain in detail.

Answer 1

@PostMapping(value = "/devices/upload", produces = { MediaType.APPLICATION_JSON_VALUE })
    public ResponseEntity<Map<String, String>> handleFileUpload(@RequestParam("file") MultipartFile file)
            throws IOException {
        LOGGER.info("DeviceController:handleFileUpload start");
        String refId = String.valueOf(System.currentTimeMillis());
        String fileName = file.getOriginalFilename();
        Map<String, String> result = new HashMap<>();
        result.put("refId", refId);
        if (file.isEmpty()) {
            result.put("msg", "Empty File: " + fileName);
            return new ResponseEntity<>(result, HttpStatus.BAD_REQUEST);
        }

        try (InputStream inStream = file.getInputStream()) {
            List<String> lines = IOUtils.readLines(inStream, "UTF-8");
            ExecutorService service = DMResourceManager.getExecutorService();
            service.submit(() -> {

                try {
                    deviceRegisterService.register(refId, fileName, lines);
                } catch (FileNotFoundException | SQLException e) {
                    LOGGER.error("Error while calling deviceRegisterService's register method ", e);
                }
            });

            result.put("msg", "Submitted File: " + fileName);
            LOGGER.info("DeviceController:handleFileUpload end");
            return new ResponseEntity<>(result, HttpStatus.OK);
        }
    }

public void register(String refId, String originalFileName, List<String> lines)
            throws SQLException, FileNotFoundException {
        LOGGER.info("DeviceRegisterService:register");
        deviceRepository.uploadDevice(refId, originalFileName, lines);
    }

public void uploadDevice(String refId, String originalFileName, List<String> lines) throws DataAccessException {

        String sql = "INSERT INTO device_registration(Device_Id, Device_Name, Serial_Number, Device_Description, Device_Type, Manufacturer_Name, Manufactured_Date, Device_IMEI , Sim_Number , Sim_Type, Battery_Type, Hardware_Version, Base_Firmware_Version, Firmware_Filename, Config_Version, Config_Filename, Device_Mac_Address, Device_RFID_Info, RFID_Version, RFID_Filename, Status)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        for (int i = 1; i < lines.size() - 1; i++) {
            String[] arrOfStr = lines.get(i).split(",");
            for (int j = 0; j < arrOfStr.length; j = +arrOfStr.length) {
                jdbcTemplate.update(sql, UUID.randomUUID().toString(), arrOfStr[0], arrOfStr[1], arrOfStr[2],
                        arrOfStr[3], arrOfStr[4], arrOfStr[5], arrOfStr[6], arrOfStr[7], arrOfStr[8], arrOfStr[9],
                        arrOfStr[10], arrOfStr[11], arrOfStr[12], arrOfStr[13], arrOfStr[14], arrOfStr[15],
                        arrOfStr[16], arrOfStr[17], arrOfStr[18], arrOfStr[19]);
            }
        }
        LOGGER.info("reference Id generated after inserting " + originalFileName + " + file in db:" + refId);
    }


@Component
public class DMResourceManager {
    private final static Logger LOGGER = LoggerFactory.getLogger(DMResourceManager.class);

    private final static ListeningExecutorService executorService =
            MoreExecutors.listeningDecorator(Executors.newFixedThreadPool(10,
                    new ThreadFactoryBuilder().setNameFormat("dm-api-pool-%d").build()));


    private DMResourceManager() {

    }

    public static ListeningExecutorService getExecutorService() {
        return (executorService);
    }

    @PreDestroy
    public void destroy() {
        LOGGER.info("Attempting ExecutorService.shutdown()...");
        executorService.shutdown();
        try {
            if (!executorService.awaitTermination(3, TimeUnit.SECONDS)) {
                LOGGER.info("ExecutorService.shutdownNow() issued.");
                executorService.shutdownNow();
                if (!executorService.awaitTermination(3, TimeUnit.SECONDS)) {
                    LOGGER.warn("ExecutorService failed to shutdownNow() in 15 seconds.");
                }
            }
        } catch (InterruptedException ie) {
            // (Re-)Cancel if current thread also interrupted
            executorService.shutdownNow();
            // Preserve interrupt status
            Thread.currentThread().interrupt();
            LOGGER.warn("ExecutorService interrupted during shutdown.", ie);
        }
        LOGGER.info("ExecutorService.shutdown() complete.");
    }`enter code here`
}

Answer 2

You can use jackson dataformat library for csv parsing.

<dependency>
    <groupId>com.fasterxml.jackson.dataformat</groupId>
    <artifactId>jackson-dataformat-csv</artifactId>       
    <version>2.5.3</version>
</dependency>

And for code, you can do like:

CsvSchema bootstrapSchema = CsvSchema.emptySchema().withHeader();
CsvMapper mapper = new CsvMapper();
MappingIterator<YourDto> readValues = mapper.readerFor(type).with(bootstrapSchema).readValues(file);
List<YourDto> allValues= readValues.readAll();

After you have list of values, you can save it in mysql. Dto will contain all the fields available in csv, for ex.

class YourDto{
    String name;
    String email;

    public YourDto() {
        super();
    }

    public YourDto(String name, String email) {
        super();
        this.name = name;
        this.email = email;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

}

csv will look like:

name,email
John Doe, johndoe@gmail.com
Mark Page, markpage@gmail.com