Row-Bean Wiki

CSV reader writer - bean mapping - easy bean extraction from CSV file

Brought to you by: ogodineau

Row Bean 0.9.0

ROW-Bean API

Row-Bean is a CSV-Bean JAVA API . Row-Bean provides a mechanism to map csv file content to java beans and revers. For each use, a XML description must describe the wished mapping.

Current versions

0.9.0 - 06-07-2009

Download other releases

Purpose

There are all kinds of APIs providing mappings, among them in particular the xml-Object mapping.
Persistance solutions exist too (as serialization). So why focus on a mapping CSV-Bean and particularly why focus on CSV format while XML is a great way, practical and adopted, to exchange?

Even if XML is very popular, it requires a certain minimum amount of practical and technical knowledge that non-programmers do not have.
From my own experience, I have had to import database from files customers gave me. These files have been either created manually or from a software export, both came from customers. This means that I've had no control over the format and structure of these files. Customers, not programmers, using the tools at their disposal, ie the office suite. Thus, CSV and Excel, easy to produce and read were the most commonly used and I've had to do with. A mapping xml- object API wouldn't have been useful here since i couldn't impose format and file structure.

From this experience, the idea of this API came.

We provide a way to build mapping CSV object ie a way to read objects from files which describe them and a way to write in files the description of these objects.

We restrict the area of our purpose to the following case :
*CSV file map a class type bean
* Each line describes in the same way an instance of this class and must correspond a string to each bean property.

"bean" must be understood as follows:
* A bean must have a constructor without arguments
* The properties of a bean must be accessed via setter and getter methods following the usual naming conventions.

Documentation

[[Row-Bean API| French Documentation]]

Code Examples

CSV import and export

We propose here to illustrate the use of the API with an example concerning the import and export of persons.
A person is limited to the following characteristics:

Name
Firstname
Company
Address

This gives the Person class as in the following extract :

 public class Person {
    private String name;
    private String firstName;
    private String companyName;
    private Address address;
    private Date lastUpdate;

    ...

    public String toString() {
        return "name:" + (name != null ? name : "") + ", firstname:" + (firstName != null ? firstName : "")
                + ", company:" + (companyName != null ? companyName : "") + ", address:"
                + (address != null ? address.toString() : "") + ", last update:"
                + (lastUpdate != null ? lastUpdate : "");
    }
 }

Of course, this is a bean. We add to this characteristics a lastUpdate field.
The address field is also a bean class like following :

 public class Address {
    /**
     * Number Extension
     * @author Olivier Godineau
     *
     */
    public static enum Extension {
       A, B, C, D;
    }

    private Extension extension;
    private int numero;
    private String streetName;
    private String postalCode;
    private String city;

 ...

    public String toString() {
        return numero + (extension != null ? extension.toString() : "") + ", " + (streetName != null ? streetName : "")
                + " " + (city != null ? city : "") + " " + (postalCode != null ? postalCode : "");
    }
 }

The presentations are done, let's go to our import and export examples.

Import from CSV file

We have a CSV file from which to extract persons who are described:
name,firstname,companyName,address ext,address num, address street,address postal code, address city,last Update,last Update hour
Holmes,Skerlock,consulting detective,B,221, Baker street, NW1 6XE,London,17-10-1904,17:34
Lupin,Arsène,Gentleman Cambrioleur Cie,,15,rue Guy de Maupassant,76790,Etretat,23-01-1937,8:51

To do this, we use the class BeanReader. The following sample write on the console persons extracted from the file:

 /**
  * For sample. Imports from CSV file some persons.
  * @author Olivier Godineau
  * @see Person
  */
 public class ImportPerson {
    private BeanReader<Person> oReader;

    public ImportPerson(File csvFile, File configFile) throws IOException, LoadException {
        IReader reader = new CSVReader(csvFile, "UTF-8",true);

        this.oReader = new BeanReader<Person>(configFile, reader);
    }

    public void display() {
        try {
            System.out.println("New created Persons from CSV file :");
            while (oReader.hasNext()) {
                try {
            Person person = oReader.next();
            System.out.println(person.toString());
        } catch (ParseException ex) {
            System.out.println("Something goes wrong on parsing");
            ex.printStackTrace();
        }
            }
        } finally {
            try {
                oReader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    public static void main(String[] args) {
        ImportPerson importPerson;
        try {
            importPerson = new ImportPerson(new File(ImportPerson.class.getResource("PersontoImport.csv").getFile()),
                    new File(ImportPerson.class.getResource("readerCfg.xml").getFile()));
            importPerson.display();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (LoadException e) {
            e.printStackTrace();
        }
    }
 }
~~~~~

Instantiate class BeanReader <person> requires two elements:

* A IReader instance able to extract each line of the file. In our case, we need a CSV reader, so we use the class CSVReader.
* An XML file describing how to be interpreted each line with the aim to produce a bean Person.


The import is very simple and use a loop:

    try {
       while (oReader.hasNext()) {
          try {
    Person person = oReader.next();
            ...
      } catch (ParseException ex) {
    ...
     }
       }
    } finally {
        try {
            oReader.close();
        } catch (IOException e) {
           ...
        }
    }

To do this possible, we need to make a XML mapping description :

<bean-reader class="" schemalocation="http://www.example.org/row-bean bean-row.xsd">
</bean-reader>

Let's comment  this configuration:

* C1: The root of the pattern of reading is the <code>bean-reader</code> XML node. <code>Class</code> argument should give the class of the bean class Person.
* C2: <code>reader-bean</code> node allows to describe the properties to be mapped with the property nodes describe within. This element has a required attribute called <code>name</code> which gives the bean field name. This element also has a <code>class</code> attribute optional. By default, the API instantiates the property from the class the bean declared (in our example, this is the class String). But if the class to instantiate is not this  declared(for example, the declared class is abstract or an interface) then it is necessary to specify this attribute class.
* C3: For each property, you must define from what string instantiate this person field. For this, we use a <code>getter</code> node.

There are several getter types. The simplest is the <code>simple</code> element that identifies a column on each line. The <code>rank</code> attribute identifies the column to select it. Here, the first column of each line must match the field called name. Note that the first column has the rank 0 and not 1.

* C4: For each property, we instantiate the property from a string. It is the role of the <code>parser</code> element. In most cases it is not necessary to specify a parser, since the API attempts to associate one by default (for the primitive, the enums and classes with a constructor taking an argument of type String).

In the case of property of type <code>Date</code>, you must use the <code>date</code> element. This element requires an attribute <code>format</code>. Its value must be consistent with the description which is made in the documentation of the Java class <code>java.text.SimpleDateFormat</code>. Here, the proposed <code>format</code> will transform the string "17-10-1904 17:34" in a date.

* C5: For lastUpdate Person field, its String representation is based on two columns "Last Update" and "Update last hour". Use simple getter is not enough. We need to concate columns, the appropriate getter is the <code>assemble</code> XML element.
* C6: Sometimes the strings extracted from the lines don't match exactly the field format. Here, the expected format requires a space between the two strings to concate. For this use, we need a <code>filter</code> XML Element.

A filter allows you to convert a string into another. Here, we use a decorator filter  which allows to prefix or suffix the given string.

* C7: If a field is a bean we need to set its properties. Here,we encounter this case with the person address. We use the same means to define its properties to those of class Person.

###Export to CSV file###

We propose here the opposite mechanism: Write a CSV file with objects of type Person. To do this we use the class <code>BeanWriter</code>.
Our code example instantiates  two person and writes them to to a file:

/*
* For sample. Exports into CSV file some persons.
*
* @author Olivier Godineau
*
* @see Person
/
public class ExportPerson {

private BeanWriter<person> oWriter = null;</person>

public ExportPerson(File csvFile, File configFile) throws IOException, LoadException {
    IWriter writer = new CSVWriter(csvFile, "UTF-8");
    oWriter = new BeanWriter<Person>(configFile, writer);
}

public void display() {
    try {
        System.out.println("I fill your file ...");
        oWriter.writeHeaders();

        Person person = new Person();
        person.setCompanyName("consulting detective");
        person.setFirstName("Skerlock");
        person.setLastUpdate(new Date());
        person.setName("Holmes");
        Address address = new Address();
        address.setCity("London");
        address.setExtension(Extension.B);
        address.setNumero(221);
        address.setPostalCode("NW1 6XE");
        address.setStreetName("Baker street");
        person.setAddress(address);

        try {
    oWriter.write(person);
        } catch (PropertyException ex) {
    ex.printStackTrace();
        }

        person = new Person();
        person.setCompanyName("Gentleman Cambrioleur Cie");
        person.setFirstName("Arsène");
        person.setLastUpdate(new Date());
        person.setName("Lupin");
        address = new Address();
        address.setCity("Etretat");
        address.setExtension(null);
        address.setNumero(15);
        address.setPostalCode("76790");
        address.setStreetName("rue Guy de Maupassant");
        person.setAddress(address);

       try {
    oWriter.write(person);
       } catch (PropertyException ex) {
    ex.printStackTrace();
       }

        System.out.println("Ok it's done");
    } finally {
        try {
            oWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

public static void main(String[] args) {
if (args == null || args[0] == null || "".equals(args[0].trim()))
System.out.println("Usage : " + ExportPerson.class + " exportFile");

   ExportPerson exportPerson;
   try {
       exportPerson = new ExportPerson(new File(args[0]), new File(ExportPerson.class.getResource("writerCfg.xml")
               .getFile()));
       exportPerson.display();
   } catch (IOException e) {
       e.printStackTrace();
   } catch (LoadException e) {
       e.printStackTrace();
   }

}

public ExportPerson(File csvFile, File configFile) throws IOException, LoadException {
IWriter writer = new CSVWriter(csvFile, "UTF-8");
oWriter = new BeanWriter<person>(configFile, writer);
}</person>

}

Instantiate class <code>BeanWriter<person></code> requires two elements:

* A IWriter able to write each person you pass it to a file. In our case, to write into a csv file we need the <code>CSVWriter</code> class.
* An XML file which describe the Person properties to write.

The tricky part is the construction of the XML file:

<bean-writer class="" schemalocation="http://www.example.org/row-bean bean-row.xsd">
</bean-writer>

Let's comment this configuration:

* C1: The root of all writing configuration is the <code>bean-writer</code> XML Node. <code>class</CODE> argument is required to give the class to write.
* C2: The <code>bean-writer</code> allows to describe all of the columns a line should content with <code>column</code> nodes. On each line, columns wil be written in the order in which they are described.

A <code>column</code> element is  used to describe the properties that it should represent. The <code>name</code> attribute of a <code>column</code> is just for interest as a header name to the column. If the attribute is blank, a default name will be created based on the number of the column in the line. The <code>name</code> attribute of each <code>property</code> element is required to set the name of the corresponding bean property.

* C3: When a property is a bean that we want to represent its properties, then it is possible to nest  <code>properties</code> and <code>property</code>.This is the case here with the address field where we want to represent its properties in different columns.

* C4: API writes a non primitive field by invoking the <code>toString()</code> method thereof. In the case of a <code>Date</code> field, <code>toString()</code> method does not allow us to represent its value like we want. We need a specific <code>formatter</code>. A date formatter has an attribute that give the pattern to be used to write. Its value must be consistent with the description which is made in the documentation of the Java class <code>java.text.SimpleDateFormat</code>.

XSD Schema
-----------
The XSD schema describing the XML structure to describe a <code>BeanReader</code> or <code>BeanWriter</code> is not available on the site.

Despite of this, you can get the XSD schema attached to yourAPI version with a simple command line :
~~~~~~~
 java -jar <row-bean.jar> where <row-bean.jar> is the name of your  jar distribution.
~~~~~~~

Running in a console indicates where your copy is located (normally in the directory where you launched the command):

~ $ java -jar row-bean.jar

Copy of bean-row.xsd ...

Done

See your copy / home / olivier / bean-row.xsd

Note that you must declare in your XML descriptor the target namespace defined in this schema :

xmlns="http://www.example.org/row-bean"

Customization
-------------
In your mapping to best meet your needs, you can submit your own implementations for the following elements :getter, filter, formatter or parser.

Declare your own implementation with custom element as in the excerpt below: 

~~~~~~~
 <parser>
   <custom class="olg.csv.bean.MyCustomParser">
     <property name="parametre0">
       <value>valeur1</value>
     </property>
     <property name="parametre1">
       <value>valeur2</value>
     </property>
   </custom>
 </parser>

Define an implementation of a getter element consists in extend the olg.csv.bean.AbstractGetter class and implement its doGet (Row line) method.
Define an implementation of a filtre element consists in extend the olg.csv.bean.AbstractStringFilter class and implement its doFiltre(String value) method.
Define an implementation of a parser element consists in extend the olg.csv.bean.parser.AbstractParser class and implement its parse(String s) method.
Define an implementation of a formatter element consists in extend the olg.csv.bean.Formatter class and implement its toString(T t) method.

Limitations

Currently, this API don't propose reader and writer for Excel files.
It isnt possible to map properties like arrays or Collections. Some developments should arrive...

It supports mapping with bean properties like:

primitive type (or corresponding class)
bean type
enum type
class with constructor taking a string argument.

Currently, the API does not require any other API function.

This API works fine with a JRE 1.5 or higher.