Vaadin JPAContainer, Filterable table and related entities

In the web 2.0 framework Vaadin you have containers which provide data to be displayed in your application.
These containers are very flexible and can for example be a databasebackend, a JPA system or your own implementation.

In my past post I showed you how to implement filtering for related fields.

Vaadin also provides many data aware components, for example a table component.
The table component is very sophisticated and allows displaying huge amounts of data in the webbrowser. The table has a lazy loading system, so as only the visible parts of a table are retrieved from the backend and sent to the webbrowser.

There also exists a addon component which has built in filter and sort support.

When using the table with jpa container, then you have to use several tricks to allow filtering on related fields.

The first trick is to display the related fields in the table

For this the simplest way is to add these related fields to the main entity you display.
You don't have to store the property, it's enough for the JPA container when you have a getXXX() method.

That way it displays the additional properties in the table. You could also use this way to show calculated related fields in the table.

This could look like this:
public String getProjectInfos()
{
    return projectNr+" - "+name;
}

When you now display these in the table, you can also filter on that field.
But then, you will get a error message, telling you that the sql select did not find the field for the where condition.

For this to work, you have to use the second trick

You can build your own FilterGenerator which then builds the correct criterias for your tables and relations.

contentTable.setFilterGenerator(new MyFilterGenerator());

The filter generator has different methods, when you don't want to override them, you can just return NULL and then the default behaviour is done.

For us the interesting method is the generateFilter() method.
Here you can implement your own conditions.

If you for example wish to filter with the LIKE statement, then you can do it this way:

@Override
public Container.Filter generateFilter(Object propertyId, Object value)
{
    if ("contract".equals(propertyId))
    {
        if (value != null && value instanceof String)
        {
            return new Like("contractID", value.toString()+"%");
        }
    }
    return null;
}

To now filter on a related table, you can use the IN condition, which then builds the correct sql statements.

@Override
public Container.Filter generateFilter(Object propertyId, Object value)
{
       if ("contracts".equals(propertyId))
        {
            if (value != null && value instanceof String)
            {
                String  lsNr= (String) value;
                EntityManager em= ((MyVaadinUI)UI.getCurrent()).getEntityManager();
                TypedQuery<Shippings> tq= em.createNamedQuery("Shippings.findByLikeProjectNr", Shippings.class);
                tq.setParameter("projectNr", lsNr+"%");
                List<Shippings> rs= tq.getResultList();
                if (rs.isEmpty())
                {
                    return new IsNull("shipping");
                }
                else
                {
                    if (rs.size() > 2000)
                    {
                        Notification.show("To many entries", "\n\nMake more restrictions", Notification.Type.WARNING_MESSAGE);
                        return new IsNull("shipping");
                    }
                    else
                    {
                        return new In("shipping", rs);
                    }
                }
            }
        }
    return null;
}

So when the property contracts has some value, we filter the contracts by projectNr and use the resulting result set to specify as the IN criteria.

If you are still with me, then you probably have a compiler error when you try this code.
The reason is, that the default JPAContainer has no implementation of the IN criteria.
Unfortunally the design of the JPAContainer does not allow to expand the capabilities in that area.

Fortunally there exists a fork of the JPAContainer which just provides the required IN() clause.
You can download the sources and add them to your project.

The source can be found here: https://github.com/lelmarir/jpacontainer

You can learn how to show related / nested properties in this post.