How to use import/export for related entities in SpringBot

There are scenarios when a single Using Import/Export with Data Table action is required to manage multiple related entities. As these scenarios vary, there is no “one size fits all” and each solution should be applied on a case by case basis.

Single CSV

A single CSV can often contain data from more than one entity. For example, the following CSV contains data from two related entities:

FIRST_NAME,LAST_NAME,EMAIL,UNIT,ADDRESS_LINE_ONE,ADDRESS_LINE_TWO
"Joe", "Blogs", "joe.blogs@example.com", "1", "123 Example Street", ""

“first name”, “last name”, and “email”, are from the User entity where as “unit”, “address line 1”, and “address line 2” are from the Address entity.

Where these two entities are related as follows:

To update the our import/export on our User entity to support importing data like this we will need to make the following changes.

1. Add transient attributes to our User entity (UserEntity.java) to support binding of these new attributes.

    // % protected region % [Add any additional class fields here] on begin
    @Transient
    @CsvBindByName(column = "UNIT")
    private int unit;
   	
    @Transient
    @CsvBindByName(column = "ADDRESS_LINE_ONE")
    private String addressLine1;
   	
    @Transient
    @CsvBindByName(column = "ADDRESS_LINE_ONE")
    private String addressLine2;
    // % protected region % [Add any additional class fields here] end
   

   We utilise the @Transient annotation here to denote a field that is not mapped to the database but is used for data transfer only. See Annotation Type Transient for more details.

2. Update our getExampleCsvHeader in the UserEntity.java

    public static String getExampleCsvHeader() {
       
     // % protected region % [Modify the headers in the CSV file here] on begin
     return "FIRST_NAME,LAST_NAME,EMAIL,UNIT,ADDRESS_LINE_ONE,ADDRESS_LINE_TWO";
     // % protected region % [Modify the headers in the CSV file here] end
    }
   

3. Add custom logic to the protected region Add any additional logic before completing the CSV Import here in the UserController.java csvUpload method to process persisting the Address entity and setting its relation back to the User entity.
4. Finally, add our custom logic to the protected region Add any additional logic before executing the Export Excluding endpoint here in the method, exportFishExcludingIds and the protection region in Add any additional logic before executing the Export endpoint here in the method exportFish within our UserController. This custom logic will set our transient attributes added in step one to the correct values.

Multi sheet XLSX file

One of the primary limitations of CSV is that it is a single flat file, so there is a limit on how much information can be imported.

One solution for this is to customise the format of the import/export functionality to support a more complex format such as XLSX.

The same model shown in the section above could be represented in this like this:

To explore how this may be achieved please see Modifying the Import/Export features in Springbot.

Using this method does have its caveats though:

• Mapping from one entity to the next is ambiguous, this will have to be defined by you. I.e. Do you use some form of internal id’s to map the entry from one sheet to the next?
• Each additional entity that you add increases the complexity of the import. For example, depending on the constraints of your model, certain entities must exist before others so the order that they are persisted is incredibly important.

Alternative means

If import/export is being used for data migration, alternative means could also be considered based on your particular circumstances.

For example:

• Leveraging the API provided by our Codebots, we can create a migration script to import or export any data we wish,
• Direct database migration, the creation of a one off migration script that directly interacts with the database could simplify the process.
• Creation of a migration service, this can run as a scheduled task, at startup or be triggered by some event. The benefit of this solution is, like using the import/export functionality and the API, we can utilise the constraints provided by our application for validation and verification of our data integrity.