How to import entities and its dependent objects via excel

Question

We have a simple data model reflecting the following...

1. User contains many @OneToMany addresses
2. User contains many @OneToMany phones
3. User contains many @OneToMany emails
4. User belongs to a @Organization via @ManyToOne

We would like the customers to capture all user information in a excel / csv sheet and provide it to our upload tool. We provide a simple user interface to capture the order of fields for the import. We didn't face any issues when we supported imports for just User (i.e. we let the end user define the order of imported fields and used the same in the excel)

We are not sure what would be the right strategy to support @OneToMany and @ManyToOne fields. The problems are as follows.

1. Not sure how to flatten the information if a User has multiple addresses into a flat line (e.g. User1 has 2 addresses, 1 phone, 3 emails and User 2 has 1 address, 1 phone ...)
2. How to handle @ManyToOne annotations (you can't expect the end user to provide a primary key - Integer id in this case), do you expect them to provide a Organization name here assuming post import the user can modify this information if necessary
3. How to handle delta values during an import operation (say 10 additional users?)
4. Do you have a single import file for Organization, Users, UserAdress and all the dependent objects?

Are there any tools / APIs which abstract some of these needs for both importing / exporting?

Answer 1

You can flatten the structure like you would do in a database i.e. read a table for users, a table for addresses and so on.

You could read the csv or excel to an in-memory-db and use hibernate to build business objects for you.

Answer 2

With DataNucleus you can map your class(es) that equates to the Excel data using JPA annotations, and load it up into your application. This can then be persisted into any other types of datastore that you require.

Answer 3

Are there any tools / APIs which abstract some of these needs for both importing / exporting?

There is DbUnit which now also supports an excel based dataset.