Writing data to Excel from Java - the simple way

On the web you can find some blogs about how to use Apache POI to create an Excel spreadsheet. They typically contain reams of code just to add a simple table and some rows of data. It's always amazing the amount of suffering people put themselves through to perform simple tasks. An example is this blog (there are others) which demonstrates some typical calls you will make using Apache POI to create a simple spreadsheet. Whilst that may represent what Apache POI requires, a user choosing to follow this way is inflicting unnecessary suffering on themselves, really. Read on ...

Here's how we would achieve the same thing as the referenced blog using JDO and DataNucleus. Firstly lets create a class that equates to a row of data in the table.

@PersistenceCapable(table="SampleDataSheet1")
public class SampleData
{
    @PrimaryKey @Column(name="Employer Name")
    String employerName;
    @Column(name="Designation")
    String designation;
    @Column(name="Country")
    String country;

    public SampleData(String e, String d, String c)
    {
       this.employerName = e;
       this.designation = d;
       this.country = c;
    }
}

Now we define our datastore persistence properties (datanucleus.properties)

javax.jdo.PersistenceManagerFactoryClass=
    org.datanucleus.jdo.JDOPersistenceManagerFactory
javax.jdo.option.ConnectionURL=excel:file:test.xls

Finally let's do some persistence

PersistenceManagerFactory pmf = 
    JDOHelper.getPersistenceManagerFactory(“datanucleus.properties”);
PersistenceManager pm = pmf.getPersistenceManager();
Transaction tx = pm.currentTransaction();
tx.begin();
SampleData ds =
    new SampleData(“Intelligent User”, “Software Engineer”, “Bolivia”);
pm.makePersistent(ds);
tx.commit();

and this generates the same output that was shown in the blog, just that the code is now readable, shorter, and uses standardised APIs, never mind the fact that you could conceivably just persist the same data to RDBMS, XML, ODF, ODBMS, Cassandra and many more datastores with a simple change of that datastore URL. Additionally it allows you to focus on objects, which is likely why you chose Java in the first place. Why put yourself through more pain ?

[Needless to say, DataNucleus would also allow you to do the same using JPA]

TJDO has left the building

As anyone who has been using DataNucleus (and JPOX before it) for some time will know, it all started out (in 2003) as TJDO. This project was forked to provide all of the missing functionality that JDO requires and users of that technology demand (starting with application identity, and all common ORM relationship patterns). Over the last 7 years components have been rewritten one-by-one

• Table handling code was upgraded to cater for JDO2 inheritance strategies


• MetaData was rewritten to match the JDO2 definition (replacing TJDO's JDO1-style handling)


• Transaction handling was rewritten to cater for XA transactions


• Mapping code was rewritten to split into java type handling and datastore handling

DataNucleus SVN trunk (v2.2) now removes the last significant component of TJDO ... its JDOQL implementation for RDBMS (known in v2.1 as "Legacy"); it is now totally replaced by the generic-query-expression-tree approach that allows many many additional JDOQL queries to execute. There still remain some TJDO vestiges but nothing of any significance. R.I.P

JDO Typesafe Queries : Part 2 - Expressions

Continuing on from the previous post, in order to be able to express components of a query in a Java style we need to represent all fields/properties/parameters/variables as expressions. The type of the field/property/parameter/variable determines its expression type. Let's take an example

public class Person
{
    String firstName;
    String lastName;
    int age;
    Person bestFriend;
}

We need to represent these field types with expressions, so let's start with

• StringExpression - for Strings


• NumericExpression - for int, long, short, double, float, Integer, Long, Short, Double, Float, BigDecimal, BigInteger etc


• BooleanExpression - for boolean, Boolean


• ByteExpression - for byte, Byte


• CharacterExpression - for char, Character


• DateExpression - for Date-based types


• ObjectExpression - for other Object-based types where we have no specific handling

Ok, so this is all well and good and we can express firstName as a StringExpression, and similarly age is a NumericExpression. So for that we can do

QPerson person = QPerson.person;
Query<Person> q = pm.newTypesafeQuery(person);
Person bob = 
    q.filter(person.firstName.eq("Bob")).executeUnique();

To represent a persistable field (i.e a 1-1 relation, bestFriend in the above example) we have another expression type PersistableExpression.

"Q" classes

Above you see use of metamodel query classes. We refer to them as "Q" classes currently, but the naming is arbitrary for now. So what does a "Q" class look like ?

public class QPerson implements PersistableExpression 
{
    public static final QPerson person = new QPerson("person");

    public final QPerson bestFriend;
    public final NumericExpression age;
    public final StringExpression name;

    ... (implementation of other PersistableExpression methods).
}

So in simple terms, we have a public field for each of the normal fields in Person, but that are of XXXExpression types. So when a user accessed QPerson.person they get the candidate for use in a query. Then they can do "person.name" and this is a StringExpression. The NumericExpressionImpl/StringExpressionImpl are the implementations for the particular provider of this typesafe query (e.g DataNucleus). You also notice above that the bestFriend field is also a QPerson, and hence a PersistableExpression, so we can chain field access as "person.bestFriend.firstName"

Methods of field types

Obviously in JDOQL (and Java), we allow some method calls. This is represented here by adding the supported methods to StringExpression, NumericExpression etc. For example StringExpression has a method toUpperCase() to match what Java allows, so we can upgrade the query example to be

QPerson person = QPerson.person;
Query<Person> q = pm.newTypesafeQuery(person);
Person bob = 
    q.filter(person.firstName.toUpperCase().eq("BOB")).executeUnique();

There are still some more challenging areas of the API to work out, but the above is just to give a further taster and provoke comment.

Please refer to the current javadocs here (Work-in-progress).