Making Freeform Queries

In most cases, the provided TableQuery will be enough to allow a developer to gain effortless access to an SQL data source. However there may arise situations when a more complex query with, for example, join expressions is needed. Or perhaps you need to redefine how the writing or filtering should be done. The FreeformQuery query delegate is provided for this exact purpose. Out of the box the FreeformQuery supports read-only access to a database, but it can be extended to allow writing also.

Getting started

Getting started with the FreeformQuery may be done as shown in the following. The connection pool initialization is similar to the TableQuery example so it is omitted here. Note that the name(s) of the primary key column(s) must be provided to the FreeformQuery manually. This is required because depending on the query the result set may or may not contain data about primary key columns. In this example, there is one primary key column with a name ‘ID’.

  1. FreeformQuery query = new FreeformQuery(
  2.         "SELECT * FROM SAMPLE", pool, "ID");
  3. SQLContainer container = new SQLContainer(query);

Limitations

While this looks just as easy as with the TableQuery, do note that there are some important caveats here. Using FreeformQuery like this (without providing FreeformQueryDelegate or FreeformStatementDelegate implementation) it can only be used as a read-only window to the resultset of the query. Additionally filtering, sorting and lazy loading features will not be supported, and the row count will be fetched in quite an inefficient manner. Bearing these limitations in mind, it becomes quite obvious that the developer is in reality meant to implement the FreeformQueryDelegate or FreeformStatementDelegate interface.

The FreeformStatementDelegate interface is an extension of the FreeformQueryDelegate interface, which returns StatementHelper objects instead of pure query String#s. This enables the developer to use prepared statetemens instead of regular statements. It is highly recommended to use the [classname]#FreeformStatementDelegate in all implementations. From this chapter onwards, we will only refer to the FreeformStatementDelegate in cases where FreeformQueryDelegate could also be applied.

Creating your own FreeformStatementDelegate

To create your own delegate for FreeformQuery you must implement some or all of the methods from the FreeformStatementDelegate interface, depending on which ones your use case requires. The interface contains eight methods which are shown below. For more detailed requirements, see the JavaDoc documentation of the interface.

  1. // Read-only queries
  2. public StatementHelper getCountStatement()
  3. public StatementHelper getQueryStatement(int offset, int limit)
  4. public StatementHelper getContainsRowQueryStatement(Object... keys)
  6. // Filtering and sorting
  7. public void setFilters(List<Filter> filters)
  8. public void setFilters(List<Filter> filters,
  9.                        FilteringMode filteringMode)
  10. public void setOrderBy(List<OrderBy> orderBys)
  12. // Write support
  13. public int storeRow(Connection conn, RowItem row)
  14. public boolean removeRow(Connection conn, RowItem row)

A simple demo implementation of this interface can be found in the SQLContainer package, more specifically in the class com.vaadin.addon.sqlcontainer.demo.DemoFreeformQueryDelegate.