High performance: exporting data to Excel in CSV format with Grails

Note: for the following “solution” to be viable you don’t have to care about formatting. If you need formatting in your exported file then you will need a special library in which case I can’t help :)

There were already several Grails posts available with tips on how to get data from Grails to Excel. While the solutions were great, they weren’t very well suited to large amounts of data. Most solutions used something like JExcelApi. Even though the library is great, it doesn’t perform very well when writing large amounts of data (which is mentioned on the JExcelApi site). Creating new java objects is slow. With JExcelApi you have to create a new object for each cell (you can’t change the coordinates of a cell after creating the object so you can’t reuse an object). Since I had to export at least 30.000 rows, maybe more, each with 10 columns of data…..well….that’s a lot of objects. That was only one of the problems though. I was using Hibernate criteria for getting the data that was supposed to go into the Excel file. Usually you do something like below (this is not the actual statement, just an example):

personCriteria = Person.createCriteria()
personList = personCriteria.list {
    if (params.name) {
        ilike("name", "%${params.name}%")
    }
    if (params.dateOfBirth) {
        eq("dateOfBirth", sdf.parse(params.dateOfBirth))
    }
    order(params.sort ?: "name", params.order ?: "asc")
}

This would return a list of people. In my case I had to get 30.000 objects from the database and query 10 properties of each object. In short……after I had my first CSV export up and running I could have put a message on the site saying “this is going to take a while, you better get a cup of coffee”.  Even on a very fast machine this took ages. This was unacceptable. I have since made 2 changes to the code which sped up the export tremendously.

1) Use Hibernate projections

In the previous criteria it is basically doing something like “SELECT * FROM …..” and putting the results into objects (oversimplified but you probably get what I mean :)). Hibernate projections give you the possibility to do things like “SELECT count(*) FROM….” or “SELECT name, dateOfBirth FROM ….”. The found rows are returned in a list, not in Person objects. So what I did is specify exactly which properties I wanted to be returned:

SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy")

personCriteria = Person.createCriteria()
personList = personCriteria.list {
    projections {
        property("name")
        property("dateOfBirth")
        property("phonenumber")
        property(".....")
        property(".....")
        property(".....")
        property(".....")
    }
    if (params.name) {
        ilike("name", "%${params.name}%")
    }
    if (params.dateOfBirth) {
        eq("dateOfBirth", sdf.parse(params.dateOfBirth))
    }
    order(params.sort ?: "name", params.order ?: "asc")
}

2) Use a CSV file instead of Excel or HTML

I tried using the JExcelApi to get a real Excel file and a HTML builder since Excel can read HTML tables as well. Since I had serious performance issues with both of them I decided to try a simple CSV format which turned out to work great. Creating a CSV file from the list returned by the optimized Hibernate criteria is incredibly easy in Grails. Here is the code including the part how it returns the CSV file to the browser:

response.setHeader("Content-disposition", "attachment; filename=person-list.csv")
response.contentType = "application/vnd.ms-excel" 

def outs = response.outputStream 

def cols = [:] 

outs << "Name;Date of birth;Phonenumber;....;.....;.....;.....\n"
personList.each() {

    outs << it.join(";")
    outs << "\n"
}
outs.flush()
outs.close() 

That’s it :). The code will probably a little more complex since you have to check for empty values (otherwise your file will contain lots of “null” text) and you’ll need some formatting in case of dates and so on but this is the basic idea. How does it perform? Well…..it went from taking 1 to 2 minutes down to about 1 second…..for 30.000 rows. I’d say that’s fast enough for most people.

Really enjoying Grails (Multiple datasources without GORM example)

Lately I’ve been trying out Grails. It’s a terrific framework for creating web applications and written in Groovy/Java. It’s similar in a lot of ways to Ruby on Rails (written in Ruby). Since I already work a lot with Java and I just prefer Groovy’s syntax to that of Ruby I decided on using Grails (this is a very personal decision, both are terrific and I urge you to try one of them if you are into creating web applications!).

Grails uses Hibernate and Spring as underlying technologies. While those were already great to use in Java,I really hated the amount of xml configuration required. Grails makes things soooo much easier.

Here is an example of how I used Grails’ Spring DSL and auto-wiring amongst other things. I wanted to create a monitoring application that had to extract information from one of several datasources based on the choice of the user. So all datasources needed to be available in the application and I should be able to select which one to use dynamically. (Btw, there are no domain objects associated with those datasources so I didn’t need GORM)

Here are some code snippets:

First up is resources.groovy (which is in the conf/spring directory). In this file you can declare extra Spring configuration that’s needed using the BeanBuilder DSL.

Look ma! No XML!

import org.apache.commons.dbcp.BasicDataSource
// Place your Spring DSL code here
beans = {
 dataSourceTest(BasicDataSource) {
 	driverClassName = "oracle.jdbc.driver.OracleDriver"
 	url = "jdbc:oracle:thin:@localhost:1521:TEST"
 	username = "someone"
 	password = "something"
 }
 dataSourceProduction(BasicDataSource) {
 	driverClassName = "oracle.jdbc.driver.OracleDriver"
 		url = "jdbc:oracle:thin:@localhost:1521:PROD"
 	username = "someone"
 	password = "something"
 }
}

So, that was easy, and no XML involved! So how do we get to those datasources in the application? Well, for this task I created a Grails service. Services in Grails are by default singletons. That’s why there’s no ‘static’ keyword to be found which is often used in java. Also, when you look at the code you’ll notice that I define the variables for the datasources but I never load them. This is another great feature of Grails. It auto-wires the datasources to the variables because they have the same name, so only declaring the variables is enough. That’s it.Here is DatabaseService.groovy:

class DatabaseService {

    def TEST = "Test"
    def PRODUCTION = "Production"

    def dataSourceTest
    def dataSourceProduction

    boolean transactional = true

    def getDataSource(dbEnv) {
        switch(dbEnv) {
            case TEST:
                return dataSourceTest
            case PRODUCTION:
                return dataSourceProduction
        }
    }
}

Great, now we have a way to get to the datasources. Now to actually use them. In the next code block another example of the Grails auto-wiring is displayed, in this case in a controller. Grails also automatically wires services to variables if they are declared with the same name as the service (without the first character capitalized). We’ll use the Groovy DSL for SQL here as well. It makes it incredibly easy to retrieve some data from the datasource.Here is the source for SomeController.groovy:

import groovy.sql.Sql
import grails.converters.XML

class CronacleController {

    def databaseService

    def index = { redirect(action:getSomeXML,params:params) }

    def getSomeXML = {
        def sql = Sql.newInstance(databaseService.getDataSource(databaseService.TEST))
        render sql.rows("select column from table") as XML
    }
}

Well, this concludes my first post on Grails/Groovy. I’m REALLY enjoying using both. It’s so much fun trying new things out without all the bloat usually surrounding it!Btw, I’m still not sure if I’m happy with the code for the service so don’t hesitate to comment :). I’m still in the early stages of learning Grails/Groovy so I’m sure things could be optimized.