Merging two database tables into a single Vaadin Treetable

| | August 4, 2015

TL;DR: How do I combine info from two database tables into a Vaadin Treetable (or, when Vaadin 7.5 is released, a heirarchical Grid)?


I have a Java Swing desktop application that does this currently, albeit probably very ineffeciently with ArrayLists of Java Beans that updates from the SQL Server every 30 seconds. Well, I’m now attempting to port this desktop app over to a Vaadin web app. The desktop app has login capabilities and I’ll eventually worry about doing the same for the web app, but for now, I just want to try and get the most basic part of this web app working: The Treetable. Or, hopefully soon, a heirarchical Grid.

To help illustrate what I’m aiming for, I’ll try and post an image I created that should show how the data from the two tables needs to merge into the treetable (using a partial screenshot of my existing desktop app):

Diagram

I am well aware of how to use the JOIN command in SQL and I’ve briefly read about Referencing Another SQLContainer, but I’m still in the early stages of learning Vaadin and still trying to wrap my head around SQLContainer, FreeformQuery, and how I need to implement FreeformStatementDelegate for my project. Not to mention that I’ll need to implement checkboxes for each row, as you can see in that photo, so that it updates the database when they are clicked. And a semi-checked state for the checkbox would be necessary for Jobs that have more than one OrderDetail item wherein only some of those OrderDetail items are completed. To get that working for my Java Swing program, I had to lean on an expert Java developer who already had most of the code ready, and boy, is it super-complicated!

If anyone can give me a high-level view of how to accomplish this task along with some examples, I would be indebted. I totally understand that I’m asking for a great deal here, and I’m willing to take it slow, step-by-step, as long as you are. I really want to fully understand this so I’m not just copy-pasting code without thinking.

2 Responses to “Merging two database tables into a single Vaadin Treetable”

  1. I’ll admit that I’m a beginner with vaadin myself and there may be much better ways of doing this, but here’s something I’ve mocked up which seems to work. It doesn’t do everything you need but it might be a base to start from. Most importantly, for changes to be saved back into the database you’ll need to update the SQLContainers when something in the container is changed.

    import com.vaadin.data.Item;
    import com.vaadin.data.Property;
    import com.vaadin.data.util.HierarchicalContainer;
    import com.vaadin.data.util.sqlcontainer.SQLContainer;
    
    @SuppressWarnings("serial")
    public class TwoTableHierarchicalContainer extends HierarchicalContainer {
        private SQLContainer parentContainer;
        private SQLContainer childContainer;
        private String parentPrimaryKey;
        private String childForeignKey;
    
        public TwoTableHierarchicalContainer(SQLContainer parentContainer, SQLContainer childContainer,
                String parentPrimaryKey, String childForeignKey) {
            this.parentContainer = parentContainer;
            this.childContainer = childContainer;
            this.parentPrimaryKey = parentPrimaryKey;
            this.childForeignKey = childForeignKey;
            init();
        }
    
        private void init() {
            for (Object containerPropertyIds : parentContainer.getContainerPropertyIds()) {
                addContainerProperty(containerPropertyIds, Object.class, "");
            }
            for (Object containerPropertyIds : childContainer.getContainerPropertyIds()) {
                addContainerProperty(containerPropertyIds, Object.class, "");
            }
            for (Object itemId : parentContainer.getItemIds()) {
                Item parent = parentContainer.getItem(itemId);
                Object newParentId = parent.getItemProperty(parentPrimaryKey).getValue();
                Item newParent = addItem(newParentId);
                setChildrenAllowed(newParentId, false);
                for (Object propertyId : parent.getItemPropertyIds()) {
                    @SuppressWarnings("unchecked")
                    Property<Object> newProperty = newParent.getItemProperty(propertyId);
                    newProperty.setValue(parent.getItemProperty(propertyId).getValue());
                }
            }
            for (Object itemId : childContainer.getItemIds()) {
                Item child = childContainer.getItem(itemId);
                Object newParentId = child.getItemProperty(childForeignKey).getValue();
                Object newChildId = addItem();
                Item newChild = getItem(newChildId);
                setChildrenAllowed(newParentId, true);
                setParent(newChildId, newParentId);
                setChildrenAllowed(newChildId, false);
                for (Object propertyId : child.getItemPropertyIds()) {
                    @SuppressWarnings("unchecked")
                    Property<Object> newProperty = newChild.getItemProperty(propertyId);
                    newProperty.setValue(child.getItemProperty(propertyId).getValue());
                }
            }
        }
    }
    
  2. I have never used SQLContainer so this might not be the answer you want. I just had a quick look at SQLContainer and I’m not sure if it will serve your purpose. For a TreeTable you will need a Container Implementing the Container.Hierarchical interface or the table will put a wrapper around it and you have to set the parent-children relations manually. You probably could extend SQLContainer and implement the methods from Container.Hierarchical in that class but this might get complicated.

    In your situation I think I’d go with implementing my own Container, probably extending AbstractContainer, to get the listener code for free, and implementing Hierarchical. There are quite some methods to implement, I know, and so this will need some time, but most methods are quickly implemented and you can start with the basic methods and add more interfaces (Ordered, Sortable, Indexed, Filterable, Collapsible,…) later.
    If done properly you’ll end up with with easy readable code that can be extended in the future without to much trouble and you will not depend on future versions of SQLContainer.

    Another good thing is that you’ll learn a lot about the data structures (Container, Item, Property) used in vaadin. But as I said I don’t really know SQLContainer so maybe there will be a better answer telling you that it is easy with the SQLContainer

    For the Checkbox feature you could go display the name/product property as a CheckBox. With Icon and Caption it looks almost like you want it. See http://demo.vaadin.com/sampler/#ui/data-input/other/check-box and set an Icon. The semi-checked state could be done with css.

    Hope this helps you finding the right solution for your task.

Leave a Reply