DataNucleus - Tutorial for JDO, persisting to Excel
Background

You saw in the DataNucleus JDO Tutorial how to configure a sample application for persisting to RDBMS. We took sample classes of Inventory , Book and its parent Product and showed

  1. how to configure them for persistence, namely which classes and which fields are to be persisted, and into which datastore "tables"
  2. how to actually perform the persistence and how to access objects from the datastore

In that tutorial we used an RDBMS datastore (HSQLDB for the downloadable sample). This tutorial shows how easy it is to change that to persist that very same data to a different datastore. In this case we choose Excel, but could have chosen any of the other datastores supported by DataNucleus





Persistence to Excel

What if we want to persist that same data to a spreadsheet like Excel (XLS) ? Well the changes necessary are minimal. In fact, for Part 1 of that tutorial we simply change the URL of the datastore in datanucleus.properties , like this

javax.jdo.option.ConnectionURL=excel:file:tutorial.xls

and then put datanucleus-excel and Apache POI in the CLASSPATH and run it!!!

If we move on to the second part of that tutorial we can also control the worksheet and column names used by Excel, as well as the positions in the worksheet of each column. So let's define a file package-excel.orm

<orm>
    <package name="org.datanucleus.samples.jdo.tutorial">
        <class name="Inventory" table="Inventories">
            <field name="name">
                <column name="Name" length="100"/>
            </field>
            <field name="products"/>
        </class>

        <class name="Product" table="Products">
            <inheritance strategy="complete-table"/>
            <field name="id">
                <column name="Id" position="0"/>
            </field>
            <field name="name">
                <column name="Name" position="1"/>
            </field>
            <field name="description">
                <column name="Description" position="2"/>
            </field>
            <field name="price">
                <column name="Price" position="3"/>
            </field>
        </class>

        <class name="Book" table="Books">
            <inheritance strategy="complete-table"/>
            <field name="Product.id">
                <column name="Id" position="0"/>
            </field>
            <field name="author">
                <column name="Author" position="4"/>
            </field>
            <field name="isbn">
                <column name="ISBN" position="5"/>
            </field>
            <field name="publisher">
                <column name="Publisher" position="6"/>
            </field>
        </class>
    </package>
</orm>

With Excel the only inheritance strategy supported currently is complete-table so we use that, and we will have 3 worksheets in our spreadsheet, one called Inventories , one called Products and one called Books . We also set the relative positions of the columns. Finally we set this property to pick up the mapping info

javax.jdo.option.Mapping=excel




Summary

As you can see, with very minimal changes, we can make our tutorial persist the data to an Excel spreadsheet. In fact we have not changed any model code, nor any persistence code and simply defined how it will be mapped to the spreadsheet. This flexibility and portability is not available with any other persistence tool .