One of the most useful tools in my collection is the objectify-led library – http://www.objectify.be/objectify-led/ (full disclosure: I wrote it). It allows you to annotate fields in your classes with @Property and then bind information to them automatically. It also allows you to retrieve @Property-annotated fields along with any other annotations that are set on them.

Adding a @Property annotation to Play model entities allows us to add new functionality with minimum effort. In this example, I’m going to load an Excel spreadsheet from the client via Play, parse it into objectify-led property contexts, validate, bind and persist.

First, we define a HTML form to upload the file from. See here for further details.

Orders/upload.html

   
     <form action="@{Orders.uploadOrders()}" method="POST"
                enctype="multipart/form-data">
        <input type="file" id="ordersFile" name="ordersFile" />
        <input type="submit" value="Upload" />
    </form>

Our Order model class is annotated with JPA and objectify-led annotations.

models.Order

    
    @Entity
    public class Order extends play.db.jpa.Model
    {
        @Column(nullable = false)
        @be.objectify.led.Property("Order code")
        public String orderCode;

        @Column(length = 255)
        @be.objectify.led.Property("Description")
        public String description;

        @be.objectify.led.Property("Quantity")
        public int quantity;
    }

The Orders controller just passes the file and entity type to the ExcelBinder class, and receives a list of Order instances with bound fields back.

controllers.Orders

    
    public static void uploadOrders(File ordersFile)
    {
        List<Order> orders = ExcelBinder.bind(ordersFile, Order.class);
        for (Order order : orders)
        {
            order.save();
        }
    }

Each row in the spreadsheet represents one Order instance, with the exception of the first row – we treat this row as the column headers. The trick here is to have the column header match the name given to the @Property on the corresponding field. So, in our spreadsheet we have three columns: “Order code”, “Description” and “Quantity”.

The ExcelBinder class uses an arbitrary library (in most cases JExcelAPI works well) to extract each row from the spreadsheet and

  • Get the column headers from the first row
  • Create a be.objectify.led.PropertyContext for each subsequent row

The implementation of the PropertyContext is very simple – it just wraps a Map:
utils.MapPropertyContext

    public class MapPropertyContext implements PropertyContext
    {
        private Map<String, String> data = new HashMap<String, String>();

        public void setValue(String name,
                                    String value)
        {
            data.put(name, value);
        }

        public String getValue(String name)
        {
            return data.get(name);
        }
    }

I’m using Workbook, Sheet and Cell based on JExcelApi. If you use a different library, just retool as necessary. Also, I’m not handling exceptions here just to keep the code a bit more readable – if you use this, you’ll need to add in a few try/catch blocks.
utils.ExcelBinder

    
    public class ExcelBinder
    {
        public static <T> List bind(File file,
                                                  Class<T> modelType)
        {
            jxl.Workbook workbook = jxl.Workbook.getWorkbook(file);
            jxl.Sheet sheet = workbook.getSheet(0);
            List<String> headerNames = getHeaderNames(sheet);
            
            List<T> objects = new ArrayList<T>();
            // iterate over each non-header row and make it into an object
            for (int i = 1; i < sheet.getRows(); i++)
            {
                PropertyContext propertyContext = getPropertyContext(sheet,
                                                                                           i,
                                                                                           headerNames);
                PropertySetter propertySetter = new PropertySetter(propertyContext);
                T t = modelType.newInstance();
                propertySetter.process(t);
                objects.add(t);
            }
            return objects;
        }

        private static List<String> getHeaderNames(Sheet sheet)
        {
            List<String> headerNames = new ArrayList<String>();
            Cell[] headerCells = sheet.getRow(0);
            for (Cell cell : headerCells)
            {
                headerNames.add(cell.getContents());
            }

            return headerNames;
        }

        private static PropertyContext getPropertyContext(Sheet sheet,
                                                                            int rowIndex,
                                                                            List<String> headerNames)
        {
            Cell[] cells = sheet.getRow(rowIndex);
            MapPropertyContext propertyContext = new MapPropertyContext();
            for (int i = 0; i < headerNames.size(); i++)
            {
                propertyContext.setValue(headerNames.get(i),
                                                   cells[i].getContents());
            }

            return propertyContext;
        }
    }

So, quite a lot of code but the important thing to understand is that if you had various other model types, such as e.g. Customer, ShoppingList, etc that were also annotated with @Property you can reuse all the code above without changing a single line!
For example,

    List<Customer> customers = ExcelBinder.bind(file, Customer.class);

will take a spreadsheet and bind it to customer instances. The only things that are non-reusable are upload.html and the uploadFile method of Orders. Once MapPropertyContext and ExcelBinder have been added to your codebase, you can add Excel-model binding with a single line as above!

Also, this example used Excel but you could also support CSV, XML, YAML – any arbitrary data source in fact. Once your model has been annotated with @Property, all you need to do is load the data into a PropertyContext and the binding will happen.

You can download a complete working example here: uploads

7 thoughts on “play & objectify-led – binding uploaded data to application models the lazy way, part 1

  1. I think that where it says

    PropertyContext propertyContext = getPropertyContext(sheet,
    i,
    headerNames);
    PropertySetter propertySetter = new PropertySetter(context);

    should say

    PropertyContext propertyContext = getPropertyContext(sheet,
    i,
    headerNames);
    PropertySetter propertySetter = new PropertySetter(propertyContext);

    otherwise I don’t know where “context” comes from… am I right???

    saludos

    sas

    BTW: really great article, I’ll keep an eye on your blog…

  2. I get this error when I try to upload a file..

    Execution exception
    StringIndexOutOfBoundsException occured : String index out of range: 68

    jxl.Workbook workbook = jxl.Workbook.getWorkbook(file);

    Can you also provide an example of the file to be uploaded.

    Great Article.

  3. I’ve added an example implementation. Unzip, cd into the directory and “play run”.

    Example implementation with excel files

    In the root directory are two excel files – customers.xls and orders.xls. Upload customers.xls first and you can see how the Customer field of Order is also bound automatically when you then upload orders.xls

  4. Hi Steve,

    Your uploads example is excellent, is there a way to export that data to excel maybe after enhancing a cell.

    Thank you.

    1. Sure – you can use the same library as in the other example (JExcel) or Apache POI or something similar. If you use be.objectify.led.PropertyDigger to check your target object for property names, you can then generate the columns with names and typed cells. The be.objectify.led.Property annotation now has a “meta” property you can use to supply hints for more involved generation (but be careful you don’t pollute your model with these!)

Leave a Reply

Your email address will not be published. Required fields are marked *