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 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'.
FreeformQuery query = new FreeformQuery( "SELECT * FROM SAMPLE", pool, "ID"); SQLContainer container = new SQLContainer(query);
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 FreeformStatementDelegate
in
all implementations. From this chapter onwards, we will only refer to the
FreeformStatementDelegate
in cases where
FreeformQueryDelegate
could also be applied.
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.
// Read-only queries public StatementHelper getCountStatement() public StatementHelper getQueryStatement(int offset, int limit) public StatementHelper getContainsRowQueryStatement(Object... keys) // Filtering and sorting public void setFilters(List<Filter> filters) public void setFilters(List<Filter> filters, FilteringMode filteringMode) public void setOrderBy(List<OrderBy> orderBys) // Write support public int storeRow(Connection conn, RowItem row) 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
.