How to read data from csv file and store it in the database ? Spring Boot

Question

For example I have an entity of Users consisting of username,phonenumber and address. I want to read all these fields from a csv file and store it in the respective table in the database?

Can any one Help me by describing how to do that? Or is there any documentation on how to do that?

Answer 1

I assume that you want the user to upload the file from some UI. Depending on the exact way in which you build UI, you might:

• Send a multipart HTTP POST request (mime type = multipart/form-data; see What should a Multipart HTTP request with multiple files look like?)
• Send a simple POST request with the body directly containing the file contents.

Either of the two can be fairly easily solved using Spring.

Assuming that we have the following entity:

@Data
@Entity
public class User {
    @Id
    private String username;
    private String phoneNumber;
    private String address;
}

And we define a Spring Data repository for accessing the database:

public interface UserRepository extends JpaRepository<User, String> {

}

For the CSV deserialization, I would propose using Jackson. Spring Boot already comes with Jackson, but we need to add a data format extension for CSV in your pom:

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

This way, we can create a simple utility method that knows to read a CSV for a given POJO class:

public class CsvUtils {
    private static final CsvMapper mapper = new CsvMapper();
    public static <T> List<T> read(Class<T> clazz, InputStream stream) throws IOException {
        CsvSchema schema = mapper.schemaFor(clazz).withHeader().withColumnReordering(true);
        ObjectReader reader = mapper.readerFor(clazz).with(schema);
        return reader.<T>readValues(stream).readAll();
    }
}

And then we create a simple Rest Controller for handling the upload(s):

@RestController
@RequiredArgsConstructor
public class UserController {
    private final UserRepository repository;

    @PostMapping(value = "/upload", consumes = "text/csv")
    public void uploadSimple(@RequestBody InputStream body) {
        repository.saveAll(CsvUtils.read(User.class, body));
    }

    @PostMapping(value = "/upload", consumes = "multipart/form-data")
    public void uploadMultipart(@RequestParam("file") MultipartFile file) {
        repository.saveAll(CsvUtils.read(User.class, file.getInputStream()));
    }
}

In case you also need some HTML for doing the upload, the following snippet is a minimal working example:

<form action="/upload" method="post" enctype="multipart/form-data">
    <input type="file" name="file" id="file" />
    <input type="submit" name="submit" value="Submit" />
</form>

---

Later edit: If you want to also validate the incoming data, first annotate your entity class attribute with javax.validation constraints. For example:

@Data
@Entity
class User {
    @Id
    @Email
    @NotEmpty
    private String username;
    @Pattern(regexp = "[0-9 ()-]{4,12}")
    private String phoneNumber;
    private String address;
}

Then you can chose where do perform the actual validation call:

1. Service level. This is what I personally recommend in this case, as it is fairly easy to setup and would perform the validations early enough. For this you introduce a simple @Service class between the controller and the repository.

@Service
@Validated
@RequiredArgsConstructor
class UserService {
    private final UserRepository repository;
    public void saveAll(@Valid List<User> users) {
        repository.saveAll(users);
    }
}

You would then use this service class instead of the repository inside the controller class.

2. Repository level: here you don't actually need to do anything. If you annotate your entity classes with validation constraints, Hibernate would automatically call the validation in a pre-insert listener (BeanValidationEventListener).

3. Controller level. This is trickier to setup. Move the CSV deserialization in a custom HttpMessageConverter. You should also add this converter to the FormHttpMessageConverter (such that it can use it to deserialize a part of the multi-part request). You could then theoretically just declare the @Valid List<User> as inputs for your controller methods and Spring would automatically call the message converter based on the mime type and then call the validator. See Add JSON message converter for multipart/form-data for an example.

Lastly you can always manually call the validation whenever you want: Manually call Spring Annotation Validation.

Answer 2

You can achieve that easily with openCSV. For a known POJO User, you just map the CSV columns(headers in your case) to corresponding fields in the POJO.

Just add the following to you dependency, check for the latest version for your application though.

<dependency> 
<groupId>com.opencsv</groupId> 
<artifactId>opencsv</artifactId> 
<version>4.1</version> 
</dependency> 

this link guides you through https://www.geeksforgeeks.org/mapping-csv-to-javabeans-using-opencsv/