Data pivot (CSV)

Code summary

@Autowired
private CsvForJpa csvForJpa;
...
csvForJpa.insert("csv", Entity.class);

See CsvForJpa.java

Description

Maintaining data for the tests may be a pain. The actual solutions are:

• external data stored as a dump: hard to update, to manipulate data and to handle the generated identifier
• fully coded in the tests with some em.persist(..) in the JUnit: the more data, the more amount of code, no global view of the data. On the other side, you gain a strongly typed dataset.

In addition of these solution, come the CSV format:

• CSV is common, compact (more than JSON), and easy to handle: filtering, ordering, etc. with you favorite Excel like editor
• Mostly all database editors support import or export in CSV format
• CSV parsing and generation are fast and simple and do not involve very much libraries

The CSV format is not mandatory for your data, there is a built-in integration.

Sample

Test classe

public class MyTest {
  @Autowired
  private CsvForJpa csvForJpa;
  public @Test void test() {
    csvForJpa.insert("csv", PublicProfile.class, Person.class);
    // Data is now in my base
  }
}

Entities

public @Entity @Getter @Setter class PublicProfile {
  private @Id String id;
  private Date since;
}
public @Entity @Getter @Setter class Person extends AbstractPersistable<Integer> {
  private String firstName;
  private String lastName;
  private Integer age;
  private @ManyToOne PublicProfile profile;
}

CSV files public-profile.csv, publicprofile.csv are accepted

jdoe;2018-04-01
jdoe2;12/08/2012
henry;2018-01-04T09:23:45.017+01:00
alpha

person.csv

John;Doe;21;jdoe
Jane;Doe;21;jdoe2
Marc;;23
Henry;;henry

This will read the file csv/person.csv and will persist one entry per row in MyEntity JPA entity. Then will do the same for csv/public-profile.csv.

More complexe usage

Many options are available: separator, encoding and headers.

As a CSV file contains only data of one entity, it could be difficult to handle the relation between two entities. In the previous sample, the relationship was conventional with id property, and the order was following the filds declaration order. For a more complexe usage, header must be explicitly defined

Entities

public @Entity @Getter @Setter class PublicProfile AbstractPersistable<Integer> {
  private String displayName;
}
public @Entity @Getter @Setter class PrivateProfile {
  private @Id String login;
  private String mail;
}
public @Entity @Getter @Setter class Person extends AbstractPersistable<Integer> {
  private String firstName;
  private String lastName;
  private Integer age;
  private @ManyToOne PrivateProfile publicProfile;
  private @ManyToOne PublicProfile privateProfile;
  private @ManyToOne PublicProfile friendProfile;
}

CSV files public-profile.csv

displayName
jdoe
jdoe2

private-profile.csv

login;mail
login1;[email protected]
login2;[email protected]

person.csv

firstName;lastName;age;publicProfile.displayName;privateProfile.mail;friendProfile.id!
John;Doe;21;jdoe;[email protected];1
Jane;Doe;21;jdoe2;[email protected];2

Code sheets

• Order matters:
  • Inside the CSV, a row can reference itself and or a previous one
  • Since an entity can refer to another one, the class parameters must follow this relationship
• Date formats: yyyy-MM-dd'T'HH:mm:ssZ, yyyy-MM-dd'T'HH:mm:ss.SSSXXX, yyyy/MM/dd, dd/MM/yyyy and optional HH:mm, HH:mm:ss and / or - separator variants.
• Header format: jpaProperty(.joinProperty)?!?
  • jpaProperty: Raw value, date, enumeration value (case insensitive),...
  • jpaProperty.joinProperty: Join property. Implies a SELECT * FROM JoinEntity WHERE joinProperty = :value
  • jpaProperty.generatedId!: Join to a generated @Id property/ Implies a SELECT * FROM JoinEntity and then select the n-th raw. These way, you can join the table by its generated incremented identifier.
• CsvForJpa#insert(..) insert the entries without deleting the previous ones.
• CsvForJpa#reset(..) will delete all previous JPA entries in the reversed classes order, and then will insert the JPA entries from the CSV.
• CsvForJpa#cleanup(..) will delete all previous JPA entries in the reversed classes order.
• CsvForJpa#toJpa(..) will only return the corresponding JPA entries.