I honestly think you are better off taking the Demo code and making the modifications I described. It’s possible this Vaadin Demo code is more recent than what I used, which I found by searching the internet.
Having said that, I will paste the code here.
Here again is the delegate CustomersStatementDelegate. This time I include the role-based filter method. If the user is not an admin they only get back one row-- the company they work for. Those lines will not compile for you but should give you an idea how to use the filter. If you don’t need row-level security then just return null from getRoleBasedFilter (or remove the code that calls this method from AbstractStatementDelegate.)
public class CustomersStatementDelegate extends AbstractStatementDelegate implements FreeformStatementDelegate {
@Override
StringBuffer getSelectQueryClause() {
return new StringBuffer("SELECT customer_id, name, is_active FROM customers");
}
@Override
StringBuffer getCountQueryClause() {
return new StringBuffer("SELECT COUNT(customer_id) FROM customers");
}
@Override
Filter getRoleBasedFilter() {
Filter filter = null;
User me = User.getCurrentUser();
if (!me.isSystemAdmin()) {
filter = new Compare.Equal("customer_id", me.getCompanyId());
}
return filter;
}
}
Here is the main thing you wanted: the class AbstractStatementDelegate.
[code]
public abstract class AbstractStatementDelegate implements FreeformStatementDelegate {
protected List<Filter> filters;
private List<OrderBy> orderBys;
abstract StringBuffer getSelectQueryClause();
abstract StringBuffer getCountQueryClause();
abstract Filter getRoleBasedFilter();
public AbstractStatementDelegate() {
super();
}
private List<Filter> combineFilters(List<Filter> first, Filter second) {
if (second == null) {
return first;
} else if (first == null) {
return Arrays.asList(second);
}
ArrayList<Filter> union = new ArrayList<Filter>(first.size() + 1);
union.addAll(first);
union.add(second);
return union;
}
public StatementHelper getQueryStatement(int offset, int limit) throws UnsupportedOperationException {
StatementHelper sh = new StatementHelper();
StringBuffer query = getSelectQueryClause();
List<Filter> allFilters = combineFilters(filters, getRoleBasedFilter());
if (allFilters != null) {
query.append(QueryBuilder.getWhereStringForFilters(allFilters, sh));
}
query.append(getOrderByString());
if (offset != 0 || limit != 0) {
query.append(" LIMIT ").append(limit);
query.append(" OFFSET ").append(offset);
}
sh.setQueryString(query.toString());
return sh;
}
private String getOrderByString() {
StringBuffer orderBuffer = new StringBuffer("");
if (orderBys != null && !orderBys.isEmpty()) {
orderBuffer.append(" ORDER BY ");
OrderBy lastOrderBy = orderBys.get(orderBys.size() - 1);
for (OrderBy orderBy : orderBys) {
orderBuffer.append(SQLUtil.escapeSQL(orderBy.getColumn()));
if (orderBy.isAscending()) {
orderBuffer.append(" ASC");
} else {
orderBuffer.append(" DESC");
}
if (orderBy != lastOrderBy) {
orderBuffer.append(", ");
}
}
}
return orderBuffer.toString();
}
public StatementHelper getCountStatement() throws UnsupportedOperationException {
StatementHelper sh = new StatementHelper();
StringBuffer query = getCountQueryClause();
List<Filter> allFilters = combineFilters(filters, getRoleBasedFilter());
if (allFilters != null) {
query.append(QueryBuilder.getWhereStringForFilters(allFilters, sh));
}
sh.setQueryString(query.toString());
return sh;
}
public void setFilters(List<Filter> filters) throws UnsupportedOperationException {
this.filters = filters;
}
public void setFilter(Filter filter) throws UnsupportedOperationException {
this.filters = Arrays.asList(filter);
}
public void setOrderBy(List<OrderBy> orderBys) throws UnsupportedOperationException {
this.orderBys = orderBys;
}
@Override
public StatementHelper getContainsRowQueryStatement(Object... keys) throws UnsupportedOperationException {
throw new UnsupportedOperationException(""); // default WHERE clause is good enough for our simple queries
}
public int storeRow(Connection conn, RowItem row) throws SQLException {
throw new UnsupportedOperationException("List is read-only.");
}
public boolean removeRow(Connection conn, RowItem row) throws UnsupportedOperationException, SQLException {
throw new UnsupportedOperationException("List is read-only.");
}
@Deprecated
public String getQueryString(int offset, int limit) throws UnsupportedOperationException {
throw new UnsupportedOperationException("Use getQueryStatement method.");
}
@Deprecated
public String getCountQuery() throws UnsupportedOperationException {
throw new UnsupportedOperationException("Use getCountStatement method.");
}
@Deprecated
public String getContainsRowQueryString(Object... keys) throws UnsupportedOperationException {
throw new UnsupportedOperationException("Please use getContainsRowQueryStatement method.");
}
}
[/code]Lastly, here is sample code that creates a SQLContainer, given the query and connection pool. You’ll need to substitute your own connection pool obviously.
AbstractStatementDelegate stmtDelegate = new CustomersStatementDelegate();
FreeformQuery query = stmtDelegate.getSelectQueryClause(), JPAUtilities.getConnectionPool(), "customer_id");
query.setDelegate(stmtDelegate);
container = new SQLContainer(query);
OPTIONAL IMPROVEMENT:
I found it annoying to have to supply both the query string and the statement delegate when creating the FreeformQuery. They are redundant. So I hid that in yet another class:
[code]
public class DelegateQuery extends FreeformQuery {
/**
* Creates a new freeform query delegate to be used with the
* {@link SQLContainer}. Constructed with a prepared statement instead of a query
* string. Gets around the validation requiring the query string to be
* non-null.
*/
public DelegateQuery(AbstractStatementDelegate freeformQueryDelegate, JDBCConnectionPool connectionPool,
String… primaryKeyColumns) {
super(freeformQueryDelegate.getSelectQueryClause().toString(), connectionPool, primaryKeyColumns);
this.setDelegate(freeformQueryDelegate);
}
}
[/code]The sample code for creating the SQLContainer then becomes:
[code]
AbstractStatementDelegate stmtDelegate = new CustomersStatementDelegate();
FreeformQuery query = new DelegateQuery(stmtDelegate, JPAUtilities.getConnectionPool(), “customer_id”);
container = new SQLContainer(query)
[/code]It only saves one line of code, but it is arguably cleaner. (The fact that FreeformQuery requires a query string even if you want to construct it from a delegate is one more way that statement delegates feel less finished/polished than other parts of the Vaadin framework.)
Hope this helps,
Matt